1-- 2-- ALTER TABLE ADD COLUMN DEFAULT test 3-- 4SET search_path = fast_default; 5CREATE SCHEMA fast_default; 6CREATE TABLE m(id OID); 7INSERT INTO m VALUES (NULL::OID); 8CREATE FUNCTION set(tabname name) RETURNS VOID 9AS $$ 10BEGIN 11 UPDATE m 12 SET id = (SELECT c.relfilenode 13 FROM pg_class AS c, pg_namespace AS s 14 WHERE c.relname = tabname 15 AND c.relnamespace = s.oid 16 AND s.nspname = 'fast_default'); 17END; 18$$ LANGUAGE 'plpgsql'; 19CREATE FUNCTION comp() RETURNS TEXT 20AS $$ 21BEGIN 22 RETURN (SELECT CASE 23 WHEN m.id = c.relfilenode THEN 'Unchanged' 24 ELSE 'Rewritten' 25 END 26 FROM m, pg_class AS c, pg_namespace AS s 27 WHERE c.relname = 't' 28 AND c.relnamespace = s.oid 29 AND s.nspname = 'fast_default'); 30END; 31$$ LANGUAGE 'plpgsql'; 32CREATE FUNCTION log_rewrite() RETURNS event_trigger 33LANGUAGE plpgsql as 34$func$ 35 36declare 37 this_schema text; 38begin 39 select into this_schema relnamespace::regnamespace::text 40 from pg_class 41 where oid = pg_event_trigger_table_rewrite_oid(); 42 if this_schema = 'fast_default' 43 then 44 RAISE NOTICE 'rewriting table % for reason %', 45 pg_event_trigger_table_rewrite_oid()::regclass, 46 pg_event_trigger_table_rewrite_reason(); 47 end if; 48end; 49$func$; 50CREATE TABLE has_volatile AS 51SELECT * FROM generate_series(1,10) id; 52CREATE EVENT TRIGGER has_volatile_rewrite 53 ON table_rewrite 54 EXECUTE PROCEDURE log_rewrite(); 55-- only the last of these should trigger a rewrite 56ALTER TABLE has_volatile ADD col1 int; 57ALTER TABLE has_volatile ADD col2 int DEFAULT 1; 58ALTER TABLE has_volatile ADD col3 timestamptz DEFAULT current_timestamp; 59ALTER TABLE has_volatile ADD col4 int DEFAULT (random() * 10000)::int; 60NOTICE: rewriting table has_volatile for reason 2 61-- Test a large sample of different datatypes 62CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT 1); 63SELECT set('t'); 64 set 65----- 66 67(1 row) 68 69INSERT INTO T VALUES (1), (2); 70ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT 'hello', 71 ALTER COLUMN c_int SET DEFAULT 2; 72INSERT INTO T VALUES (3), (4); 73ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'world', 74 ALTER COLUMN c_bpchar SET DEFAULT 'dog'; 75INSERT INTO T VALUES (5), (6); 76ALTER TABLE T ADD COLUMN c_date DATE DEFAULT '2016-06-02', 77 ALTER COLUMN c_text SET DEFAULT 'cat'; 78INSERT INTO T VALUES (7), (8); 79ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP DEFAULT '2016-09-01 12:00:00', 80 ADD COLUMN c_timestamp_null TIMESTAMP, 81 ALTER COLUMN c_date SET DEFAULT '2010-01-01'; 82INSERT INTO T VALUES (9), (10); 83ALTER TABLE T ADD COLUMN c_array TEXT[] 84 DEFAULT '{"This", "is", "the", "real", "world"}', 85 ALTER COLUMN c_timestamp SET DEFAULT '1970-12-31 11:12:13', 86 ALTER COLUMN c_timestamp_null SET DEFAULT '2016-09-29 12:00:00'; 87INSERT INTO T VALUES (11), (12); 88ALTER TABLE T ADD COLUMN c_small SMALLINT DEFAULT -5, 89 ADD COLUMN c_small_null SMALLINT, 90 ALTER COLUMN c_array 91 SET DEFAULT '{"This", "is", "no", "fantasy"}'; 92INSERT INTO T VALUES (13), (14); 93ALTER TABLE T ADD COLUMN c_big BIGINT DEFAULT 180000000000018, 94 ALTER COLUMN c_small SET DEFAULT 9, 95 ALTER COLUMN c_small_null SET DEFAULT 13; 96INSERT INTO T VALUES (15), (16); 97ALTER TABLE T ADD COLUMN c_num NUMERIC DEFAULT 1.00000000001, 98 ALTER COLUMN c_big SET DEFAULT -9999999999999999; 99INSERT INTO T VALUES (17), (18); 100ALTER TABLE T ADD COLUMN c_time TIME DEFAULT '12:00:00', 101 ALTER COLUMN c_num SET DEFAULT 2.000000000000002; 102INSERT INTO T VALUES (19), (20); 103ALTER TABLE T ADD COLUMN c_interval INTERVAL DEFAULT '1 day', 104 ALTER COLUMN c_time SET DEFAULT '23:59:59'; 105INSERT INTO T VALUES (21), (22); 106ALTER TABLE T ADD COLUMN c_hugetext TEXT DEFAULT repeat('abcdefg',1000), 107 ALTER COLUMN c_interval SET DEFAULT '3 hours'; 108INSERT INTO T VALUES (23), (24); 109ALTER TABLE T ALTER COLUMN c_interval DROP DEFAULT, 110 ALTER COLUMN c_hugetext SET DEFAULT repeat('poiuyt', 1000); 111INSERT INTO T VALUES (25), (26); 112ALTER TABLE T ALTER COLUMN c_bpchar DROP DEFAULT, 113 ALTER COLUMN c_date DROP DEFAULT, 114 ALTER COLUMN c_text DROP DEFAULT, 115 ALTER COLUMN c_timestamp DROP DEFAULT, 116 ALTER COLUMN c_array DROP DEFAULT, 117 ALTER COLUMN c_small DROP DEFAULT, 118 ALTER COLUMN c_big DROP DEFAULT, 119 ALTER COLUMN c_num DROP DEFAULT, 120 ALTER COLUMN c_time DROP DEFAULT, 121 ALTER COLUMN c_hugetext DROP DEFAULT; 122INSERT INTO T VALUES (27), (28); 123SELECT pk, c_int, c_bpchar, c_text, c_date, c_timestamp, 124 c_timestamp_null, c_array, c_small, c_small_null, 125 c_big, c_num, c_time, c_interval, 126 c_hugetext = repeat('abcdefg',1000) as c_hugetext_origdef, 127 c_hugetext = repeat('poiuyt', 1000) as c_hugetext_newdef 128FROM T ORDER BY pk; 129 pk | c_int | c_bpchar | c_text | c_date | c_timestamp | c_timestamp_null | c_array | c_small | c_small_null | c_big | c_num | c_time | c_interval | c_hugetext_origdef | c_hugetext_newdef 130----+-------+----------+--------+------------+--------------------------+--------------------------+--------------------------+---------+--------------+-------------------+-------------------+----------+------------+--------------------+------------------- 131 1 | 1 | hello | world | 06-02-2016 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 132 2 | 1 | hello | world | 06-02-2016 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 133 3 | 2 | hello | world | 06-02-2016 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 134 4 | 2 | hello | world | 06-02-2016 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 135 5 | 2 | dog | world | 06-02-2016 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 136 6 | 2 | dog | world | 06-02-2016 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 137 7 | 2 | dog | cat | 06-02-2016 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 138 8 | 2 | dog | cat | 06-02-2016 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 139 9 | 2 | dog | cat | 01-01-2010 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 140 10 | 2 | dog | cat | 01-01-2010 | Thu Sep 01 12:00:00 2016 | | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 141 11 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 142 12 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,the,real,world} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 143 13 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 144 14 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | -5 | | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 145 15 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 146 16 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | 180000000000018 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 147 17 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 148 18 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 1.00000000001 | 12:00:00 | @ 1 day | t | f 149 19 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 2.000000000000002 | 12:00:00 | @ 1 day | t | f 150 20 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 2.000000000000002 | 12:00:00 | @ 1 day | t | f 151 21 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 2.000000000000002 | 23:59:59 | @ 1 day | t | f 152 22 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 2.000000000000002 | 23:59:59 | @ 1 day | t | f 153 23 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 2.000000000000002 | 23:59:59 | @ 3 hours | t | f 154 24 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 2.000000000000002 | 23:59:59 | @ 3 hours | t | f 155 25 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 2.000000000000002 | 23:59:59 | | f | t 156 26 | 2 | dog | cat | 01-01-2010 | Thu Dec 31 11:12:13 1970 | Thu Sep 29 12:00:00 2016 | {This,is,no,fantasy} | 9 | 13 | -9999999999999999 | 2.000000000000002 | 23:59:59 | | f | t 157 27 | 2 | | | | | Thu Sep 29 12:00:00 2016 | | | 13 | | | | | | 158 28 | 2 | | | | | Thu Sep 29 12:00:00 2016 | | | 13 | | | | | | 159(28 rows) 160 161SELECT comp(); 162 comp 163----------- 164 Unchanged 165(1 row) 166 167DROP TABLE T; 168-- Test expressions in the defaults 169CREATE OR REPLACE FUNCTION foo(a INT) RETURNS TEXT AS $$ 170DECLARE res TEXT := ''; 171 i INT; 172BEGIN 173 i := 0; 174 WHILE (i < a) LOOP 175 res := res || chr(ascii('a') + i); 176 i := i + 1; 177 END LOOP; 178 RETURN res; 179END; $$ LANGUAGE PLPGSQL STABLE; 180CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT LENGTH(foo(6))); 181SELECT set('t'); 182 set 183----- 184 185(1 row) 186 187INSERT INTO T VALUES (1), (2); 188ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT foo(4), 189 ALTER COLUMN c_int SET DEFAULT LENGTH(foo(8)); 190INSERT INTO T VALUES (3), (4); 191ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT foo(6), 192 ALTER COLUMN c_bpchar SET DEFAULT foo(3); 193INSERT INTO T VALUES (5), (6); 194ALTER TABLE T ADD COLUMN c_date DATE 195 DEFAULT '2016-06-02'::DATE + LENGTH(foo(10)), 196 ALTER COLUMN c_text SET DEFAULT foo(12); 197INSERT INTO T VALUES (7), (8); 198ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP 199 DEFAULT '2016-09-01'::DATE + LENGTH(foo(10)), 200 ALTER COLUMN c_date 201 SET DEFAULT '2010-01-01'::DATE - LENGTH(foo(4)); 202INSERT INTO T VALUES (9), (10); 203ALTER TABLE T ADD COLUMN c_array TEXT[] 204 DEFAULT ('{"This", "is", "' || foo(4) || 205 '","the", "real", "world"}')::TEXT[], 206 ALTER COLUMN c_timestamp 207 SET DEFAULT '1970-12-31'::DATE + LENGTH(foo(30)); 208INSERT INTO T VALUES (11), (12); 209ALTER TABLE T ALTER COLUMN c_int DROP DEFAULT, 210 ALTER COLUMN c_array 211 SET DEFAULT ('{"This", "is", "' || foo(1) || 212 '", "fantasy"}')::text[]; 213INSERT INTO T VALUES (13), (14); 214ALTER TABLE T ALTER COLUMN c_bpchar DROP DEFAULT, 215 ALTER COLUMN c_date DROP DEFAULT, 216 ALTER COLUMN c_text DROP DEFAULT, 217 ALTER COLUMN c_timestamp DROP DEFAULT, 218 ALTER COLUMN c_array DROP DEFAULT; 219INSERT INTO T VALUES (15), (16); 220SELECT * FROM T; 221 pk | c_int | c_bpchar | c_text | c_date | c_timestamp | c_array 222----+-------+----------+--------------+------------+--------------------------+------------------------------- 223 1 | 6 | abcd | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 224 2 | 6 | abcd | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 225 3 | 8 | abcd | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 226 4 | 8 | abcd | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 227 5 | 8 | abc | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 228 6 | 8 | abc | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 229 7 | 8 | abc | abcdefghijkl | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 230 8 | 8 | abc | abcdefghijkl | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 231 9 | 8 | abc | abcdefghijkl | 12-28-2009 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 232 10 | 8 | abc | abcdefghijkl | 12-28-2009 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} 233 11 | 8 | abc | abcdefghijkl | 12-28-2009 | Sat Jan 30 00:00:00 1971 | {This,is,abcd,the,real,world} 234 12 | 8 | abc | abcdefghijkl | 12-28-2009 | Sat Jan 30 00:00:00 1971 | {This,is,abcd,the,real,world} 235 13 | | abc | abcdefghijkl | 12-28-2009 | Sat Jan 30 00:00:00 1971 | {This,is,a,fantasy} 236 14 | | abc | abcdefghijkl | 12-28-2009 | Sat Jan 30 00:00:00 1971 | {This,is,a,fantasy} 237 15 | | | | | | 238 16 | | | | | | 239(16 rows) 240 241SELECT comp(); 242 comp 243----------- 244 Unchanged 245(1 row) 246 247DROP TABLE T; 248DROP FUNCTION foo(INT); 249-- Fall back to full rewrite for volatile expressions 250CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); 251INSERT INTO T VALUES (1); 252SELECT set('t'); 253 set 254----- 255 256(1 row) 257 258-- now() is stable, because it returns the transaction timestamp 259ALTER TABLE T ADD COLUMN c1 TIMESTAMP DEFAULT now(); 260SELECT comp(); 261 comp 262----------- 263 Unchanged 264(1 row) 265 266-- clock_timestamp() is volatile 267ALTER TABLE T ADD COLUMN c2 TIMESTAMP DEFAULT clock_timestamp(); 268NOTICE: rewriting table t for reason 2 269SELECT comp(); 270 comp 271----------- 272 Rewritten 273(1 row) 274 275DROP TABLE T; 276-- Simple querie 277CREATE TABLE T (pk INT NOT NULL PRIMARY KEY); 278SELECT set('t'); 279 set 280----- 281 282(1 row) 283 284INSERT INTO T SELECT * FROM generate_series(1, 10) a; 285ALTER TABLE T ADD COLUMN c_bigint BIGINT NOT NULL DEFAULT -1; 286INSERT INTO T SELECT b, b - 10 FROM generate_series(11, 20) a(b); 287ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'hello'; 288INSERT INTO T SELECT b, b - 10, (b + 10)::text FROM generate_series(21, 30) a(b); 289-- WHERE clause 290SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1; 291 c_bigint | c_text 292----------+-------- 293 -1 | hello 294(1 row) 295 296EXPLAIN (VERBOSE TRUE, COSTS FALSE) 297SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1; 298 QUERY PLAN 299---------------------------------------------- 300 Limit 301 Output: c_bigint, c_text 302 -> Seq Scan on fast_default.t 303 Output: c_bigint, c_text 304 Filter: (t.c_bigint = '-1'::integer) 305(5 rows) 306 307SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1; 308 c_bigint | c_text 309----------+-------- 310 -1 | hello 311(1 row) 312 313EXPLAIN (VERBOSE TRUE, COSTS FALSE) SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1; 314 QUERY PLAN 315-------------------------------------------- 316 Limit 317 Output: c_bigint, c_text 318 -> Seq Scan on fast_default.t 319 Output: c_bigint, c_text 320 Filter: (t.c_text = 'hello'::text) 321(5 rows) 322 323-- COALESCE 324SELECT COALESCE(c_bigint, pk), COALESCE(c_text, pk::text) 325FROM T 326ORDER BY pk LIMIT 10; 327 coalesce | coalesce 328----------+---------- 329 -1 | hello 330 -1 | hello 331 -1 | hello 332 -1 | hello 333 -1 | hello 334 -1 | hello 335 -1 | hello 336 -1 | hello 337 -1 | hello 338 -1 | hello 339(10 rows) 340 341-- Aggregate function 342SELECT SUM(c_bigint), MAX(c_text COLLATE "C" ), MIN(c_text COLLATE "C") FROM T; 343 sum | max | min 344-----+-------+----- 345 200 | hello | 31 346(1 row) 347 348-- ORDER BY 349SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10; 350 pk | c_bigint | c_text 351----+----------+-------- 352 1 | -1 | hello 353 2 | -1 | hello 354 3 | -1 | hello 355 4 | -1 | hello 356 5 | -1 | hello 357 6 | -1 | hello 358 7 | -1 | hello 359 8 | -1 | hello 360 9 | -1 | hello 361 10 | -1 | hello 362(10 rows) 363 364EXPLAIN (VERBOSE TRUE, COSTS FALSE) 365SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10; 366 QUERY PLAN 367---------------------------------------------- 368 Limit 369 Output: pk, c_bigint, c_text 370 -> Sort 371 Output: pk, c_bigint, c_text 372 Sort Key: t.c_bigint, t.c_text, t.pk 373 -> Seq Scan on fast_default.t 374 Output: pk, c_bigint, c_text 375(7 rows) 376 377-- LIMIT 378SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10; 379 pk | c_bigint | c_text 380----+----------+-------- 381 11 | 1 | hello 382 12 | 2 | hello 383 13 | 3 | hello 384 14 | 4 | hello 385 15 | 5 | hello 386 16 | 6 | hello 387 17 | 7 | hello 388 18 | 8 | hello 389 19 | 9 | hello 390 20 | 10 | hello 391(10 rows) 392 393EXPLAIN (VERBOSE TRUE, COSTS FALSE) 394SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10; 395 QUERY PLAN 396---------------------------------------------------- 397 Limit 398 Output: pk, c_bigint, c_text 399 -> Sort 400 Output: pk, c_bigint, c_text 401 Sort Key: t.c_bigint, t.c_text, t.pk 402 -> Seq Scan on fast_default.t 403 Output: pk, c_bigint, c_text 404 Filter: (t.c_bigint > '-1'::integer) 405(8 rows) 406 407-- DELETE with RETURNING 408DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *; 409 pk | c_bigint | c_text 410----+----------+-------- 411 10 | -1 | hello 412 11 | 1 | hello 413 12 | 2 | hello 414 13 | 3 | hello 415 14 | 4 | hello 416 15 | 5 | hello 417 16 | 6 | hello 418 17 | 7 | hello 419 18 | 8 | hello 420 19 | 9 | hello 421 20 | 10 | hello 422(11 rows) 423 424EXPLAIN (VERBOSE TRUE, COSTS FALSE) 425DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *; 426 QUERY PLAN 427----------------------------------------------------------- 428 Delete on fast_default.t 429 Output: pk, c_bigint, c_text 430 -> Bitmap Heap Scan on fast_default.t 431 Output: ctid 432 Recheck Cond: ((t.pk >= 10) AND (t.pk <= 20)) 433 -> Bitmap Index Scan on t_pkey 434 Index Cond: ((t.pk >= 10) AND (t.pk <= 20)) 435(7 rows) 436 437-- UPDATE 438UPDATE T SET c_text = '"' || c_text || '"' WHERE pk < 10; 439SELECT * FROM T WHERE c_text LIKE '"%"' ORDER BY PK; 440 pk | c_bigint | c_text 441----+----------+--------- 442 1 | -1 | "hello" 443 2 | -1 | "hello" 444 3 | -1 | "hello" 445 4 | -1 | "hello" 446 5 | -1 | "hello" 447 6 | -1 | "hello" 448 7 | -1 | "hello" 449 8 | -1 | "hello" 450 9 | -1 | "hello" 451(9 rows) 452 453SELECT comp(); 454 comp 455----------- 456 Unchanged 457(1 row) 458 459DROP TABLE T; 460-- Combine with other DDL 461CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); 462SELECT set('t'); 463 set 464----- 465 466(1 row) 467 468INSERT INTO T VALUES (1), (2); 469ALTER TABLE T ADD COLUMN c_int INT NOT NULL DEFAULT -1; 470INSERT INTO T VALUES (3), (4); 471ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'Hello'; 472INSERT INTO T VALUES (5), (6); 473ALTER TABLE T ALTER COLUMN c_text SET DEFAULT 'world', 474 ALTER COLUMN c_int SET DEFAULT 1; 475INSERT INTO T VALUES (7), (8); 476SELECT * FROM T ORDER BY pk; 477 pk | c_int | c_text 478----+-------+-------- 479 1 | -1 | Hello 480 2 | -1 | Hello 481 3 | -1 | Hello 482 4 | -1 | Hello 483 5 | -1 | Hello 484 6 | -1 | Hello 485 7 | 1 | world 486 8 | 1 | world 487(8 rows) 488 489-- Add an index 490CREATE INDEX i ON T(c_int, c_text); 491SELECT c_text FROM T WHERE c_int = -1; 492 c_text 493-------- 494 Hello 495 Hello 496 Hello 497 Hello 498 Hello 499 Hello 500(6 rows) 501 502SELECT comp(); 503 comp 504----------- 505 Unchanged 506(1 row) 507 508-- query to exercise expand_tuple function 509CREATE TABLE t1 AS 510SELECT 1::int AS a , 2::int AS b 511FROM generate_series(1,20) q; 512ALTER TABLE t1 ADD COLUMN c text; 513SELECT a, 514 stddev(cast((SELECT sum(1) FROM generate_series(1,20) x) AS float4)) 515 OVER (PARTITION BY a,b,c ORDER BY b) 516 AS z 517FROM t1; 518 a | z 519---+--- 520 1 | 0 521 1 | 0 522 1 | 0 523 1 | 0 524 1 | 0 525 1 | 0 526 1 | 0 527 1 | 0 528 1 | 0 529 1 | 0 530 1 | 0 531 1 | 0 532 1 | 0 533 1 | 0 534 1 | 0 535 1 | 0 536 1 | 0 537 1 | 0 538 1 | 0 539 1 | 0 540(20 rows) 541 542DROP TABLE T; 543-- test that we account for missing columns without defaults correctly 544-- in expand_tuple, and that rows are correctly expanded for triggers 545CREATE FUNCTION test_trigger() 546RETURNS trigger 547LANGUAGE plpgsql 548AS $$ 549 550begin 551 raise notice 'old tuple: %', to_json(OLD)::text; 552 if TG_OP = 'DELETE' 553 then 554 return OLD; 555 else 556 return NEW; 557 end if; 558end; 559 560$$; 561-- 2 new columns, both have defaults 562CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 563INSERT INTO t (a,b,c) VALUES (1,2,3); 564ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; 565ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; 566CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 567SELECT * FROM t; 568 id | a | b | c | x | y 569----+---+---+---+---+--- 570 1 | 1 | 2 | 3 | 4 | 5 571(1 row) 572 573UPDATE t SET y = 2; 574NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":4,"y":5} 575SELECT * FROM t; 576 id | a | b | c | x | y 577----+---+---+---+---+--- 578 1 | 1 | 2 | 3 | 4 | 2 579(1 row) 580 581DROP TABLE t; 582-- 2 new columns, first has default 583CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 584INSERT INTO t (a,b,c) VALUES (1,2,3); 585ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; 586ALTER TABLE t ADD COLUMN y int; 587CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 588SELECT * FROM t; 589 id | a | b | c | x | y 590----+---+---+---+---+--- 591 1 | 1 | 2 | 3 | 4 | 592(1 row) 593 594UPDATE t SET y = 2; 595NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":4,"y":null} 596SELECT * FROM t; 597 id | a | b | c | x | y 598----+---+---+---+---+--- 599 1 | 1 | 2 | 3 | 4 | 2 600(1 row) 601 602DROP TABLE t; 603-- 2 new columns, second has default 604CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 605INSERT INTO t (a,b,c) VALUES (1,2,3); 606ALTER TABLE t ADD COLUMN x int; 607ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; 608CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 609SELECT * FROM t; 610 id | a | b | c | x | y 611----+---+---+---+---+--- 612 1 | 1 | 2 | 3 | | 5 613(1 row) 614 615UPDATE t SET y = 2; 616NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":null,"y":5} 617SELECT * FROM t; 618 id | a | b | c | x | y 619----+---+---+---+---+--- 620 1 | 1 | 2 | 3 | | 2 621(1 row) 622 623DROP TABLE t; 624-- 2 new columns, neither has default 625CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 626INSERT INTO t (a,b,c) VALUES (1,2,3); 627ALTER TABLE t ADD COLUMN x int; 628ALTER TABLE t ADD COLUMN y int; 629CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 630SELECT * FROM t; 631 id | a | b | c | x | y 632----+---+---+---+---+--- 633 1 | 1 | 2 | 3 | | 634(1 row) 635 636UPDATE t SET y = 2; 637NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":null,"y":null} 638SELECT * FROM t; 639 id | a | b | c | x | y 640----+---+---+---+---+--- 641 1 | 1 | 2 | 3 | | 2 642(1 row) 643 644DROP TABLE t; 645-- same as last 4 tests but here the last original column has a NULL value 646-- 2 new columns, both have defaults 647CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 648INSERT INTO t (a,b,c) VALUES (1,2,NULL); 649ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; 650ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; 651CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 652SELECT * FROM t; 653 id | a | b | c | x | y 654----+---+---+---+---+--- 655 1 | 1 | 2 | | 4 | 5 656(1 row) 657 658UPDATE t SET y = 2; 659NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":4,"y":5} 660SELECT * FROM t; 661 id | a | b | c | x | y 662----+---+---+---+---+--- 663 1 | 1 | 2 | | 4 | 2 664(1 row) 665 666DROP TABLE t; 667-- 2 new columns, first has default 668CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 669INSERT INTO t (a,b,c) VALUES (1,2,NULL); 670ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; 671ALTER TABLE t ADD COLUMN y int; 672CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 673SELECT * FROM t; 674 id | a | b | c | x | y 675----+---+---+---+---+--- 676 1 | 1 | 2 | | 4 | 677(1 row) 678 679UPDATE t SET y = 2; 680NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":4,"y":null} 681SELECT * FROM t; 682 id | a | b | c | x | y 683----+---+---+---+---+--- 684 1 | 1 | 2 | | 4 | 2 685(1 row) 686 687DROP TABLE t; 688-- 2 new columns, second has default 689CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 690INSERT INTO t (a,b,c) VALUES (1,2,NULL); 691ALTER TABLE t ADD COLUMN x int; 692ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; 693CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 694SELECT * FROM t; 695 id | a | b | c | x | y 696----+---+---+---+---+--- 697 1 | 1 | 2 | | | 5 698(1 row) 699 700UPDATE t SET y = 2; 701NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":null,"y":5} 702SELECT * FROM t; 703 id | a | b | c | x | y 704----+---+---+---+---+--- 705 1 | 1 | 2 | | | 2 706(1 row) 707 708DROP TABLE t; 709-- 2 new columns, neither has default 710CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); 711INSERT INTO t (a,b,c) VALUES (1,2,NULL); 712ALTER TABLE t ADD COLUMN x int; 713ALTER TABLE t ADD COLUMN y int; 714CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); 715SELECT * FROM t; 716 id | a | b | c | x | y 717----+---+---+---+---+--- 718 1 | 1 | 2 | | | 719(1 row) 720 721UPDATE t SET y = 2; 722NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":null,"y":null} 723SELECT * FROM t; 724 id | a | b | c | x | y 725----+---+---+---+---+--- 726 1 | 1 | 2 | | | 2 727(1 row) 728 729DROP TABLE t; 730-- make sure expanded tuple has correct self pointer 731-- it will be required by the RI trigger doing the cascading delete 732CREATE TABLE leader (a int PRIMARY KEY, b int); 733CREATE TABLE follower (a int REFERENCES leader ON DELETE CASCADE, b int); 734INSERT INTO leader VALUES (1, 1), (2, 2); 735ALTER TABLE leader ADD c int; 736ALTER TABLE leader DROP c; 737DELETE FROM leader; 738-- check that ALTER TABLE ... ALTER TYPE does the right thing 739CREATE TABLE vtype( a integer); 740INSERT INTO vtype VALUES (1); 741ALTER TABLE vtype ADD COLUMN b DOUBLE PRECISION DEFAULT 0.2; 742ALTER TABLE vtype ADD COLUMN c BOOLEAN DEFAULT true; 743SELECT * FROM vtype; 744 a | b | c 745---+-----+--- 746 1 | 0.2 | t 747(1 row) 748 749ALTER TABLE vtype 750 ALTER b TYPE text USING b::text, 751 ALTER c TYPE text USING c::text; 752NOTICE: rewriting table vtype for reason 4 753SELECT * FROM vtype; 754 a | b | c 755---+-----+------ 756 1 | 0.2 | true 757(1 row) 758 759-- also check the case that doesn't rewrite the table 760CREATE TABLE vtype2 (a int); 761INSERT INTO vtype2 VALUES (1); 762ALTER TABLE vtype2 ADD COLUMN b varchar(10) DEFAULT 'xxx'; 763ALTER TABLE vtype2 ALTER COLUMN b SET DEFAULT 'yyy'; 764INSERT INTO vtype2 VALUES (2); 765ALTER TABLE vtype2 ALTER COLUMN b TYPE varchar(20) USING b::varchar(20); 766SELECT * FROM vtype2; 767 a | b 768---+----- 769 1 | xxx 770 2 | yyy 771(2 rows) 772 773-- Ensure that defaults are checked when evaluating whether HOT update 774-- is possible, this was broken for a while: 775-- https://postgr.es/m/20190202133521.ylauh3ckqa7colzj%40alap3.anarazel.de 776BEGIN; 777CREATE TABLE t(); 778INSERT INTO t DEFAULT VALUES; 779ALTER TABLE t ADD COLUMN a int DEFAULT 1; 780CREATE INDEX ON t(a); 781-- set column with a default 1 to NULL, due to a bug that wasn't 782-- noticed has heap_getattr buggily returned NULL for default columns 783UPDATE t SET a = NULL; 784-- verify that index and non-index scans show the same result 785SET LOCAL enable_seqscan = true; 786SELECT * FROM t WHERE a IS NULL; 787 a 788--- 789 790(1 row) 791 792SET LOCAL enable_seqscan = false; 793SELECT * FROM t WHERE a IS NULL; 794 a 795--- 796 797(1 row) 798 799ROLLBACK; 800-- verify that a default set on a non-plain table doesn't set a missing 801-- value on the attribute 802CREATE FOREIGN DATA WRAPPER dummy; 803CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; 804CREATE FOREIGN TABLE ft1 (c1 integer NOT NULL) SERVER s0; 805ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer DEFAULT 0; 806ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10); 807SELECT count(*) 808 FROM pg_attribute 809 WHERE attrelid = 'ft1'::regclass AND 810 (attmissingval IS NOT NULL OR atthasmissing); 811 count 812------- 813 0 814(1 row) 815 816-- cleanup 817DROP FOREIGN TABLE ft1; 818DROP SERVER s0; 819DROP FOREIGN DATA WRAPPER dummy; 820DROP TABLE vtype; 821DROP TABLE vtype2; 822DROP TABLE follower; 823DROP TABLE leader; 824DROP FUNCTION test_trigger(); 825DROP TABLE t1; 826DROP FUNCTION set(name); 827DROP FUNCTION comp(); 828DROP TABLE m; 829DROP TABLE has_volatile; 830DROP EVENT TRIGGER has_volatile_rewrite; 831DROP FUNCTION log_rewrite; 832DROP SCHEMA fast_default; 833-- Leave a table with an active fast default in place, for pg_upgrade testing 834set search_path = public; 835create table has_fast_default(f1 int); 836insert into has_fast_default values(1); 837alter table has_fast_default add column f2 int default 42; 838table has_fast_default; 839 f1 | f2 840----+---- 841 1 | 42 842(1 row) 843 844