1-- 2-- ALTER TABLE ADD COLUMN DEFAULT test 3-- 4 5SET search_path = fast_default; 6CREATE SCHEMA fast_default; 7CREATE TABLE m(id OID); 8INSERT INTO m VALUES (NULL::OID); 9 10CREATE FUNCTION set(tabname name) RETURNS VOID 11AS $$ 12BEGIN 13 UPDATE m 14 SET id = (SELECT c.relfilenode 15 FROM pg_class AS c, pg_namespace AS s 16 WHERE c.relname = tabname 17 AND c.relnamespace = s.oid 18 AND s.nspname = 'fast_default'); 19END; 20$$ LANGUAGE 'plpgsql'; 21 22CREATE FUNCTION comp() RETURNS TEXT 23AS $$ 24BEGIN 25 RETURN (SELECT CASE 26 WHEN m.id = c.relfilenode THEN 'Unchanged' 27 ELSE 'Rewritten' 28 END 29 FROM m, pg_class AS c, pg_namespace AS s 30 WHERE c.relname = 't' 31 AND c.relnamespace = s.oid 32 AND s.nspname = 'fast_default'); 33END; 34$$ LANGUAGE 'plpgsql'; 35 36CREATE FUNCTION log_rewrite() RETURNS event_trigger 37LANGUAGE plpgsql as 38$func$ 39 40declare 41 this_schema text; 42begin 43 select into this_schema relnamespace::regnamespace::text 44 from pg_class 45 where oid = pg_event_trigger_table_rewrite_oid(); 46 if this_schema = 'fast_default' 47 then 48 RAISE NOTICE 'rewriting table % for reason %', 49 pg_event_trigger_table_rewrite_oid()::regclass, 50 pg_event_trigger_table_rewrite_reason(); 51 end if; 52end; 53$func$; 54 55CREATE TABLE has_volatile AS 56SELECT * FROM generate_series(1,10) id; 57 58 59CREATE EVENT TRIGGER has_volatile_rewrite 60 ON table_rewrite 61 EXECUTE PROCEDURE log_rewrite(); 62 63-- only the last of these should trigger a rewrite 64ALTER TABLE has_volatile ADD col1 int; 65ALTER TABLE has_volatile ADD col2 int DEFAULT 1; 66ALTER TABLE has_volatile ADD col3 timestamptz DEFAULT current_timestamp; 67ALTER TABLE has_volatile ADD col4 int DEFAULT (random() * 10000)::int; 68 69 70 71-- Test a large sample of different datatypes 72CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT 1); 73 74SELECT set('t'); 75 76INSERT INTO T VALUES (1), (2); 77 78ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT 'hello', 79 ALTER COLUMN c_int SET DEFAULT 2; 80 81INSERT INTO T VALUES (3), (4); 82 83 84ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'world', 85 ALTER COLUMN c_bpchar SET DEFAULT 'dog'; 86 87INSERT INTO T VALUES (5), (6); 88 89ALTER TABLE T ADD COLUMN c_date DATE DEFAULT '2016-06-02', 90 ALTER COLUMN c_text SET DEFAULT 'cat'; 91 92INSERT INTO T VALUES (7), (8); 93 94ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP DEFAULT '2016-09-01 12:00:00', 95 ADD COLUMN c_timestamp_null TIMESTAMP, 96 ALTER COLUMN c_date SET DEFAULT '2010-01-01'; 97 98INSERT INTO T VALUES (9), (10); 99 100ALTER TABLE T ADD COLUMN c_array TEXT[] 101 DEFAULT '{"This", "is", "the", "real", "world"}', 102 ALTER COLUMN c_timestamp SET DEFAULT '1970-12-31 11:12:13', 103 ALTER COLUMN c_timestamp_null SET DEFAULT '2016-09-29 12:00:00'; 104 105INSERT INTO T VALUES (11), (12); 106 107ALTER TABLE T ADD COLUMN c_small SMALLINT DEFAULT -5, 108 ADD COLUMN c_small_null SMALLINT, 109 ALTER COLUMN c_array 110 SET DEFAULT '{"This", "is", "no", "fantasy"}'; 111 112INSERT INTO T VALUES (13), (14); 113 114ALTER TABLE T ADD COLUMN c_big BIGINT DEFAULT 180000000000018, 115 ALTER COLUMN c_small SET DEFAULT 9, 116 ALTER COLUMN c_small_null SET DEFAULT 13; 117 118INSERT INTO T VALUES (15), (16); 119 120ALTER TABLE T ADD COLUMN c_num NUMERIC DEFAULT 1.00000000001, 121 ALTER COLUMN c_big SET DEFAULT -9999999999999999; 122 123INSERT INTO T VALUES (17), (18); 124 125ALTER TABLE T ADD COLUMN c_time TIME DEFAULT '12:00:00', 126 ALTER COLUMN c_num SET DEFAULT 2.000000000000002; 127 128INSERT INTO T VALUES (19), (20); 129 130ALTER TABLE T ADD COLUMN c_interval INTERVAL DEFAULT '1 day', 131 ALTER COLUMN c_time SET DEFAULT '23:59:59'; 132 133INSERT INTO T VALUES (21), (22); 134 135ALTER TABLE T ADD COLUMN c_hugetext TEXT DEFAULT repeat('abcdefg',1000), 136 ALTER COLUMN c_interval SET DEFAULT '3 hours'; 137 138INSERT INTO T VALUES (23), (24); 139 140ALTER TABLE T ALTER COLUMN c_interval DROP DEFAULT, 141 ALTER COLUMN c_hugetext SET DEFAULT repeat('poiuyt', 1000); 142 143INSERT INTO T VALUES (25), (26); 144 145ALTER TABLE T ALTER COLUMN c_bpchar DROP DEFAULT, 146 ALTER COLUMN c_date DROP DEFAULT, 147 ALTER COLUMN c_text DROP DEFAULT, 148 ALTER COLUMN c_timestamp DROP DEFAULT, 149 ALTER COLUMN c_array DROP DEFAULT, 150 ALTER COLUMN c_small DROP DEFAULT, 151 ALTER COLUMN c_big DROP DEFAULT, 152 ALTER COLUMN c_num DROP DEFAULT, 153 ALTER COLUMN c_time DROP DEFAULT, 154 ALTER COLUMN c_hugetext DROP DEFAULT; 155 156INSERT INTO T VALUES (27), (28); 157 158SELECT pk, c_int, c_bpchar, c_text, c_date, c_timestamp, 159 c_timestamp_null, c_array, c_small, c_small_null, 160 c_big, c_num, c_time, c_interval, 161 c_hugetext = repeat('abcdefg',1000) as c_hugetext_origdef, 162 c_hugetext = repeat('poiuyt', 1000) as c_hugetext_newdef 163FROM T ORDER BY pk; 164 165SELECT comp(); 166 167DROP TABLE T; 168 169-- Test expressions in the defaults 170CREATE OR REPLACE FUNCTION foo(a INT) RETURNS TEXT AS $$ 171DECLARE res TEXT := ''; 172 i INT; 173BEGIN 174 i := 0; 175 WHILE (i < a) LOOP 176 res := res || chr(ascii('a') + i); 177 i := i + 1; 178 END LOOP; 179 RETURN res; 180END; $$ LANGUAGE PLPGSQL STABLE; 181 182CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT LENGTH(foo(6))); 183 184SELECT set('t'); 185 186INSERT INTO T VALUES (1), (2); 187 188ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT foo(4), 189 ALTER COLUMN c_int SET DEFAULT LENGTH(foo(8)); 190 191INSERT INTO T VALUES (3), (4); 192 193ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT foo(6), 194 ALTER COLUMN c_bpchar SET DEFAULT foo(3); 195 196INSERT INTO T VALUES (5), (6); 197 198ALTER TABLE T ADD COLUMN c_date DATE 199 DEFAULT '2016-06-02'::DATE + LENGTH(foo(10)), 200 ALTER COLUMN c_text SET DEFAULT foo(12); 201 202INSERT INTO T VALUES (7), (8); 203 204ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP 205 DEFAULT '2016-09-01'::DATE + LENGTH(foo(10)), 206 ALTER COLUMN c_date 207 SET DEFAULT '2010-01-01'::DATE - LENGTH(foo(4)); 208 209INSERT INTO T VALUES (9), (10); 210 211ALTER TABLE T ADD COLUMN c_array TEXT[] 212 DEFAULT ('{"This", "is", "' || foo(4) || 213 '","the", "real", "world"}')::TEXT[], 214 ALTER COLUMN c_timestamp 215 SET DEFAULT '1970-12-31'::DATE + LENGTH(foo(30)); 216 217INSERT INTO T VALUES (11), (12); 218 219ALTER TABLE T ALTER COLUMN c_int DROP DEFAULT, 220 ALTER COLUMN c_array 221 SET DEFAULT ('{"This", "is", "' || foo(1) || 222 '", "fantasy"}')::text[]; 223 224INSERT INTO T VALUES (13), (14); 225 226ALTER TABLE T ALTER COLUMN c_bpchar DROP DEFAULT, 227 ALTER COLUMN c_date DROP DEFAULT, 228 ALTER COLUMN c_text DROP DEFAULT, 229 ALTER COLUMN c_timestamp DROP DEFAULT, 230 ALTER COLUMN c_array DROP DEFAULT; 231 232INSERT INTO T VALUES (15), (16); 233 234SELECT * FROM T; 235 236SELECT comp(); 237 238DROP TABLE T; 239 240DROP FUNCTION foo(INT); 241 242-- Fall back to full rewrite for volatile expressions 243CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); 244 245INSERT INTO T VALUES (1); 246 247SELECT set('t'); 248 249-- now() is stable, because it returns the transaction timestamp 250ALTER TABLE T ADD COLUMN c1 TIMESTAMP DEFAULT now(); 251 252SELECT comp(); 253 254-- clock_timestamp() is volatile 255ALTER TABLE T ADD COLUMN c2 TIMESTAMP DEFAULT clock_timestamp(); 256 257SELECT comp(); 258 259DROP TABLE T; 260 261-- Simple querie 262CREATE TABLE T (pk INT NOT NULL PRIMARY KEY); 263 264SELECT set('t'); 265 266INSERT INTO T SELECT * FROM generate_series(1, 10) a; 267 268ALTER TABLE T ADD COLUMN c_bigint BIGINT NOT NULL DEFAULT -1; 269 270INSERT INTO T SELECT b, b - 10 FROM generate_series(11, 20) a(b); 271 272ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'hello'; 273 274INSERT INTO T SELECT b, b - 10, (b + 10)::text FROM generate_series(21, 30) a(b); 275 276-- WHERE clause 277SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1; 278 279EXPLAIN (VERBOSE TRUE, COSTS FALSE) 280SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1; 281 282SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1; 283 284EXPLAIN (VERBOSE TRUE, COSTS FALSE) SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1; 285 286 287-- COALESCE 288SELECT COALESCE(c_bigint, pk), COALESCE(c_text, pk::text) 289FROM T 290ORDER BY pk LIMIT 10; 291 292-- Aggregate function 293SELECT SUM(c_bigint), MAX(c_text COLLATE "C" ), MIN(c_text COLLATE "C") FROM T; 294 295-- ORDER BY 296SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10; 297 298EXPLAIN (VERBOSE TRUE, COSTS FALSE) 299SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10; 300 301-- LIMIT 302SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10; 303 304EXPLAIN (VERBOSE TRUE, COSTS FALSE) 305SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10; 306 307-- DELETE with RETURNING 308DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *; 309EXPLAIN (VERBOSE TRUE, COSTS FALSE) 310DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *; 311 312-- UPDATE 313UPDATE T SET c_text = '"' || c_text || '"' WHERE pk < 10; 314SELECT * FROM T WHERE c_text LIKE '"%"' ORDER BY PK; 315 316SELECT comp(); 317 318DROP TABLE T; 319 320 321-- Combine with other DDL 322CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); 323 324SELECT set('t'); 325 326INSERT INTO T VALUES (1), (2); 327 328ALTER TABLE T ADD COLUMN c_int INT NOT NULL DEFAULT -1; 329 330INSERT INTO T VALUES (3), (4); 331 332ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'Hello'; 333 334INSERT INTO T VALUES (5), (6); 335 336ALTER TABLE T ALTER COLUMN c_text SET DEFAULT 'world', 337 ALTER COLUMN c_int SET DEFAULT 1; 338 339INSERT INTO T VALUES (7), (8); 340 341SELECT * FROM T ORDER BY pk; 342 343-- Add an index 344CREATE INDEX i ON T(c_int, c_text); 345 346SELECT c_text FROM T WHERE c_int = -1; 347 348SELECT comp(); 349 350-- query to exercise expand_tuple function 351CREATE TABLE t1 AS 352SELECT 1::int AS a , 2::int AS b 353FROM generate_series(1,20) q; 354 355ALTER TABLE t1 ADD COLUMN c text; 356 357SELECT a, 358 stddev(cast((SELECT sum(1) FROM generate_series(1,20) x) AS float4)) 359 OVER (PARTITION BY a,b,c ORDER BY b) 360 AS z 361FROM t1; 362 363DROP TABLE T; 364 365-- test that we account for missing columns without defaults correctly 366-- in expand_tuple, and that rows are correctly expanded for triggers 367 368CREATE FUNCTION test_trigger() 369RETURNS trigger 370LANGUAGE plpgsql 371AS $$ 372 373begin 374 raise notice 'old tuple: %', to_json(OLD)::text; 375 if TG_OP = 'DELETE' 376 then 377 return OLD; 378 else 379 return NEW; 380 end if; 381end; 382 383$$; 384 385-- 2 new columns, both have defaults 386CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 387INSERT INTO t (a,b,c) VALUES (1,2,3); 388ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; 389ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; 390CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 391SELECT * FROM t; 392UPDATE t SET y = 2; 393SELECT * FROM t; 394DROP TABLE t; 395 396-- 2 new columns, first has default 397CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 398INSERT INTO t (a,b,c) VALUES (1,2,3); 399ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; 400ALTER TABLE t ADD COLUMN y int; 401CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 402SELECT * FROM t; 403UPDATE t SET y = 2; 404SELECT * FROM t; 405DROP TABLE t; 406 407-- 2 new columns, second has default 408CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 409INSERT INTO t (a,b,c) VALUES (1,2,3); 410ALTER TABLE t ADD COLUMN x int; 411ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; 412CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 413SELECT * FROM t; 414UPDATE t SET y = 2; 415SELECT * FROM t; 416DROP TABLE t; 417 418-- 2 new columns, neither has default 419CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 420INSERT INTO t (a,b,c) VALUES (1,2,3); 421ALTER TABLE t ADD COLUMN x int; 422ALTER TABLE t ADD COLUMN y int; 423CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 424SELECT * FROM t; 425UPDATE t SET y = 2; 426SELECT * FROM t; 427DROP TABLE t; 428 429-- same as last 4 tests but here the last original column has a NULL value 430-- 2 new columns, both have defaults 431CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 432INSERT INTO t (a,b,c) VALUES (1,2,NULL); 433ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; 434ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; 435CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 436SELECT * FROM t; 437UPDATE t SET y = 2; 438SELECT * FROM t; 439DROP TABLE t; 440 441-- 2 new columns, first has default 442CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 443INSERT INTO t (a,b,c) VALUES (1,2,NULL); 444ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; 445ALTER TABLE t ADD COLUMN y int; 446CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 447SELECT * FROM t; 448UPDATE t SET y = 2; 449SELECT * FROM t; 450DROP TABLE t; 451 452-- 2 new columns, second has default 453CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 454INSERT INTO t (a,b,c) VALUES (1,2,NULL); 455ALTER TABLE t ADD COLUMN x int; 456ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; 457CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 458SELECT * FROM t; 459UPDATE t SET y = 2; 460SELECT * FROM t; 461DROP TABLE t; 462 463-- 2 new columns, neither has default 464CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 465INSERT INTO t (a,b,c) VALUES (1,2,NULL); 466ALTER TABLE t ADD COLUMN x int; 467ALTER TABLE t ADD COLUMN y int; 468CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 469SELECT * FROM t; 470UPDATE t SET y = 2; 471SELECT * FROM t; 472DROP TABLE t; 473 474-- make sure expanded tuple has correct self pointer 475-- it will be required by the RI trigger doing the cascading delete 476 477CREATE TABLE leader (a int PRIMARY KEY, b int); 478CREATE TABLE follower (a int REFERENCES leader ON DELETE CASCADE, b int); 479INSERT INTO leader VALUES (1, 1), (2, 2); 480ALTER TABLE leader ADD c int; 481ALTER TABLE leader DROP c; 482DELETE FROM leader; 483 484-- check that ALTER TABLE ... ALTER TYPE does the right thing 485 486CREATE TABLE vtype( a integer); 487INSERT INTO vtype VALUES (1); 488ALTER TABLE vtype ADD COLUMN b DOUBLE PRECISION DEFAULT 0.2; 489ALTER TABLE vtype ADD COLUMN c BOOLEAN DEFAULT true; 490SELECT * FROM vtype; 491ALTER TABLE vtype 492 ALTER b TYPE text USING b::text, 493 ALTER c TYPE text USING c::text; 494SELECT * FROM vtype; 495 496-- also check the case that doesn't rewrite the table 497 498CREATE TABLE vtype2 (a int); 499INSERT INTO vtype2 VALUES (1); 500ALTER TABLE vtype2 ADD COLUMN b varchar(10) DEFAULT 'xxx'; 501ALTER TABLE vtype2 ALTER COLUMN b SET DEFAULT 'yyy'; 502INSERT INTO vtype2 VALUES (2); 503 504ALTER TABLE vtype2 ALTER COLUMN b TYPE varchar(20) USING b::varchar(20); 505SELECT * FROM vtype2; 506 507 508-- Ensure that defaults are checked when evaluating whether HOT update 509-- is possible, this was broken for a while: 510-- https://postgr.es/m/20190202133521.ylauh3ckqa7colzj%40alap3.anarazel.de 511BEGIN; 512CREATE TABLE t(); 513INSERT INTO t DEFAULT VALUES; 514ALTER TABLE t ADD COLUMN a int DEFAULT 1; 515CREATE INDEX ON t(a); 516-- set column with a default 1 to NULL, due to a bug that wasn't 517-- noticed has heap_getattr buggily returned NULL for default columns 518UPDATE t SET a = NULL; 519 520-- verify that index and non-index scans show the same result 521SET LOCAL enable_seqscan = true; 522SELECT * FROM t WHERE a IS NULL; 523SET LOCAL enable_seqscan = false; 524SELECT * FROM t WHERE a IS NULL; 525ROLLBACK; 526 527-- verify that a default set on a non-plain table doesn't set a missing 528-- value on the attribute 529CREATE FOREIGN DATA WRAPPER dummy; 530CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; 531CREATE FOREIGN TABLE ft1 (c1 integer NOT NULL) SERVER s0; 532ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer DEFAULT 0; 533ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10); 534SELECT count(*) 535 FROM pg_attribute 536 WHERE attrelid = 'ft1'::regclass AND 537 (attmissingval IS NOT NULL OR atthasmissing); 538 539-- cleanup 540DROP FOREIGN TABLE ft1; 541DROP SERVER s0; 542DROP FOREIGN DATA WRAPPER dummy; 543DROP TABLE vtype; 544DROP TABLE vtype2; 545DROP TABLE follower; 546DROP TABLE leader; 547DROP FUNCTION test_trigger(); 548DROP TABLE t1; 549DROP FUNCTION set(name); 550DROP FUNCTION comp(); 551DROP TABLE m; 552DROP TABLE has_volatile; 553DROP EVENT TRIGGER has_volatile_rewrite; 554DROP FUNCTION log_rewrite; 555DROP SCHEMA fast_default; 556 557-- Leave a table with an active fast default in place, for pg_upgrade testing 558set search_path = public; 559create table has_fast_default(f1 int); 560insert into has_fast_default values(1); 561alter table has_fast_default add column f2 int default 42; 562table has_fast_default; 563