diff options
Diffstat (limited to 'src/test/regress/expected/fast_default.out')
| -rw-r--r-- | src/test/regress/expected/fast_default.out | 843 |
1 files changed, 843 insertions, 0 deletions
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out new file mode 100644 index 0000000..91f2571 --- /dev/null +++ b/src/test/regress/expected/fast_default.out @@ -0,0 +1,843 @@ +-- +-- ALTER TABLE ADD COLUMN DEFAULT test +-- +SET search_path = fast_default; +CREATE SCHEMA fast_default; +CREATE TABLE m(id OID); +INSERT INTO m VALUES (NULL::OID); +CREATE FUNCTION set(tabname name) RETURNS VOID +AS $$ +BEGIN + UPDATE m + SET id = (SELECT c.relfilenode + FROM pg_class AS c, pg_namespace AS s + WHERE c.relname = tabname + AND c.relnamespace = s.oid + AND s.nspname = 'fast_default'); +END; +$$ LANGUAGE 'plpgsql'; +CREATE FUNCTION comp() RETURNS TEXT +AS $$ +BEGIN + RETURN (SELECT CASE + WHEN m.id = c.relfilenode THEN 'Unchanged' + ELSE 'Rewritten' + END + FROM m, pg_class AS c, pg_namespace AS s + WHERE c.relname = 't' + AND c.relnamespace = s.oid + AND s.nspname = 'fast_default'); +END; +$$ LANGUAGE 'plpgsql'; +CREATE FUNCTION log_rewrite() RETURNS event_trigger +LANGUAGE plpgsql as +$func$ + +declare + this_schema text; +begin + select into this_schema relnamespace::regnamespace::text + from pg_class + where oid = pg_event_trigger_table_rewrite_oid(); + if this_schema = 'fast_default' + then + RAISE NOTICE 'rewriting table % for reason %', + pg_event_trigger_table_rewrite_oid()::regclass, + pg_event_trigger_table_rewrite_reason(); + end if; +end; +$func$; +CREATE TABLE has_volatile AS +SELECT * FROM generate_series(1,10) id; +CREATE EVENT TRIGGER has_volatile_rewrite + ON table_rewrite + EXECUTE PROCEDURE log_rewrite(); +-- only the last of these should trigger a rewrite +ALTER TABLE has_volatile ADD col1 int; +ALTER TABLE has_volatile ADD col2 int DEFAULT 1; +ALTER TABLE has_volatile ADD col3 timestamptz DEFAULT current_timestamp; +ALTER TABLE has_volatile ADD col4 int DEFAULT (random() * 10000)::int; +NOTICE: rewriting table has_volatile for reason 2 +-- Test a large sample of different datatypes +CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT 1); +SELECT set('t'); + set +----- + +(1 row) + +INSERT INTO T VALUES (1), (2); +ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT 'hello', + ALTER COLUMN c_int SET DEFAULT 2; +INSERT INTO T VALUES (3), (4); +ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'world', + ALTER COLUMN c_bpchar SET DEFAULT 'dog'; +INSERT INTO T VALUES (5), (6); +ALTER TABLE T ADD COLUMN c_date DATE DEFAULT '2016-06-02', + ALTER COLUMN c_text SET DEFAULT 'cat'; +INSERT INTO T VALUES (7), (8); +ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP DEFAULT '2016-09-01 12:00:00', + ADD COLUMN c_timestamp_null TIMESTAMP, + ALTER COLUMN c_date SET DEFAULT '2010-01-01'; +INSERT INTO T VALUES (9), (10); +ALTER TABLE T ADD COLUMN c_array TEXT[] + DEFAULT '{"This", "is", "the", "real", "world"}', + ALTER COLUMN c_timestamp SET DEFAULT '1970-12-31 11:12:13', + ALTER COLUMN c_timestamp_null SET DEFAULT '2016-09-29 12:00:00'; +INSERT INTO T VALUES (11), (12); +ALTER TABLE T ADD COLUMN c_small SMALLINT DEFAULT -5, + ADD COLUMN c_small_null SMALLINT, + ALTER COLUMN c_array + SET DEFAULT '{"This", "is", "no", "fantasy"}'; +INSERT INTO T VALUES (13), (14); +ALTER TABLE T ADD COLUMN c_big BIGINT DEFAULT 180000000000018, + ALTER COLUMN c_small SET DEFAULT 9, + ALTER COLUMN c_small_null SET DEFAULT 13; +INSERT INTO T VALUES (15), (16); +ALTER TABLE T ADD COLUMN c_num NUMERIC DEFAULT 1.00000000001, + ALTER COLUMN c_big SET DEFAULT -9999999999999999; +INSERT INTO T VALUES (17), (18); +ALTER TABLE T ADD COLUMN c_time TIME DEFAULT '12:00:00', + ALTER COLUMN c_num SET DEFAULT 2.000000000000002; +INSERT INTO T VALUES (19), (20); +ALTER TABLE T ADD COLUMN c_interval INTERVAL DEFAULT '1 day', + ALTER COLUMN c_time SET DEFAULT '23:59:59'; +INSERT INTO T VALUES (21), (22); +ALTER TABLE T ADD COLUMN c_hugetext TEXT DEFAULT repeat('abcdefg',1000), + ALTER COLUMN c_interval SET DEFAULT '3 hours'; +INSERT INTO T VALUES (23), (24); +ALTER TABLE T ALTER COLUMN c_interval DROP DEFAULT, + ALTER COLUMN c_hugetext SET DEFAULT repeat('poiuyt', 1000); +INSERT INTO T VALUES (25), (26); +ALTER TABLE T ALTER COLUMN c_bpchar DROP DEFAULT, + ALTER COLUMN c_date DROP DEFAULT, + ALTER COLUMN c_text DROP DEFAULT, + ALTER COLUMN c_timestamp DROP DEFAULT, + ALTER COLUMN c_array DROP DEFAULT, + ALTER COLUMN c_small DROP DEFAULT, + ALTER COLUMN c_big DROP DEFAULT, + ALTER COLUMN c_num DROP DEFAULT, + ALTER COLUMN c_time DROP DEFAULT, + ALTER COLUMN c_hugetext DROP DEFAULT; +INSERT INTO T VALUES (27), (28); +SELECT 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 = repeat('abcdefg',1000) as c_hugetext_origdef, + c_hugetext = repeat('poiuyt', 1000) as c_hugetext_newdef +FROM T ORDER BY pk; + 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 +----+-------+----------+--------+------------+--------------------------+--------------------------+--------------------------+---------+--------------+-------------------+-------------------+----------+------------+--------------------+------------------- + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 27 | 2 | | | | | Thu Sep 29 12:00:00 2016 | | | 13 | | | | | | + 28 | 2 | | | | | Thu Sep 29 12:00:00 2016 | | | 13 | | | | | | +(28 rows) + +SELECT comp(); + comp +----------- + Unchanged +(1 row) + +DROP TABLE T; +-- Test expressions in the defaults +CREATE OR REPLACE FUNCTION foo(a INT) RETURNS TEXT AS $$ +DECLARE res TEXT := ''; + i INT; +BEGIN + i := 0; + WHILE (i < a) LOOP + res := res || chr(ascii('a') + i); + i := i + 1; + END LOOP; + RETURN res; +END; $$ LANGUAGE PLPGSQL STABLE; +CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT LENGTH(foo(6))); +SELECT set('t'); + set +----- + +(1 row) + +INSERT INTO T VALUES (1), (2); +ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT foo(4), + ALTER COLUMN c_int SET DEFAULT LENGTH(foo(8)); +INSERT INTO T VALUES (3), (4); +ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT foo(6), + ALTER COLUMN c_bpchar SET DEFAULT foo(3); +INSERT INTO T VALUES (5), (6); +ALTER TABLE T ADD COLUMN c_date DATE + DEFAULT '2016-06-02'::DATE + LENGTH(foo(10)), + ALTER COLUMN c_text SET DEFAULT foo(12); +INSERT INTO T VALUES (7), (8); +ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP + DEFAULT '2016-09-01'::DATE + LENGTH(foo(10)), + ALTER COLUMN c_date + SET DEFAULT '2010-01-01'::DATE - LENGTH(foo(4)); +INSERT INTO T VALUES (9), (10); +ALTER TABLE T ADD COLUMN c_array TEXT[] + DEFAULT ('{"This", "is", "' || foo(4) || + '","the", "real", "world"}')::TEXT[], + ALTER COLUMN c_timestamp + SET DEFAULT '1970-12-31'::DATE + LENGTH(foo(30)); +INSERT INTO T VALUES (11), (12); +ALTER TABLE T ALTER COLUMN c_int DROP DEFAULT, + ALTER COLUMN c_array + SET DEFAULT ('{"This", "is", "' || foo(1) || + '", "fantasy"}')::text[]; +INSERT INTO T VALUES (13), (14); +ALTER TABLE T ALTER COLUMN c_bpchar DROP DEFAULT, + ALTER COLUMN c_date DROP DEFAULT, + ALTER COLUMN c_text DROP DEFAULT, + ALTER COLUMN c_timestamp DROP DEFAULT, + ALTER COLUMN c_array DROP DEFAULT; +INSERT INTO T VALUES (15), (16); +SELECT * FROM T; + pk | c_int | c_bpchar | c_text | c_date | c_timestamp | c_array +----+-------+----------+--------------+------------+--------------------------+------------------------------- + 1 | 6 | abcd | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 2 | 6 | abcd | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 3 | 8 | abcd | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 4 | 8 | abcd | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 5 | 8 | abc | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 6 | 8 | abc | abcdef | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 7 | 8 | abc | abcdefghijkl | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 8 | 8 | abc | abcdefghijkl | 06-12-2016 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 9 | 8 | abc | abcdefghijkl | 12-28-2009 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 10 | 8 | abc | abcdefghijkl | 12-28-2009 | Sun Sep 11 00:00:00 2016 | {This,is,abcd,the,real,world} + 11 | 8 | abc | abcdefghijkl | 12-28-2009 | Sat Jan 30 00:00:00 1971 | {This,is,abcd,the,real,world} + 12 | 8 | abc | abcdefghijkl | 12-28-2009 | Sat Jan 30 00:00:00 1971 | {This,is,abcd,the,real,world} + 13 | | abc | abcdefghijkl | 12-28-2009 | Sat Jan 30 00:00:00 1971 | {This,is,a,fantasy} + 14 | | abc | abcdefghijkl | 12-28-2009 | Sat Jan 30 00:00:00 1971 | {This,is,a,fantasy} + 15 | | | | | | + 16 | | | | | | +(16 rows) + +SELECT comp(); + comp +----------- + Unchanged +(1 row) + +DROP TABLE T; +DROP FUNCTION foo(INT); +-- Fall back to full rewrite for volatile expressions +CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); +INSERT INTO T VALUES (1); +SELECT set('t'); + set +----- + +(1 row) + +-- now() is stable, because it returns the transaction timestamp +ALTER TABLE T ADD COLUMN c1 TIMESTAMP DEFAULT now(); +SELECT comp(); + comp +----------- + Unchanged +(1 row) + +-- clock_timestamp() is volatile +ALTER TABLE T ADD COLUMN c2 TIMESTAMP DEFAULT clock_timestamp(); +NOTICE: rewriting table t for reason 2 +SELECT comp(); + comp +----------- + Rewritten +(1 row) + +DROP TABLE T; +-- Simple querie +CREATE TABLE T (pk INT NOT NULL PRIMARY KEY); +SELECT set('t'); + set +----- + +(1 row) + +INSERT INTO T SELECT * FROM generate_series(1, 10) a; +ALTER TABLE T ADD COLUMN c_bigint BIGINT NOT NULL DEFAULT -1; +INSERT INTO T SELECT b, b - 10 FROM generate_series(11, 20) a(b); +ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'hello'; +INSERT INTO T SELECT b, b - 10, (b + 10)::text FROM generate_series(21, 30) a(b); +-- WHERE clause +SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1; + c_bigint | c_text +----------+-------- + -1 | hello +(1 row) + +EXPLAIN (VERBOSE TRUE, COSTS FALSE) +SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1; + QUERY PLAN +---------------------------------------------- + Limit + Output: c_bigint, c_text + -> Seq Scan on fast_default.t + Output: c_bigint, c_text + Filter: (t.c_bigint = '-1'::integer) +(5 rows) + +SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1; + c_bigint | c_text +----------+-------- + -1 | hello +(1 row) + +EXPLAIN (VERBOSE TRUE, COSTS FALSE) SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1; + QUERY PLAN +-------------------------------------------- + Limit + Output: c_bigint, c_text + -> Seq Scan on fast_default.t + Output: c_bigint, c_text + Filter: (t.c_text = 'hello'::text) +(5 rows) + +-- COALESCE +SELECT COALESCE(c_bigint, pk), COALESCE(c_text, pk::text) +FROM T +ORDER BY pk LIMIT 10; + coalesce | coalesce +----------+---------- + -1 | hello + -1 | hello + -1 | hello + -1 | hello + -1 | hello + -1 | hello + -1 | hello + -1 | hello + -1 | hello + -1 | hello +(10 rows) + +-- Aggregate function +SELECT SUM(c_bigint), MAX(c_text COLLATE "C" ), MIN(c_text COLLATE "C") FROM T; + sum | max | min +-----+-------+----- + 200 | hello | 31 +(1 row) + +-- ORDER BY +SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10; + pk | c_bigint | c_text +----+----------+-------- + 1 | -1 | hello + 2 | -1 | hello + 3 | -1 | hello + 4 | -1 | hello + 5 | -1 | hello + 6 | -1 | hello + 7 | -1 | hello + 8 | -1 | hello + 9 | -1 | hello + 10 | -1 | hello +(10 rows) + +EXPLAIN (VERBOSE TRUE, COSTS FALSE) +SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10; + QUERY PLAN +---------------------------------------------- + Limit + Output: pk, c_bigint, c_text + -> Sort + Output: pk, c_bigint, c_text + Sort Key: t.c_bigint, t.c_text, t.pk + -> Seq Scan on fast_default.t + Output: pk, c_bigint, c_text +(7 rows) + +-- LIMIT +SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10; + pk | c_bigint | c_text +----+----------+-------- + 11 | 1 | hello + 12 | 2 | hello + 13 | 3 | hello + 14 | 4 | hello + 15 | 5 | hello + 16 | 6 | hello + 17 | 7 | hello + 18 | 8 | hello + 19 | 9 | hello + 20 | 10 | hello +(10 rows) + +EXPLAIN (VERBOSE TRUE, COSTS FALSE) +SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10; + QUERY PLAN +---------------------------------------------------- + Limit + Output: pk, c_bigint, c_text + -> Sort + Output: pk, c_bigint, c_text + Sort Key: t.c_bigint, t.c_text, t.pk + -> Seq Scan on fast_default.t + Output: pk, c_bigint, c_text + Filter: (t.c_bigint > '-1'::integer) +(8 rows) + +-- DELETE with RETURNING +DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *; + pk | c_bigint | c_text +----+----------+-------- + 10 | -1 | hello + 11 | 1 | hello + 12 | 2 | hello + 13 | 3 | hello + 14 | 4 | hello + 15 | 5 | hello + 16 | 6 | hello + 17 | 7 | hello + 18 | 8 | hello + 19 | 9 | hello + 20 | 10 | hello +(11 rows) + +EXPLAIN (VERBOSE TRUE, COSTS FALSE) +DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *; + QUERY PLAN +----------------------------------------------------------- + Delete on fast_default.t + Output: pk, c_bigint, c_text + -> Bitmap Heap Scan on fast_default.t + Output: ctid + Recheck Cond: ((t.pk >= 10) AND (t.pk <= 20)) + -> Bitmap Index Scan on t_pkey + Index Cond: ((t.pk >= 10) AND (t.pk <= 20)) +(7 rows) + +-- UPDATE +UPDATE T SET c_text = '"' || c_text || '"' WHERE pk < 10; +SELECT * FROM T WHERE c_text LIKE '"%"' ORDER BY PK; + pk | c_bigint | c_text +----+----------+--------- + 1 | -1 | "hello" + 2 | -1 | "hello" + 3 | -1 | "hello" + 4 | -1 | "hello" + 5 | -1 | "hello" + 6 | -1 | "hello" + 7 | -1 | "hello" + 8 | -1 | "hello" + 9 | -1 | "hello" +(9 rows) + +SELECT comp(); + comp +----------- + Unchanged +(1 row) + +DROP TABLE T; +-- Combine with other DDL +CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); +SELECT set('t'); + set +----- + +(1 row) + +INSERT INTO T VALUES (1), (2); +ALTER TABLE T ADD COLUMN c_int INT NOT NULL DEFAULT -1; +INSERT INTO T VALUES (3), (4); +ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'Hello'; +INSERT INTO T VALUES (5), (6); +ALTER TABLE T ALTER COLUMN c_text SET DEFAULT 'world', + ALTER COLUMN c_int SET DEFAULT 1; +INSERT INTO T VALUES (7), (8); +SELECT * FROM T ORDER BY pk; + pk | c_int | c_text +----+-------+-------- + 1 | -1 | Hello + 2 | -1 | Hello + 3 | -1 | Hello + 4 | -1 | Hello + 5 | -1 | Hello + 6 | -1 | Hello + 7 | 1 | world + 8 | 1 | world +(8 rows) + +-- Add an index +CREATE INDEX i ON T(c_int, c_text); +SELECT c_text FROM T WHERE c_int = -1; + c_text +-------- + Hello + Hello + Hello + Hello + Hello + Hello +(6 rows) + +SELECT comp(); + comp +----------- + Unchanged +(1 row) + +-- query to exercise expand_tuple function +CREATE TABLE t1 AS +SELECT 1::int AS a , 2::int AS b +FROM generate_series(1,20) q; +ALTER TABLE t1 ADD COLUMN c text; +SELECT a, + stddev(cast((SELECT sum(1) FROM generate_series(1,20) x) AS float4)) + OVER (PARTITION BY a,b,c ORDER BY b) + AS z +FROM t1; + a | z +---+--- + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 + 1 | 0 +(20 rows) + +DROP TABLE T; +-- test that we account for missing columns without defaults correctly +-- in expand_tuple, and that rows are correctly expanded for triggers +CREATE FUNCTION test_trigger() +RETURNS trigger +LANGUAGE plpgsql +AS $$ + +begin + raise notice 'old tuple: %', to_json(OLD)::text; + if TG_OP = 'DELETE' + then + return OLD; + else + return NEW; + end if; +end; + +$$; +-- 2 new columns, both have defaults +CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); +INSERT INTO t (a,b,c) VALUES (1,2,3); +ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; +ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; +CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | 3 | 4 | 5 +(1 row) + +UPDATE t SET y = 2; +NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":4,"y":5} +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | 3 | 4 | 2 +(1 row) + +DROP TABLE t; +-- 2 new columns, first has default +CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); +INSERT INTO t (a,b,c) VALUES (1,2,3); +ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; +ALTER TABLE t ADD COLUMN y int; +CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | 3 | 4 | +(1 row) + +UPDATE t SET y = 2; +NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":4,"y":null} +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | 3 | 4 | 2 +(1 row) + +DROP TABLE t; +-- 2 new columns, second has default +CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); +INSERT INTO t (a,b,c) VALUES (1,2,3); +ALTER TABLE t ADD COLUMN x int; +ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; +CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | 3 | | 5 +(1 row) + +UPDATE t SET y = 2; +NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":null,"y":5} +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | 3 | | 2 +(1 row) + +DROP TABLE t; +-- 2 new columns, neither has default +CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); +INSERT INTO t (a,b,c) VALUES (1,2,3); +ALTER TABLE t ADD COLUMN x int; +ALTER TABLE t ADD COLUMN y int; +CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | 3 | | +(1 row) + +UPDATE t SET y = 2; +NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":null,"y":null} +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | 3 | | 2 +(1 row) + +DROP TABLE t; +-- same as last 4 tests but here the last original column has a NULL value +-- 2 new columns, both have defaults +CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); +INSERT INTO t (a,b,c) VALUES (1,2,NULL); +ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; +ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; +CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | | 4 | 5 +(1 row) + +UPDATE t SET y = 2; +NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":4,"y":5} +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | | 4 | 2 +(1 row) + +DROP TABLE t; +-- 2 new columns, first has default +CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); +INSERT INTO t (a,b,c) VALUES (1,2,NULL); +ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4; +ALTER TABLE t ADD COLUMN y int; +CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | | 4 | +(1 row) + +UPDATE t SET y = 2; +NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":4,"y":null} +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | | 4 | 2 +(1 row) + +DROP TABLE t; +-- 2 new columns, second has default +CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); +INSERT INTO t (a,b,c) VALUES (1,2,NULL); +ALTER TABLE t ADD COLUMN x int; +ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5; +CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | | | 5 +(1 row) + +UPDATE t SET y = 2; +NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":null,"y":5} +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | | | 2 +(1 row) + +DROP TABLE t; +-- 2 new columns, neither has default +CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int); +INSERT INTO t (a,b,c) VALUES (1,2,NULL); +ALTER TABLE t ADD COLUMN x int; +ALTER TABLE t ADD COLUMN y int; +CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger(); +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | | | +(1 row) + +UPDATE t SET y = 2; +NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":null,"y":null} +SELECT * FROM t; + id | a | b | c | x | y +----+---+---+---+---+--- + 1 | 1 | 2 | | | 2 +(1 row) + +DROP TABLE t; +-- make sure expanded tuple has correct self pointer +-- it will be required by the RI trigger doing the cascading delete +CREATE TABLE leader (a int PRIMARY KEY, b int); +CREATE TABLE follower (a int REFERENCES leader ON DELETE CASCADE, b int); +INSERT INTO leader VALUES (1, 1), (2, 2); +ALTER TABLE leader ADD c int; +ALTER TABLE leader DROP c; +DELETE FROM leader; +-- check that ALTER TABLE ... ALTER TYPE does the right thing +CREATE TABLE vtype( a integer); +INSERT INTO vtype VALUES (1); +ALTER TABLE vtype ADD COLUMN b DOUBLE PRECISION DEFAULT 0.2; +ALTER TABLE vtype ADD COLUMN c BOOLEAN DEFAULT true; +SELECT * FROM vtype; + a | b | c +---+-----+--- + 1 | 0.2 | t +(1 row) + +ALTER TABLE vtype + ALTER b TYPE text USING b::text, + ALTER c TYPE text USING c::text; +NOTICE: rewriting table vtype for reason 4 +SELECT * FROM vtype; + a | b | c +---+-----+------ + 1 | 0.2 | true +(1 row) + +-- also check the case that doesn't rewrite the table +CREATE TABLE vtype2 (a int); +INSERT INTO vtype2 VALUES (1); +ALTER TABLE vtype2 ADD COLUMN b varchar(10) DEFAULT 'xxx'; +ALTER TABLE vtype2 ALTER COLUMN b SET DEFAULT 'yyy'; +INSERT INTO vtype2 VALUES (2); +ALTER TABLE vtype2 ALTER COLUMN b TYPE varchar(20) USING b::varchar(20); +SELECT * FROM vtype2; + a | b +---+----- + 1 | xxx + 2 | yyy +(2 rows) + +-- Ensure that defaults are checked when evaluating whether HOT update +-- is possible, this was broken for a while: +-- https://postgr.es/m/20190202133521.ylauh3ckqa7colzj%40alap3.anarazel.de +BEGIN; +CREATE TABLE t(); +INSERT INTO t DEFAULT VALUES; +ALTER TABLE t ADD COLUMN a int DEFAULT 1; +CREATE INDEX ON t(a); +-- set column with a default 1 to NULL, due to a bug that wasn't +-- noticed has heap_getattr buggily returned NULL for default columns +UPDATE t SET a = NULL; +-- verify that index and non-index scans show the same result +SET LOCAL enable_seqscan = true; +SELECT * FROM t WHERE a IS NULL; + a +--- + +(1 row) + +SET LOCAL enable_seqscan = false; +SELECT * FROM t WHERE a IS NULL; + a +--- + +(1 row) + +ROLLBACK; +-- verify that a default set on a non-plain table doesn't set a missing +-- value on the attribute +CREATE FOREIGN DATA WRAPPER dummy; +CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; +CREATE FOREIGN TABLE ft1 (c1 integer NOT NULL) SERVER s0; +ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer DEFAULT 0; +ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10); +SELECT count(*) + FROM pg_attribute + WHERE attrelid = 'ft1'::regclass AND + (attmissingval IS NOT NULL OR atthasmissing); + count +------- + 0 +(1 row) + +-- cleanup +DROP FOREIGN TABLE ft1; +DROP SERVER s0; +DROP FOREIGN DATA WRAPPER dummy; +DROP TABLE vtype; +DROP TABLE vtype2; +DROP TABLE follower; +DROP TABLE leader; +DROP FUNCTION test_trigger(); +DROP TABLE t1; +DROP FUNCTION set(name); +DROP FUNCTION comp(); +DROP TABLE m; +DROP TABLE has_volatile; +DROP EVENT TRIGGER has_volatile_rewrite; +DROP FUNCTION log_rewrite; +DROP SCHEMA fast_default; +-- Leave a table with an active fast default in place, for pg_upgrade testing +set search_path = public; +create table has_fast_default(f1 int); +insert into has_fast_default values(1); +alter table has_fast_default add column f2 int default 42; +table has_fast_default; + f1 | f2 +----+---- + 1 | 42 +(1 row) + |