diff -U3 /home/postgres/postgres/src/test/regress/expected/temp.out /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/temp.out --- /home/postgres/postgres/src/test/regress/expected/temp.out 2025-12-25 10:44:37.166411289 +0000 +++ /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/temp.out 2025-12-25 10:46:53.996982550 +0000 @@ -1,568 +1 @@ --- --- TEMP --- Test temp relations and indexes --- --- test temp table/index masking -CREATE TABLE temptest(col int); -CREATE INDEX i_temptest ON temptest(col); -CREATE TEMP TABLE temptest(tcol int); -CREATE INDEX i_temptest ON temptest(tcol); -SELECT * FROM temptest; - tcol ------- -(0 rows) - -DROP INDEX i_temptest; -DROP TABLE temptest; -SELECT * FROM temptest; - col ------ -(0 rows) - -DROP INDEX i_temptest; -DROP TABLE temptest; --- test temp table selects -CREATE TABLE temptest(col int); -INSERT INTO temptest VALUES (1); -CREATE TEMP TABLE temptest(tcol float); -INSERT INTO temptest VALUES (2.1); -SELECT * FROM temptest; - tcol ------- - 2.1 -(1 row) - -DROP TABLE temptest; -SELECT * FROM temptest; - col ------ - 1 -(1 row) - -DROP TABLE temptest; --- test temp table deletion -CREATE TEMP TABLE temptest(col int); -\c -SELECT * FROM temptest; -ERROR: relation "temptest" does not exist -LINE 1: SELECT * FROM temptest; - ^ --- Test ON COMMIT DELETE ROWS -CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS; --- while we're here, verify successful truncation of index with SQL function -CREATE INDEX ON temptest(bit_length('')); -BEGIN; -INSERT INTO temptest VALUES (1); -INSERT INTO temptest VALUES (2); -SELECT * FROM temptest; - col ------ - 1 - 2 -(2 rows) - -COMMIT; -SELECT * FROM temptest; - col ------ -(0 rows) - -DROP TABLE temptest; -BEGIN; -CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; -SELECT * FROM temptest; - col ------ - 1 -(1 row) - -COMMIT; -SELECT * FROM temptest; - col ------ -(0 rows) - -DROP TABLE temptest; --- Test ON COMMIT DROP -BEGIN; -CREATE TEMP TABLE temptest(col int) ON COMMIT DROP; -INSERT INTO temptest VALUES (1); -INSERT INTO temptest VALUES (2); -SELECT * FROM temptest; - col ------ - 1 - 2 -(2 rows) - -COMMIT; -SELECT * FROM temptest; -ERROR: relation "temptest" does not exist -LINE 1: SELECT * FROM temptest; - ^ -BEGIN; -CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1; -SELECT * FROM temptest; - col ------ - 1 -(1 row) - -COMMIT; -SELECT * FROM temptest; -ERROR: relation "temptest" does not exist -LINE 1: SELECT * FROM temptest; - ^ --- Test it with a CHECK condition that produces a toasted pg_constraint entry -BEGIN; -do $$ -begin - execute format($cmd$ - CREATE TEMP TABLE temptest (col text CHECK (col < %L)) ON COMMIT DROP - $cmd$, - (SELECT string_agg(g.i::text || ':' || random()::text, '|') - FROM generate_series(1, 100) g(i))); -end$$; -SELECT * FROM temptest; - col ------ -(0 rows) - -COMMIT; -SELECT * FROM temptest; -ERROR: relation "temptest" does not exist -LINE 1: SELECT * FROM temptest; - ^ --- ON COMMIT is only allowed for TEMP -CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS; -ERROR: ON COMMIT can only be used on temporary tables -CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; -ERROR: ON COMMIT can only be used on temporary tables --- Test foreign keys -BEGIN; -CREATE TEMP TABLE temptest1(col int PRIMARY KEY); -CREATE TEMP TABLE temptest2(col int REFERENCES temptest1) - ON COMMIT DELETE ROWS; -INSERT INTO temptest1 VALUES (1); -INSERT INTO temptest2 VALUES (1); -COMMIT; -SELECT * FROM temptest1; - col ------ - 1 -(1 row) - -SELECT * FROM temptest2; - col ------ -(0 rows) - -BEGIN; -CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; -CREATE TEMP TABLE temptest4(col int REFERENCES temptest3); -COMMIT; -ERROR: unsupported ON COMMIT and foreign key combination -DETAIL: Table "temptest4" references "temptest3", but they do not have the same ON COMMIT setting. --- Test manipulation of temp schema's placement in search path -create table public.whereami (f1 text); -insert into public.whereami values ('public'); -create temp table whereami (f1 text); -insert into whereami values ('temp'); -create function public.whoami() returns text - as $$select 'public'::text$$ language sql; -create function pg_temp.whoami() returns text - as $$select 'temp'::text$$ language sql; --- default should have pg_temp implicitly first, but only for tables -select * from whereami; - f1 ------- - temp -(1 row) - -select whoami(); - whoami --------- - public -(1 row) - --- can list temp first explicitly, but it still doesn't affect functions -set search_path = pg_temp, public; -select * from whereami; - f1 ------- - temp -(1 row) - -select whoami(); - whoami --------- - public -(1 row) - --- or put it last for security -set search_path = public, pg_temp; -select * from whereami; - f1 --------- - public -(1 row) - -select whoami(); - whoami --------- - public -(1 row) - --- you can invoke a temp function explicitly, though -select pg_temp.whoami(); - whoami --------- - temp -(1 row) - -drop table public.whereami; --- types in temp schema -set search_path = pg_temp, public; -create domain pg_temp.nonempty as text check (value <> ''); --- function-syntax invocation of types matches rules for functions -select nonempty(''); -ERROR: function nonempty(unknown) does not exist -LINE 1: select nonempty(''); - ^ -DETAIL: There is no function of that name. -select pg_temp.nonempty(''); -ERROR: value for domain nonempty violates check constraint "nonempty_check" --- other syntax matches rules for tables -select ''::nonempty; -ERROR: value for domain nonempty violates check constraint "nonempty_check" -reset search_path; --- For partitioned temp tables, ON COMMIT actions ignore storage-less --- partitioned tables. -begin; -create temp table temp_parted_oncommit (a int) - partition by list (a) on commit delete rows; -create temp table temp_parted_oncommit_1 - partition of temp_parted_oncommit - for values in (1) on commit delete rows; -insert into temp_parted_oncommit values (1); -commit; --- partitions are emptied by the previous commit -select * from temp_parted_oncommit; - a ---- -(0 rows) - -drop table temp_parted_oncommit; --- Check dependencies between ON COMMIT actions with a partitioned --- table and its partitions. Using ON COMMIT DROP on a parent removes --- the whole set. -begin; -create temp table temp_parted_oncommit_test (a int) - partition by list (a) on commit drop; -create temp table temp_parted_oncommit_test1 - partition of temp_parted_oncommit_test - for values in (1) on commit delete rows; -create temp table temp_parted_oncommit_test2 - partition of temp_parted_oncommit_test - for values in (2) on commit drop; -insert into temp_parted_oncommit_test values (1), (2); -commit; --- no relations remain in this case. -select relname from pg_class where relname ~ '^temp_parted_oncommit_test'; - relname ---------- -(0 rows) - --- Using ON COMMIT DELETE on a partitioned table does not remove --- all rows if partitions preserve their data. -begin; -create temp table temp_parted_oncommit_test (a int) - partition by list (a) on commit delete rows; -create temp table temp_parted_oncommit_test1 - partition of temp_parted_oncommit_test - for values in (1) on commit preserve rows; -create temp table temp_parted_oncommit_test2 - partition of temp_parted_oncommit_test - for values in (2) on commit drop; -insert into temp_parted_oncommit_test values (1), (2); -commit; --- Data from the remaining partition is still here as its rows are --- preserved. -select * from temp_parted_oncommit_test; - a ---- - 1 -(1 row) - --- two relations remain in this case. -select relname from pg_class where relname ~ '^temp_parted_oncommit_test' - order by relname; - relname ----------------------------- - temp_parted_oncommit_test - temp_parted_oncommit_test1 -(2 rows) - -drop table temp_parted_oncommit_test; --- Check dependencies between ON COMMIT actions with inheritance trees. --- Using ON COMMIT DROP on a parent removes the whole set. -begin; -create temp table temp_inh_oncommit_test (a int) on commit drop; -create temp table temp_inh_oncommit_test1 () - inherits(temp_inh_oncommit_test) on commit delete rows; -insert into temp_inh_oncommit_test1 values (1); -commit; --- no relations remain in this case -select relname from pg_class where relname ~ '^temp_inh_oncommit_test'; - relname ---------- -(0 rows) - --- Data on the parent is removed, and the child goes away. -begin; -create temp table temp_inh_oncommit_test (a int) on commit delete rows; -create temp table temp_inh_oncommit_test1 () - inherits(temp_inh_oncommit_test) on commit drop; -insert into temp_inh_oncommit_test1 values (1); -insert into temp_inh_oncommit_test values (1); -commit; -select * from temp_inh_oncommit_test; - a ---- -(0 rows) - --- one relation remains -select relname from pg_class where relname ~ '^temp_inh_oncommit_test'; - relname ------------------------- - temp_inh_oncommit_test -(1 row) - -drop table temp_inh_oncommit_test; --- Tests with two-phase commit --- Transactions creating objects in a temporary namespace cannot be used --- with two-phase commit. --- These cases generate errors about temporary namespace. --- Function creation -begin; -create function pg_temp.twophase_func() returns void as - $$ select '2pc_func'::text $$ language sql; -prepare transaction 'twophase_func'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects --- Function drop -create function pg_temp.twophase_func() returns void as - $$ select '2pc_func'::text $$ language sql; -begin; -drop function pg_temp.twophase_func(); -prepare transaction 'twophase_func'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects --- Operator creation -begin; -create operator pg_temp.@@ (leftarg = int4, rightarg = int4, procedure = int4mi); -prepare transaction 'twophase_operator'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects --- These generate errors about temporary tables. -begin; -create type pg_temp.twophase_type as (a int); -prepare transaction 'twophase_type'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects -begin; -create view pg_temp.twophase_view as select 1; -prepare transaction 'twophase_view'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects -begin; -create sequence pg_temp.twophase_seq; -prepare transaction 'twophase_sequence'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects --- Temporary tables cannot be used with two-phase commit. -create temp table twophase_tab (a int); -begin; -select a from twophase_tab; - a ---- -(0 rows) - -prepare transaction 'twophase_tab'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects -begin; -insert into twophase_tab values (1); -prepare transaction 'twophase_tab'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects -begin; -lock twophase_tab in access exclusive mode; -prepare transaction 'twophase_tab'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects -begin; -drop table twophase_tab; -prepare transaction 'twophase_tab'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects --- Corner case: current_schema may create a temporary schema if namespace --- creation is pending, so check after that. First reset the connection --- to remove the temporary namespace. -\c - -SET search_path TO 'pg_temp'; -BEGIN; -SELECT current_schema() ~ 'pg_temp' AS is_temp_schema; - is_temp_schema ----------------- - t -(1 row) - -PREPARE TRANSACTION 'twophase_search'; -ERROR: cannot PREPARE a transaction that has operated on temporary objects --- Tests to verify we recover correctly from exhausting buffer pins and --- related matters. --- use lower possible buffer limit to make the test cheaper -\c -SET temp_buffers = 100; -CREATE TEMPORARY TABLE test_temp(a int not null unique, b TEXT not null, cnt int not null); -INSERT INTO test_temp SELECT generate_series(1, 10000) as id, repeat('a', 200), 0; --- should be at least 2x as large than temp_buffers -SELECT pg_relation_size('test_temp') / current_setting('block_size')::int8 > 200; - ?column? ----------- - t -(1 row) - --- Don't want cursor names and plpgsql function lines in the error messages -\set VERBOSITY terse -/* helper function to create cursors for each page in [p_start, p_end] */ -CREATE FUNCTION test_temp_pin(p_start int, p_end int) -RETURNS void -LANGUAGE plpgsql -AS $f$ - DECLARE - cursorname text; - query text; - BEGIN - FOR i IN p_start..p_end LOOP - cursorname = 'c_'||i; - query = format($q$DECLARE %I CURSOR FOR SELECT ctid FROM test_temp WHERE ctid >= '( %s, 1)'::tid $q$, cursorname, i); - EXECUTE query; - EXECUTE 'FETCH NEXT FROM '||cursorname; - -- for test development - -- RAISE NOTICE '%: %', cursorname, query; - END LOOP; - END; -$f$; --- Test overflow of temp table buffers is handled correctly -BEGIN; --- should work, below max -SELECT test_temp_pin(0, 9); - test_temp_pin ---------------- - -(1 row) - --- should fail, too many buffers pinned -SELECT test_temp_pin(10, 105); -ERROR: no empty local buffer available -ROLLBACK; -BEGIN; --- have some working cursors to test after errors -SELECT test_temp_pin(0, 9); - test_temp_pin ---------------- - -(1 row) - -FETCH NEXT FROM c_3; - ctid -------- - (3,2) -(1 row) - --- exhaust buffer pins in subtrans, check things work after -SAVEPOINT rescue_me; -SELECT test_temp_pin(10, 105); -ERROR: no empty local buffer available -ROLLBACK TO SAVEPOINT rescue_me; --- pre-subtrans cursors continue to work -FETCH NEXT FROM c_3; - ctid -------- - (3,3) -(1 row) - --- new cursors with pins can be created after subtrans rollback -SELECT test_temp_pin(10, 94); - test_temp_pin ---------------- - -(1 row) - --- Check that read streams deal with lower number of pins available -SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp; - count | max_a | min_a | max_cnt --------+-------+-------+--------- - 10000 | 10000 | 1 | 0 -(1 row) - -ROLLBACK; --- Check that temp tables with existing cursors can't be dropped. -BEGIN; -SELECT test_temp_pin(0, 1); - test_temp_pin ---------------- - -(1 row) - -DROP TABLE test_temp; -ERROR: cannot DROP TABLE "test_temp" because it is being used by active queries in this session -COMMIT; --- Check that temp tables with existing cursors can't be dropped. -BEGIN; -SELECT test_temp_pin(0, 1); - test_temp_pin ---------------- - -(1 row) - -TRUNCATE test_temp; -ERROR: cannot TRUNCATE "test_temp" because it is being used by active queries in this session -COMMIT; --- Check that temp tables that are dropped in transaction that's rolled back --- preserve buffer contents -SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp; - count | max_a | min_a | max_cnt --------+-------+-------+--------- - 10000 | 10000 | 1 | 0 -(1 row) - -INSERT INTO test_temp(a, b, cnt) VALUES (-1, '', 0); -BEGIN; -INSERT INTO test_temp(a, b, cnt) VALUES (-2, '', 0); -DROP TABLE test_temp; -ROLLBACK; -SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp; - count | max_a | min_a | max_cnt --------+-------+-------+--------- - 10001 | 10000 | -1 | 0 -(1 row) - --- Check that temp table drop is transactional and preserves dirty --- buffer contents -UPDATE test_temp SET cnt = cnt + 1 WHERE a = -1; -BEGIN; -DROP TABLE test_temp; -ROLLBACK; -SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp; - count | max_a | min_a | max_cnt --------+-------+-------+--------- - 10001 | 10000 | -1 | 1 -(1 row) - --- Check that temp table truncation is transactional and preserves dirty --- buffer contents -UPDATE test_temp SET cnt = cnt + 1 WHERE a = -1; -BEGIN; -TRUNCATE test_temp; -ROLLBACK; -SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp; - count | max_a | min_a | max_cnt --------+-------+-------+--------- - 10001 | 10000 | -1 | 2 -(1 row) - --- cleanup -DROP FUNCTION test_temp_pin(int, int); +psql: error: connection to server on socket "/tmp/2f2ycaowGU/.s.PGSQL.13722" failed: could not fork new process for connection: Resource temporarily unavailable diff -U3 /home/postgres/postgres/src/test/regress/expected/prepare.out /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/prepare.out --- /home/postgres/postgres/src/test/regress/expected/prepare.out 2025-12-25 10:44:37.095771435 +0000 +++ /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/prepare.out 2025-12-25 10:46:53.992903254 +0000 @@ -1,194 +1 @@ --- Regression tests for prepareable statements. We query the content --- of the pg_prepared_statements view as prepared statements are --- created and removed. -SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements; - name | statement | parameter_types | result_types -------+-----------+-----------------+-------------- -(0 rows) - -PREPARE q1 AS SELECT 1 AS a; -EXECUTE q1; - a ---- - 1 -(1 row) - -SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements; - name | statement | parameter_types | result_types -------+------------------------------+-----------------+-------------- - q1 | PREPARE q1 AS SELECT 1 AS a; | {} | {integer} -(1 row) - --- should fail -PREPARE q1 AS SELECT 2; -ERROR: prepared statement "q1" already exists --- should succeed -DEALLOCATE q1; -PREPARE q1 AS SELECT 2; -EXECUTE q1; - ?column? ----------- - 2 -(1 row) - -PREPARE q2 AS SELECT 2 AS b; -SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements; - name | statement | parameter_types | result_types -------+------------------------------+-----------------+-------------- - q1 | PREPARE q1 AS SELECT 2; | {} | {integer} - q2 | PREPARE q2 AS SELECT 2 AS b; | {} | {integer} -(2 rows) - --- sql92 syntax -DEALLOCATE PREPARE q1; -SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements; - name | statement | parameter_types | result_types -------+------------------------------+-----------------+-------------- - q2 | PREPARE q2 AS SELECT 2 AS b; | {} | {integer} -(1 row) - -DEALLOCATE PREPARE q2; --- the view should return the empty set again -SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements; - name | statement | parameter_types | result_types -------+-----------+-----------------+-------------- -(0 rows) - --- parameterized queries -PREPARE q2(text) AS - SELECT datname, datistemplate, datallowconn - FROM pg_database WHERE datname = $1; -EXECUTE q2('postgres'); - datname | datistemplate | datallowconn -----------+---------------+-------------- - postgres | f | t -(1 row) - -PREPARE q3(text, int, float, boolean, smallint) AS - SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR - ten = $3::bigint OR true = $4 OR odd = $5::int) - ORDER BY unique1; -EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 4::bigint); - unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ----------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- - 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx - 102 | 612 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 102 | 102 | 4 | 5 | YDAAAA | OXAAAA | AAAAxx - 802 | 2908 | 0 | 2 | 2 | 2 | 2 | 802 | 802 | 802 | 802 | 4 | 5 | WEAAAA | WHEAAA | AAAAxx - 902 | 1104 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 902 | 902 | 4 | 5 | SIAAAA | MQBAAA | AAAAxx - 1002 | 2580 | 0 | 2 | 2 | 2 | 2 | 2 | 1002 | 1002 | 1002 | 4 | 5 | OMAAAA | GVDAAA | AAAAxx - 1602 | 8148 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 1602 | 1602 | 4 | 5 | QJAAAA | KBMAAA | AAAAxx - 1702 | 7940 | 0 | 2 | 2 | 2 | 2 | 702 | 1702 | 1702 | 1702 | 4 | 5 | MNAAAA | KTLAAA | AAAAxx - 2102 | 6184 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 2102 | 2102 | 4 | 5 | WCAAAA | WDJAAA | AAAAxx - 2202 | 8028 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 2202 | 2202 | 4 | 5 | SGAAAA | UWLAAA | AAAAxx - 2302 | 7112 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 2302 | 2302 | 4 | 5 | OKAAAA | ONKAAA | AAAAxx - 2902 | 6816 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 2902 | 2902 | 4 | 5 | QHAAAA | ECKAAA | AAAAxx - 3202 | 7128 | 0 | 2 | 2 | 2 | 2 | 202 | 1202 | 3202 | 3202 | 4 | 5 | ETAAAA | EOKAAA | AAAAxx - 3902 | 9224 | 0 | 2 | 2 | 2 | 2 | 902 | 1902 | 3902 | 3902 | 4 | 5 | CUAAAA | UQNAAA | AAAAxx - 4102 | 7676 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 4102 | 4102 | 4 | 5 | UBAAAA | GJLAAA | AAAAxx - 4202 | 6628 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 4202 | 4202 | 4 | 5 | QFAAAA | YUJAAA | AAAAxx - 4502 | 412 | 0 | 2 | 2 | 2 | 2 | 502 | 502 | 4502 | 4502 | 4 | 5 | ERAAAA | WPAAAA | AAAAxx - 4702 | 2520 | 0 | 2 | 2 | 2 | 2 | 702 | 702 | 4702 | 4702 | 4 | 5 | WYAAAA | YSDAAA | AAAAxx - 4902 | 1600 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 4902 | 4902 | 4 | 5 | OGAAAA | OJCAAA | AAAAxx - 5602 | 8796 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 602 | 5602 | 4 | 5 | MHAAAA | IANAAA | AAAAxx - 6002 | 8932 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 1002 | 6002 | 4 | 5 | WWAAAA | OFNAAA | AAAAxx - 6402 | 3808 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 1402 | 6402 | 4 | 5 | GMAAAA | MQFAAA | AAAAxx - 7602 | 1040 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 2602 | 7602 | 4 | 5 | KGAAAA | AOBAAA | AAAAxx - 7802 | 7508 | 0 | 2 | 2 | 2 | 2 | 802 | 1802 | 2802 | 7802 | 4 | 5 | COAAAA | UCLAAA | AAAAxx - 8002 | 9980 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 3002 | 8002 | 4 | 5 | UVAAAA | WTOAAA | AAAAxx - 8302 | 7800 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 3302 | 8302 | 4 | 5 | IHAAAA | AOLAAA | AAAAxx - 8402 | 5708 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 3402 | 8402 | 4 | 5 | ELAAAA | OLIAAA | AAAAxx - 8602 | 5440 | 0 | 2 | 2 | 2 | 2 | 602 | 602 | 3602 | 8602 | 4 | 5 | WSAAAA | GBIAAA | AAAAxx - 9502 | 1812 | 0 | 2 | 2 | 2 | 2 | 502 | 1502 | 4502 | 9502 | 4 | 5 | MBAAAA | SRCAAA | AAAAxx - 9602 | 9972 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 4602 | 9602 | 4 | 5 | IFAAAA | OTOAAA | AAAAxx -(29 rows) - --- too few params -EXECUTE q3('bool'); -ERROR: wrong number of parameters for prepared statement "q3" -DETAIL: Expected 5 parameters but got 1. --- too many params -EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 4::bigint, true); -ERROR: wrong number of parameters for prepared statement "q3" -DETAIL: Expected 5 parameters but got 6. --- wrong param types -EXECUTE q3(5::smallint, 10.5::float, false, 4::bigint, 'bytea'); -ERROR: parameter $3 of type boolean cannot be coerced to the expected type double precision -LINE 1: EXECUTE q3(5::smallint, 10.5::float, false, 4::bigint, 'byte... - ^ -HINT: You will need to rewrite or cast the expression. --- invalid type -PREPARE q4(nonexistenttype) AS SELECT $1; -ERROR: type "nonexistenttype" does not exist -LINE 1: PREPARE q4(nonexistenttype) AS SELECT $1; - ^ --- create table as execute -PREPARE q5(int, text) AS - SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 - ORDER BY unique1; -CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA'); -SELECT * FROM q5_prep_results; - unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ----------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- - 200 | 9441 | 0 | 0 | 0 | 0 | 0 | 200 | 200 | 200 | 200 | 0 | 1 | SHAAAA | DZNAAA | HHHHxx - 497 | 9092 | 1 | 1 | 7 | 17 | 97 | 497 | 497 | 497 | 497 | 194 | 195 | DTAAAA | SLNAAA | AAAAxx - 1173 | 6699 | 1 | 1 | 3 | 13 | 73 | 173 | 1173 | 1173 | 1173 | 146 | 147 | DTAAAA | RXJAAA | VVVVxx - 1849 | 8143 | 1 | 1 | 9 | 9 | 49 | 849 | 1849 | 1849 | 1849 | 98 | 99 | DTAAAA | FBMAAA | VVVVxx - 2525 | 64 | 1 | 1 | 5 | 5 | 25 | 525 | 525 | 2525 | 2525 | 50 | 51 | DTAAAA | MCAAAA | AAAAxx - 3201 | 7309 | 1 | 1 | 1 | 1 | 1 | 201 | 1201 | 3201 | 3201 | 2 | 3 | DTAAAA | DVKAAA | HHHHxx - 3877 | 4060 | 1 | 1 | 7 | 17 | 77 | 877 | 1877 | 3877 | 3877 | 154 | 155 | DTAAAA | EAGAAA | AAAAxx - 4553 | 4113 | 1 | 1 | 3 | 13 | 53 | 553 | 553 | 4553 | 4553 | 106 | 107 | DTAAAA | FCGAAA | HHHHxx - 5229 | 6407 | 1 | 1 | 9 | 9 | 29 | 229 | 1229 | 229 | 5229 | 58 | 59 | DTAAAA | LMJAAA | VVVVxx - 5905 | 9537 | 1 | 1 | 5 | 5 | 5 | 905 | 1905 | 905 | 5905 | 10 | 11 | DTAAAA | VCOAAA | HHHHxx - 6581 | 4686 | 1 | 1 | 1 | 1 | 81 | 581 | 581 | 1581 | 6581 | 162 | 163 | DTAAAA | GYGAAA | OOOOxx - 7257 | 1895 | 1 | 1 | 7 | 17 | 57 | 257 | 1257 | 2257 | 7257 | 114 | 115 | DTAAAA | XUCAAA | VVVVxx - 7933 | 4514 | 1 | 1 | 3 | 13 | 33 | 933 | 1933 | 2933 | 7933 | 66 | 67 | DTAAAA | QRGAAA | OOOOxx - 8609 | 5918 | 1 | 1 | 9 | 9 | 9 | 609 | 609 | 3609 | 8609 | 18 | 19 | DTAAAA | QTIAAA | OOOOxx - 9285 | 8469 | 1 | 1 | 5 | 5 | 85 | 285 | 1285 | 4285 | 9285 | 170 | 171 | DTAAAA | TNMAAA | HHHHxx - 9961 | 2058 | 1 | 1 | 1 | 1 | 61 | 961 | 1961 | 4961 | 9961 | 122 | 123 | DTAAAA | EBDAAA | OOOOxx -(16 rows) - -CREATE TEMPORARY TABLE q5_prep_nodata AS EXECUTE q5(200, 'DTAAAA') - WITH NO DATA; -SELECT * FROM q5_prep_nodata; - unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ----------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- -(0 rows) - --- unknown or unspecified parameter types: should succeed -PREPARE q6 AS - SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; -PREPARE q7(unknown) AS - SELECT * FROM road WHERE thepath = $1; --- DML statements -PREPARE q8 AS - UPDATE tenk1 SET stringu1 = $2 WHERE unique1 = $1; -SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements - ORDER BY name; - name | statement | parameter_types | result_types -------+------------------------------------------------------------------+----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------- - q2 | PREPARE q2(text) AS +| {text} | {name,boolean,boolean} - | SELECT datname, datistemplate, datallowconn +| | - | FROM pg_database WHERE datname = $1; | | - q3 | PREPARE q3(text, int, float, boolean, smallint) AS +| {text,integer,"double precision",boolean,smallint} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name} - | SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR+| | - | ten = $3::bigint OR true = $4 OR odd = $5::int) +| | - | ORDER BY unique1; | | - q5 | PREPARE q5(int, text) AS +| {integer,text} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name} - | SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +| | - | ORDER BY unique1; | | - q6 | PREPARE q6 AS +| {integer,name} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name} - | SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | | - q7 | PREPARE q7(unknown) AS +| {path} | {text,path} - | SELECT * FROM road WHERE thepath = $1; | | - q8 | PREPARE q8 AS +| {integer,name} | - | UPDATE tenk1 SET stringu1 = $2 WHERE unique1 = $1; | | -(6 rows) - --- test DEALLOCATE ALL; -DEALLOCATE ALL; -SELECT name, statement, parameter_types FROM pg_prepared_statements - ORDER BY name; - name | statement | parameter_types -------+-----------+----------------- -(0 rows) - +psql: error: connection to server on socket "/tmp/2f2ycaowGU/.s.PGSQL.13722" failed: could not fork new process for connection: Resource temporarily unavailable diff -U3 /home/postgres/postgres/src/test/regress/expected/alter_table.out /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/alter_table.out --- /home/postgres/postgres/src/test/regress/expected/alter_table.out 2025-12-25 10:44:36.886924026 +0000 +++ /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/alter_table.out 2025-12-25 10:46:53.992135273 +0000 @@ -1,4842 +1 @@ --- --- ALTER_TABLE --- --- Clean up in case a prior regression run failed -SET client_min_messages TO 'warning'; -DROP ROLE IF EXISTS regress_alter_table_user1; -RESET client_min_messages; -CREATE USER regress_alter_table_user1; --- --- add attribute --- -CREATE TABLE attmp (initial int4); -COMMENT ON TABLE attmp_wrong IS 'table comment'; -ERROR: relation "attmp_wrong" does not exist -COMMENT ON TABLE attmp IS 'table comment'; -COMMENT ON TABLE attmp IS NULL; -ALTER TABLE attmp ADD COLUMN xmin integer; -- fails -ERROR: column name "xmin" conflicts with a system column name -ALTER TABLE attmp ADD COLUMN a int4 default 3; -ALTER TABLE attmp ADD COLUMN b name; -ALTER TABLE attmp ADD COLUMN c text; -ALTER TABLE attmp ADD COLUMN d float8; -ALTER TABLE attmp ADD COLUMN e float4; -ALTER TABLE attmp ADD COLUMN f int2; -ALTER TABLE attmp ADD COLUMN g polygon; -ALTER TABLE attmp ADD COLUMN i char; -ALTER TABLE attmp ADD COLUMN k int4; -ALTER TABLE attmp ADD COLUMN l tid; -ALTER TABLE attmp ADD COLUMN m xid; -ALTER TABLE attmp ADD COLUMN n oidvector; ---ALTER TABLE attmp ADD COLUMN o lock; -ALTER TABLE attmp ADD COLUMN p boolean; -ALTER TABLE attmp ADD COLUMN q point; -ALTER TABLE attmp ADD COLUMN r lseg; -ALTER TABLE attmp ADD COLUMN s path; -ALTER TABLE attmp ADD COLUMN t box; -ALTER TABLE attmp ADD COLUMN v timestamp; -ALTER TABLE attmp ADD COLUMN w interval; -ALTER TABLE attmp ADD COLUMN x float8[]; -ALTER TABLE attmp ADD COLUMN y float4[]; -ALTER TABLE attmp ADD COLUMN z int2[]; -INSERT INTO attmp (a, b, c, d, e, f, g, i, k, l, m, n, p, q, r, s, t, - v, w, x, y, z) - VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', - 'c', - 314159, '(1,1)', '512', - '1 2 3 4 5 6 7 8', true, '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', - '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', - 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); -SELECT * FROM attmp; - initial | a | b | c | d | e | f | g | i | k | l | m | n | p | q | r | s | t | v | w | x | y | z ----------+---+------+------+-----+-----+---+-----------------------+---+--------+-------+-----+-----------------+---+-----------+-----------------------+-----------------------------+---------------------+--------------------------+------------------+-----------+-----------+----------- - | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | c | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | t | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4} -(1 row) - -DROP TABLE attmp; --- the wolf bug - schema mods caused inconsistent row descriptors -CREATE TABLE attmp ( - initial int4 -); -ALTER TABLE attmp ADD COLUMN a int4; -ALTER TABLE attmp ADD COLUMN b name; -ALTER TABLE attmp ADD COLUMN c text; -ALTER TABLE attmp ADD COLUMN d float8; -ALTER TABLE attmp ADD COLUMN e float4; -ALTER TABLE attmp ADD COLUMN f int2; -ALTER TABLE attmp ADD COLUMN g polygon; -ALTER TABLE attmp ADD COLUMN i char; -ALTER TABLE attmp ADD COLUMN k int4; -ALTER TABLE attmp ADD COLUMN l tid; -ALTER TABLE attmp ADD COLUMN m xid; -ALTER TABLE attmp ADD COLUMN n oidvector; ---ALTER TABLE attmp ADD COLUMN o lock; -ALTER TABLE attmp ADD COLUMN p boolean; -ALTER TABLE attmp ADD COLUMN q point; -ALTER TABLE attmp ADD COLUMN r lseg; -ALTER TABLE attmp ADD COLUMN s path; -ALTER TABLE attmp ADD COLUMN t box; -ALTER TABLE attmp ADD COLUMN v timestamp; -ALTER TABLE attmp ADD COLUMN w interval; -ALTER TABLE attmp ADD COLUMN x float8[]; -ALTER TABLE attmp ADD COLUMN y float4[]; -ALTER TABLE attmp ADD COLUMN z int2[]; -INSERT INTO attmp (a, b, c, d, e, f, g, i, k, l, m, n, p, q, r, s, t, - v, w, x, y, z) - VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', - 'c', - 314159, '(1,1)', '512', - '1 2 3 4 5 6 7 8', true, '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', - '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', - 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); -SELECT * FROM attmp; - initial | a | b | c | d | e | f | g | i | k | l | m | n | p | q | r | s | t | v | w | x | y | z ----------+---+------+------+-----+-----+---+-----------------------+---+--------+-------+-----+-----------------+---+-----------+-----------------------+-----------------------------+---------------------+--------------------------+------------------+-----------+-----------+----------- - | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | c | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | t | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4} -(1 row) - -CREATE INDEX attmp_idx ON attmp (a, (d + e), b); -ALTER INDEX attmp_idx ALTER COLUMN 0 SET STATISTICS 1000; -ERROR: column number must be in range from 1 to 32767 -LINE 1: ALTER INDEX attmp_idx ALTER COLUMN 0 SET STATISTICS 1000; - ^ -ALTER INDEX attmp_idx ALTER COLUMN 1 SET STATISTICS 1000; -ERROR: cannot alter statistics on non-expression column "a" of index "attmp_idx" -HINT: Alter statistics on table column instead. -ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS 1000; -\d+ attmp_idx - Index "public.attmp_idx" - Column | Type | Key? | Definition | Storage | Stats target ---------+------------------+------+------------+---------+-------------- - a | integer | yes | a | plain | - expr | double precision | yes | (d + e) | plain | 1000 - b | cstring | yes | b | plain | -btree, for table "public.attmp" - -ALTER INDEX attmp_idx ALTER COLUMN 3 SET STATISTICS 1000; -ERROR: cannot alter statistics on non-expression column "b" of index "attmp_idx" -HINT: Alter statistics on table column instead. -ALTER INDEX attmp_idx ALTER COLUMN 4 SET STATISTICS 1000; -ERROR: column number 4 of relation "attmp_idx" does not exist -ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS -1; -DROP TABLE attmp; --- --- rename - check on both non-temp and temp tables --- -CREATE TABLE attmp (regtable int); -CREATE TEMP TABLE attmp (attmptable int); -ALTER TABLE attmp RENAME TO attmp_new; -SELECT * FROM attmp; - regtable ----------- -(0 rows) - -SELECT * FROM attmp_new; - attmptable ------------- -(0 rows) - -ALTER TABLE attmp RENAME TO attmp_new2; -SELECT * FROM attmp; -- should fail -ERROR: relation "attmp" does not exist -LINE 1: SELECT * FROM attmp; - ^ -SELECT * FROM attmp_new; - attmptable ------------- -(0 rows) - -SELECT * FROM attmp_new2; - regtable ----------- -(0 rows) - -DROP TABLE attmp_new; -DROP TABLE attmp_new2; --- check rename of partitioned tables and indexes also -CREATE TABLE part_attmp (a int primary key) partition by range (a); -CREATE TABLE part_attmp1 PARTITION OF part_attmp FOR VALUES FROM (0) TO (100); -ALTER INDEX part_attmp_pkey RENAME TO part_attmp_index; -ALTER INDEX part_attmp1_pkey RENAME TO part_attmp1_index; -ALTER TABLE part_attmp RENAME TO part_at2tmp; -ALTER TABLE part_attmp1 RENAME TO part_at2tmp1; -SET ROLE regress_alter_table_user1; -ALTER INDEX part_attmp_index RENAME TO fail; -ERROR: must be owner of index part_attmp_index -ALTER INDEX part_attmp1_index RENAME TO fail; -ERROR: must be owner of index part_attmp1_index -ALTER TABLE part_at2tmp RENAME TO fail; -ERROR: must be owner of table part_at2tmp -ALTER TABLE part_at2tmp1 RENAME TO fail; -ERROR: must be owner of table part_at2tmp1 -RESET ROLE; -DROP TABLE part_at2tmp; --- --- check renaming to a table's array type's autogenerated name --- (the array type's name should get out of the way) --- -CREATE TABLE attmp_array (id int); -CREATE TABLE attmp_array2 (id int); -SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype; - typname --------------- - _attmp_array -(1 row) - -SELECT typname FROM pg_type WHERE oid = 'attmp_array2[]'::regtype; - typname ---------------- - _attmp_array2 -(1 row) - -ALTER TABLE attmp_array2 RENAME TO _attmp_array; -SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype; - typname ---------------- - __attmp_array -(1 row) - -SELECT typname FROM pg_type WHERE oid = '_attmp_array[]'::regtype; - typname ------------------ - __attmp_array_1 -(1 row) - -DROP TABLE _attmp_array; -DROP TABLE attmp_array; --- renaming to table's own array type's name is an interesting corner case -CREATE TABLE attmp_array (id int); -SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype; - typname --------------- - _attmp_array -(1 row) - -ALTER TABLE attmp_array RENAME TO _attmp_array; -SELECT typname FROM pg_type WHERE oid = '_attmp_array[]'::regtype; - typname ---------------- - __attmp_array -(1 row) - -DROP TABLE _attmp_array; --- ALTER TABLE ... RENAME on non-table relations --- renaming indexes (FIXME: this should probably test the index's functionality) -ALTER INDEX IF EXISTS __onek_unique1 RENAME TO attmp_onek_unique1; -NOTICE: relation "__onek_unique1" does not exist, skipping -ALTER INDEX IF EXISTS __attmp_onek_unique1 RENAME TO onek_unique1; -NOTICE: relation "__attmp_onek_unique1" does not exist, skipping -ALTER INDEX onek_unique1 RENAME TO attmp_onek_unique1; -ALTER INDEX attmp_onek_unique1 RENAME TO onek_unique1; -SET ROLE regress_alter_table_user1; -ALTER INDEX onek_unique1 RENAME TO fail; -- permission denied -ERROR: must be owner of index onek_unique1 -RESET ROLE; --- rename statements with mismatching statement and object types -CREATE TABLE alter_idx_rename_test (a INT); -CREATE INDEX alter_idx_rename_test_idx ON alter_idx_rename_test (a); -CREATE TABLE alter_idx_rename_test_parted (a INT) PARTITION BY LIST (a); -CREATE INDEX alter_idx_rename_test_parted_idx ON alter_idx_rename_test_parted (a); -BEGIN; -ALTER INDEX alter_idx_rename_test RENAME TO alter_idx_rename_test_2; -ALTER INDEX alter_idx_rename_test_parted RENAME TO alter_idx_rename_test_parted_2; -SELECT relation::regclass, mode FROM pg_locks -WHERE pid = pg_backend_pid() AND locktype = 'relation' - AND relation::regclass::text LIKE 'alter\_idx%' -ORDER BY relation::regclass::text COLLATE "C"; - relation | mode ---------------------------------+--------------------- - alter_idx_rename_test_2 | AccessExclusiveLock - alter_idx_rename_test_parted_2 | AccessExclusiveLock -(2 rows) - -COMMIT; -BEGIN; -ALTER INDEX alter_idx_rename_test_idx RENAME TO alter_idx_rename_test_idx_2; -ALTER INDEX alter_idx_rename_test_parted_idx RENAME TO alter_idx_rename_test_parted_idx_2; -SELECT relation::regclass, mode FROM pg_locks -WHERE pid = pg_backend_pid() AND locktype = 'relation' - AND relation::regclass::text LIKE 'alter\_idx%' -ORDER BY relation::regclass::text COLLATE "C"; - relation | mode -------------------------------------+-------------------------- - alter_idx_rename_test_idx_2 | ShareUpdateExclusiveLock - alter_idx_rename_test_parted_idx_2 | ShareUpdateExclusiveLock -(2 rows) - -COMMIT; -BEGIN; -ALTER TABLE alter_idx_rename_test_idx_2 RENAME TO alter_idx_rename_test_idx_3; -ALTER TABLE alter_idx_rename_test_parted_idx_2 RENAME TO alter_idx_rename_test_parted_idx_3; -SELECT relation::regclass, mode FROM pg_locks -WHERE pid = pg_backend_pid() AND locktype = 'relation' - AND relation::regclass::text LIKE 'alter\_idx%' -ORDER BY relation::regclass::text COLLATE "C"; - relation | mode -------------------------------------+--------------------- - alter_idx_rename_test_idx_3 | AccessExclusiveLock - alter_idx_rename_test_parted_idx_3 | AccessExclusiveLock -(2 rows) - -COMMIT; -DROP TABLE alter_idx_rename_test_2; --- renaming views -CREATE VIEW attmp_view (unique1) AS SELECT unique1 FROM tenk1; -ALTER TABLE attmp_view RENAME TO attmp_view_new; -SET ROLE regress_alter_table_user1; -ALTER VIEW attmp_view_new RENAME TO fail; -- permission denied -ERROR: must be owner of view attmp_view_new -RESET ROLE; --- hack to ensure we get an indexscan here -set enable_seqscan to off; -set enable_bitmapscan to off; --- 5 values, sorted -SELECT unique1 FROM tenk1 WHERE unique1 < 5; - unique1 ---------- - 0 - 1 - 2 - 3 - 4 -(5 rows) - -reset enable_seqscan; -reset enable_bitmapscan; -DROP VIEW attmp_view_new; --- toast-like relation name -alter table stud_emp rename to pg_toast_stud_emp; -alter table pg_toast_stud_emp rename to stud_emp; --- renaming index should rename constraint as well -ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1); -ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo; -ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo; --- renaming constraint -ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0); -ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo; -ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo; --- renaming constraint should rename index as well -ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1); -DROP INDEX onek_unique1_constraint; -- to see whether it's there -ERROR: cannot drop index onek_unique1_constraint because constraint onek_unique1_constraint on table onek requires it -HINT: You can drop constraint onek_unique1_constraint on table onek instead. -ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo; -DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there -ERROR: cannot drop index onek_unique1_constraint_foo because constraint onek_unique1_constraint_foo on table onek requires it -HINT: You can drop constraint onek_unique1_constraint_foo on table onek instead. -ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo; --- renaming constraints vs. inheritance -CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int); -\d constraint_rename_test - Table "public.constraint_rename_test" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | - c | integer | | | -Check constraints: - "con1" CHECK (a > 0) - -CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test); -NOTICE: merging column "a" with inherited definition -NOTICE: merging constraint "con1" with inherited definition -\d constraint_rename_test2 - Table "public.constraint_rename_test2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | - c | integer | | | - d | integer | | | -Check constraints: - "con1" CHECK (a > 0) -Inherits: constraint_rename_test - -ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail -ERROR: cannot rename inherited constraint "con1" -ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail -ERROR: inherited constraint "con1" must be renamed in child tables too -ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok -\d constraint_rename_test - Table "public.constraint_rename_test" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | - c | integer | | | -Check constraints: - "con1foo" CHECK (a > 0) -Number of child tables: 1 (Use \d+ to list them.) - -\d constraint_rename_test2 - Table "public.constraint_rename_test2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | - c | integer | | | - d | integer | | | -Check constraints: - "con1foo" CHECK (a > 0) -Inherits: constraint_rename_test - -ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT; -ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok -ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok -\d constraint_rename_test - Table "public.constraint_rename_test" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | - c | integer | | | -Check constraints: - "con1foo" CHECK (a > 0) - "con2bar" CHECK (b > 0) NO INHERIT -Number of child tables: 1 (Use \d+ to list them.) - -\d constraint_rename_test2 - Table "public.constraint_rename_test2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | - c | integer | | | - d | integer | | | -Check constraints: - "con1foo" CHECK (a > 0) -Inherits: constraint_rename_test - -ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a); -ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok -\d constraint_rename_test - Table "public.constraint_rename_test" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | not null | - b | integer | | | - c | integer | | | -Indexes: - "con3foo" PRIMARY KEY, btree (a) -Check constraints: - "con1foo" CHECK (a > 0) - "con2bar" CHECK (b > 0) NO INHERIT -Number of child tables: 1 (Use \d+ to list them.) - -\d constraint_rename_test2 - Table "public.constraint_rename_test2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | not null | - b | integer | | | - c | integer | | | - d | integer | | | -Check constraints: - "con1foo" CHECK (a > 0) -Inherits: constraint_rename_test - -DROP TABLE constraint_rename_test2; -DROP TABLE constraint_rename_test; -ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok -NOTICE: relation "constraint_not_exist" does not exist, skipping -ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a); -NOTICE: relation "constraint_rename_test" does not exist, skipping --- renaming constraints with cache reset of target relation -CREATE TABLE constraint_rename_cache (a int, - CONSTRAINT chk_a CHECK (a > 0), - PRIMARY KEY (a)); -ALTER TABLE constraint_rename_cache - RENAME CONSTRAINT chk_a TO chk_a_new; -ALTER TABLE constraint_rename_cache - RENAME CONSTRAINT constraint_rename_cache_pkey TO constraint_rename_pkey_new; -CREATE TABLE like_constraint_rename_cache - (LIKE constraint_rename_cache INCLUDING ALL); -\d like_constraint_rename_cache - Table "public.like_constraint_rename_cache" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | not null | -Indexes: - "like_constraint_rename_cache_pkey" PRIMARY KEY, btree (a) -Check constraints: - "chk_a_new" CHECK (a > 0) - -DROP TABLE constraint_rename_cache; -DROP TABLE like_constraint_rename_cache; --- FOREIGN KEY CONSTRAINT adding TEST -CREATE TABLE attmp2 (a int primary key); -CREATE TABLE attmp3 (a int, b int); -CREATE TABLE attmp4 (a int, b int, unique(a,b)); -CREATE TABLE attmp5 (a int, b int); --- Insert rows into attmp2 (pktable) -INSERT INTO attmp2 values (1); -INSERT INTO attmp2 values (2); -INSERT INTO attmp2 values (3); -INSERT INTO attmp2 values (4); --- Insert rows into attmp3 -INSERT INTO attmp3 values (1,10); -INSERT INTO attmp3 values (1,20); -INSERT INTO attmp3 values (5,50); --- Try (and fail) to add constraint due to invalid source columns -ALTER TABLE attmp3 add constraint attmpconstr foreign key(c) references attmp2 match full; -ERROR: column "c" referenced in foreign key constraint does not exist --- Try (and fail) to add constraint due to invalid destination columns explicitly given -ALTER TABLE attmp3 add constraint attmpconstr foreign key(a) references attmp2(b) match full; -ERROR: column "b" referenced in foreign key constraint does not exist --- Try (and fail) to add constraint due to invalid data -ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full; -ERROR: insert or update on table "attmp3" violates foreign key constraint "attmpconstr" -DETAIL: Key (a)=(5) is not present in table "attmp2". --- Delete failing row -DELETE FROM attmp3 where a=5; --- Try (and succeed) -ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full; -ALTER TABLE attmp3 drop constraint attmpconstr; -INSERT INTO attmp3 values (5,50); --- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate -ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full NOT VALID; -ALTER TABLE attmp3 validate constraint attmpconstr; -ERROR: insert or update on table "attmp3" violates foreign key constraint "attmpconstr" -DETAIL: Key (a)=(5) is not present in table "attmp2". --- Delete failing row -DELETE FROM attmp3 where a=5; --- Try (and succeed) and repeat to show it works on already valid constraint -ALTER TABLE attmp3 validate constraint attmpconstr; -ALTER TABLE attmp3 validate constraint attmpconstr; --- Try a non-verified CHECK constraint -ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail -ERROR: check constraint "b_greater_than_ten" of relation "attmp3" is violated by some row -ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds -ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten_not_enforced CHECK (b > 10) NOT ENFORCED; -- succeeds -ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails -ERROR: check constraint "b_greater_than_ten" of relation "attmp3" is violated by some row -DELETE FROM attmp3 WHERE NOT b > 10; -ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds -ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds -ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten_not_enforced; -- fail -ERROR: cannot validate NOT ENFORCED constraint --- Test inherited NOT VALID CHECK constraints -select * from attmp3; - a | b ----+---- - 1 | 20 -(1 row) - -CREATE TABLE attmp6 () INHERITS (attmp3); -CREATE TABLE attmp7 () INHERITS (attmp3); -INSERT INTO attmp6 VALUES (6, 30), (7, 16); -ALTER TABLE attmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID; -ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- fails -ERROR: check constraint "b_le_20" of relation "attmp6" is violated by some row -DELETE FROM attmp6 WHERE b > 20; -ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds --- An already validated constraint must not be revalidated -CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$; -INSERT INTO attmp7 VALUES (8, 18); -ALTER TABLE attmp7 ADD CONSTRAINT identity CHECK (b = boo(b)); -NOTICE: boo: 18 -ALTER TABLE attmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID; -NOTICE: merging constraint "identity" with inherited definition -ALTER TABLE attmp3 VALIDATE CONSTRAINT identity; -NOTICE: boo: 20 -NOTICE: boo: 16 --- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT -create table parent_noinh_convalid (a int); -create table child_noinh_convalid () inherits (parent_noinh_convalid); -insert into parent_noinh_convalid values (1); -insert into child_noinh_convalid values (1); -alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid; --- fail, because of the row in parent -alter table parent_noinh_convalid validate constraint check_a_is_2; -ERROR: check constraint "check_a_is_2" of relation "parent_noinh_convalid" is violated by some row -delete from only parent_noinh_convalid; --- ok (parent itself contains no violating rows) -alter table parent_noinh_convalid validate constraint check_a_is_2; -select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2'; - convalidated --------------- - t -(1 row) - --- cleanup -drop table parent_noinh_convalid, child_noinh_convalid; --- Try (and fail) to create constraint from attmp5(a) to attmp4(a) - unique constraint on --- attmp4 is a,b -ALTER TABLE attmp5 add constraint attmpconstr foreign key(a) references attmp4(a) match full; -ERROR: there is no unique constraint matching given keys for referenced table "attmp4" -DROP TABLE attmp7; -DROP TABLE attmp6; -DROP TABLE attmp5; -DROP TABLE attmp4; -DROP TABLE attmp3; -DROP TABLE attmp2; --- NOT VALID with plan invalidation -- ensure we don't use a constraint for --- exclusion until validated -set constraint_exclusion TO 'partition'; -create table nv_parent (d date, check (false) no inherit not valid); --- not valid constraint added at creation time should automatically become valid -\d nv_parent - Table "public.nv_parent" - Column | Type | Collation | Nullable | Default ---------+------+-----------+----------+--------- - d | date | | | -Check constraints: - "nv_parent_check" CHECK (false) NO INHERIT - -create table nv_child_2010 () inherits (nv_parent); -create table nv_child_2011 () inherits (nv_parent); -alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid; -alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid; -explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31'; - QUERY PLAN ---------------------------------------------------------------------------- - Append - -> Seq Scan on nv_parent nv_parent_1 - Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) - -> Seq Scan on nv_child_2010 nv_parent_2 - Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) - -> Seq Scan on nv_child_2011 nv_parent_3 - Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) -(7 rows) - -create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent); -explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date; - QUERY PLAN ---------------------------------------------------------------------------- - Append - -> Seq Scan on nv_parent nv_parent_1 - Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) - -> Seq Scan on nv_child_2010 nv_parent_2 - Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) - -> Seq Scan on nv_child_2011 nv_parent_3 - Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) -(7 rows) - -explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date; - QUERY PLAN ---------------------------------------------------------------------------- - Append - -> Seq Scan on nv_parent nv_parent_1 - Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) - -> Seq Scan on nv_child_2010 nv_parent_2 - Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) - -> Seq Scan on nv_child_2011 nv_parent_3 - Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) - -> Seq Scan on nv_child_2009 nv_parent_4 - Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) -(9 rows) - --- after validation, the constraint should be used -alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check; -explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date; - QUERY PLAN ---------------------------------------------------------------------------- - Append - -> Seq Scan on nv_parent nv_parent_1 - Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) - -> Seq Scan on nv_child_2010 nv_parent_2 - Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) - -> Seq Scan on nv_child_2009 nv_parent_3 - Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) -(7 rows) - --- add an inherited NOT VALID constraint -alter table nv_parent add check (d between '2001-01-01'::date and '2099-12-31'::date) not valid; -\d nv_child_2009 - Table "public.nv_child_2009" - Column | Type | Collation | Nullable | Default ---------+------+-----------+----------+--------- - d | date | | | -Check constraints: - "nv_child_2009_d_check" CHECK (d >= '01-01-2009'::date AND d <= '12-31-2009'::date) - "nv_parent_d_check" CHECK (d >= '01-01-2001'::date AND d <= '12-31-2099'::date) NOT VALID -Inherits: nv_parent - --- we leave nv_parent and children around to help test pg_dump logic --- Foreign key adding test with mixed types --- Note: these tables are TEMP to avoid name conflicts when this test --- is run in parallel with foreign_key.sql. -CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY); -INSERT INTO PKTABLE VALUES(42); -CREATE TEMP TABLE FKTABLE (ftest1 inet); --- This next should fail, because int=inet does not exist -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; -ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. --- This should also fail for the same reason, but here we --- give the column name -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); -ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -DROP TABLE FKTABLE; --- This should succeed, even though they are different types, --- because int=int8 exists and is a member of the integer opfamily -CREATE TEMP TABLE FKTABLE (ftest1 int8); -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; --- Check it actually works -INSERT INTO FKTABLE VALUES(42); -- should succeed -INSERT INTO FKTABLE VALUES(43); -- should fail -ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" -DETAIL: Key (ftest1)=(43) is not present in table "pktable". -DROP TABLE FKTABLE; --- This should fail, because we'd have to cast numeric to int which is --- not an implicit coercion (or use numeric=numeric, but that's not part --- of the integer opfamily) -CREATE TEMP TABLE FKTABLE (ftest1 numeric); -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; -ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: numeric and integer. -DROP TABLE FKTABLE; -DROP TABLE PKTABLE; --- On the other hand, this should work because int implicitly promotes to --- numeric, and we allow promotion on the FK side -CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY); -INSERT INTO PKTABLE VALUES(42); -CREATE TEMP TABLE FKTABLE (ftest1 int); -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; --- Check it actually works -INSERT INTO FKTABLE VALUES(42); -- should succeed -INSERT INTO FKTABLE VALUES(43); -- should fail -ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" -DETAIL: Key (ftest1)=(43) is not present in table "pktable". -DROP TABLE FKTABLE; -DROP TABLE PKTABLE; -CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, - PRIMARY KEY(ptest1, ptest2)); --- This should fail, because we just chose really odd types -CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; -ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: cidr and integer. -DROP TABLE FKTABLE; --- Again, so should this... -CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) - references pktable(ptest1, ptest2); -ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: cidr and integer. -DROP TABLE FKTABLE; --- This fails because we mixed up the column ordering -CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet); -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) - references pktable(ptest2, ptest1); -ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" of the referencing table and "ptest2" of the referenced table are of incompatible types: integer and inet. --- As does this... -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) - references pktable(ptest1, ptest2); -ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest2" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -DROP TABLE FKTABLE; -DROP TABLE PKTABLE; --- Test that ALTER CONSTRAINT updates trigger deferrability properly -CREATE TEMP TABLE PKTABLE (ptest1 int primary key); -CREATE TEMP TABLE FKTABLE (ftest1 int); -ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable - ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; -ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable - ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable - ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE; -ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable - ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE; -ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable - ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; -ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable - ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; -ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE; -SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred -FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint -WHERE tgrelid = 'pktable'::regclass -ORDER BY 1,2,3; - conname | tgfoid | tgtype | tgdeferrable | tginitdeferred ----------+------------------------+--------+--------------+---------------- - fkdd | "RI_FKey_cascade_del" | 9 | f | f - fkdd | "RI_FKey_noaction_upd" | 17 | t | t - fkdd2 | "RI_FKey_cascade_del" | 9 | f | f - fkdd2 | "RI_FKey_noaction_upd" | 17 | t | t - fkdi | "RI_FKey_cascade_del" | 9 | f | f - fkdi | "RI_FKey_noaction_upd" | 17 | t | f - fkdi2 | "RI_FKey_cascade_del" | 9 | f | f - fkdi2 | "RI_FKey_noaction_upd" | 17 | t | f - fknd | "RI_FKey_cascade_del" | 9 | f | f - fknd | "RI_FKey_noaction_upd" | 17 | f | f - fknd2 | "RI_FKey_cascade_del" | 9 | f | f - fknd2 | "RI_FKey_noaction_upd" | 17 | f | f -(12 rows) - -SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred -FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint -WHERE tgrelid = 'fktable'::regclass -ORDER BY 1,2,3; - conname | tgfoid | tgtype | tgdeferrable | tginitdeferred ----------+---------------------+--------+--------------+---------------- - fkdd | "RI_FKey_check_ins" | 5 | t | t - fkdd | "RI_FKey_check_upd" | 17 | t | t - fkdd2 | "RI_FKey_check_ins" | 5 | t | t - fkdd2 | "RI_FKey_check_upd" | 17 | t | t - fkdi | "RI_FKey_check_ins" | 5 | t | f - fkdi | "RI_FKey_check_upd" | 17 | t | f - fkdi2 | "RI_FKey_check_ins" | 5 | t | f - fkdi2 | "RI_FKey_check_upd" | 17 | t | f - fknd | "RI_FKey_check_ins" | 5 | f | f - fknd | "RI_FKey_check_upd" | 17 | f | f - fknd2 | "RI_FKey_check_ins" | 5 | f | f - fknd2 | "RI_FKey_check_upd" | 17 | f | f -(12 rows) - --- temp tables should go away by themselves, need not drop them. --- test check constraint adding -create table atacc1 ( test int ); --- add a check constraint -alter table atacc1 add constraint atacc_test1 check (test>3); --- should fail -insert into atacc1 (test) values (2); -ERROR: new row for relation "atacc1" violates check constraint "atacc_test1" -DETAIL: Failing row contains (2). --- should succeed -insert into atacc1 (test) values (4); -drop table atacc1; --- let's do one where the check fails when added -create table atacc1 ( test int ); --- insert a soon to be failing row -insert into atacc1 (test) values (2); --- add a check constraint (fails) -alter table atacc1 add constraint atacc_test1 check (test>3); -ERROR: check constraint "atacc_test1" of relation "atacc1" is violated by some row -insert into atacc1 (test) values (4); -drop table atacc1; --- let's do one where the check fails because the column doesn't exist -create table atacc1 ( test int ); --- add a check constraint (fails) -alter table atacc1 add constraint atacc_test1 check (test1>3); -ERROR: column "test1" does not exist -HINT: Perhaps you meant to reference the column "atacc1.test". -drop table atacc1; --- something a little more complicated -create table atacc1 ( test int, test2 int, test3 int); --- add a check constraint (fails) -alter table atacc1 add constraint atacc_test1 check (test+test23), test2 int); -alter table atacc1 add check (test2>test); --- should fail for $2 -insert into atacc1 (test2, test) values (3, 4); -ERROR: new row for relation "atacc1" violates check constraint "atacc1_check" -DETAIL: Failing row contains (4, 3). -drop table atacc1; --- inheritance related tests -create table atacc1 (test int); -create table atacc2 (test2 int); -create table atacc3 (test3 int) inherits (atacc1, atacc2); -alter table atacc2 add constraint foo check (test2>0); --- fail and then succeed on atacc2 -insert into atacc2 (test2) values (-3); -ERROR: new row for relation "atacc2" violates check constraint "foo" -DETAIL: Failing row contains (-3). -insert into atacc2 (test2) values (3); --- fail and then succeed on atacc3 -insert into atacc3 (test2) values (-3); -ERROR: new row for relation "atacc3" violates check constraint "foo" -DETAIL: Failing row contains (null, -3, null). -insert into atacc3 (test2) values (3); -drop table atacc3; -drop table atacc2; -drop table atacc1; --- same things with one created with INHERIT -create table atacc1 (test int); -create table atacc2 (test2 int); -create table atacc3 (test3 int) inherits (atacc1, atacc2); -alter table atacc3 no inherit atacc2; --- fail -alter table atacc3 no inherit atacc2; -ERROR: relation "atacc2" is not a parent of relation "atacc3" --- make sure it really isn't a child -insert into atacc3 (test2) values (3); -select test2 from atacc2; - test2 -------- -(0 rows) - --- fail due to missing constraint -alter table atacc2 add constraint foo check (test2>0); -alter table atacc3 inherit atacc2; -ERROR: child table is missing constraint "foo" --- fail due to missing column -alter table atacc3 rename test2 to testx; -alter table atacc3 inherit atacc2; -ERROR: child table is missing column "test2" --- fail due to mismatched data type -alter table atacc3 add test2 bool; -alter table atacc3 inherit atacc2; -ERROR: child table "atacc3" has different type for column "test2" -alter table atacc3 drop test2; --- succeed -alter table atacc3 add test2 int; -update atacc3 set test2 = 4 where test2 is null; -alter table atacc3 add constraint foo check (test2>0); -alter table atacc3 inherit atacc2; --- fail due to duplicates and circular inheritance -alter table atacc3 inherit atacc2; -ERROR: relation "atacc2" would be inherited from more than once -alter table atacc2 inherit atacc3; -ERROR: circular inheritance not allowed -DETAIL: "atacc3" is already a child of "atacc2". -alter table atacc2 inherit atacc2; -ERROR: circular inheritance not allowed -DETAIL: "atacc2" is already a child of "atacc2". --- test that we really are a child now (should see 4 not 3 and cascade should go through) -select test2 from atacc2; - test2 -------- - 4 -(1 row) - -drop table atacc2 cascade; -NOTICE: drop cascades to table atacc3 -drop table atacc1; --- adding only to a parent is allowed as of 9.2 -create table atacc1 (test int); -create table atacc2 (test2 int) inherits (atacc1); --- ok: -alter table atacc1 add constraint foo check (test>0) no inherit; --- check constraint is not there on child -insert into atacc2 (test) values (-3); --- check constraint is there on parent -insert into atacc1 (test) values (-3); -ERROR: new row for relation "atacc1" violates check constraint "foo" -DETAIL: Failing row contains (-3). -insert into atacc1 (test) values (3); --- fail, violating row: -alter table atacc2 add constraint foo check (test>0) no inherit; -ERROR: check constraint "foo" of relation "atacc2" is violated by some row -drop table atacc2; -drop table atacc1; --- test unique constraint adding -create table atacc1 ( test int ) ; --- add a unique constraint -alter table atacc1 add constraint atacc_test1 unique (test); --- insert first value -insert into atacc1 (test) values (2); --- should fail -insert into atacc1 (test) values (2); -ERROR: duplicate key value violates unique constraint "atacc_test1" -DETAIL: Key (test)=(2) already exists. --- should succeed -insert into atacc1 (test) values (4); --- try to create duplicates via alter table using - should fail -alter table atacc1 alter column test type integer using 0; -ERROR: could not create unique index "atacc_test1" -DETAIL: Key (test)=(0) is duplicated. -drop table atacc1; --- let's do one where the unique constraint fails when added -create table atacc1 ( test int ); --- insert soon to be failing rows -insert into atacc1 (test) values (2); -insert into atacc1 (test) values (2); --- add a unique constraint (fails) -alter table atacc1 add constraint atacc_test1 unique (test); -ERROR: could not create unique index "atacc_test1" -DETAIL: Key (test)=(2) is duplicated. -insert into atacc1 (test) values (3); -drop table atacc1; --- let's do one where the unique constraint fails --- because the column doesn't exist -create table atacc1 ( test int ); --- add a unique constraint (fails) -alter table atacc1 add constraint atacc_test1 unique (test1); -ERROR: column "test1" named in key does not exist -drop table atacc1; --- something a little more complicated -create table atacc1 ( test int, test2 int); --- add a unique constraint -alter table atacc1 add constraint atacc_test1 unique (test, test2); --- insert initial value -insert into atacc1 (test,test2) values (4,4); --- should fail -insert into atacc1 (test,test2) values (4,4); -ERROR: duplicate key value violates unique constraint "atacc_test1" -DETAIL: Key (test, test2)=(4, 4) already exists. --- should all succeed -insert into atacc1 (test,test2) values (4,5); -insert into atacc1 (test,test2) values (5,4); -insert into atacc1 (test,test2) values (5,5); -drop table atacc1; --- lets do some naming tests -create table atacc1 (test int, test2 int, unique(test)); -alter table atacc1 add unique (test2); --- should fail for @@ second one @@ -insert into atacc1 (test2, test) values (3, 3); -insert into atacc1 (test2, test) values (2, 3); -ERROR: duplicate key value violates unique constraint "atacc1_test_key" -DETAIL: Key (test)=(3) already exists. -drop table atacc1; --- test primary key constraint adding -create table atacc1 ( id serial, test int) ; --- add a primary key constraint -alter table atacc1 add constraint atacc_test1 primary key (test); --- insert first value -insert into atacc1 (test) values (2); --- should fail -insert into atacc1 (test) values (2); -ERROR: duplicate key value violates unique constraint "atacc_test1" -DETAIL: Key (test)=(2) already exists. --- should succeed -insert into atacc1 (test) values (4); --- inserting NULL should fail -insert into atacc1 (test) values(NULL); -ERROR: null value in column "test" of relation "atacc1" violates not-null constraint -DETAIL: Failing row contains (4, null). --- try adding a second primary key (should fail) -alter table atacc1 add constraint atacc_oid1 primary key(id); -ERROR: multiple primary keys for table "atacc1" are not allowed --- drop first primary key constraint -alter table atacc1 drop constraint atacc_test1 restrict; --- try adding a primary key on oid (should succeed) -alter table atacc1 add constraint atacc_oid1 primary key(id); -drop table atacc1; --- let's do one where the primary key constraint fails when added -create table atacc1 ( test int ); --- insert soon to be failing rows -insert into atacc1 (test) values (2); -insert into atacc1 (test) values (2); --- add a primary key (fails) -alter table atacc1 add constraint atacc_test1 primary key (test); -ERROR: could not create unique index "atacc_test1" -DETAIL: Key (test)=(2) is duplicated. -insert into atacc1 (test) values (3); -drop table atacc1; --- let's do another one where the primary key constraint fails when added -create table atacc1 ( test int ); --- insert soon to be failing row -insert into atacc1 (test) values (NULL); --- add a primary key (fails) -alter table atacc1 add constraint atacc_test1 primary key (test); -ERROR: column "test" of relation "atacc1" contains null values -insert into atacc1 (test) values (3); -drop table atacc1; --- let's do one where the primary key constraint fails --- because the column doesn't exist -create table atacc1 ( test int ); --- add a primary key constraint (fails) -alter table atacc1 add constraint atacc_test1 primary key (test1); -ERROR: column "test1" of relation "atacc1" does not exist -drop table atacc1; --- adding a new column as primary key to a non-empty table. --- should fail unless the column has a non-null default value. -create table atacc1 ( test int ); -insert into atacc1 (test) values (0); --- add a primary key column without a default (fails). -alter table atacc1 add column test2 int primary key; -ERROR: column "test2" of relation "atacc1" contains null values --- now add a primary key column with a default (succeeds). -alter table atacc1 add column test2 int default 0 primary key; -drop table atacc1; --- this combination used to have order-of-execution problems (bug #15580) -create table atacc1 (a int); -insert into atacc1 values(1); -alter table atacc1 - add column b float8 not null default random(), - add primary key(a); -drop table atacc1; --- additionally, we've seen issues with foreign key validation not being --- properly delayed until after a table rewrite. Check that works ok. -create table atacc1 (a int primary key); -alter table atacc1 add constraint atacc1_fkey foreign key (a) references atacc1 (a) not valid; -alter table atacc1 validate constraint atacc1_fkey, alter a type bigint; -drop table atacc1; --- we've also seen issues with check constraints being validated at the wrong --- time when there's a pending table rewrite. -create table atacc1 (a bigint, b int); -insert into atacc1 values(1,1); -alter table atacc1 add constraint atacc1_chk check(b = 1) not valid; -alter table atacc1 validate constraint atacc1_chk, alter a type int; -drop table atacc1; --- same as above, but ensure the constraint violation is detected -create table atacc1 (a bigint, b int); -insert into atacc1 values(1,2); -alter table atacc1 add constraint atacc1_chk check(b = 1) not valid; -alter table atacc1 validate constraint atacc1_chk, alter a type int; -ERROR: check constraint "atacc1_chk" of relation "atacc1" is violated by some row -drop table atacc1; --- something a little more complicated -create table atacc1 ( test int, test2 int); --- add a primary key constraint -alter table atacc1 add constraint atacc_test1 primary key (test, test2); --- try adding a second primary key - should fail -alter table atacc1 add constraint atacc_test2 primary key (test); -ERROR: multiple primary keys for table "atacc1" are not allowed --- insert initial value -insert into atacc1 (test,test2) values (4,4); --- should fail -insert into atacc1 (test,test2) values (4,4); -ERROR: duplicate key value violates unique constraint "atacc_test1" -DETAIL: Key (test, test2)=(4, 4) already exists. -insert into atacc1 (test,test2) values (NULL,3); -ERROR: null value in column "test" of relation "atacc1" violates not-null constraint -DETAIL: Failing row contains (null, 3). -insert into atacc1 (test,test2) values (3, NULL); -ERROR: null value in column "test2" of relation "atacc1" violates not-null constraint -DETAIL: Failing row contains (3, null). -insert into atacc1 (test,test2) values (NULL,NULL); -ERROR: null value in column "test" of relation "atacc1" violates not-null constraint -DETAIL: Failing row contains (null, null). --- should all succeed -insert into atacc1 (test,test2) values (4,5); -insert into atacc1 (test,test2) values (5,4); -insert into atacc1 (test,test2) values (5,5); -drop table atacc1; --- lets do some naming tests -create table atacc1 (test int, test2 int, primary key(test)); --- only first should succeed -insert into atacc1 (test2, test) values (3, 3); -insert into atacc1 (test2, test) values (2, 3); -ERROR: duplicate key value violates unique constraint "atacc1_pkey" -DETAIL: Key (test)=(3) already exists. -insert into atacc1 (test2, test) values (1, NULL); -ERROR: null value in column "test" of relation "atacc1" violates not-null constraint -DETAIL: Failing row contains (null, 1). -drop table atacc1; --- alter table / alter column [set/drop] not null tests --- try altering system catalogs, should fail -alter table pg_class alter column relname drop not null; -ERROR: permission denied: "pg_class" is a system catalog -alter table pg_class alter relname set not null; -ERROR: permission denied: "pg_class" is a system catalog --- try altering non-existent table, should fail -alter table non_existent alter column bar set not null; -ERROR: relation "non_existent" does not exist -alter table non_existent alter column bar drop not null; -ERROR: relation "non_existent" does not exist --- test setting columns to null and not null and vice versa --- test checking for null values and primary key -create table atacc1 (test int not null); -alter table atacc1 add constraint "atacc1_pkey" primary key (test); -\d atacc1 - Table "public.atacc1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - test | integer | | not null | -Indexes: - "atacc1_pkey" PRIMARY KEY, btree (test) - -alter table atacc1 alter column test drop not null; -ERROR: column "test" is in a primary key -\d atacc1 - Table "public.atacc1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - test | integer | | not null | -Indexes: - "atacc1_pkey" PRIMARY KEY, btree (test) - -alter table atacc1 drop constraint "atacc1_pkey"; -alter table atacc1 alter column test drop not null; -\d atacc1 - Table "public.atacc1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - test | integer | | | - -insert into atacc1 values (null); -alter table atacc1 alter test set not null; -ERROR: column "test" of relation "atacc1" contains null values -delete from atacc1; -alter table atacc1 alter test set not null; --- try altering a non-existent column, should fail -alter table atacc1 alter bar set not null; -ERROR: column "bar" of relation "atacc1" does not exist -alter table atacc1 alter bar drop not null; -ERROR: column "bar" of relation "atacc1" does not exist --- try creating a view and altering that, should fail -create view myview as select * from atacc1; -alter table myview alter column test drop not null; -ERROR: ALTER action ALTER COLUMN ... DROP NOT NULL cannot be performed on relation "myview" -DETAIL: This operation is not supported for views. -alter table myview alter column test set not null; -ERROR: ALTER action ALTER COLUMN ... SET NOT NULL cannot be performed on relation "myview" -DETAIL: This operation is not supported for views. -drop view myview; -drop table atacc1; --- set not null verified by constraints -create table atacc1 (test_a int, test_b int); -insert into atacc1 values (null, 1); --- constraint not cover all values, should fail -alter table atacc1 add constraint atacc1_constr_or check(test_a is not null or test_b < 10); -alter table atacc1 alter test_a set not null; -ERROR: column "test_a" of relation "atacc1" contains null values -alter table atacc1 drop constraint atacc1_constr_or; --- not valid constraint, should fail -alter table atacc1 add constraint atacc1_constr_invalid check(test_a is not null) not valid; -alter table atacc1 alter test_a set not null; -ERROR: column "test_a" of relation "atacc1" contains null values -alter table atacc1 drop constraint atacc1_constr_invalid; --- with valid constraint -update atacc1 set test_a = 1; -alter table atacc1 add constraint atacc1_constr_a_valid check(test_a is not null); -alter table atacc1 alter test_a set not null; -delete from atacc1; -insert into atacc1 values (2, null); -alter table atacc1 alter test_a drop not null; --- test multiple set not null at same time --- test_a checked by atacc1_constr_a_valid, test_b should fail by table scan -alter table atacc1 alter test_a set not null, alter test_b set not null; -ERROR: column "test_b" of relation "atacc1" contains null values --- commands order has no importance -alter table atacc1 alter test_b set not null, alter test_a set not null; -ERROR: column "test_b" of relation "atacc1" contains null values --- valid one by table scan, one by check constraints -update atacc1 set test_b = 1; -alter table atacc1 alter test_b set not null, alter test_a set not null; -alter table atacc1 alter test_a drop not null, alter test_b drop not null; --- both column has check constraints -alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null); -alter table atacc1 alter test_b set not null, alter test_a set not null; -drop table atacc1; --- not null not valid with partitions -CREATE TABLE atnnparted (id int, col1 int) PARTITION BY LIST (id); -ALTER TABLE atnnparted ADD CONSTRAINT dummy_constr NOT NULL id NOT VALID; -CREATE TABLE atnnpart1 (col1 int, id int); -ALTER TABLE atnnpart1 ADD CONSTRAINT another_constr NOT NULL id; -ALTER TABLE atnnpart1 ADD PRIMARY KEY (id); -ALTER TABLE atnnparted ATTACH PARTITION atnnpart1 FOR VALUES IN ('1'); -\d+ atnnpart* - Table "public.atnnpart1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - col1 | integer | | | | plain | | - id | integer | | not null | | plain | | -Partition of: atnnparted FOR VALUES IN (1) -Partition constraint: ((id IS NOT NULL) AND (id = 1)) -Indexes: - "atnnpart1_pkey" PRIMARY KEY, btree (id) -Not-null constraints: - "another_constr" NOT NULL "id" (inherited) - - Index "public.atnnpart1_pkey" - Column | Type | Key? | Definition | Storage | Stats target ---------+---------+------+------------+---------+-------------- - id | integer | yes | id | plain | -primary key, btree, for table "public.atnnpart1" - - Partitioned table "public.atnnparted" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id | integer | | not null | | plain | | - col1 | integer | | | | plain | | -Partition key: LIST (id) -Not-null constraints: - "dummy_constr" NOT NULL "id" NOT VALID -Partitions: atnnpart1 FOR VALUES IN (1) - -BEGIN; -ALTER TABLE atnnparted VALIDATE CONSTRAINT dummy_constr; -\d+ atnnpart* - Table "public.atnnpart1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - col1 | integer | | | | plain | | - id | integer | | not null | | plain | | -Partition of: atnnparted FOR VALUES IN (1) -Partition constraint: ((id IS NOT NULL) AND (id = 1)) -Indexes: - "atnnpart1_pkey" PRIMARY KEY, btree (id) -Not-null constraints: - "another_constr" NOT NULL "id" (inherited) - - Index "public.atnnpart1_pkey" - Column | Type | Key? | Definition | Storage | Stats target ---------+---------+------+------------+---------+-------------- - id | integer | yes | id | plain | -primary key, btree, for table "public.atnnpart1" - - Partitioned table "public.atnnparted" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id | integer | | not null | | plain | | - col1 | integer | | | | plain | | -Partition key: LIST (id) -Not-null constraints: - "dummy_constr" NOT NULL "id" -Partitions: atnnpart1 FOR VALUES IN (1) - -ROLLBACK; --- leave a table in this state for the pg_upgrade test --- test inheritance -create table parent (a int); -create table child (b varchar(255)) inherits (parent); -alter table parent alter a set not null; -insert into parent values (NULL); -ERROR: null value in column "a" of relation "parent" violates not-null constraint -DETAIL: Failing row contains (null). -insert into child (a, b) values (NULL, 'foo'); -ERROR: null value in column "a" of relation "child" violates not-null constraint -DETAIL: Failing row contains (null, foo). -alter table parent alter a drop not null; -insert into parent values (NULL); -insert into child (a, b) values (NULL, 'foo'); -alter table only parent alter a set not null; -ERROR: column "a" of relation "parent" contains null values -alter table child alter a set not null; -ERROR: column "a" of relation "child" contains null values -drop table child; -drop table parent; --- test setting and removing default values -create table def_test ( - c1 int4 default 5, - c2 text default 'initial_default' -); -insert into def_test default values; -alter table def_test alter column c1 drop default; -insert into def_test default values; -alter table def_test alter column c2 drop default; -insert into def_test default values; -alter table def_test alter column c1 set default 10; -alter table def_test alter column c2 set default 'new_default'; -insert into def_test default values; -select * from def_test; - c1 | c2 -----+----------------- - 5 | initial_default - | initial_default - | - 10 | new_default -(4 rows) - --- set defaults to an incorrect type: this should fail -alter table def_test alter column c1 set default 'wrong_datatype'; -ERROR: invalid input syntax for type integer: "wrong_datatype" -alter table def_test alter column c2 set default 20; --- set defaults on a non-existent column: this should fail -alter table def_test alter column c3 set default 30; -ERROR: column "c3" of relation "def_test" does not exist --- set defaults on views: we need to create a view, add a rule --- to allow insertions into it, and then alter the view to add --- a default -create view def_view_test as select * from def_test; -create rule def_view_test_ins as - on insert to def_view_test - do instead insert into def_test select new.*; -insert into def_view_test default values; -alter table def_view_test alter column c1 set default 45; -insert into def_view_test default values; -alter table def_view_test alter column c2 set default 'view_default'; -insert into def_view_test default values; -select * from def_view_test; - c1 | c2 -----+----------------- - 5 | initial_default - | initial_default - | - 10 | new_default - | - 45 | - 45 | view_default -(7 rows) - -drop rule def_view_test_ins on def_view_test; -drop view def_view_test; -drop table def_test; --- alter table / drop column tests --- try altering system catalogs, should fail -alter table pg_class drop column relname; -ERROR: permission denied: "pg_class" is a system catalog --- try altering non-existent table, should fail -alter table nosuchtable drop column bar; -ERROR: relation "nosuchtable" does not exist --- test dropping columns -create table atacc1 (a int4 not null, b int4, c int4 not null, d int4); -insert into atacc1 values (1, 2, 3, 4); -alter table atacc1 drop a; -alter table atacc1 drop a; -ERROR: column "a" of relation "atacc1" does not exist --- SELECTs -select * from atacc1; - b | c | d ----+---+--- - 2 | 3 | 4 -(1 row) - -select * from atacc1 order by a; -ERROR: column "a" does not exist -LINE 1: select * from atacc1 order by a; - ^ -select * from atacc1 order by "........pg.dropped.1........"; -ERROR: column "........pg.dropped.1........" does not exist -LINE 1: select * from atacc1 order by "........pg.dropped.1........"... - ^ -select * from atacc1 group by a; -ERROR: column "a" does not exist -LINE 1: select * from atacc1 group by a; - ^ -select * from atacc1 group by "........pg.dropped.1........"; -ERROR: column "........pg.dropped.1........" does not exist -LINE 1: select * from atacc1 group by "........pg.dropped.1........"... - ^ -select atacc1.* from atacc1; - b | c | d ----+---+--- - 2 | 3 | 4 -(1 row) - -select a from atacc1; -ERROR: column "a" does not exist -LINE 1: select a from atacc1; - ^ -select atacc1.a from atacc1; -ERROR: column atacc1.a does not exist -LINE 1: select atacc1.a from atacc1; - ^ -select b,c,d from atacc1; - b | c | d ----+---+--- - 2 | 3 | 4 -(1 row) - -select a,b,c,d from atacc1; -ERROR: column "a" does not exist -LINE 1: select a,b,c,d from atacc1; - ^ -select * from atacc1 where a = 1; -ERROR: column "a" does not exist -LINE 1: select * from atacc1 where a = 1; - ^ -select "........pg.dropped.1........" from atacc1; -ERROR: column "........pg.dropped.1........" does not exist -LINE 1: select "........pg.dropped.1........" from atacc1; - ^ -select atacc1."........pg.dropped.1........" from atacc1; -ERROR: column atacc1.........pg.dropped.1........ does not exist -LINE 1: select atacc1."........pg.dropped.1........" from atacc1; - ^ -select "........pg.dropped.1........",b,c,d from atacc1; -ERROR: column "........pg.dropped.1........" does not exist -LINE 1: select "........pg.dropped.1........",b,c,d from atacc1; - ^ -select * from atacc1 where "........pg.dropped.1........" = 1; -ERROR: column "........pg.dropped.1........" does not exist -LINE 1: select * from atacc1 where "........pg.dropped.1........" = ... - ^ --- UPDATEs -update atacc1 set a = 3; -ERROR: column "a" of relation "atacc1" does not exist -LINE 1: update atacc1 set a = 3; - ^ -update atacc1 set b = 2 where a = 3; -ERROR: column "a" does not exist -LINE 1: update atacc1 set b = 2 where a = 3; - ^ -update atacc1 set "........pg.dropped.1........" = 3; -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -LINE 1: update atacc1 set "........pg.dropped.1........" = 3; - ^ -update atacc1 set b = 2 where "........pg.dropped.1........" = 3; -ERROR: column "........pg.dropped.1........" does not exist -LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"... - ^ --- INSERTs -insert into atacc1 values (10, 11, 12, 13); -ERROR: INSERT has more expressions than target columns -LINE 1: insert into atacc1 values (10, 11, 12, 13); - ^ -insert into atacc1 values (default, 11, 12, 13); -ERROR: INSERT has more expressions than target columns -LINE 1: insert into atacc1 values (default, 11, 12, 13); - ^ -insert into atacc1 values (11, 12, 13); -insert into atacc1 (a) values (10); -ERROR: column "a" of relation "atacc1" does not exist -LINE 1: insert into atacc1 (a) values (10); - ^ -insert into atacc1 (a) values (default); -ERROR: column "a" of relation "atacc1" does not exist -LINE 1: insert into atacc1 (a) values (default); - ^ -insert into atacc1 (a,b,c,d) values (10,11,12,13); -ERROR: column "a" of relation "atacc1" does not exist -LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13); - ^ -insert into atacc1 (a,b,c,d) values (default,11,12,13); -ERROR: column "a" of relation "atacc1" does not exist -LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13); - ^ -insert into atacc1 (b,c,d) values (11,12,13); -insert into atacc1 ("........pg.dropped.1........") values (10); -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -LINE 1: insert into atacc1 ("........pg.dropped.1........") values (... - ^ -insert into atacc1 ("........pg.dropped.1........") values (default); -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -LINE 1: insert into atacc1 ("........pg.dropped.1........") values (... - ^ -insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13); -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va... - ^ -insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13); -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va... - ^ --- DELETEs -delete from atacc1 where a = 3; -ERROR: column "a" does not exist -LINE 1: delete from atacc1 where a = 3; - ^ -delete from atacc1 where "........pg.dropped.1........" = 3; -ERROR: column "........pg.dropped.1........" does not exist -LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3; - ^ -delete from atacc1; --- try dropping a non-existent column, should fail -alter table atacc1 drop bar; -ERROR: column "bar" of relation "atacc1" does not exist --- try removing an oid column, should succeed (as it's nonexistent) -alter table atacc1 SET WITHOUT OIDS; --- try adding an oid column, should fail (not supported) -alter table atacc1 SET WITH OIDS; -ERROR: syntax error at or near "WITH" -LINE 1: alter table atacc1 SET WITH OIDS; - ^ --- try dropping the xmin column, should fail -alter table atacc1 drop xmin; -ERROR: cannot drop system column "xmin" --- try creating a view and altering that, should fail -create view myview as select * from atacc1; -select * from myview; - b | c | d ----+---+--- -(0 rows) - -alter table myview drop d; -ERROR: ALTER action DROP COLUMN cannot be performed on relation "myview" -DETAIL: This operation is not supported for views. -drop view myview; --- test some commands to make sure they fail on the dropped column -analyze atacc1(a); -ERROR: column "a" of relation "atacc1" does not exist -analyze atacc1("........pg.dropped.1........"); -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -vacuum analyze atacc1(a); -ERROR: column "a" of relation "atacc1" does not exist -vacuum analyze atacc1("........pg.dropped.1........"); -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -comment on column atacc1.a is 'testing'; -ERROR: column "a" of relation "atacc1" does not exist -comment on column atacc1."........pg.dropped.1........" is 'testing'; -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -alter table atacc1 alter a set storage plain; -ERROR: column "a" of relation "atacc1" does not exist -alter table atacc1 alter "........pg.dropped.1........" set storage plain; -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -alter table atacc1 alter a set statistics 0; -ERROR: column "a" of relation "atacc1" does not exist -alter table atacc1 alter "........pg.dropped.1........" set statistics 0; -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -alter table atacc1 alter a set default 3; -ERROR: column "a" of relation "atacc1" does not exist -alter table atacc1 alter "........pg.dropped.1........" set default 3; -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -alter table atacc1 alter a drop default; -ERROR: column "a" of relation "atacc1" does not exist -alter table atacc1 alter "........pg.dropped.1........" drop default; -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -alter table atacc1 alter a set not null; -ERROR: column "a" of relation "atacc1" does not exist -alter table atacc1 alter "........pg.dropped.1........" set not null; -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -alter table atacc1 alter a drop not null; -ERROR: column "a" of relation "atacc1" does not exist -alter table atacc1 alter "........pg.dropped.1........" drop not null; -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -alter table atacc1 rename a to x; -ERROR: column "a" does not exist -alter table atacc1 rename "........pg.dropped.1........" to x; -ERROR: column "........pg.dropped.1........" does not exist -alter table atacc1 add primary key(a); -ERROR: column "a" of relation "atacc1" does not exist -alter table atacc1 add primary key("........pg.dropped.1........"); -ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist -alter table atacc1 add unique(a); -ERROR: column "a" named in key does not exist -alter table atacc1 add unique("........pg.dropped.1........"); -ERROR: column "........pg.dropped.1........" named in key does not exist -alter table atacc1 add check (a > 3); -ERROR: column "a" does not exist -alter table atacc1 add check ("........pg.dropped.1........" > 3); -ERROR: column "........pg.dropped.1........" does not exist -create table atacc2 (id int4 unique); -alter table atacc1 add foreign key (a) references atacc2(id); -ERROR: column "a" referenced in foreign key constraint does not exist -alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id); -ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist -alter table atacc2 add foreign key (id) references atacc1(a); -ERROR: column "a" referenced in foreign key constraint does not exist -alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........"); -ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist -drop table atacc2; -create index "testing_idx" on atacc1(a); -ERROR: column "a" does not exist -create index "testing_idx" on atacc1("........pg.dropped.1........"); -ERROR: column "........pg.dropped.1........" does not exist --- test create as and select into -insert into atacc1 values (21, 22, 23); -create table attest1 as select * from atacc1; -select * from attest1; - b | c | d -----+----+---- - 21 | 22 | 23 -(1 row) - -drop table attest1; -select * into attest2 from atacc1; -select * from attest2; - b | c | d -----+----+---- - 21 | 22 | 23 -(1 row) - -drop table attest2; --- try dropping all columns -alter table atacc1 drop c; -alter table atacc1 drop d; -alter table atacc1 drop b; -select * from atacc1; --- -(1 row) - -drop table atacc1; --- test constraint error reporting in presence of dropped columns -create table atacc1 (id serial primary key, value int check (value < 10)); -insert into atacc1(value) values (100); -ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check" -DETAIL: Failing row contains (1, 100). -alter table atacc1 drop column value; -alter table atacc1 add column value int check (value < 10); -insert into atacc1(value) values (100); -ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check" -DETAIL: Failing row contains (2, 100). -insert into atacc1(id, value) values (null, 0); -ERROR: null value in column "id" of relation "atacc1" violates not-null constraint -DETAIL: Failing row contains (null, 0). -drop table atacc1; --- test inheritance -create table parent (a int, b int, c int); -insert into parent values (1, 2, 3); -alter table parent drop a; -create table child (d varchar(255)) inherits (parent); -insert into child values (12, 13, 'testing'); -select * from parent; - b | c -----+---- - 2 | 3 - 12 | 13 -(2 rows) - -select * from child; - b | c | d -----+----+--------- - 12 | 13 | testing -(1 row) - -alter table parent drop c; -select * from parent; - b ----- - 2 - 12 -(2 rows) - -select * from child; - b | d -----+--------- - 12 | testing -(1 row) - -drop table child; -drop table parent; --- check error cases for inheritance column merging -create table parent (a float8, b numeric(10,4), c text collate "C"); -create table child (a float4) inherits (parent); -- fail -NOTICE: merging column "a" with inherited definition -ERROR: column "a" has a type conflict -DETAIL: double precision versus real -create table child (b decimal(10,7)) inherits (parent); -- fail -NOTICE: moving and merging column "b" with inherited definition -DETAIL: User-specified column moved to the position of the inherited column. -ERROR: column "b" has a type conflict -DETAIL: numeric(10,4) versus numeric(10,7) -create table child (c text collate "POSIX") inherits (parent); -- fail -NOTICE: moving and merging column "c" with inherited definition -DETAIL: User-specified column moved to the position of the inherited column. -ERROR: column "c" has a collation conflict -DETAIL: "C" versus "POSIX" -create table child (a double precision, b decimal(10,4)) inherits (parent); -NOTICE: merging column "a" with inherited definition -NOTICE: merging column "b" with inherited definition -drop table child; -drop table parent; --- test copy in/out -create table attest (a int4, b int4, c int4); -insert into attest values (1,2,3); -alter table attest drop a; -copy attest to stdout; -2 3 -copy attest(a) to stdout; -ERROR: column "a" of relation "attest" does not exist -copy attest("........pg.dropped.1........") to stdout; -ERROR: column "........pg.dropped.1........" of relation "attest" does not exist -copy attest from stdin; -ERROR: extra data after last expected column -CONTEXT: COPY attest, line 1: "10 11 12" -select * from attest; - b | c ----+--- - 2 | 3 -(1 row) - -copy attest from stdin; -select * from attest; - b | c -----+---- - 2 | 3 - 21 | 22 -(2 rows) - -copy attest(a) from stdin; -ERROR: column "a" of relation "attest" does not exist -copy attest("........pg.dropped.1........") from stdin; -ERROR: column "........pg.dropped.1........" of relation "attest" does not exist -copy attest(b,c) from stdin; -select * from attest; - b | c -----+---- - 2 | 3 - 21 | 22 - 31 | 32 -(3 rows) - -drop table attest; --- test inheritance -create table dropColumn (a int, b int, e int); -create table dropColumnChild (c int) inherits (dropColumn); -create table dropColumnAnother (d int) inherits (dropColumnChild); --- these two should fail -alter table dropColumnchild drop column a; -ERROR: cannot drop inherited column "a" -alter table only dropColumnChild drop column b; -ERROR: cannot drop inherited column "b" --- these three should work -alter table only dropColumn drop column e; -alter table dropColumnChild drop column c; -alter table dropColumn drop column a; -create table renameColumn (a int); -create table renameColumnChild (b int) inherits (renameColumn); -create table renameColumnAnother (c int) inherits (renameColumnChild); --- these three should fail -alter table renameColumnChild rename column a to d; -ERROR: cannot rename inherited column "a" -alter table only renameColumnChild rename column a to d; -ERROR: inherited column "a" must be renamed in child tables too -alter table only renameColumn rename column a to d; -ERROR: inherited column "a" must be renamed in child tables too --- these should work -alter table renameColumn rename column a to d; -alter table renameColumnChild rename column b to a; --- these should work -alter table if exists doesnt_exist_tab rename column a to d; -NOTICE: relation "doesnt_exist_tab" does not exist, skipping -alter table if exists doesnt_exist_tab rename column b to a; -NOTICE: relation "doesnt_exist_tab" does not exist, skipping --- this should work -alter table renameColumn add column w int; --- this should fail -alter table only renameColumn add column x int; -ERROR: column must be added to child tables too --- this should work -alter table renameColumn add column x int check (x > 0) not enforced; --- this should fail -alter table renameColumn add column y int check (x > 0) not enforced enforced; -ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed -LINE 1: ...Column add column y int check (x > 0) not enforced enforced; - ^ --- Test corner cases in dropping of inherited columns -create table p1 (f1 int, f2 int); -create table c1 (f1 int not null) inherits(p1); -NOTICE: merging column "f1" with inherited definition --- should be rejected since c1.f1 is inherited -alter table c1 drop column f1; -ERROR: cannot drop inherited column "f1" --- should work -alter table p1 drop column f1; --- c1.f1 is still there, but no longer inherited -select f1 from c1; - f1 ----- -(0 rows) - -alter table c1 drop column f1; -select f1 from c1; -ERROR: column "f1" does not exist -LINE 1: select f1 from c1; - ^ -HINT: Perhaps you meant to reference the column "c1.f2". -drop table p1 cascade; -NOTICE: drop cascades to table c1 -create table p1 (f1 int, f2 int); -create table c1 () inherits(p1); --- should be rejected since c1.f1 is inherited -alter table c1 drop column f1; -ERROR: cannot drop inherited column "f1" -alter table p1 drop column f1; --- c1.f1 is dropped now, since there is no local definition for it -select f1 from c1; -ERROR: column "f1" does not exist -LINE 1: select f1 from c1; - ^ -HINT: Perhaps you meant to reference the column "c1.f2". -drop table p1 cascade; -NOTICE: drop cascades to table c1 -create table p1 (f1 int, f2 int); -create table c1 () inherits(p1); --- should be rejected since c1.f1 is inherited -alter table c1 drop column f1; -ERROR: cannot drop inherited column "f1" -alter table only p1 drop column f1; --- c1.f1 is NOT dropped, but must now be considered non-inherited -alter table c1 drop column f1; -drop table p1 cascade; -NOTICE: drop cascades to table c1 -create table p1 (f1 int, f2 int); -create table c1 (f1 int not null) inherits(p1); -NOTICE: merging column "f1" with inherited definition --- should be rejected since c1.f1 is inherited -alter table c1 drop column f1; -ERROR: cannot drop inherited column "f1" -alter table only p1 drop column f1; --- c1.f1 is still there, but no longer inherited -alter table c1 drop column f1; -drop table p1 cascade; -NOTICE: drop cascades to table c1 -create table p1(id int, name text); -create table p2(id2 int, name text, height int); -create table c1(age int) inherits(p1,p2); -NOTICE: merging multiple inherited definitions of column "name" -create table gc1() inherits (c1); -select relname, attname, attinhcount, attislocal -from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) -where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped -order by relname, attnum; - relname | attname | attinhcount | attislocal ----------+---------+-------------+------------ - c1 | id | 1 | f - c1 | name | 2 | f - c1 | id2 | 1 | f - c1 | height | 1 | f - c1 | age | 0 | t - gc1 | id | 1 | f - gc1 | name | 1 | f - gc1 | id2 | 1 | f - gc1 | height | 1 | f - gc1 | age | 1 | f - p1 | id | 0 | t - p1 | name | 0 | t - p2 | id2 | 0 | t - p2 | name | 0 | t - p2 | height | 0 | t -(15 rows) - --- should work -alter table only p1 drop column name; --- should work. Now c1.name is local and inhcount is 0. -alter table p2 drop column name; --- should be rejected since its inherited -alter table gc1 drop column name; -ERROR: cannot drop inherited column "name" --- should work, and drop gc1.name along -alter table c1 drop column name; --- should fail: column does not exist -alter table gc1 drop column name; -ERROR: column "name" of relation "gc1" does not exist --- should work and drop the attribute in all tables -alter table p2 drop column height; --- IF EXISTS test -create table dropColumnExists (); -alter table dropColumnExists drop column non_existing; --fail -ERROR: column "non_existing" of relation "dropcolumnexists" does not exist -alter table dropColumnExists drop column if exists non_existing; --succeed -NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping -select relname, attname, attinhcount, attislocal -from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) -where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped -order by relname, attnum; - relname | attname | attinhcount | attislocal ----------+---------+-------------+------------ - c1 | id | 1 | f - c1 | id2 | 1 | f - c1 | age | 0 | t - gc1 | id | 1 | f - gc1 | id2 | 1 | f - gc1 | age | 1 | f - p1 | id | 0 | t - p2 | id2 | 0 | t -(8 rows) - -drop table p1, p2 cascade; -NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to table c1 -drop cascades to table gc1 --- test attinhcount tracking with merged columns -create table depth0(); -create table depth1(c text) inherits (depth0); -create table depth2() inherits (depth1); -alter table depth0 add c text; -NOTICE: merging definition of column "c" for child "depth1" -select attrelid::regclass, attname, attinhcount, attislocal -from pg_attribute -where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2') -order by attrelid::regclass::text, attnum; - attrelid | attname | attinhcount | attislocal -----------+---------+-------------+------------ - depth0 | c | 0 | t - depth1 | c | 1 | t - depth2 | c | 1 | f -(3 rows) - --- test renumbering of child-table columns in inherited operations -create table p1 (f1 int); -create table c1 (f2 text, f3 int) inherits (p1); -alter table p1 add column a1 int check (a1 > 0); -alter table p1 add column f2 text; -NOTICE: merging definition of column "f2" for child "c1" -insert into p1 values (1,2,'abc'); -insert into c1 values(11,'xyz',33,0); -- should fail -ERROR: new row for relation "c1" violates check constraint "p1_a1_check" -DETAIL: Failing row contains (11, xyz, 33, 0). -insert into c1 values(11,'xyz',33,22); -select * from p1; - f1 | a1 | f2 -----+----+----- - 1 | 2 | abc - 11 | 22 | xyz -(2 rows) - -update p1 set a1 = a1 + 1, f2 = upper(f2); -select * from p1; - f1 | a1 | f2 -----+----+----- - 1 | 3 | ABC - 11 | 23 | XYZ -(2 rows) - -drop table p1 cascade; -NOTICE: drop cascades to table c1 --- test that operations with a dropped column do not try to reference --- its datatype -create domain mytype as text; -create temp table foo (f1 text, f2 mytype, f3 text); -insert into foo values('bb','cc','dd'); -select * from foo; - f1 | f2 | f3 -----+----+---- - bb | cc | dd -(1 row) - -drop domain mytype cascade; -NOTICE: drop cascades to column f2 of table foo -select * from foo; - f1 | f3 -----+---- - bb | dd -(1 row) - -insert into foo values('qq','rr'); -select * from foo; - f1 | f3 -----+---- - bb | dd - qq | rr -(2 rows) - -update foo set f3 = 'zz'; -select * from foo; - f1 | f3 -----+---- - bb | zz - qq | zz -(2 rows) - -select f3,max(f1) from foo group by f3; - f3 | max -----+----- - zz | qq -(1 row) - --- Simple tests for alter table column type -alter table foo alter f1 TYPE integer; -- fails -ERROR: column "f1" cannot be cast automatically to type integer -HINT: You might need to specify "USING f1::integer". -alter table foo alter f1 TYPE varchar(10); -create table anothertab (atcol1 serial8, atcol2 boolean, - constraint anothertab_chk check (atcol1 <= 3)); -insert into anothertab (atcol1, atcol2) values (default, true); -insert into anothertab (atcol1, atcol2) values (default, false); -select * from anothertab; - atcol1 | atcol2 ---------+-------- - 1 | t - 2 | f -(2 rows) - -alter table anothertab alter column atcol1 type boolean; -- fails -ERROR: column "atcol1" cannot be cast automatically to type boolean -HINT: You might need to specify "USING atcol1::boolean". -alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails -ERROR: result of USING clause for column "atcol1" cannot be cast automatically to type boolean -HINT: You might need to add an explicit cast. -alter table anothertab alter column atcol1 type integer; -select * from anothertab; - atcol1 | atcol2 ---------+-------- - 1 | t - 2 | f -(2 rows) - -insert into anothertab (atcol1, atcol2) values (45, null); -- fails -ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk" -DETAIL: Failing row contains (45, null). -insert into anothertab (atcol1, atcol2) values (default, null); -select * from anothertab; - atcol1 | atcol2 ---------+-------- - 1 | t - 2 | f - 3 | -(3 rows) - -alter table anothertab alter column atcol2 type text - using case when atcol2 is true then 'IT WAS TRUE' - when atcol2 is false then 'IT WAS FALSE' - else 'IT WAS NULL!' end; -select * from anothertab; - atcol1 | atcol2 ---------+-------------- - 1 | IT WAS TRUE - 2 | IT WAS FALSE - 3 | IT WAS NULL! -(3 rows) - -alter table anothertab alter column atcol1 type boolean - using case when atcol1 % 2 = 0 then true else false end; -- fails -ERROR: default for column "atcol1" cannot be cast automatically to type boolean -alter table anothertab alter column atcol1 drop default; -alter table anothertab alter column atcol1 type boolean - using case when atcol1 % 2 = 0 then true else false end; -- fails -ERROR: operator does not exist: boolean <= integer -DETAIL: No operator of that name accepts the given argument types. -HINT: You might need to add explicit type casts. -alter table anothertab drop constraint anothertab_chk; -alter table anothertab drop constraint anothertab_chk; -- fails -ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist -alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds -NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping -alter table anothertab alter column atcol1 type boolean - using case when atcol1 % 2 = 0 then true else false end; -select * from anothertab; - atcol1 | atcol2 ---------+-------------- - f | IT WAS TRUE - t | IT WAS FALSE - f | IT WAS NULL! -(3 rows) - -drop table anothertab; --- Test index handling in alter table column type (cf. bugs #15835, #15865) -create table anothertab(f1 int primary key, f2 int unique, - f3 int, f4 int, f5 int); -alter table anothertab - add exclude using btree (f3 with =); -alter table anothertab - add exclude using btree (f4 with =) where (f4 is not null); -alter table anothertab - add exclude using btree (f4 with =) where (f5 > 0); -alter table anothertab - add unique(f1,f4); -create index on anothertab(f2,f3); -create unique index on anothertab(f4); -\d anothertab - Table "public.anothertab" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - f1 | integer | | not null | - f2 | integer | | | - f3 | integer | | | - f4 | integer | | | - f5 | integer | | | -Indexes: - "anothertab_pkey" PRIMARY KEY, btree (f1) - "anothertab_f1_f4_key" UNIQUE CONSTRAINT, btree (f1, f4) - "anothertab_f2_f3_idx" btree (f2, f3) - "anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2) - "anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =) - "anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL) - "anothertab_f4_excl1" EXCLUDE USING btree (f4 WITH =) WHERE (f5 > 0) - "anothertab_f4_idx" UNIQUE, btree (f4) - -alter table anothertab alter column f1 type bigint; -alter table anothertab - alter column f2 type bigint, - alter column f3 type bigint, - alter column f4 type bigint; -alter table anothertab alter column f5 type bigint; -\d anothertab - Table "public.anothertab" - Column | Type | Collation | Nullable | Default ---------+--------+-----------+----------+--------- - f1 | bigint | | not null | - f2 | bigint | | | - f3 | bigint | | | - f4 | bigint | | | - f5 | bigint | | | -Indexes: - "anothertab_pkey" PRIMARY KEY, btree (f1) - "anothertab_f1_f4_key" UNIQUE CONSTRAINT, btree (f1, f4) - "anothertab_f2_f3_idx" btree (f2, f3) - "anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2) - "anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =) - "anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL) - "anothertab_f4_excl1" EXCLUDE USING btree (f4 WITH =) WHERE (f5 > 0) - "anothertab_f4_idx" UNIQUE, btree (f4) - -drop table anothertab; --- test that USING expressions are parsed before column alter type / drop steps -create table another (f1 int, f2 text, f3 text); -insert into another values(1, 'one', 'uno'); -insert into another values(2, 'two', 'due'); -insert into another values(3, 'three', 'tre'); -select * from another; - f1 | f2 | f3 -----+-------+----- - 1 | one | uno - 2 | two | due - 3 | three | tre -(3 rows) - -alter table another - alter f1 type text using f2 || ' and ' || f3 || ' more', - alter f2 type bigint using f1 * 10, - drop column f3; -select * from another; - f1 | f2 ---------------------+---- - one and uno more | 10 - two and due more | 20 - three and tre more | 30 -(3 rows) - -drop table another; --- Create an index that skips WAL, then perform a SET DATA TYPE that skips --- rewriting the index. -begin; -create table skip_wal_skip_rewrite_index (c varchar(10) primary key); -alter table skip_wal_skip_rewrite_index alter c type varchar(20); -commit; --- We disallow changing table's row type if it's used for storage -create table at_tab1 (a int, b text); -create table at_tab2 (x int, y at_tab1); -alter table at_tab1 alter column b type varchar; -- fails -ERROR: cannot alter table "at_tab1" because column "at_tab2.y" uses its row type -drop table at_tab2; --- Use of row type in an expression is defended differently -create table at_tab2 (x int, y text, check((x,y)::at_tab1 = (1,'42')::at_tab1)); -alter table at_tab1 alter column b type varchar; -- allowed, but ... -insert into at_tab2 values(1,'42'); -- ... this will fail -ERROR: ROW() column has type text instead of type character varying -drop table at_tab1, at_tab2; --- Check it for a partitioned table, too -create table at_tab1 (a int, b text) partition by list(a); -create table at_tab2 (x int, y at_tab1); -alter table at_tab1 alter column b type varchar; -- fails -ERROR: cannot alter table "at_tab1" because column "at_tab2.y" uses its row type -drop table at_tab1, at_tab2; --- Alter column type that's part of a partitioned index -create table at_partitioned (a int, b text) partition by range (a); -create table at_part_1 partition of at_partitioned for values from (0) to (1000); -insert into at_partitioned values (512, '0.123'); -create table at_part_2 (b text, a int); -insert into at_part_2 values ('1.234', 1024); -create index on at_partitioned (b); -create index on at_partitioned (a); -\d at_part_1 - Table "public.at_part_1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | text | | | -Partition of: at_partitioned FOR VALUES FROM (0) TO (1000) -Indexes: - "at_part_1_a_idx" btree (a) - "at_part_1_b_idx" btree (b) - -\d at_part_2 - Table "public.at_part_2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - b | text | | | - a | integer | | | - -alter table at_partitioned attach partition at_part_2 for values from (1000) to (2000); -\d at_part_2 - Table "public.at_part_2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - b | text | | | - a | integer | | | -Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000) -Indexes: - "at_part_2_a_idx" btree (a) - "at_part_2_b_idx" btree (b) - -alter table at_partitioned alter column b type numeric using b::numeric; -\d at_part_1 - Table "public.at_part_1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | numeric | | | -Partition of: at_partitioned FOR VALUES FROM (0) TO (1000) -Indexes: - "at_part_1_a_idx" btree (a) - "at_part_1_b_idx" btree (b) - -\d at_part_2 - Table "public.at_part_2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - b | numeric | | | - a | integer | | | -Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000) -Indexes: - "at_part_2_a_idx" btree (a) - "at_part_2_b_idx" btree (b) - -drop table at_partitioned; --- Alter column type when no table rewrite is required --- Also check that comments are preserved -create table at_partitioned(id int, name varchar(64), unique (id, name)) - partition by hash(id); -comment on constraint at_partitioned_id_name_key on at_partitioned is 'parent constraint'; -comment on index at_partitioned_id_name_key is 'parent index'; -create table at_partitioned_0 partition of at_partitioned - for values with (modulus 2, remainder 0); -comment on constraint at_partitioned_0_id_name_key on at_partitioned_0 is 'child 0 constraint'; -comment on index at_partitioned_0_id_name_key is 'child 0 index'; -create table at_partitioned_1 partition of at_partitioned - for values with (modulus 2, remainder 1); -comment on constraint at_partitioned_1_id_name_key on at_partitioned_1 is 'child 1 constraint'; -comment on index at_partitioned_1_id_name_key is 'child 1 index'; -insert into at_partitioned values(1, 'foo'); -insert into at_partitioned values(3, 'bar'); -create temp table old_oids as - select relname, oid as oldoid, relfilenode as oldfilenode - from pg_class where relname like 'at_partitioned%'; -select relname, - c.oid = oldoid as orig_oid, - case relfilenode - when 0 then 'none' - when c.oid then 'own' - when oldfilenode then 'orig' - else 'OTHER' - end as storage, - obj_description(c.oid, 'pg_class') as desc - from pg_class c left join old_oids using (relname) - where relname like 'at_partitioned%' - order by relname; - relname | orig_oid | storage | desc -------------------------------+----------+---------+--------------- - at_partitioned | t | none | - at_partitioned_0 | t | own | - at_partitioned_0_id_name_key | t | own | child 0 index - at_partitioned_1 | t | own | - at_partitioned_1_id_name_key | t | own | child 1 index - at_partitioned_id_name_key | t | none | parent index -(6 rows) - -select conname, obj_description(oid, 'pg_constraint') as desc - from pg_constraint where conname like 'at_partitioned%' - order by conname; - conname | desc -------------------------------+-------------------- - at_partitioned_0_id_name_key | child 0 constraint - at_partitioned_1_id_name_key | child 1 constraint - at_partitioned_id_name_key | parent constraint -(3 rows) - -alter table at_partitioned alter column name type varchar(127); -select relname, - c.oid = oldoid as orig_oid, - case relfilenode - when 0 then 'none' - when c.oid then 'own' - when oldfilenode then 'orig' - else 'OTHER' - end as storage, - obj_description(c.oid, 'pg_class') as desc - from pg_class c left join old_oids using (relname) - where relname like 'at_partitioned%' - order by relname; - relname | orig_oid | storage | desc -------------------------------+----------+---------+-------------- - at_partitioned | t | none | - at_partitioned_0 | t | own | - at_partitioned_0_id_name_key | f | own | - at_partitioned_1 | t | own | - at_partitioned_1_id_name_key | f | own | - at_partitioned_id_name_key | f | none | parent index -(6 rows) - -select conname, obj_description(oid, 'pg_constraint') as desc - from pg_constraint where conname like 'at_partitioned%' - order by conname; - conname | desc -------------------------------+------------------- - at_partitioned_0_id_name_key | - at_partitioned_1_id_name_key | - at_partitioned_id_name_key | parent constraint -(3 rows) - --- Don't remove this DROP, it exposes bug #15672 -drop table at_partitioned; --- disallow recursive containment of row types -create temp table recur1 (f1 int); -alter table recur1 add column f2 recur1; -- fails -ERROR: composite type recur1 cannot be made a member of itself -alter table recur1 add column f2 recur1[]; -- fails -ERROR: composite type recur1 cannot be made a member of itself -create domain array_of_recur1 as recur1[]; -alter table recur1 add column f2 array_of_recur1; -- fails -ERROR: composite type recur1 cannot be made a member of itself -create temp table recur2 (f1 int, f2 recur1); -alter table recur1 add column f2 recur2; -- fails -ERROR: composite type recur1 cannot be made a member of itself -alter table recur1 add column f2 int; -alter table recur1 alter column f2 type recur2; -- fails -ERROR: composite type recur1 cannot be made a member of itself --- SET STORAGE may need to add a TOAST table -create table test_storage (a text, c text storage plain); -select reltoastrelid <> 0 as has_toast_table - from pg_class where oid = 'test_storage'::regclass; - has_toast_table ------------------ - t -(1 row) - -alter table test_storage alter a set storage plain; --- rewrite table to remove its TOAST table; need a non-constant column default -alter table test_storage add b int default random()::int; -select reltoastrelid <> 0 as has_toast_table - from pg_class where oid = 'test_storage'::regclass; - has_toast_table ------------------ - f -(1 row) - -alter table test_storage alter a set storage default; -- re-add TOAST table -select reltoastrelid <> 0 as has_toast_table - from pg_class where oid = 'test_storage'::regclass; - has_toast_table ------------------ - t -(1 row) - --- check STORAGE correctness -create table test_storage_failed (a text, b int storage extended); -ERROR: column data type integer can only have storage PLAIN --- test that SET STORAGE propagates to index correctly -create index test_storage_idx on test_storage (b, a); -alter table test_storage alter column a set storage external; -\d+ test_storage - Table "public.test_storage" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+-------------------+----------+--------------+------------- - a | text | | | | external | | - c | text | | | | plain | | - b | integer | | | random()::integer | plain | | -Indexes: - "test_storage_idx" btree (b, a) - -\d+ test_storage_idx - Index "public.test_storage_idx" - Column | Type | Key? | Definition | Storage | Stats target ---------+---------+------+------------+----------+-------------- - b | integer | yes | b | plain | - a | text | yes | a | external | -btree, for table "public.test_storage" - --- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779) -CREATE TABLE test_inh_check (a float check (a > 10.2), b float); -CREATE TABLE test_inh_check_child() INHERITS(test_inh_check); -\d test_inh_check - Table "public.test_inh_check" - Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+--------- - a | double precision | | | - b | double precision | | | -Check constraints: - "test_inh_check_a_check" CHECK (a > 10.2::double precision) -Number of child tables: 1 (Use \d+ to list them.) - -\d test_inh_check_child - Table "public.test_inh_check_child" - Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+--------- - a | double precision | | | - b | double precision | | | -Check constraints: - "test_inh_check_a_check" CHECK (a > 10.2::double precision) -Inherits: test_inh_check - -select relname, conname, coninhcount, conislocal, connoinherit - from pg_constraint c, pg_class r - where relname like 'test_inh_check%' and c.conrelid = r.oid - order by 1, 2; - relname | conname | coninhcount | conislocal | connoinherit -----------------------+------------------------+-------------+------------+-------------- - test_inh_check | test_inh_check_a_check | 0 | t | f - test_inh_check_child | test_inh_check_a_check | 1 | f | f -(2 rows) - -ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric; -\d test_inh_check - Table "public.test_inh_check" - Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+--------- - a | numeric | | | - b | double precision | | | -Check constraints: - "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision) -Number of child tables: 1 (Use \d+ to list them.) - -\d test_inh_check_child - Table "public.test_inh_check_child" - Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+--------- - a | numeric | | | - b | double precision | | | -Check constraints: - "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision) -Inherits: test_inh_check - -select relname, conname, coninhcount, conislocal, connoinherit - from pg_constraint c, pg_class r - where relname like 'test_inh_check%' and c.conrelid = r.oid - order by 1, 2; - relname | conname | coninhcount | conislocal | connoinherit -----------------------+------------------------+-------------+------------+-------------- - test_inh_check | test_inh_check_a_check | 0 | t | f - test_inh_check_child | test_inh_check_a_check | 1 | f | f -(2 rows) - --- also try noinherit, local, and local+inherited cases -ALTER TABLE test_inh_check ADD CONSTRAINT bnoinherit CHECK (b > 100) NO INHERIT; -ALTER TABLE test_inh_check_child ADD CONSTRAINT blocal CHECK (b < 1000); -ALTER TABLE test_inh_check_child ADD CONSTRAINT bmerged CHECK (b > 1); -ALTER TABLE test_inh_check ADD CONSTRAINT bmerged CHECK (b > 1); -NOTICE: merging constraint "bmerged" with inherited definition -\d test_inh_check - Table "public.test_inh_check" - Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+--------- - a | numeric | | | - b | double precision | | | -Check constraints: - "bmerged" CHECK (b > 1::double precision) - "bnoinherit" CHECK (b > 100::double precision) NO INHERIT - "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision) -Number of child tables: 1 (Use \d+ to list them.) - -\d test_inh_check_child - Table "public.test_inh_check_child" - Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+--------- - a | numeric | | | - b | double precision | | | -Check constraints: - "blocal" CHECK (b < 1000::double precision) - "bmerged" CHECK (b > 1::double precision) - "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision) -Inherits: test_inh_check - -select relname, conname, coninhcount, conislocal, connoinherit - from pg_constraint c, pg_class r - where relname like 'test_inh_check%' and c.conrelid = r.oid - order by 1, 2; - relname | conname | coninhcount | conislocal | connoinherit -----------------------+------------------------+-------------+------------+-------------- - test_inh_check | bmerged | 0 | t | f - test_inh_check | bnoinherit | 0 | t | t - test_inh_check | test_inh_check_a_check | 0 | t | f - test_inh_check_child | blocal | 0 | t | f - test_inh_check_child | bmerged | 1 | t | f - test_inh_check_child | test_inh_check_a_check | 1 | f | f -(6 rows) - -ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric; -NOTICE: merging constraint "bmerged" with inherited definition -\d test_inh_check - Table "public.test_inh_check" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | numeric | | | - b | numeric | | | -Check constraints: - "bmerged" CHECK (b::double precision > 1::double precision) - "bnoinherit" CHECK (b::double precision > 100::double precision) NO INHERIT - "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision) -Number of child tables: 1 (Use \d+ to list them.) - -\d test_inh_check_child - Table "public.test_inh_check_child" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | numeric | | | - b | numeric | | | -Check constraints: - "blocal" CHECK (b::double precision < 1000::double precision) - "bmerged" CHECK (b::double precision > 1::double precision) - "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision) -Inherits: test_inh_check - -select relname, conname, coninhcount, conislocal, connoinherit - from pg_constraint c, pg_class r - where relname like 'test_inh_check%' and c.conrelid = r.oid - order by 1, 2; - relname | conname | coninhcount | conislocal | connoinherit -----------------------+------------------------+-------------+------------+-------------- - test_inh_check | bmerged | 0 | t | f - test_inh_check | bnoinherit | 0 | t | t - test_inh_check | test_inh_check_a_check | 0 | t | f - test_inh_check_child | blocal | 0 | t | f - test_inh_check_child | bmerged | 1 | t | f - test_inh_check_child | test_inh_check_a_check | 1 | f | f -(6 rows) - --- ALTER COLUMN TYPE with different schema in children --- Bug at https://postgr.es/m/20170102225618.GA10071@telsasoft.com -CREATE TABLE test_type_diff (f1 int); -CREATE TABLE test_type_diff_c (extra smallint) INHERITS (test_type_diff); -ALTER TABLE test_type_diff ADD COLUMN f2 int; -INSERT INTO test_type_diff_c VALUES (1, 2, 3); -ALTER TABLE test_type_diff ALTER COLUMN f2 TYPE bigint USING f2::bigint; -CREATE TABLE test_type_diff2 (int_two int2, int_four int4, int_eight int8); -CREATE TABLE test_type_diff2_c1 (int_four int4, int_eight int8, int_two int2); -CREATE TABLE test_type_diff2_c2 (int_eight int8, int_two int2, int_four int4); -CREATE TABLE test_type_diff2_c3 (int_two int2, int_four int4, int_eight int8); -ALTER TABLE test_type_diff2_c1 INHERIT test_type_diff2; -ALTER TABLE test_type_diff2_c2 INHERIT test_type_diff2; -ALTER TABLE test_type_diff2_c3 INHERIT test_type_diff2; -INSERT INTO test_type_diff2_c1 VALUES (1, 2, 3); -INSERT INTO test_type_diff2_c2 VALUES (4, 5, 6); -INSERT INTO test_type_diff2_c3 VALUES (7, 8, 9); -ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8; --- whole-row references are disallowed -ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2)); -ERROR: cannot convert whole-row table reference -DETAIL: USING expression contains a whole-row table reference. --- check for rollback of ANALYZE corrupting table property flags (bug #11638) -CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text); -CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text); -BEGIN; -ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey; -ANALYZE check_fk_presence_2; -ROLLBACK; -\d check_fk_presence_2 - Table "public.check_fk_presence_2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - id | integer | | | - t | text | | | -Foreign-key constraints: - "check_fk_presence_2_id_fkey" FOREIGN KEY (id) REFERENCES check_fk_presence_1(id) - -DROP TABLE check_fk_presence_1, check_fk_presence_2; --- check column addition within a view (bug #14876) -create table at_base_table(id int, stuff text); -insert into at_base_table values (23, 'skidoo'); -create view at_view_1 as select * from at_base_table bt; -create view at_view_2 as select *, to_json(v1) as j from at_view_1 v1; -\d+ at_view_1 - View "public.at_view_1" - Column | Type | Collation | Nullable | Default | Storage | Description ---------+---------+-----------+----------+---------+----------+------------- - id | integer | | | | plain | - stuff | text | | | | extended | -View definition: - SELECT id, - stuff - FROM at_base_table bt; - -\d+ at_view_2 - View "public.at_view_2" - Column | Type | Collation | Nullable | Default | Storage | Description ---------+---------+-----------+----------+---------+----------+------------- - id | integer | | | | plain | - stuff | text | | | | extended | - j | json | | | | extended | -View definition: - SELECT id, - stuff, - to_json(v1.*) AS j - FROM at_view_1 v1; - -explain (verbose, costs off) select * from at_view_2; - QUERY PLAN ----------------------------------------------------------- - Seq Scan on public.at_base_table bt - Output: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff)) -(2 rows) - -select * from at_view_2; - id | stuff | j -----+--------+---------------------------- - 23 | skidoo | {"id":23,"stuff":"skidoo"} -(1 row) - -create or replace view at_view_1 as select *, 2+2 as more from at_base_table bt; -\d+ at_view_1 - View "public.at_view_1" - Column | Type | Collation | Nullable | Default | Storage | Description ---------+---------+-----------+----------+---------+----------+------------- - id | integer | | | | plain | - stuff | text | | | | extended | - more | integer | | | | plain | -View definition: - SELECT id, - stuff, - 2 + 2 AS more - FROM at_base_table bt; - -\d+ at_view_2 - View "public.at_view_2" - Column | Type | Collation | Nullable | Default | Storage | Description ---------+---------+-----------+----------+---------+----------+------------- - id | integer | | | | plain | - stuff | text | | | | extended | - j | json | | | | extended | -View definition: - SELECT id, - stuff, - to_json(v1.*) AS j - FROM at_view_1 v1; - -explain (verbose, costs off) select * from at_view_2; - QUERY PLAN -------------------------------------------------------------- - Seq Scan on public.at_base_table bt - Output: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff, 4)) -(2 rows) - -select * from at_view_2; - id | stuff | j -----+--------+------------------------------------- - 23 | skidoo | {"id":23,"stuff":"skidoo","more":4} -(1 row) - -drop view at_view_2; -drop view at_view_1; -drop table at_base_table; --- related case (bug #17811) -begin; -create temp table t1 as select * from int8_tbl; -create temp view v1 as select 1::int8 as q1; -create temp view v2 as select * from v1; -create or replace temp view v1 with (security_barrier = true) - as select * from t1; -create temp table log (q1 int8, q2 int8); -create rule v1_upd_rule as on update to v1 - do also insert into log values (new.*); -update v2 set q1 = q1 + 1 where q1 = 123; -select * from t1; - q1 | q2 -------------------+------------------- - 4567890123456789 | 123 - 4567890123456789 | 4567890123456789 - 4567890123456789 | -4567890123456789 - 124 | 456 - 124 | 4567890123456789 -(5 rows) - -select * from log; - q1 | q2 ------+------------------ - 124 | 456 - 124 | 4567890123456789 -(2 rows) - -rollback; --- check adding a column not itself requiring a rewrite, together with --- a column requiring a default (bug #16038) --- ensure that rewrites aren't silently optimized away, removing the --- value of the test -CREATE FUNCTION check_ddl_rewrite(p_tablename regclass, p_ddl text) -RETURNS boolean -LANGUAGE plpgsql AS $$ -DECLARE - v_relfilenode oid; -BEGIN - v_relfilenode := relfilenode FROM pg_class WHERE oid = p_tablename; - - EXECUTE p_ddl; - - RETURN v_relfilenode <> (SELECT relfilenode FROM pg_class WHERE oid = p_tablename); -END; -$$; -CREATE TABLE rewrite_test(col text); -INSERT INTO rewrite_test VALUES ('something'); -INSERT INTO rewrite_test VALUES (NULL); --- empty[12] don't need rewrite, but notempty[12]_rewrite will force one -SELECT check_ddl_rewrite('rewrite_test', $$ - ALTER TABLE rewrite_test - ADD COLUMN empty1 text, - ADD COLUMN notempty1_rewrite serial; -$$); - check_ddl_rewrite -------------------- - t -(1 row) - -SELECT check_ddl_rewrite('rewrite_test', $$ - ALTER TABLE rewrite_test - ADD COLUMN notempty2_rewrite serial, - ADD COLUMN empty2 text; -$$); - check_ddl_rewrite -------------------- - t -(1 row) - --- also check that fast defaults cause no problem, first without rewrite -SELECT check_ddl_rewrite('rewrite_test', $$ - ALTER TABLE rewrite_test - ADD COLUMN empty3 text, - ADD COLUMN notempty3_norewrite int default 42; -$$); - check_ddl_rewrite -------------------- - f -(1 row) - -SELECT check_ddl_rewrite('rewrite_test', $$ - ALTER TABLE rewrite_test - ADD COLUMN notempty4_norewrite int default 42, - ADD COLUMN empty4 text; -$$); - check_ddl_rewrite -------------------- - f -(1 row) - --- then with rewrite -SELECT check_ddl_rewrite('rewrite_test', $$ - ALTER TABLE rewrite_test - ADD COLUMN empty5 text, - ADD COLUMN notempty5_norewrite int default 42, - ADD COLUMN notempty5_rewrite serial; -$$); - check_ddl_rewrite -------------------- - t -(1 row) - -SELECT check_ddl_rewrite('rewrite_test', $$ - ALTER TABLE rewrite_test - ADD COLUMN notempty6_rewrite serial, - ADD COLUMN empty6 text, - ADD COLUMN notempty6_norewrite int default 42; -$$); - check_ddl_rewrite -------------------- - t -(1 row) - --- cleanup -DROP FUNCTION check_ddl_rewrite(regclass, text); -DROP TABLE rewrite_test; --- --- lock levels --- -drop type lockmodes; -ERROR: type "lockmodes" does not exist -create type lockmodes as enum ( - 'SIReadLock' -,'AccessShareLock' -,'RowShareLock' -,'RowExclusiveLock' -,'ShareUpdateExclusiveLock' -,'ShareLock' -,'ShareRowExclusiveLock' -,'ExclusiveLock' -,'AccessExclusiveLock' -); -drop view my_locks; -ERROR: view "my_locks" does not exist -create or replace view my_locks as -select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode -from pg_locks l join pg_class c on l.relation = c.oid -where virtualtransaction = ( - select virtualtransaction - from pg_locks - where transactionid = pg_current_xact_id()::xid) -and locktype = 'relation' -and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog') -and c.relname != 'my_locks' -group by c.relname; -create table alterlock (f1 int primary key, f2 text); -insert into alterlock values (1, 'foo'); -create table alterlock2 (f3 int primary key, f1 int); -insert into alterlock2 values (1, 1); -begin; alter table alterlock alter column f2 set statistics 150; -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock -(1 row) - -rollback; -begin; alter table alterlock cluster on alterlock_pkey; -select * from my_locks order by 1; - relname | max_lockmode -----------------+-------------------------- - alterlock | ShareUpdateExclusiveLock - alterlock_pkey | ShareUpdateExclusiveLock -(2 rows) - -commit; -begin; alter table alterlock set without cluster; -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock -(1 row) - -commit; -begin; alter table alterlock set (fillfactor = 100); -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock - pg_toast | ShareUpdateExclusiveLock -(2 rows) - -commit; -begin; alter table alterlock reset (fillfactor); -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock - pg_toast | ShareUpdateExclusiveLock -(2 rows) - -commit; -begin; alter table alterlock set (toast.autovacuum_enabled = off); -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock - pg_toast | ShareUpdateExclusiveLock -(2 rows) - -commit; -begin; alter table alterlock set (autovacuum_enabled = off); -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock - pg_toast | ShareUpdateExclusiveLock -(2 rows) - -commit; -begin; alter table alterlock alter column f2 set (n_distinct = 1); -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock -(1 row) - -rollback; --- test that mixing options with different lock levels works as expected -begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80); -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock - pg_toast | ShareUpdateExclusiveLock -(2 rows) - -commit; -begin; alter table alterlock alter column f2 set storage extended; -select * from my_locks order by 1; - relname | max_lockmode ------------+--------------------- - alterlock | AccessExclusiveLock -(1 row) - -rollback; -begin; alter table alterlock alter column f2 set default 'x'; -select * from my_locks order by 1; - relname | max_lockmode ------------+--------------------- - alterlock | AccessExclusiveLock -(1 row) - -rollback; -begin; -create function ttdummy () returns trigger language plpgsql as -$$ begin return new; end $$; -create trigger ttdummy - before delete or update on alterlock - for each row - execute procedure - ttdummy (1, 1); -select * from my_locks order by 1; - relname | max_lockmode ------------+----------------------- - alterlock | ShareRowExclusiveLock -(1 row) - -rollback; -begin; -select * from my_locks order by 1; - relname | max_lockmode ----------+-------------- -(0 rows) - -alter table alterlock2 add foreign key (f1) references alterlock (f1); -select * from my_locks order by 1; - relname | max_lockmode ------------------+----------------------- - alterlock | ShareRowExclusiveLock - alterlock2 | ShareRowExclusiveLock - alterlock2_pkey | AccessShareLock - alterlock_pkey | AccessShareLock -(4 rows) - -rollback; -begin; -alter table alterlock2 -add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID; -select * from my_locks order by 1; - relname | max_lockmode -------------+----------------------- - alterlock | ShareRowExclusiveLock - alterlock2 | ShareRowExclusiveLock -(2 rows) - -commit; -begin; -alter table alterlock2 validate constraint alterlock2nv; -select * from my_locks order by 1; - relname | max_lockmode ------------------+-------------------------- - alterlock | RowShareLock - alterlock2 | ShareUpdateExclusiveLock - alterlock2_pkey | AccessShareLock - alterlock_pkey | AccessShareLock -(4 rows) - -rollback; -create or replace view my_locks as -select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode -from pg_locks l join pg_class c on l.relation = c.oid -where virtualtransaction = ( - select virtualtransaction - from pg_locks - where transactionid = pg_current_xact_id()::xid) -and locktype = 'relation' -and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog') -and c.relname = 'my_locks' -group by c.relname; --- raise exception -alter table my_locks set (autovacuum_enabled = false); -ERROR: unrecognized parameter "autovacuum_enabled" -alter view my_locks set (autovacuum_enabled = false); -ERROR: unrecognized parameter "autovacuum_enabled" -alter table my_locks reset (autovacuum_enabled); -alter view my_locks reset (autovacuum_enabled); -begin; -alter view my_locks set (security_barrier=off); -select * from my_locks order by 1; - relname | max_lockmode -----------+--------------------- - my_locks | AccessExclusiveLock -(1 row) - -alter view my_locks reset (security_barrier); -rollback; --- this test intentionally applies the ALTER TABLE command against a view, but --- uses a view option so we expect this to succeed. This form of SQL is --- accepted for historical reasons, as shown in the docs for ALTER VIEW -begin; -alter table my_locks set (security_barrier=off); -select * from my_locks order by 1; - relname | max_lockmode -----------+--------------------- - my_locks | AccessExclusiveLock -(1 row) - -alter table my_locks reset (security_barrier); -rollback; --- cleanup -drop table alterlock2; -drop table alterlock; -drop view my_locks; -drop type lockmodes; --- --- alter function --- -create function test_strict(text) returns text as - 'select coalesce($1, ''got passed a null'');' - language sql returns null on null input; -select test_strict(NULL); - test_strict -------------- - -(1 row) - -alter function test_strict(text) called on null input; -select test_strict(NULL); - test_strict -------------------- - got passed a null -(1 row) - -create function non_strict(text) returns text as - 'select coalesce($1, ''got passed a null'');' - language sql called on null input; -select non_strict(NULL); - non_strict -------------------- - got passed a null -(1 row) - -alter function non_strict(text) returns null on null input; -select non_strict(NULL); - non_strict ------------- - -(1 row) - --- --- alter object set schema --- -create schema alter1; -create schema alter2; -create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0)); -create view alter1.v1 as select * from alter1.t1; -create function alter1.plus1(int) returns int as 'select $1+1' language sql; -create domain alter1.posint integer check (value > 0); -create type alter1.ctype as (f1 int, f2 text); -create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql -as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2'; -create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype); -create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as - operator 1 alter1.=(alter1.ctype, alter1.ctype); -create conversion alter1.latin1_to_utf8 for 'latin1' to 'utf8' from iso8859_1_to_utf8; -create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); -create text search configuration alter1.cfg(parser = alter1.prs); -create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize); -create text search dictionary alter1.dict(template = alter1.tmpl); -insert into alter1.t1(f2) values(11); -insert into alter1.t1(f2) values(12); -alter table alter1.t1 set schema alter1; -- no-op, same schema -alter table alter1.t1 set schema alter2; -alter table alter1.v1 set schema alter2; -alter function alter1.plus1(int) set schema alter2; -alter domain alter1.posint set schema alter2; -alter operator class alter1.ctype_hash_ops using hash set schema alter2; -alter operator family alter1.ctype_hash_ops using hash set schema alter2; -alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2; -alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2; -alter type alter1.ctype set schema alter1; -- no-op, same schema -alter type alter1.ctype set schema alter2; -alter conversion alter1.latin1_to_utf8 set schema alter2; -alter text search parser alter1.prs set schema alter2; -alter text search configuration alter1.cfg set schema alter2; -alter text search template alter1.tmpl set schema alter2; -alter text search dictionary alter1.dict set schema alter2; --- this should succeed because nothing is left in alter1 -drop schema alter1; -insert into alter2.t1(f2) values(13); -insert into alter2.t1(f2) values(14); -select * from alter2.t1; - f1 | f2 -----+---- - 1 | 11 - 2 | 12 - 3 | 13 - 4 | 14 -(4 rows) - -select * from alter2.v1; - f1 | f2 -----+---- - 1 | 11 - 2 | 12 - 3 | 13 - 4 | 14 -(4 rows) - -select alter2.plus1(41); - plus1 -------- - 42 -(1 row) - --- clean up -drop schema alter2 cascade; -NOTICE: drop cascades to 13 other objects -DETAIL: drop cascades to table alter2.t1 -drop cascades to view alter2.v1 -drop cascades to function alter2.plus1(integer) -drop cascades to type alter2.posint -drop cascades to type alter2.ctype -drop cascades to function alter2.same(alter2.ctype,alter2.ctype) -drop cascades to operator alter2.=(alter2.ctype,alter2.ctype) -drop cascades to operator family alter2.ctype_hash_ops for access method hash -drop cascades to conversion alter2.latin1_to_utf8 -drop cascades to text search parser alter2.prs -drop cascades to text search configuration alter2.cfg -drop cascades to text search template alter2.tmpl -drop cascades to text search dictionary alter2.dict --- --- composite types --- -CREATE TYPE test_type AS (a int); -\d test_type - Composite type "public.test_type" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - -ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails -ERROR: relation "nosuchtype" does not exist -ALTER TYPE test_type ADD ATTRIBUTE b text; -\d test_type - Composite type "public.test_type" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | text | | | - -ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails -ERROR: column "b" of relation "test_type" already exists -ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar; -\d test_type - Composite type "public.test_type" - Column | Type | Collation | Nullable | Default ---------+-------------------+-----------+----------+--------- - a | integer | | | - b | character varying | | | - -ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer; -\d test_type - Composite type "public.test_type" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | - -ALTER TYPE test_type DROP ATTRIBUTE b; -\d test_type - Composite type "public.test_type" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - -ALTER TYPE test_type DROP ATTRIBUTE c; -- fails -ERROR: column "c" of relation "test_type" does not exist -ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c; -NOTICE: column "c" of relation "test_type" does not exist, skipping -ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean; -\d test_type - Composite type "public.test_type" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - d | boolean | | | - -ALTER TYPE test_type RENAME ATTRIBUTE a TO aa; -ERROR: column "a" does not exist -ALTER TYPE test_type RENAME ATTRIBUTE d TO dd; -\d test_type - Composite type "public.test_type" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - dd | boolean | | | - -DROP TYPE test_type; -CREATE TYPE test_type1 AS (a int, b text); -CREATE TABLE test_tbl1 (x int, y test_type1); -ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails -ERROR: cannot alter type "test_type1" because column "test_tbl1.y" uses it -DROP TABLE test_tbl1; -CREATE TABLE test_tbl1 (x int, y text); -CREATE INDEX test_tbl1_idx ON test_tbl1((row(x,y)::test_type1)); -ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails -ERROR: cannot alter type "test_type1" because column "test_tbl1_idx.row" uses it -DROP TABLE test_tbl1; -DROP TYPE test_type1; -CREATE TYPE test_type2 AS (a int, b text); -CREATE TABLE test_tbl2 OF test_type2; -CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2); -\d test_type2 - Composite type "public.test_type2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | text | | | - -\d test_tbl2 - Table "public.test_tbl2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | text | | | -Number of child tables: 1 (Use \d+ to list them.) -Typed table of type: test_type2 - -ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails -ERROR: cannot alter type "test_type2" because it is the type of a typed table -HINT: Use ALTER ... CASCADE to alter the typed tables too. -ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE; -\d test_type2 - Composite type "public.test_type2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | text | | | - c | text | | | - -\d test_tbl2 - Table "public.test_tbl2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | text | | | - c | text | | | -Number of child tables: 1 (Use \d+ to list them.) -Typed table of type: test_type2 - -ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails -ERROR: cannot alter type "test_type2" because it is the type of a typed table -HINT: Use ALTER ... CASCADE to alter the typed tables too. -ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE; -\d test_type2 - Composite type "public.test_type2" - Column | Type | Collation | Nullable | Default ---------+-------------------+-----------+----------+--------- - a | integer | | | - b | character varying | | | - c | text | | | - -\d test_tbl2 - Table "public.test_tbl2" - Column | Type | Collation | Nullable | Default ---------+-------------------+-----------+----------+--------- - a | integer | | | - b | character varying | | | - c | text | | | -Number of child tables: 1 (Use \d+ to list them.) -Typed table of type: test_type2 - -ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails -ERROR: cannot alter type "test_type2" because it is the type of a typed table -HINT: Use ALTER ... CASCADE to alter the typed tables too. -ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE; -\d test_type2 - Composite type "public.test_type2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - c | text | | | - -\d test_tbl2 - Table "public.test_tbl2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - c | text | | | -Number of child tables: 1 (Use \d+ to list them.) -Typed table of type: test_type2 - -ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails -ERROR: cannot alter type "test_type2" because it is the type of a typed table -HINT: Use ALTER ... CASCADE to alter the typed tables too. -ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE; -\d test_type2 - Composite type "public.test_type2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - aa | integer | | | - c | text | | | - -\d test_tbl2 - Table "public.test_tbl2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - aa | integer | | | - c | text | | | -Number of child tables: 1 (Use \d+ to list them.) -Typed table of type: test_type2 - -\d test_tbl2_subclass - Table "public.test_tbl2_subclass" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - aa | integer | | | - c | text | | | -Inherits: test_tbl2 - -DROP TABLE test_tbl2_subclass, test_tbl2; -DROP TYPE test_type2; -CREATE TYPE test_typex AS (a int, b text); -CREATE TABLE test_tblx (x int, y test_typex check ((y).a > 0)); -ALTER TYPE test_typex DROP ATTRIBUTE a; -- fails -ERROR: cannot drop column a of composite type test_typex because other objects depend on it -DETAIL: constraint test_tblx_y_check on table test_tblx depends on column a of composite type test_typex -HINT: Use DROP ... CASCADE to drop the dependent objects too. -ALTER TYPE test_typex DROP ATTRIBUTE a CASCADE; -NOTICE: drop cascades to constraint test_tblx_y_check on table test_tblx -\d test_tblx - Table "public.test_tblx" - Column | Type | Collation | Nullable | Default ---------+------------+-----------+----------+--------- - x | integer | | | - y | test_typex | | | - -DROP TABLE test_tblx; -DROP TYPE test_typex; --- This test isn't that interesting on its own, but the purpose is to leave --- behind a table to test pg_upgrade with. The table has a composite type --- column in it, and the composite type has a dropped attribute. -CREATE TYPE test_type3 AS (a int); -CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3; -ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int; -CREATE TYPE test_type_empty AS (); -DROP TYPE test_type_empty; --- --- typed tables: OF / NOT OF --- -CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2)); -ALTER TYPE tt_t0 DROP ATTRIBUTE z; -CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK -CREATE TABLE tt1 (x int, y bigint); -- wrong base type -CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod -CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order -CREATE TABLE tt4 (x int); -- too few columns -CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns -CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent -CREATE TABLE tt7 (x int, q text, y numeric(8,2)); -ALTER TABLE tt7 DROP q; -- OK -ALTER TABLE tt0 OF tt_t0; -ALTER TABLE tt1 OF tt_t0; -ERROR: table "tt1" has different type for column "y" -ALTER TABLE tt2 OF tt_t0; -ERROR: table "tt2" has different type for column "y" -ALTER TABLE tt3 OF tt_t0; -ERROR: table has column "y" where type requires "x" -ALTER TABLE tt4 OF tt_t0; -ERROR: table is missing column "y" -ALTER TABLE tt5 OF tt_t0; -ERROR: table has extra column "z" -ALTER TABLE tt6 OF tt_t0; -ERROR: typed tables cannot inherit -ALTER TABLE tt7 OF tt_t0; -CREATE TYPE tt_t1 AS (x int, y numeric(8,2)); -ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table -ALTER TABLE tt7 NOT OF; -\d tt7 - Table "public.tt7" - Column | Type | Collation | Nullable | Default ---------+--------------+-----------+----------+--------- - x | integer | | | - y | numeric(8,2) | | | - --- make sure we can drop a constraint on the parent but it remains on the child -CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL)); -CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent); -ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check"; --- should fail -INSERT INTO test_drop_constr_child (c) VALUES (NULL); -ERROR: new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check" -DETAIL: Failing row contains (null). -DROP TABLE test_drop_constr_parent CASCADE; -NOTICE: drop cascades to table test_drop_constr_child --- --- IF EXISTS test --- -ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; -NOTICE: relation "tt8" does not exist, skipping -ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f); -NOTICE: relation "tt8" does not exist, skipping -ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); -NOTICE: relation "tt8" does not exist, skipping -ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0; -NOTICE: relation "tt8" does not exist, skipping -ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1; -NOTICE: relation "tt8" does not exist, skipping -ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2; -NOTICE: relation "tt8" does not exist, skipping -CREATE TABLE tt8(a int); -CREATE SCHEMA alter2; -ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; -ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f); -ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); -ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0; -ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1; -ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2; -\d alter2.tt8 - Table "alter2.tt8" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - f1 | integer | | not null | 0 -Indexes: - "xxx" PRIMARY KEY, btree (f1) -Check constraints: - "tt8_f_check" CHECK (f1 >= 0 AND f1 <= 10) - -DROP TABLE alter2.tt8; -DROP SCHEMA alter2; --- --- Check conflicts between index and CHECK constraint names --- -CREATE TABLE tt9(c integer); -ALTER TABLE tt9 ADD CHECK(c > 1); -ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name -ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3); -ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4); -- fail, dup name -ERROR: constraint "foo" for relation "tt9" already exists -ALTER TABLE tt9 ADD UNIQUE(c); -ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name -ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name -ERROR: relation "tt9_c_key" already exists -ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name -ERROR: constraint "foo" for relation "tt9" already exists -ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5); -- fail, dup name -ERROR: constraint "tt9_c_key" for relation "tt9" already exists -ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6); -ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name -\d tt9 - Table "public.tt9" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c | integer | | | -Indexes: - "tt9_c_key" UNIQUE CONSTRAINT, btree (c) - "tt9_c_key1" UNIQUE CONSTRAINT, btree (c) - "tt9_c_key3" UNIQUE CONSTRAINT, btree (c) -Check constraints: - "foo" CHECK (c > 3) - "tt9_c_check" CHECK (c > 1) - "tt9_c_check1" CHECK (c > 2) - "tt9_c_key2" CHECK (c > 6) - -DROP TABLE tt9; --- Check that comments on constraints and indexes are not lost at ALTER TABLE. -CREATE TABLE comment_test ( - id int, - constraint id_notnull_constraint not null id, - positive_col int CHECK (positive_col > 0), - indexed_col int, - CONSTRAINT comment_test_pk PRIMARY KEY (id)); -CREATE INDEX comment_test_index ON comment_test(indexed_col); -COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test'; -COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test'; -COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col'; -COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test'; -COMMENT ON CONSTRAINT id_notnull_constraint ON comment_test IS 'NOT NULL constraint of comment_test'; -COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test'; -SELECT col_description('comment_test'::regclass, 1) as comment; - comment ------------------------------ - Column 'id' on comment_test -(1 row) - -SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2; - index | comment ---------------------+----------------------------------------------- - comment_test_index | Simple index on comment_test - comment_test_pk | Index backing the PRIMARY KEY of comment_test -(2 rows) - -SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2; - constraint | comment ----------------------------------+----------------------------------------------- - comment_test_pk | PRIMARY KEY constraint of comment_test - comment_test_positive_col_check | CHECK constraint on comment_test.positive_col - id_notnull_constraint | NOT NULL constraint of comment_test -(3 rows) - --- Change the datatype of all the columns. ALTER TABLE is optimized to not --- rebuild an index if the new data type is binary compatible with the old --- one. Check do a dummy ALTER TABLE that doesn't change the datatype --- first, to test that no-op codepath, and another one that does. -ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int; -ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text; -ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int; -ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; -ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int; -ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint; --- Some error cases. -ALTER TABLE comment_test ALTER COLUMN xmin SET DATA TYPE x; -ERROR: cannot alter system column "xmin" -LINE 1: ALTER TABLE comment_test ALTER COLUMN xmin SET DATA TYPE x; - ^ -ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE x; -ERROR: type "x" does not exist -LINE 1: ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE x; - ^ -ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int COLLATE "C"; -ERROR: collations are not supported by type integer -LINE 1: ...LE comment_test ALTER COLUMN id SET DATA TYPE int COLLATE "C... - ^ --- Check that the comments are intact. -SELECT col_description('comment_test'::regclass, 1) as comment; - comment ------------------------------ - Column 'id' on comment_test -(1 row) - -SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2; - index | comment ---------------------+----------------------------------------------- - comment_test_index | Simple index on comment_test - comment_test_pk | Index backing the PRIMARY KEY of comment_test -(2 rows) - -SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2; - constraint | comment ----------------------------------+----------------------------------------------- - comment_test_pk | PRIMARY KEY constraint of comment_test - comment_test_positive_col_check | CHECK constraint on comment_test.positive_col - id_notnull_constraint | NOT NULL constraint of comment_test -(3 rows) - --- Check compatibility for foreign keys and comments. This is done --- separately as rebuilding the column type of the parent leads --- to an error and would reduce the test scope. -CREATE TABLE comment_test_child ( - id text CONSTRAINT comment_test_child_fk REFERENCES comment_test); -CREATE INDEX comment_test_child_fk ON comment_test_child(id); -COMMENT ON COLUMN comment_test_child.id IS 'Column ''id'' on comment_test_child'; -COMMENT ON INDEX comment_test_child_fk IS 'Index backing the FOREIGN KEY of comment_test_child'; -COMMENT ON CONSTRAINT comment_test_child_fk ON comment_test_child IS 'FOREIGN KEY constraint of comment_test_child'; --- Change column type of parent -ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; -ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int USING id::integer; -ERROR: foreign key constraint "comment_test_child_fk" cannot be implemented -DETAIL: Key columns "id" of the referencing table and "id" of the referenced table are of incompatible types: text and integer. --- Comments should be intact -SELECT col_description('comment_test_child'::regclass, 1) as comment; - comment ------------------------------------ - Column 'id' on comment_test_child -(1 row) - -SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test_child'::regclass ORDER BY 1, 2; - index | comment ------------------------+----------------------------------------------------- - comment_test_child_fk | Index backing the FOREIGN KEY of comment_test_child -(1 row) - -SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test_child'::regclass ORDER BY 1, 2; - constraint | comment ------------------------+---------------------------------------------- - comment_test_child_fk | FOREIGN KEY constraint of comment_test_child -(1 row) - --- Check that we map relation oids to filenodes and back correctly. Only --- display bad mappings so the test output doesn't change all the time. A --- filenode function call can return NULL for a relation dropped concurrently --- with the call's surrounding query, so ignore a NULL mapped_oid for --- relations that no longer exist after all calls finish. --- Temporary relations are ignored, as not supported by pg_filenode_relation(). -CREATE TEMP TABLE filenode_mapping AS -SELECT - oid, mapped_oid, reltablespace, relfilenode, relname -FROM pg_class, - pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid -WHERE relkind IN ('r', 'i', 'S', 't', 'm') - AND relpersistence != 't' - AND mapped_oid IS DISTINCT FROM oid; -SELECT m.* FROM filenode_mapping m LEFT JOIN pg_class c ON c.oid = m.oid -WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL; - oid | mapped_oid | reltablespace | relfilenode | relname ------+------------+---------------+-------------+--------- -(0 rows) - --- Checks on creating and manipulation of user defined relations in --- pg_catalog. -SHOW allow_system_table_mods; - allow_system_table_mods -------------------------- - off -(1 row) - --- disallowed because of search_path issues with pg_dump -CREATE TABLE pg_catalog.new_system_table(); -ERROR: permission denied to create "pg_catalog.new_system_table" -DETAIL: System catalog modifications are currently disallowed. --- instead create in public first, move to catalog -CREATE TABLE new_system_table(id serial primary key, othercol text); -ALTER TABLE new_system_table SET SCHEMA pg_catalog; -ALTER TABLE new_system_table SET SCHEMA public; -ALTER TABLE new_system_table SET SCHEMA pg_catalog; --- will be ignored -- already there: -ALTER TABLE new_system_table SET SCHEMA pg_catalog; -ALTER TABLE new_system_table RENAME TO old_system_table; -CREATE INDEX old_system_table__othercol ON old_system_table (othercol); -INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata'); -UPDATE old_system_table SET id = -id; -DELETE FROM old_system_table WHERE othercol = 'somedata'; -TRUNCATE old_system_table; -ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey; -ALTER TABLE old_system_table DROP COLUMN othercol; -DROP TABLE old_system_table; --- set logged -CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT); -- has sequence, toast --- check relpersistence of an unlogged table -SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1' -UNION ALL -SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1' -UNION ALL -SELECT r.relname || ' toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1' -ORDER BY relname; - relname | relkind | relpersistence ------------------------+---------+---------------- - unlogged1 | r | u - unlogged1 toast index | i | u - unlogged1 toast table | t | u - unlogged1_f1_seq | S | u - unlogged1_pkey | i | u -(5 rows) - -CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key -CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key -ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key -ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists -ERROR: could not change table "unlogged2" to logged because it references unlogged table "unlogged1" -ALTER TABLE unlogged1 SET LOGGED; --- check relpersistence of an unlogged table after changing to permanent -SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1' -UNION ALL -SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1' -UNION ALL -SELECT r.relname || ' toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1' -ORDER BY relname; - relname | relkind | relpersistence ------------------------+---------+---------------- - unlogged1 | r | p - unlogged1 toast index | i | p - unlogged1 toast table | t | p - unlogged1_f1_seq | S | p - unlogged1_pkey | i | p -(5 rows) - -ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing -DROP TABLE unlogged3; -DROP TABLE unlogged2; -DROP TABLE unlogged1; --- set unlogged -CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT); -- has sequence, toast --- check relpersistence of a permanent table -SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1' -UNION ALL -SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1' -UNION ALL -SELECT r.relname ||' toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1' -ORDER BY relname; - relname | relkind | relpersistence ----------------------+---------+---------------- - logged1 | r | p - logged1 toast index | i | p - logged1 toast table | t | p - logged1_f1_seq | S | p - logged1_pkey | i | p -(5 rows) - -CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key -CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key -ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists -ERROR: could not change table "logged1" to unlogged because it references logged table "logged2" -ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key -ALTER TABLE logged2 SET UNLOGGED; -ALTER TABLE logged1 SET UNLOGGED; --- check relpersistence of a permanent table after changing to unlogged -SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1' -UNION ALL -SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1' -UNION ALL -SELECT r.relname || ' toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1' -ORDER BY relname; - relname | relkind | relpersistence ----------------------+---------+---------------- - logged1 | r | u - logged1 toast index | i | u - logged1 toast table | t | u - logged1_f1_seq | S | u - logged1_pkey | i | u -(5 rows) - -ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing -DROP TABLE logged3; -DROP TABLE logged2; -DROP TABLE logged1; --- test ADD COLUMN IF NOT EXISTS -CREATE TABLE test_add_column(c1 integer); -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - -ALTER TABLE test_add_column - ADD COLUMN c2 integer; -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - c2 | integer | | | - -ALTER TABLE test_add_column - ADD COLUMN c2 integer; -- fail because c2 already exists -ERROR: column "c2" of relation "test_add_column" already exists -ALTER TABLE ONLY test_add_column - ADD COLUMN c2 integer; -- fail because c2 already exists -ERROR: column "c2" of relation "test_add_column" already exists -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - c2 | integer | | | - -ALTER TABLE test_add_column - ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists -NOTICE: column "c2" of relation "test_add_column" already exists, skipping -ALTER TABLE ONLY test_add_column - ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists -NOTICE: column "c2" of relation "test_add_column" already exists, skipping -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - c2 | integer | | | - -ALTER TABLE test_add_column - ADD COLUMN c2 integer, -- fail because c2 already exists - ADD COLUMN c3 integer primary key; -ERROR: column "c2" of relation "test_add_column" already exists -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - c2 | integer | | | - -ALTER TABLE test_add_column - ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists - ADD COLUMN c3 integer primary key; -NOTICE: column "c2" of relation "test_add_column" already exists, skipping -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - c2 | integer | | | - c3 | integer | | not null | -Indexes: - "test_add_column_pkey" PRIMARY KEY, btree (c3) - -ALTER TABLE test_add_column - ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists - ADD COLUMN IF NOT EXISTS c3 integer primary key; -- skipping because c3 already exists -NOTICE: column "c2" of relation "test_add_column" already exists, skipping -NOTICE: column "c3" of relation "test_add_column" already exists, skipping -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - c2 | integer | | | - c3 | integer | | not null | -Indexes: - "test_add_column_pkey" PRIMARY KEY, btree (c3) - -ALTER TABLE test_add_column - ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists - ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists - ADD COLUMN c4 integer REFERENCES test_add_column; -NOTICE: column "c2" of relation "test_add_column" already exists, skipping -NOTICE: column "c3" of relation "test_add_column" already exists, skipping -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - c2 | integer | | | - c3 | integer | | not null | - c4 | integer | | | -Indexes: - "test_add_column_pkey" PRIMARY KEY, btree (c3) -Foreign-key constraints: - "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) -Referenced by: - TABLE "test_add_column" CONSTRAINT "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) - -ALTER TABLE test_add_column - ADD COLUMN IF NOT EXISTS c4 integer REFERENCES test_add_column; -NOTICE: column "c4" of relation "test_add_column" already exists, skipping -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - c1 | integer | | | - c2 | integer | | | - c3 | integer | | not null | - c4 | integer | | | -Indexes: - "test_add_column_pkey" PRIMARY KEY, btree (c3) -Foreign-key constraints: - "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) -Referenced by: - TABLE "test_add_column" CONSTRAINT "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) - -ALTER TABLE test_add_column - ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 8); -\d test_add_column - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------------------------------------------- - c1 | integer | | | - c2 | integer | | | - c3 | integer | | not null | - c4 | integer | | | - c5 | integer | | not null | nextval('test_add_column_c5_seq'::regclass) -Indexes: - "test_add_column_pkey" PRIMARY KEY, btree (c3) -Check constraints: - "test_add_column_c5_check" CHECK (c5 > 8) -Foreign-key constraints: - "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) -Referenced by: - TABLE "test_add_column" CONSTRAINT "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) - -ALTER TABLE test_add_column - ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 10); -NOTICE: column "c5" of relation "test_add_column" already exists, skipping -\d test_add_column* - Table "public.test_add_column" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------------------------------------------- - c1 | integer | | | - c2 | integer | | | - c3 | integer | | not null | - c4 | integer | | | - c5 | integer | | not null | nextval('test_add_column_c5_seq'::regclass) -Indexes: - "test_add_column_pkey" PRIMARY KEY, btree (c3) -Check constraints: - "test_add_column_c5_check" CHECK (c5 > 8) -Foreign-key constraints: - "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) -Referenced by: - TABLE "test_add_column" CONSTRAINT "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) - - Sequence "public.test_add_column_c5_seq" - Type | Start | Minimum | Maximum | Increment | Cycles? | Cache ----------+-------+---------+------------+-----------+---------+------- - integer | 1 | 1 | 2147483647 | 1 | no | 1 -Owned by: public.test_add_column.c5 - - Index "public.test_add_column_pkey" - Column | Type | Key? | Definition ---------+---------+------+------------ - c3 | integer | yes | c3 -primary key, btree, for table "public.test_add_column" - -DROP TABLE test_add_column; -\d test_add_column* --- assorted cases with multiple ALTER TABLE steps -CREATE TABLE ataddindex(f1 INT); -INSERT INTO ataddindex VALUES (42), (43); -CREATE UNIQUE INDEX ataddindexi0 ON ataddindex(f1); -ALTER TABLE ataddindex - ADD PRIMARY KEY USING INDEX ataddindexi0, - ALTER f1 TYPE BIGINT; -\d ataddindex - Table "public.ataddindex" - Column | Type | Collation | Nullable | Default ---------+--------+-----------+----------+--------- - f1 | bigint | | not null | -Indexes: - "ataddindexi0" PRIMARY KEY, btree (f1) - -DROP TABLE ataddindex; -CREATE TABLE ataddindex(f1 VARCHAR(10)); -INSERT INTO ataddindex(f1) VALUES ('foo'), ('a'); -ALTER TABLE ataddindex - ALTER f1 SET DATA TYPE TEXT, - ADD EXCLUDE ((f1 LIKE 'a') WITH =); -\d ataddindex - Table "public.ataddindex" - Column | Type | Collation | Nullable | Default ---------+------+-----------+----------+--------- - f1 | text | | | -Indexes: - "ataddindex_expr_excl" EXCLUDE USING btree ((f1 ~~ 'a'::text) WITH =) - -DROP TABLE ataddindex; -CREATE TABLE ataddindex(id int, ref_id int); -ALTER TABLE ataddindex - ADD PRIMARY KEY (id), - ADD FOREIGN KEY (ref_id) REFERENCES ataddindex; -\d ataddindex - Table "public.ataddindex" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - id | integer | | not null | - ref_id | integer | | | -Indexes: - "ataddindex_pkey" PRIMARY KEY, btree (id) -Foreign-key constraints: - "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id) -Referenced by: - TABLE "ataddindex" CONSTRAINT "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id) - -DROP TABLE ataddindex; -CREATE TABLE ataddindex(id int, ref_id int); -ALTER TABLE ataddindex - ADD UNIQUE (id), - ADD FOREIGN KEY (ref_id) REFERENCES ataddindex (id); -\d ataddindex - Table "public.ataddindex" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - id | integer | | | - ref_id | integer | | | -Indexes: - "ataddindex_id_key" UNIQUE CONSTRAINT, btree (id) -Foreign-key constraints: - "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id) -Referenced by: - TABLE "ataddindex" CONSTRAINT "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id) - -DROP TABLE ataddindex; -CREATE TABLE atnotnull1 (); -ALTER TABLE atnotnull1 - ADD COLUMN a INT, - ALTER a SET NOT NULL; -ALTER TABLE atnotnull1 - ADD COLUMN b INT, - ADD NOT NULL b; -ALTER TABLE atnotnull1 - ADD COLUMN c INT, - ADD PRIMARY KEY (c); -\d+ atnotnull1 - Table "public.atnotnull1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | - b | integer | | not null | | plain | | - c | integer | | not null | | plain | | -Indexes: - "atnotnull1_pkey" PRIMARY KEY, btree (c) -Not-null constraints: - "atnotnull1_a_not_null" NOT NULL "a" - "atnotnull1_b_not_null" NOT NULL "b" - "atnotnull1_c_not_null" NOT NULL "c" - --- cannot drop column that is part of the partition key -CREATE TABLE partitioned ( - a int, - b int -) PARTITION BY RANGE (a, (a+b+1)); -ALTER TABLE partitioned DROP COLUMN a; -ERROR: cannot drop column "a" because it is part of the partition key of relation "partitioned" -ALTER TABLE partitioned ALTER COLUMN a TYPE char(5); -ERROR: cannot alter column "a" because it is part of the partition key of relation "partitioned" -LINE 1: ALTER TABLE partitioned ALTER COLUMN a TYPE char(5); - ^ -ALTER TABLE partitioned DROP COLUMN b; -ERROR: cannot drop column "b" because it is part of the partition key of relation "partitioned" -ALTER TABLE partitioned ALTER COLUMN b TYPE char(5); -ERROR: cannot alter column "b" because it is part of the partition key of relation "partitioned" -LINE 1: ALTER TABLE partitioned ALTER COLUMN b TYPE char(5); - ^ --- specifying storage parameters for partitioned tables is not supported -ALTER TABLE partitioned SET (fillfactor=100); -ERROR: cannot specify storage parameters for a partitioned table -HINT: Specify storage parameters for its leaf partitions instead. --- partitioned table cannot participate in regular inheritance -CREATE TABLE nonpartitioned ( - a int, - b int -); -ALTER TABLE partitioned INHERIT nonpartitioned; -ERROR: cannot change inheritance of partitioned table -ALTER TABLE nonpartitioned INHERIT partitioned; -ERROR: cannot inherit from partitioned table "partitioned" --- cannot add NO INHERIT constraint to partitioned tables -ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT; -ERROR: cannot add NO INHERIT constraint to partitioned table "partitioned" -DROP TABLE partitioned, nonpartitioned; --- --- ATTACH PARTITION --- --- check that target table is partitioned -CREATE TABLE unparted ( - a int -); -CREATE TABLE fail_part (like unparted); -ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a'); -ERROR: ALTER action ATTACH PARTITION cannot be performed on relation "unparted" -DETAIL: This operation is not supported for tables. -DROP TABLE unparted, fail_part; --- check that partition bound is compatible -CREATE TABLE list_parted ( - a int NOT NULL, - b char(2) COLLATE "C", - CONSTRAINT check_a CHECK (a > 0) -) PARTITION BY LIST (a); -CREATE TABLE fail_part (LIKE list_parted); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10); -ERROR: invalid bound specification for a list partition -LINE 1: ...list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) T... - ^ -DROP TABLE fail_part; --- check that the table being attached exists -ALTER TABLE list_parted ATTACH PARTITION nonexistent FOR VALUES IN (1); -ERROR: relation "nonexistent" does not exist --- check ownership of the source table -CREATE ROLE regress_test_me; -CREATE ROLE regress_test_not_me; -CREATE TABLE not_owned_by_me (LIKE list_parted); -ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me; -SET SESSION AUTHORIZATION regress_test_me; -CREATE TABLE owned_by_me ( - a int -) PARTITION BY LIST (a); -ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1); -ERROR: must be owner of table not_owned_by_me -RESET SESSION AUTHORIZATION; -DROP TABLE owned_by_me, not_owned_by_me; -DROP ROLE regress_test_not_me; -DROP ROLE regress_test_me; --- check that the table being attached is not part of regular inheritance -CREATE TABLE parent (LIKE list_parted); -CREATE TABLE child () INHERITS (parent); -ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1); -ERROR: cannot attach inheritance child as partition -ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1); -ERROR: cannot attach inheritance parent as partition -DROP TABLE child; --- now it should work, with a little tweak -ALTER TABLE parent ADD CONSTRAINT check_a CHECK (a > 0); -ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1); --- test insert/update, per bug #18550 -INSERT INTO parent VALUES (1); -UPDATE parent SET a = 2 WHERE a = 1; -ERROR: new row for relation "parent" violates partition constraint -DETAIL: Failing row contains (2, null). -DROP TABLE parent CASCADE; --- check any TEMP-ness -CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a); -CREATE TABLE perm_part (a int); -ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1); -ERROR: cannot attach a permanent relation as partition of temporary relation "temp_parted" -DROP TABLE temp_parted, perm_part; --- check that the table being attached is not a typed table -CREATE TYPE mytype AS (a int); -CREATE TABLE fail_part OF mytype; -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: cannot attach a typed table as partition -DROP TYPE mytype CASCADE; -NOTICE: drop cascades to table fail_part --- check that the table being attached has only columns present in the parent -CREATE TABLE fail_part (like list_parted, c int); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: table "fail_part" contains column "c" not found in parent "list_parted" -DETAIL: The new partition may contain only the columns present in parent. -DROP TABLE fail_part; --- check that the table being attached has every column of the parent -CREATE TABLE fail_part (a int NOT NULL); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: child table is missing column "b" -DROP TABLE fail_part; --- check that columns match in type, collation and NOT NULL status -CREATE TABLE fail_part ( - b char(3), - a int NOT NULL -); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: child table "fail_part" has different type for column "b" -ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "POSIX"; -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: child table "fail_part" has different collation for column "b" -DROP TABLE fail_part; --- check that the table being attached has all constraints of the parent -CREATE TABLE fail_part ( - b char(2) COLLATE "C", - a int NOT NULL -); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: child table is missing constraint "check_a" --- check that the constraint matches in definition with parent's constraint -ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: child table "fail_part" has different definition for check constraint "check_a" -DROP TABLE fail_part; --- check the attributes and constraints after partition is attached -CREATE TABLE part_1 ( - a int NOT NULL, - b char(2) COLLATE "C", - CONSTRAINT check_a CHECK (a > 0) -); -ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1); --- attislocal and conislocal are always false for merged attributes and constraints respectively. -SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0; - attislocal | attinhcount -------------+------------- - f | 1 - f | 1 -(2 rows) - -SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a'; - conislocal | coninhcount -------------+------------- - f | 1 -(1 row) - --- check that NOT NULL NO INHERIT cannot be merged to a normal NOT NULL -CREATE TABLE part_fail (a int NOT NULL NO INHERIT, - b char(2) COLLATE "C", - CONSTRAINT check_a CHECK (a > 0) -); -ALTER TABLE list_parted ATTACH PARTITION part_fail FOR VALUES IN (2); -ERROR: constraint "part_fail_a_not_null" conflicts with non-inherited constraint on child table "part_fail" -DROP TABLE part_fail; --- check that the new partition won't overlap with an existing partition -CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: partition "fail_part" would overlap partition "part_1" -LINE 1: ...LE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); - ^ -DROP TABLE fail_part; --- check that an existing table can be attached as a default partition -CREATE TABLE def_part (LIKE list_parted INCLUDING CONSTRAINTS); -ALTER TABLE list_parted ATTACH PARTITION def_part DEFAULT; --- check attaching default partition fails if a default partition already --- exists -CREATE TABLE fail_def_part (LIKE part_1 INCLUDING CONSTRAINTS); -ALTER TABLE list_parted ATTACH PARTITION fail_def_part DEFAULT; -ERROR: partition "fail_def_part" conflicts with existing default partition "def_part" -LINE 1: ...ER TABLE list_parted ATTACH PARTITION fail_def_part DEFAULT; - ^ --- check validation when attaching list partitions -CREATE TABLE list_parted2 ( - a int, - b char -) PARTITION BY LIST (a); --- check that violating rows are correctly reported -CREATE TABLE part_2 (LIKE list_parted2); -INSERT INTO part_2 VALUES (3, 'a'); -ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); -ERROR: partition constraint of relation "part_2" is violated by some row --- should be ok after deleting the bad row -DELETE FROM part_2; -ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); --- check partition cannot be attached if default has some row for its values -CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT; -INSERT INTO list_parted2_def VALUES (11, 'z'); -CREATE TABLE part_3 (LIKE list_parted2); -ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11); -ERROR: updated partition constraint for default partition "list_parted2_def" would be violated by some row --- should be ok after deleting the bad row -DELETE FROM list_parted2_def WHERE a = 11; -ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11); --- adding constraints that describe the desired partition constraint --- (or more restrictive) will help skip the validation scan -CREATE TABLE part_3_4 ( - LIKE list_parted2, - CONSTRAINT check_a CHECK (a IN (3)) -); --- however, if a list partition does not accept nulls, there should be --- an explicit NOT NULL constraint on the partition key column for the --- validation scan to be skipped; -ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); --- adding a NOT NULL constraint will cause the scan to be skipped -ALTER TABLE list_parted2 DETACH PARTITION part_3_4; -ALTER TABLE part_3_4 ALTER a SET NOT NULL; -ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); --- check if default partition scan skipped -ALTER TABLE list_parted2_def ADD CONSTRAINT check_a CHECK (a IN (5, 6)); -CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66); --- check validation when attaching range partitions -CREATE TABLE range_parted ( - a int, - b int -) PARTITION BY RANGE (a, b); --- check that violating rows are correctly reported -CREATE TABLE part1 ( - a int NOT NULL CHECK (a = 1), - b int NOT NULL CHECK (b >= 1 AND b <= 10) -); -INSERT INTO part1 VALUES (1, 10); --- Remember the TO bound is exclusive -ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); -ERROR: partition constraint of relation "part1" is violated by some row --- should be ok after deleting the bad row -DELETE FROM part1; -ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); --- adding constraints that describe the desired partition constraint --- (or more restrictive) will help skip the validation scan -CREATE TABLE part2 ( - a int NOT NULL CHECK (a = 1), - b int NOT NULL CHECK (b >= 10 AND b < 18) -); -ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20); --- Create default partition -CREATE TABLE partr_def1 PARTITION OF range_parted DEFAULT; --- Only one default partition is allowed, hence, following should give error -CREATE TABLE partr_def2 (LIKE part1 INCLUDING CONSTRAINTS); -ALTER TABLE range_parted ATTACH PARTITION partr_def2 DEFAULT; -ERROR: partition "partr_def2" conflicts with existing default partition "partr_def1" -LINE 1: ...LTER TABLE range_parted ATTACH PARTITION partr_def2 DEFAULT; - ^ --- Overlapping partitions cannot be attached, hence, following should give error -INSERT INTO partr_def1 VALUES (2, 10); -CREATE TABLE part3 (LIKE range_parted); -ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (2, 10) TO (2, 20); -ERROR: updated partition constraint for default partition "partr_def1" would be violated by some row --- Attaching partitions should be successful when there are no overlapping rows -ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (3, 10) TO (3, 20); --- check that leaf partitions are scanned when attaching a partitioned --- table -CREATE TABLE part_5 ( - LIKE list_parted2 -) PARTITION BY LIST (b); --- check that violating rows are correctly reported -CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a'); -INSERT INTO part_5_a (a, b) VALUES (6, 'a'); -ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); -ERROR: partition constraint of relation "part_5_a" is violated by some row --- delete the faulting row and also add a constraint to skip the scan -DELETE FROM part_5_a WHERE a NOT IN (3); -ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5); -ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); -ALTER TABLE list_parted2 DETACH PARTITION part_5; -ALTER TABLE part_5 DROP CONSTRAINT check_a; --- scan should again be skipped, even though NOT NULL is now a column property -ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL; -ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); --- Check the case where attnos of the partitioning columns in the table being --- attached differs from the parent. It should not affect the constraint- --- checking logic that allows to skip the scan. -CREATE TABLE part_6 ( - c int, - LIKE list_parted2, - CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 6) -); -ALTER TABLE part_6 DROP c; -ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6); --- Similar to above, but the table being attached is a partitioned table --- whose partition has still different attnos for the root partitioning --- columns. -CREATE TABLE part_7 ( - LIKE list_parted2, - CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7) -) PARTITION BY LIST (b); -CREATE TABLE part_7_a_null ( - c int, - d int, - e int, - LIKE list_parted2, -- 'a' will have attnum = 4 - CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'), - CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7) -); -ALTER TABLE part_7_a_null DROP c, DROP d, DROP e; -ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN ('a', null); -ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7); --- Same example, but check this time that the constraint correctly detects --- violating rows -ALTER TABLE list_parted2 DETACH PARTITION part_7; -ALTER TABLE part_7 DROP CONSTRAINT check_a; -- thusly, scan won't be skipped -INSERT INTO part_7 (a, b) VALUES (8, null), (9, 'a'); -SELECT tableoid::regclass, a, b FROM part_7 order by a; - tableoid | a | b ----------------+---+--- - part_7_a_null | 8 | - part_7_a_null | 9 | a -(2 rows) - -ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7); -ERROR: partition constraint of relation "part_7_a_null" is violated by some row --- check that leaf partitions of default partition are scanned when --- attaching a partitioned table. -ALTER TABLE part_5 DROP CONSTRAINT check_a; -CREATE TABLE part5_def PARTITION OF part_5 DEFAULT PARTITION BY LIST(a); -CREATE TABLE part5_def_p1 PARTITION OF part5_def FOR VALUES IN (5); -INSERT INTO part5_def_p1 VALUES (5, 'y'); -CREATE TABLE part5_p1 (LIKE part_5); -ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y'); -ERROR: updated partition constraint for default partition "part5_def_p1" would be violated by some row --- should be ok after deleting the bad row -DELETE FROM part5_def_p1 WHERE b = 'y'; -ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y'); --- check that the table being attached is not already a partition -ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); -ERROR: "part_2" is already a partition --- check that circular inheritance is not allowed -ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b'); -ERROR: circular inheritance not allowed -DETAIL: "part_5" is already a child of "list_parted2". -ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); -ERROR: circular inheritance not allowed -DETAIL: "list_parted2" is already a child of "list_parted2". --- If a partitioned table being created or an existing table being attached --- as a partition does not have a constraint that would allow validation scan --- to be skipped, but an individual partition does, then the partition's --- validation scan is skipped. -CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a); -CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b); -CREATE TABLE quuux_default1 PARTITION OF quuux_default ( - CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1) -) FOR VALUES IN ('b'); -CREATE TABLE quuux1 (a int, b text); -ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate! -CREATE TABLE quuux2 (a int, b text); -ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation -DROP TABLE quuux1, quuux2; --- should validate for quuux1, but not for quuux2 -CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1); -CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2); -DROP TABLE quuux; --- check validation when attaching hash partitions --- Use hand-rolled hash functions and operator class to get predictable result --- on different machines. part_test_int4_ops is defined in test_setup.sql. --- check that the new partition won't overlap with an existing partition -CREATE TABLE hash_parted ( - a int, - b int -) PARTITION BY HASH (a part_test_int4_ops); -CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0); -CREATE TABLE fail_part (LIKE hpart_1); -ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4); -ERROR: partition "fail_part" would overlap partition "hpart_1" -LINE 1: ...hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODU... - ^ -ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 0); -ERROR: partition "fail_part" would overlap partition "hpart_1" -LINE 1: ...hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODU... - ^ -DROP TABLE fail_part; --- check validation when attaching hash partitions --- check that violating rows are correctly reported -CREATE TABLE hpart_2 (LIKE hash_parted); -INSERT INTO hpart_2 VALUES (3, 0); -ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1); -ERROR: partition constraint of relation "hpart_2" is violated by some row --- should be ok after deleting the bad row -DELETE FROM hpart_2; -ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1); --- check that leaf partitions are scanned when attaching a partitioned --- table -CREATE TABLE hpart_5 ( - LIKE hash_parted -) PARTITION BY LIST (b); --- check that violating rows are correctly reported -CREATE TABLE hpart_5_a PARTITION OF hpart_5 FOR VALUES IN ('1', '2', '3'); -INSERT INTO hpart_5_a (a, b) VALUES (7, 1); -ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2); -ERROR: partition constraint of relation "hpart_5_a" is violated by some row --- should be ok after deleting the bad row -DELETE FROM hpart_5_a; -ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2); --- check that the table being attach is with valid modulus and remainder value -CREATE TABLE fail_part(LIKE hash_parted); -ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 0, REMAINDER 1); -ERROR: modulus for hash partition must be an integer value greater than zero -ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 8); -ERROR: remainder for hash partition must be less than modulus -ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 3, REMAINDER 2); -ERROR: every hash partition modulus must be a factor of the next larger modulus -DETAIL: The new modulus 3 is not a factor of 4, the modulus of existing partition "hpart_1". -DROP TABLE fail_part; --- --- DETACH PARTITION --- --- check that the table is partitioned at all -CREATE TABLE regular_table (a int); -ALTER TABLE regular_table DETACH PARTITION any_name; -ERROR: ALTER action DETACH PARTITION cannot be performed on relation "regular_table" -DETAIL: This operation is not supported for tables. -ALTER TABLE regular_table DETACH PARTITION any_name CONCURRENTLY; -ERROR: ALTER action DETACH PARTITION cannot be performed on relation "regular_table" -DETAIL: This operation is not supported for tables. -ALTER TABLE regular_table DETACH PARTITION any_name FINALIZE; -ERROR: ALTER action DETACH PARTITION ... FINALIZE cannot be performed on relation "regular_table" -DETAIL: This operation is not supported for tables. -DROP TABLE regular_table; --- check that the partition being detached exists at all -ALTER TABLE list_parted2 DETACH PARTITION part_4; -ERROR: relation "part_4" does not exist -ALTER TABLE hash_parted DETACH PARTITION hpart_4; -ERROR: relation "hpart_4" does not exist --- check that the partition being detached is actually a partition of the parent -CREATE TABLE not_a_part (a int); -ALTER TABLE list_parted2 DETACH PARTITION not_a_part; -ERROR: relation "not_a_part" is not a partition of relation "list_parted2" -ALTER TABLE list_parted2 DETACH PARTITION part_1; -ERROR: relation "part_1" is not a partition of relation "list_parted2" -ALTER TABLE hash_parted DETACH PARTITION not_a_part; -ERROR: relation "not_a_part" is not a partition of relation "hash_parted" -DROP TABLE not_a_part; --- check that, after being detached, attinhcount/coninhcount is dropped to 0 and --- attislocal/conislocal is set to true -ALTER TABLE list_parted2 DETACH PARTITION part_3_4; -SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0; - attinhcount | attislocal --------------+------------ - 0 | t - 0 | t -(2 rows) - -SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a'; - coninhcount | conislocal --------------+------------ - 0 | t -(1 row) - -DROP TABLE part_3_4; --- check that a detached partition is not dropped on dropping a partitioned table -CREATE TABLE range_parted2 ( - a int -) PARTITION BY RANGE(a); -CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100); -ALTER TABLE range_parted2 DETACH PARTITION part_rp; -DROP TABLE range_parted2; -SELECT * from part_rp; - a ---- -(0 rows) - -DROP TABLE part_rp; --- concurrent detach -CREATE TABLE range_parted2 ( - a int -) PARTITION BY RANGE(a); -CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100); -BEGIN; --- doesn't work in a partition block -ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY; -ERROR: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block -COMMIT; -CREATE TABLE part_rpd PARTITION OF range_parted2 DEFAULT; --- doesn't work if there's a default partition -ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY; -ERROR: cannot detach partitions concurrently when a default partition exists --- doesn't work for the default partition -ALTER TABLE range_parted2 DETACH PARTITION part_rpd CONCURRENTLY; -ERROR: cannot detach partitions concurrently when a default partition exists -DROP TABLE part_rpd; --- works fine -ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY; -\d+ range_parted2 - Partitioned table "public.range_parted2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Partition key: RANGE (a) -Number of partitions: 0 - -DROP TABLE range_parted2; --- Test that hash partitions continue to work after they're concurrently --- detached (bugs #18371, #19070) -CREATE TABLE hash_parted2 (a int) PARTITION BY HASH(a); -CREATE TABLE part_hp PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 0); -ALTER TABLE hash_parted2 DETACH PARTITION part_hp CONCURRENTLY; -DROP TABLE hash_parted2; -INSERT INTO part_hp VALUES (1); -DROP TABLE part_hp; --- Check ALTER TABLE commands for partitioned tables and partitions --- cannot add/drop column to/from *only* the parent -ALTER TABLE ONLY list_parted2 ADD COLUMN c int; -ERROR: column must be added to child tables too -ALTER TABLE ONLY list_parted2 DROP COLUMN b; -ERROR: cannot drop column from only the partitioned table when partitions exist -HINT: Do not specify the ONLY keyword. --- cannot add a column to partition or drop an inherited one -ALTER TABLE part_2 ADD COLUMN c text; -ERROR: cannot add column to a partition -ALTER TABLE part_2 DROP COLUMN b; -ERROR: cannot drop inherited column "b" --- Nor rename, alter type -ALTER TABLE part_2 RENAME COLUMN b to c; -ERROR: cannot rename inherited column "b" -ALTER TABLE part_2 ALTER COLUMN b TYPE text; -ERROR: cannot alter inherited column "b" -LINE 1: ALTER TABLE part_2 ALTER COLUMN b TYPE text; - ^ --- cannot add NOT NULL or check constraints to *only* the parent, when --- partitions exist -ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL; -ERROR: constraint must be added to child tables too -HINT: Do not specify the ONLY keyword. -ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); -ERROR: constraint must be added to child tables too --- dropping them is ok though -ALTER TABLE list_parted2 ALTER b SET NOT NULL; -ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL; -ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); -ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b; --- ... and the partitions should still have both -\d+ part_2 - Table "public.part_2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------------+-----------+----------+---------+----------+--------------+------------- - a | integer | | | | plain | | - b | character(1) | | not null | | extended | | -Partition of: list_parted2 FOR VALUES IN (2) -Partition constraint: ((a IS NOT NULL) AND (a = 2)) -Check constraints: - "check_b" CHECK (b <> 'zz'::bpchar) -Not-null constraints: - "list_parted2_b_not_null" NOT NULL "b" - --- It's alright though, if no partitions are yet created -CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a); -ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL; -ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0); -DROP TABLE parted_no_parts; --- cannot drop inherited NOT NULL or check constraints from partition -ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0); -ALTER TABLE part_2 ALTER b DROP NOT NULL; -ERROR: column "b" is marked NOT NULL in parent table -ALTER TABLE part_2 DROP CONSTRAINT check_a2; -ERROR: cannot drop inherited constraint "check_a2" of relation "part_2" --- can't drop NOT NULL from under an invalid PK -CREATE TABLE list_parted3 (a int NOT NULL) PARTITION BY LIST (a); -CREATE TABLE list_parted3_1 PARTITION OF list_parted3 FOR VALUES IN (1); -ALTER TABLE ONLY list_parted3 ADD PRIMARY KEY (a); -ALTER TABLE ONLY list_parted3 DROP CONSTRAINT list_parted3_a_not_null; -ERROR: column "a" is in a primary key --- Doesn't make sense to add NO INHERIT constraints on partitioned tables -ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT; -ERROR: cannot add NO INHERIT constraint to partitioned table "list_parted2" --- check that a partition cannot participate in regular inheritance -CREATE TABLE inh_test () INHERITS (part_2); -ERROR: cannot inherit from partition "part_2" -CREATE TABLE inh_test (LIKE part_2); -ALTER TABLE inh_test INHERIT part_2; -ERROR: cannot inherit from a partition -ALTER TABLE part_2 INHERIT inh_test; -ERROR: cannot change inheritance of a partition --- cannot drop or alter type of partition key columns of lower level --- partitioned tables; for example, part_5, which is list_parted2's --- partition, is partitioned on b; -ALTER TABLE list_parted2 DROP COLUMN b; -ERROR: cannot drop column "b" because it is part of the partition key of relation "part_5" -ALTER TABLE list_parted2 ALTER COLUMN b TYPE text; -ERROR: cannot alter column "b" because it is part of the partition key of relation "part_5" -LINE 1: ALTER TABLE list_parted2 ALTER COLUMN b TYPE text; - ^ --- dropping non-partition key columns should be allowed on the parent table. -ALTER TABLE list_parted DROP COLUMN b; -SELECT * FROM list_parted; - a ---- -(0 rows) - --- cleanup -DROP TABLE list_parted, list_parted2, range_parted, list_parted3; -DROP TABLE fail_def_part; -DROP TABLE hash_parted; --- more tests for certain multi-level partitioning scenarios -create table p (a int, b int) partition by range (a, b); -create table p1 (b int, a int not null) partition by range (b); -create table p11 (like p1); -alter table p11 drop a; -alter table p11 add a int; -alter table p11 drop a; -alter table p11 add a int not null; --- attnum for key attribute 'a' is different in p, p1, and p11 -select attrelid::regclass, attname, attnum -from pg_attribute -where attname = 'a' - and (attrelid = 'p'::regclass - or attrelid = 'p1'::regclass - or attrelid = 'p11'::regclass) -order by attrelid::regclass::text; - attrelid | attname | attnum -----------+---------+-------- - p | a | 1 - p1 | a | 2 - p11 | a | 4 -(3 rows) - -alter table p1 attach partition p11 for values from (2) to (5); -insert into p1 (a, b) values (2, 3); --- check that partition validation scan correctly detects violating rows -alter table p attach partition p1 for values from (1, 2) to (1, 10); -ERROR: partition constraint of relation "p11" is violated by some row --- cleanup -drop table p; -drop table p1; --- validate constraint on partitioned tables should only scan leaf partitions -create table parted_validate_test (a int) partition by list (a); -create table parted_validate_test_1 partition of parted_validate_test for values in (0, 1); -alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid; -alter table parted_validate_test validate constraint parted_validate_test_chka; -drop table parted_validate_test; --- test alter column options -CREATE TABLE attmp(i integer); -INSERT INTO attmp VALUES (1); -ALTER TABLE attmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2); -ALTER TABLE attmp ALTER COLUMN i RESET (n_distinct_inherited); -ANALYZE attmp; -DROP TABLE attmp; -DROP USER regress_alter_table_user1; --- check that violating rows are correctly reported when attaching as the --- default partition -create table defpart_attach_test (a int) partition by list (a); -create table defpart_attach_test1 partition of defpart_attach_test for values in (1); -create table defpart_attach_test_d (b int, a int); -alter table defpart_attach_test_d drop b; -insert into defpart_attach_test_d values (1), (2); --- error because its constraint as the default partition would be violated --- by the row containing 1 -alter table defpart_attach_test attach partition defpart_attach_test_d default; -ERROR: partition constraint of relation "defpart_attach_test_d" is violated by some row -delete from defpart_attach_test_d where a = 1; -alter table defpart_attach_test_d add check (a > 1); --- should be attached successfully and without needing to be scanned -alter table defpart_attach_test attach partition defpart_attach_test_d default; --- check that attaching a partition correctly reports any rows in the default --- partition that should not be there for the new partition to be attached --- successfully -create table defpart_attach_test_2 (like defpart_attach_test_d); -alter table defpart_attach_test attach partition defpart_attach_test_2 for values in (2); -ERROR: updated partition constraint for default partition "defpart_attach_test_d" would be violated by some row -drop table defpart_attach_test; --- check combinations of temporary and permanent relations when attaching --- partitions. -create table perm_part_parent (a int) partition by list (a); -create temp table temp_part_parent (a int) partition by list (a); -create table perm_part_child (a int); -create temp table temp_part_child (a int); -alter table temp_part_parent attach partition perm_part_child default; -- error -ERROR: cannot attach a permanent relation as partition of temporary relation "temp_part_parent" -alter table perm_part_parent attach partition temp_part_child default; -- error -ERROR: cannot attach a temporary relation as partition of permanent relation "perm_part_parent" -alter table temp_part_parent attach partition temp_part_child default; -- ok -drop table perm_part_parent cascade; -drop table temp_part_parent cascade; --- check that attaching partitions to a table while it is being used is --- prevented -create table tab_part_attach (a int) partition by list (a); -create or replace function func_part_attach() returns trigger - language plpgsql as $$ - begin - execute 'create table tab_part_attach_1 (a int)'; - execute 'alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)'; - return null; - end $$; -create trigger trig_part_attach before insert on tab_part_attach - for each statement execute procedure func_part_attach(); -insert into tab_part_attach values (1); -ERROR: cannot ALTER TABLE "tab_part_attach" because it is being used by active queries in this session -CONTEXT: SQL statement "alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)" -PL/pgSQL function func_part_attach() line 4 at EXECUTE -drop table tab_part_attach; -drop function func_part_attach(); --- test case where the partitioning operator is a SQL function whose --- evaluation results in the table's relcache being rebuilt partway through --- the execution of an ATTACH PARTITION command -create function at_test_sql_partop (int4, int4) returns int language sql -as $$ select case when $1 = $2 then 0 when $1 > $2 then 1 else -1 end; $$; -create operator class at_test_sql_partop for type int4 using btree as - operator 1 < (int4, int4), operator 2 <= (int4, int4), - operator 3 = (int4, int4), operator 4 >= (int4, int4), - operator 5 > (int4, int4), function 1 at_test_sql_partop(int4, int4); -create table at_test_sql_partop (a int) partition by range (a at_test_sql_partop); -create table at_test_sql_partop_1 (a int); -alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values from (0) to (10); -drop table at_test_sql_partop; -drop operator class at_test_sql_partop using btree; -drop function at_test_sql_partop; -/* Test case for bug #16242 */ --- We create a parent and child where the child has missing --- non-null attribute values, and arrange to pass them through --- tuple conversion from the child to the parent tupdesc -create table bar1 (a integer, b integer not null default 1) - partition by range (a); -create table bar2 (a integer); -insert into bar2 values (1); -alter table bar2 add column b integer not null default 1; --- (at this point bar2 contains tuple with natts=1) -alter table bar1 attach partition bar2 default; --- this works: -select * from bar1; - a | b ----+--- - 1 | 1 -(1 row) - --- this exercises tuple conversion: -create function xtrig() - returns trigger language plpgsql -as $$ - declare - r record; - begin - for r in select * from old loop - raise info 'a=%, b=%', r.a, r.b; - end loop; - return NULL; - end; -$$; -create trigger xtrig - after update on bar1 - referencing old table as old - for each statement execute procedure xtrig(); -update bar1 set a = a + 1; -INFO: a=1, b=1 -/* End test case for bug #16242 */ -/* Test case for bug #17409 */ -create table attbl (p1 int constraint pk_attbl primary key); -create table atref (c1 int references attbl(p1)); -cluster attbl using pk_attbl; -alter table attbl alter column p1 set data type bigint; -alter table atref alter column c1 set data type bigint; -drop table attbl, atref; -create table attbl (p1 int constraint pk_attbl primary key); -alter table attbl replica identity using index pk_attbl; -create table atref (c1 int references attbl(p1)); -alter table attbl alter column p1 set data type bigint; -alter table atref alter column c1 set data type bigint; -drop table attbl, atref; -/* End test case for bug #17409 */ -/* Test case for bug #18970 */ -create table attbl(a int); -create table atref(b attbl check ((b).a is not null)); -alter table attbl alter column a type numeric; -- someday this should work -ERROR: cannot alter table "attbl" because column "atref.b" uses its row type -alter table atref drop constraint atref_b_check; -create statistics atref_stat on ((b).a is not null) from atref; -alter table attbl alter column a type numeric; -- someday this should work -ERROR: cannot alter table "attbl" because column "atref.b" uses its row type -drop statistics atref_stat; -create index atref_idx on atref (((b).a)); -alter table attbl alter column a type numeric; -- someday this should work -ERROR: cannot alter table "attbl" because column "atref.b" uses its row type -drop table attbl, atref; -/* End test case for bug #18970 */ --- Test that ALTER TABLE rewrite preserves a clustered index --- for normal indexes and indexes on constraints. -create table alttype_cluster (a int); -alter table alttype_cluster add primary key (a); -create index alttype_cluster_ind on alttype_cluster (a); -alter table alttype_cluster cluster on alttype_cluster_ind; --- Normal index remains clustered. -select indexrelid::regclass, indisclustered from pg_index - where indrelid = 'alttype_cluster'::regclass - order by indexrelid::regclass::text; - indexrelid | indisclustered -----------------------+---------------- - alttype_cluster_ind | t - alttype_cluster_pkey | f -(2 rows) - -alter table alttype_cluster alter a type bigint; -select indexrelid::regclass, indisclustered from pg_index - where indrelid = 'alttype_cluster'::regclass - order by indexrelid::regclass::text; - indexrelid | indisclustered -----------------------+---------------- - alttype_cluster_ind | t - alttype_cluster_pkey | f -(2 rows) - --- Constraint index remains clustered. -alter table alttype_cluster cluster on alttype_cluster_pkey; -select indexrelid::regclass, indisclustered from pg_index - where indrelid = 'alttype_cluster'::regclass - order by indexrelid::regclass::text; - indexrelid | indisclustered -----------------------+---------------- - alttype_cluster_ind | f - alttype_cluster_pkey | t -(2 rows) - -alter table alttype_cluster alter a type int; -select indexrelid::regclass, indisclustered from pg_index - where indrelid = 'alttype_cluster'::regclass - order by indexrelid::regclass::text; - indexrelid | indisclustered -----------------------+---------------- - alttype_cluster_ind | f - alttype_cluster_pkey | t -(2 rows) - -drop table alttype_cluster; --- --- Check that attaching or detaching a partitioned partition correctly leads --- to its partitions' constraint being updated to reflect the parent's --- newly added/removed constraint -create table target_parted (a int, b int) partition by list (a); -create table attach_parted (a int, b int) partition by list (b); -create table attach_parted_part1 partition of attach_parted for values in (1); --- insert a row directly into the leaf partition so that its partition --- constraint is built and stored in the relcache -insert into attach_parted_part1 values (1, 1); --- the following better invalidate the partition constraint of the leaf --- partition too... -alter table target_parted attach partition attach_parted for values in (1); --- ...such that the following insert fails -insert into attach_parted_part1 values (2, 1); -ERROR: new row for relation "attach_parted_part1" violates partition constraint -DETAIL: Failing row contains (2, 1). --- ...and doesn't when the partition is detached along with its own partition -alter table target_parted detach partition attach_parted; -insert into attach_parted_part1 values (2, 1); --- Test altering table having publication -create schema alter1; -create schema alter2; -create table alter1.t1 (a int); -set client_min_messages = 'ERROR'; -create publication pub1 for table alter1.t1, tables in schema alter2; -reset client_min_messages; -alter table alter1.t1 set schema alter2; -\d+ alter2.t1 - Table "alter2.t1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Publications: - "pub1" - -drop publication pub1; -drop schema alter1 cascade; -drop schema alter2 cascade; -NOTICE: drop cascades to table alter2.t1 +psql: error: connection to server on socket "/tmp/2f2ycaowGU/.s.PGSQL.13722" failed: could not fork new process for connection: Resource temporarily unavailable diff -U3 /home/postgres/postgres/src/test/regress/expected/sequence.out /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/sequence.out --- /home/postgres/postgres/src/test/regress/expected/sequence.out 2025-12-25 10:44:37.144068709 +0000 +++ /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/sequence.out 2025-12-25 10:46:54.000262976 +0000 @@ -1,849 +1 @@ --- --- CREATE SEQUENCE --- --- various error cases -CREATE SEQUENCE sequence_testx INCREMENT BY 0; -ERROR: INCREMENT must not be zero -CREATE SEQUENCE sequence_testx INCREMENT BY -1 MINVALUE 20; -ERROR: MINVALUE (20) must be less than MAXVALUE (-1) -CREATE SEQUENCE sequence_testx INCREMENT BY 1 MAXVALUE -20; -ERROR: MINVALUE (1) must be less than MAXVALUE (-20) -CREATE SEQUENCE sequence_testx INCREMENT BY -1 START 10; -ERROR: START value (10) cannot be greater than MAXVALUE (-1) -CREATE SEQUENCE sequence_testx INCREMENT BY 1 START -10; -ERROR: START value (-10) cannot be less than MINVALUE (1) -CREATE SEQUENCE sequence_testx CACHE 0; -ERROR: CACHE (0) must be greater than zero --- OWNED BY errors -CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word -ERROR: invalid OWNED BY option -HINT: Specify OWNED BY table.column or OWNED BY NONE. -CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid; -- not a table -ERROR: sequence cannot be owned by relation "pg_class_oid_index" -DETAIL: This operation is not supported for indexes. -CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same schema -ERROR: sequence must be in same schema as table it is linked to -CREATE TABLE sequence_test_table (a int); -CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b; -- wrong column -ERROR: column "b" of relation "sequence_test_table" does not exist -DROP TABLE sequence_test_table; --- sequence data types -CREATE SEQUENCE sequence_test5 AS integer; -CREATE SEQUENCE sequence_test6 AS smallint; -CREATE SEQUENCE sequence_test7 AS bigint; -CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000; -CREATE SEQUENCE sequence_test9 AS integer INCREMENT BY -1; -CREATE SEQUENCE sequence_test10 AS integer MINVALUE -100000 START 1; -CREATE SEQUENCE sequence_test11 AS smallint; -CREATE SEQUENCE sequence_test12 AS smallint INCREMENT -1; -CREATE SEQUENCE sequence_test13 AS smallint MINVALUE -32768; -CREATE SEQUENCE sequence_test14 AS smallint MAXVALUE 32767 INCREMENT -1; -CREATE SEQUENCE sequence_testx AS text; -ERROR: sequence type must be smallint, integer, or bigint -CREATE SEQUENCE sequence_testx AS nosuchtype; -ERROR: type "nosuchtype" does not exist -LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype; - ^ -CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000; -ERROR: MAXVALUE (100000) is out of range for sequence data type smallint -CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000; -ERROR: MINVALUE (-100000) is out of range for sequence data type smallint -ALTER SEQUENCE sequence_test5 AS smallint; -- success, max will be adjusted -ALTER SEQUENCE sequence_test8 AS smallint; -- fail, max has to be adjusted -ERROR: MAXVALUE (100000) is out of range for sequence data type smallint -ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000; -- ok now -ALTER SEQUENCE sequence_test9 AS smallint; -- success, min will be adjusted -ALTER SEQUENCE sequence_test10 AS smallint; -- fail, min has to be adjusted -ERROR: MINVALUE (-100000) is out of range for sequence data type smallint -ALTER SEQUENCE sequence_test10 AS smallint MINVALUE -20000; -- ok now -ALTER SEQUENCE sequence_test11 AS int; -- max will be adjusted -ALTER SEQUENCE sequence_test12 AS int; -- min will be adjusted -ALTER SEQUENCE sequence_test13 AS int; -- min and max will be adjusted -ALTER SEQUENCE sequence_test14 AS int; -- min and max will be adjusted ---- ---- test creation of SERIAL column ---- -CREATE TABLE serialTest1 (f1 text, f2 serial); -INSERT INTO serialTest1 VALUES ('foo'); -INSERT INTO serialTest1 VALUES ('bar'); -INSERT INTO serialTest1 VALUES ('force', 100); -INSERT INTO serialTest1 VALUES ('wrong', NULL); -ERROR: null value in column "f2" of relation "serialtest1" violates not-null constraint -DETAIL: Failing row contains (wrong, null). -SELECT * FROM serialTest1; - f1 | f2 --------+----- - foo | 1 - bar | 2 - force | 100 -(3 rows) - -SELECT pg_get_serial_sequence('serialTest1', 'f2'); - pg_get_serial_sequence ---------------------------- - public.serialtest1_f2_seq -(1 row) - --- test smallserial / bigserial -CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2, - f5 bigserial, f6 serial8); -INSERT INTO serialTest2 (f1) - VALUES ('test_defaults'); -INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6) - VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807, - 9223372036854775807), - ('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808, - -9223372036854775808); --- All these INSERTs should fail: -INSERT INTO serialTest2 (f1, f3) - VALUES ('bogus', -32769); -ERROR: smallint out of range -INSERT INTO serialTest2 (f1, f4) - VALUES ('bogus', -32769); -ERROR: smallint out of range -INSERT INTO serialTest2 (f1, f3) - VALUES ('bogus', 32768); -ERROR: smallint out of range -INSERT INTO serialTest2 (f1, f4) - VALUES ('bogus', 32768); -ERROR: smallint out of range -INSERT INTO serialTest2 (f1, f5) - VALUES ('bogus', -9223372036854775809); -ERROR: bigint out of range -INSERT INTO serialTest2 (f1, f6) - VALUES ('bogus', -9223372036854775809); -ERROR: bigint out of range -INSERT INTO serialTest2 (f1, f5) - VALUES ('bogus', 9223372036854775808); -ERROR: bigint out of range -INSERT INTO serialTest2 (f1, f6) - VALUES ('bogus', 9223372036854775808); -ERROR: bigint out of range -SELECT * FROM serialTest2 ORDER BY f2 ASC; - f1 | f2 | f3 | f4 | f5 | f6 ----------------+-------------+--------+--------+----------------------+---------------------- - test_min_vals | -2147483648 | -32768 | -32768 | -9223372036854775808 | -9223372036854775808 - test_defaults | 1 | 1 | 1 | 1 | 1 - test_max_vals | 2147483647 | 32767 | 32767 | 9223372036854775807 | 9223372036854775807 -(3 rows) - -SELECT nextval('serialTest2_f2_seq'); - nextval ---------- - 2 -(1 row) - -SELECT nextval('serialTest2_f3_seq'); - nextval ---------- - 2 -(1 row) - -SELECT nextval('serialTest2_f4_seq'); - nextval ---------- - 2 -(1 row) - -SELECT nextval('serialTest2_f5_seq'); - nextval ---------- - 2 -(1 row) - -SELECT nextval('serialTest2_f6_seq'); - nextval ---------- - 2 -(1 row) - --- basic sequence operations using both text and oid references -CREATE SEQUENCE sequence_test; -CREATE SEQUENCE IF NOT EXISTS sequence_test; -NOTICE: relation "sequence_test" already exists, skipping -SELECT nextval('sequence_test'::text); - nextval ---------- - 1 -(1 row) - -SELECT nextval('sequence_test'::regclass); - nextval ---------- - 2 -(1 row) - -SELECT currval('sequence_test'::text); - currval ---------- - 2 -(1 row) - -SELECT currval('sequence_test'::regclass); - currval ---------- - 2 -(1 row) - -SELECT setval('sequence_test'::text, 32); - setval --------- - 32 -(1 row) - -SELECT nextval('sequence_test'::regclass); - nextval ---------- - 33 -(1 row) - -SELECT setval('sequence_test'::text, 99, false); - setval --------- - 99 -(1 row) - -SELECT nextval('sequence_test'::regclass); - nextval ---------- - 99 -(1 row) - -SELECT setval('sequence_test'::regclass, 32); - setval --------- - 32 -(1 row) - -SELECT nextval('sequence_test'::text); - nextval ---------- - 33 -(1 row) - -SELECT setval('sequence_test'::regclass, 99, false); - setval --------- - 99 -(1 row) - -SELECT nextval('sequence_test'::text); - nextval ---------- - 99 -(1 row) - -DISCARD SEQUENCES; -SELECT currval('sequence_test'::regclass); -ERROR: currval of sequence "sequence_test" is not yet defined in this session -DROP SEQUENCE sequence_test; --- renaming sequences -CREATE SEQUENCE foo_seq; -ALTER TABLE foo_seq RENAME TO foo_seq_new; -SELECT * FROM foo_seq_new; - last_value | log_cnt | is_called -------------+---------+----------- - 1 | 0 | f -(1 row) - -SELECT nextval('foo_seq_new'); - nextval ---------- - 1 -(1 row) - -SELECT nextval('foo_seq_new'); - nextval ---------- - 2 -(1 row) - --- log_cnt can be higher if there is a checkpoint just at the right --- time, so just test for the expected range -SELECT last_value, log_cnt IN (31, 32) AS log_cnt_ok, is_called FROM foo_seq_new; - last_value | log_cnt_ok | is_called -------------+------------+----------- - 2 | t | t -(1 row) - -DROP SEQUENCE foo_seq_new; --- renaming serial sequences -ALTER TABLE serialtest1_f2_seq RENAME TO serialtest1_f2_foo; -INSERT INTO serialTest1 VALUES ('more'); -SELECT * FROM serialTest1; - f1 | f2 --------+----- - foo | 1 - bar | 2 - force | 100 - more | 3 -(4 rows) - --- --- Check dependencies of serial and ordinary sequences --- -CREATE TEMP SEQUENCE myseq2; -CREATE TEMP SEQUENCE myseq3; -CREATE TEMP TABLE t1 ( - f1 serial, - f2 int DEFAULT nextval('myseq2'), - f3 int DEFAULT nextval('myseq3'::text) -); --- Both drops should fail, but with different error messages: -DROP SEQUENCE t1_f1_seq; -ERROR: cannot drop sequence t1_f1_seq because other objects depend on it -DETAIL: default value for column f1 of table t1 depends on sequence t1_f1_seq -HINT: Use DROP ... CASCADE to drop the dependent objects too. -DROP SEQUENCE myseq2; -ERROR: cannot drop sequence myseq2 because other objects depend on it -DETAIL: default value for column f2 of table t1 depends on sequence myseq2 -HINT: Use DROP ... CASCADE to drop the dependent objects too. --- This however will work: -DROP SEQUENCE myseq3; -DROP TABLE t1; --- Fails because no longer existent: -DROP SEQUENCE t1_f1_seq; -ERROR: sequence "t1_f1_seq" does not exist --- Now OK: -DROP SEQUENCE myseq2; --- --- Alter sequence --- -ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24 - INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; -NOTICE: relation "sequence_test2" does not exist, skipping -ALTER SEQUENCE serialTest1 CYCLE; -- error, not a sequence -ERROR: cannot open relation "serialtest1" -DETAIL: This operation is not supported for tables. -CREATE SEQUENCE sequence_test2 START WITH 32; -CREATE SEQUENCE sequence_test4 INCREMENT BY -1; -SELECT nextval('sequence_test2'); - nextval ---------- - 32 -(1 row) - -SELECT nextval('sequence_test4'); - nextval ---------- - -1 -(1 row) - -ALTER SEQUENCE sequence_test2 RESTART; -SELECT nextval('sequence_test2'); - nextval ---------- - 32 -(1 row) - -ALTER SEQUENCE sequence_test2 RESTART WITH 0; -- error -ERROR: RESTART value (0) cannot be less than MINVALUE (1) -ALTER SEQUENCE sequence_test4 RESTART WITH 40; -- error -ERROR: RESTART value (40) cannot be greater than MAXVALUE (-1) --- test CYCLE and NO CYCLE -ALTER SEQUENCE sequence_test2 RESTART WITH 24 - INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; -SELECT nextval('sequence_test2'); - nextval ---------- - 24 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - 28 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - 32 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - 36 -(1 row) - -SELECT nextval('sequence_test2'); -- cycled - nextval ---------- - 5 -(1 row) - -ALTER SEQUENCE sequence_test2 RESTART WITH 24 - NO CYCLE; -SELECT nextval('sequence_test2'); - nextval ---------- - 24 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - 28 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - 32 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - 36 -(1 row) - -SELECT nextval('sequence_test2'); -- error -ERROR: nextval: reached maximum value of sequence "sequence_test2" (36) -ALTER SEQUENCE sequence_test2 RESTART WITH -24 START WITH -24 - INCREMENT BY -4 MINVALUE -36 MAXVALUE -5 CYCLE; -SELECT nextval('sequence_test2'); - nextval ---------- - -24 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - -28 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - -32 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - -36 -(1 row) - -SELECT nextval('sequence_test2'); -- cycled - nextval ---------- - -5 -(1 row) - -ALTER SEQUENCE sequence_test2 RESTART WITH -24 - NO CYCLE; -SELECT nextval('sequence_test2'); - nextval ---------- - -24 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - -28 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - -32 -(1 row) - -SELECT nextval('sequence_test2'); - nextval ---------- - -36 -(1 row) - -SELECT nextval('sequence_test2'); -- error -ERROR: nextval: reached minimum value of sequence "sequence_test2" (-36) --- reset -ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 32 START WITH 32 - INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; -SELECT setval('sequence_test2', -100); -- error -ERROR: setval: value -100 is out of bounds for sequence "sequence_test2" (5..36) -SELECT setval('sequence_test2', 100); -- error -ERROR: setval: value 100 is out of bounds for sequence "sequence_test2" (5..36) -SELECT setval('sequence_test2', 5); - setval --------- - 5 -(1 row) - -CREATE SEQUENCE sequence_test3; -- not read from, to test is_called --- Information schema -SELECT * FROM information_schema.sequences - WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest']) - ORDER BY sequence_name ASC; - sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option -------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+-------------- - regression | public | sequence_test10 | smallint | 16 | 2 | 0 | 1 | -20000 | 32767 | 1 | NO - regression | public | sequence_test11 | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO - regression | public | sequence_test12 | integer | 32 | 2 | 0 | -1 | -2147483648 | -1 | -1 | NO - regression | public | sequence_test13 | integer | 32 | 2 | 0 | -32768 | -2147483648 | 2147483647 | 1 | NO - regression | public | sequence_test14 | integer | 32 | 2 | 0 | 32767 | -2147483648 | 2147483647 | -1 | NO - regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES - regression | public | sequence_test3 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO - regression | public | sequence_test4 | bigint | 64 | 2 | 0 | -1 | -9223372036854775808 | -1 | -1 | NO - regression | public | sequence_test5 | smallint | 16 | 2 | 0 | 1 | 1 | 32767 | 1 | NO - regression | public | sequence_test6 | smallint | 16 | 2 | 0 | 1 | 1 | 32767 | 1 | NO - regression | public | sequence_test7 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO - regression | public | sequence_test8 | smallint | 16 | 2 | 0 | 1 | 1 | 20000 | 1 | NO - regression | public | sequence_test9 | smallint | 16 | 2 | 0 | -1 | -32768 | -1 | -1 | NO - regression | public | serialtest1_f2_foo | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO - regression | public | serialtest2_f2_seq | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO - regression | public | serialtest2_f3_seq | smallint | 16 | 2 | 0 | 1 | 1 | 32767 | 1 | NO - regression | public | serialtest2_f4_seq | smallint | 16 | 2 | 0 | 1 | 1 | 32767 | 1 | NO - regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO - regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO -(19 rows) - -SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value -FROM pg_sequences -WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest']) - ORDER BY sequencename ASC; - schemaname | sequencename | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value -------------+--------------------+-------------+----------------------+---------------------+--------------+-------+------------+------------ - public | sequence_test10 | 1 | -20000 | 32767 | 1 | f | 1 | - public | sequence_test11 | 1 | 1 | 2147483647 | 1 | f | 1 | - public | sequence_test12 | -1 | -2147483648 | -1 | -1 | f | 1 | - public | sequence_test13 | -32768 | -2147483648 | 2147483647 | 1 | f | 1 | - public | sequence_test14 | 32767 | -2147483648 | 2147483647 | -1 | f | 1 | - public | sequence_test2 | 32 | 5 | 36 | 4 | t | 1 | 5 - public | sequence_test3 | 1 | 1 | 9223372036854775807 | 1 | f | 1 | - public | sequence_test4 | -1 | -9223372036854775808 | -1 | -1 | f | 1 | -1 - public | sequence_test5 | 1 | 1 | 32767 | 1 | f | 1 | - public | sequence_test6 | 1 | 1 | 32767 | 1 | f | 1 | - public | sequence_test7 | 1 | 1 | 9223372036854775807 | 1 | f | 1 | - public | sequence_test8 | 1 | 1 | 20000 | 1 | f | 1 | - public | sequence_test9 | -1 | -32768 | -1 | -1 | f | 1 | - public | serialtest1_f2_foo | 1 | 1 | 2147483647 | 1 | f | 1 | 3 - public | serialtest2_f2_seq | 1 | 1 | 2147483647 | 1 | f | 1 | 2 - public | serialtest2_f3_seq | 1 | 1 | 32767 | 1 | f | 1 | 2 - public | serialtest2_f4_seq | 1 | 1 | 32767 | 1 | f | 1 | 2 - public | serialtest2_f5_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2 - public | serialtest2_f6_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2 -(19 rows) - -SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass); - start_value | minimum_value | maximum_value | increment | cycle_option | cache_size | data_type --------------+----------------------+---------------+-----------+--------------+------------+----------- - -1 | -9223372036854775808 | -1 | -1 | f | 1 | 20 -(1 row) - -\d sequence_test4 - Sequence "public.sequence_test4" - Type | Start | Minimum | Maximum | Increment | Cycles? | Cache ---------+-------+----------------------+---------+-----------+---------+------- - bigint | -1 | -9223372036854775808 | -1 | -1 | no | 1 - -\d serialtest2_f2_seq - Sequence "public.serialtest2_f2_seq" - Type | Start | Minimum | Maximum | Increment | Cycles? | Cache ----------+-------+---------+------------+-----------+---------+------- - integer | 1 | 1 | 2147483647 | 1 | no | 1 -Owned by: public.serialtest2.f2 - --- Test comments -COMMENT ON SEQUENCE asdf IS 'won''t work'; -ERROR: relation "asdf" does not exist -COMMENT ON SEQUENCE sequence_test2 IS 'will work'; -COMMENT ON SEQUENCE sequence_test2 IS NULL; --- Test lastval() -CREATE SEQUENCE seq; -SELECT nextval('seq'); - nextval ---------- - 1 -(1 row) - -SELECT lastval(); - lastval ---------- - 1 -(1 row) - -SELECT setval('seq', 99); - setval --------- - 99 -(1 row) - -SELECT lastval(); - lastval ---------- - 99 -(1 row) - -DISCARD SEQUENCES; -SELECT lastval(); -ERROR: lastval is not yet defined in this session -CREATE SEQUENCE seq2; -SELECT nextval('seq2'); - nextval ---------- - 1 -(1 row) - -SELECT lastval(); - lastval ---------- - 1 -(1 row) - -DROP SEQUENCE seq2; --- should fail -SELECT lastval(); -ERROR: lastval is not yet defined in this session --- unlogged sequences --- (more tests in src/test/recovery/) -CREATE UNLOGGED SEQUENCE sequence_test_unlogged; -ALTER SEQUENCE sequence_test_unlogged SET LOGGED; -\d sequence_test_unlogged - Sequence "public.sequence_test_unlogged" - Type | Start | Minimum | Maximum | Increment | Cycles? | Cache ---------+-------+---------+---------------------+-----------+---------+------- - bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 - -ALTER SEQUENCE sequence_test_unlogged SET UNLOGGED; -\d sequence_test_unlogged - Unlogged sequence "public.sequence_test_unlogged" - Type | Start | Minimum | Maximum | Increment | Cycles? | Cache ---------+-------+---------+---------------------+-----------+---------+------- - bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 - -DROP SEQUENCE sequence_test_unlogged; --- Test sequences in read-only transactions -CREATE TEMPORARY SEQUENCE sequence_test_temp1; -START TRANSACTION READ ONLY; -SELECT nextval('sequence_test_temp1'); -- ok - nextval ---------- - 1 -(1 row) - -SELECT nextval('sequence_test2'); -- error -ERROR: cannot execute nextval() in a read-only transaction -ROLLBACK; -START TRANSACTION READ ONLY; -SELECT setval('sequence_test_temp1', 1); -- ok - setval --------- - 1 -(1 row) - -SELECT setval('sequence_test2', 1); -- error -ERROR: cannot execute setval() in a read-only transaction -ROLLBACK; --- privileges tests -CREATE USER regress_seq_user; --- nextval -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT SELECT ON seq3 TO regress_seq_user; -SELECT nextval('seq3'); -ERROR: permission denied for sequence seq3 -ROLLBACK; -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT UPDATE ON seq3 TO regress_seq_user; -SELECT nextval('seq3'); - nextval ---------- - 1 -(1 row) - -ROLLBACK; -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT USAGE ON seq3 TO regress_seq_user; -SELECT nextval('seq3'); - nextval ---------- - 1 -(1 row) - -ROLLBACK; --- currval -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -SELECT nextval('seq3'); - nextval ---------- - 1 -(1 row) - -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT SELECT ON seq3 TO regress_seq_user; -SELECT currval('seq3'); - currval ---------- - 1 -(1 row) - -ROLLBACK; -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -SELECT nextval('seq3'); - nextval ---------- - 1 -(1 row) - -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT UPDATE ON seq3 TO regress_seq_user; -SELECT currval('seq3'); -ERROR: permission denied for sequence seq3 -ROLLBACK; -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -SELECT nextval('seq3'); - nextval ---------- - 1 -(1 row) - -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT USAGE ON seq3 TO regress_seq_user; -SELECT currval('seq3'); - currval ---------- - 1 -(1 row) - -ROLLBACK; --- lastval -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -SELECT nextval('seq3'); - nextval ---------- - 1 -(1 row) - -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT SELECT ON seq3 TO regress_seq_user; -SELECT lastval(); - lastval ---------- - 1 -(1 row) - -ROLLBACK; -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -SELECT nextval('seq3'); - nextval ---------- - 1 -(1 row) - -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT UPDATE ON seq3 TO regress_seq_user; -SELECT lastval(); -ERROR: permission denied for sequence seq3 -ROLLBACK; -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -SELECT nextval('seq3'); - nextval ---------- - 1 -(1 row) - -REVOKE ALL ON seq3 FROM regress_seq_user; -GRANT USAGE ON seq3 TO regress_seq_user; -SELECT lastval(); - lastval ---------- - 1 -(1 row) - -ROLLBACK; --- setval -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -CREATE SEQUENCE seq3; -REVOKE ALL ON seq3 FROM regress_seq_user; -SAVEPOINT save; -SELECT setval('seq3', 5); -ERROR: permission denied for sequence seq3 -ROLLBACK TO save; -GRANT UPDATE ON seq3 TO regress_seq_user; -SELECT setval('seq3', 5); - setval --------- - 5 -(1 row) - -SELECT nextval('seq3'); - nextval ---------- - 6 -(1 row) - -ROLLBACK; --- ALTER SEQUENCE -BEGIN; -SET LOCAL SESSION AUTHORIZATION regress_seq_user; -ALTER SEQUENCE sequence_test2 START WITH 1; -ERROR: must be owner of sequence sequence_test2 -ROLLBACK; --- Sequences should get wiped out as well: -DROP TABLE serialTest1, serialTest2; --- Make sure sequences are gone: -SELECT * FROM information_schema.sequences WHERE sequence_name IN - ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq', - 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq') - ORDER BY sequence_name ASC; - sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option -------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+-------------- - regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES -(1 row) - -DROP USER regress_seq_user; -DROP SEQUENCE seq; --- cache tests -CREATE SEQUENCE test_seq1 CACHE 10; -SELECT nextval('test_seq1'); - nextval ---------- - 1 -(1 row) - -SELECT nextval('test_seq1'); - nextval ---------- - 2 -(1 row) - -SELECT nextval('test_seq1'); - nextval ---------- - 3 -(1 row) - --- pg_get_sequence_data -SELECT last_value, is_called, page_lsn <= pg_current_wal_lsn() as lsn FROM pg_get_sequence_data('test_seq1'); - last_value | is_called | lsn -------------+-----------+----- - 10 | t | t -(1 row) - -DROP SEQUENCE test_seq1; +psql: error: connection to server on socket "/tmp/2f2ycaowGU/.s.PGSQL.13722" failed: could not fork new process for connection: Resource temporarily unavailable diff -U3 /home/postgres/postgres/src/test/regress/expected/xml_1.out /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/xml.out --- /home/postgres/postgres/src/test/regress/expected/xml_1.out 2025-12-25 10:44:37.209914888 +0000 +++ /home/postgres/postgres/build/testrun/pg_upgrade/002_pg_upgrade/data/results/xml.out 2025-12-25 10:46:54.004693728 +0000 @@ -1,1498 +1 @@ -CREATE TABLE xmltest ( - id int, - data xml -); -INSERT INTO xmltest VALUES (1, 'one'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest VALUES (1, 'one'); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -INSERT INTO xmltest VALUES (2, 'two'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest VALUES (2, 'two'); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -INSERT INTO xmltest VALUES (3, 'one', 'xml'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT pg_input_is_valid('oneone', 'xml'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT message FROM pg_input_error_info('', 'xml'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlcomment('test'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlcomment('-test'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlcomment('test-'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlcomment('--test'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlcomment('te st'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlconcat(xmlcomment('hello'), - xmlelement(NAME qux, 'foo'), - xmlcomment('world')); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlconcat('hello', 'you'); -ERROR: unsupported XML feature -LINE 1: SELECT xmlconcat('hello', 'you'); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlconcat(1, 2); -ERROR: argument of XMLCONCAT must be type xml, not type integer -LINE 1: SELECT xmlconcat(1, 2); - ^ -SELECT xmlconcat('bad', '', NULL, ''); -ERROR: unsupported XML feature -LINE 1: SELECT xmlconcat('', NULL, '', NULL, ''); -ERROR: unsupported XML feature -LINE 1: SELECT xmlconcat('', NULL, 'r'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlelement(name foo, xml 'br'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlelement(name foo, array[1, 2, 3]); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SET xmlbinary TO base64; -SELECT xmlelement(name foo, bytea 'bar'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SET xmlbinary TO hex; -SELECT xmlelement(name foo, bytea 'bar'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlelement(name foo, xmlattributes(true as bar)); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar)); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar)); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'br' as funnier)); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content ''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content ' '); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content 'abc'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content 'x'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content '&'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content '&idontexist;'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content ''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content ''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content '&idontexist;'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(content ''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document ' '); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document 'abc'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document 'x'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document '&'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document '&idontexist;'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document ''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document ''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document '&idontexist;'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlparse(document ''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name foo); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name xml); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name xmlstuff); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name foo, 'bar'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name foo, 'in?>valid'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name foo, null); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name xml, null); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name xmlstuff, null); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name foo, ' bar'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot(xml '', version no value, standalone no value); -ERROR: unsupported XML feature -LINE 1: SELECT xmlroot(xml '', version no value, standalone no... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot(xml '', version '2.0'); -ERROR: unsupported XML feature -LINE 1: SELECT xmlroot(xml '', version '2.0'); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot(xml '', version no value, standalone yes); -ERROR: unsupported XML feature -LINE 1: SELECT xmlroot(xml '', version no value, standalone ye... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot(xml '', version no value, standalone yes); -ERROR: unsupported XML feature -LINE 1: SELECT xmlroot(xml '', version no... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot(xmlroot(xml '', version '1.0'), version '1.1', standalone no); -ERROR: unsupported XML feature -LINE 1: SELECT xmlroot(xmlroot(xml '', version '1.0'), version... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot('', version no value, standalone no); -ERROR: unsupported XML feature -LINE 1: SELECT xmlroot('... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot('', version no value, standalone no value); -ERROR: unsupported XML feature -LINE 1: SELECT xmlroot('... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot('', version no value); -ERROR: unsupported XML feature -LINE 1: SELECT xmlroot('... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlroot ( - xmlelement ( - name gazonk, - xmlattributes ( - 'val' AS name, - 1 + 1 AS num - ), - xmlelement ( - NAME qux, - 'foo' - ) - ), - version '1.0', - standalone yes -); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(content data as character varying(20)) FROM xmltest; - xmlserialize --------------- -(0 rows) - -SELECT xmlserialize(content 'good' as char(10)); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(content 'good' as char(10)); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(document 'bad' as text); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(document 'bad' as text); - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- indent -SELECT xmlserialize(DOCUMENT '42' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '42<... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT '42' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '42<... - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- no indent -SELECT xmlserialize(DOCUMENT '42' AS text NO INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '42<... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT '42' AS text NO INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '42<... - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- indent non singly-rooted xml -SELECT xmlserialize(DOCUMENT '7342' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '734... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT '7342' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '734... - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- indent non singly-rooted xml with mixed contents -SELECT xmlserialize(DOCUMENT 'text node73text node42' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT 'text node73text nod... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT 'text node73text node42' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT 'text node73text nod... - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- indent singly-rooted xml with mixed contents -SELECT xmlserialize(DOCUMENT '42text node73' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '42<... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT '42text node73' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '42<... - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- indent empty string -SELECT xmlserialize(DOCUMENT '' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '' AS text INDENT); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT '' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '' AS text INDENT); - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- whitespaces -SELECT xmlserialize(DOCUMENT ' ' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT ' ' AS text INDENT); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT ' ' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT ' ' AS text INDENT); - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- indent null -SELECT xmlserialize(DOCUMENT NULL AS text INDENT); - xmlserialize --------------- - -(1 row) - -SELECT xmlserialize(CONTENT NULL AS text INDENT); - xmlserialize --------------- - -(1 row) - --- indent with XML declaration -SELECT xmlserialize(DOCUMENT '73' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '73' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '' AS text INDE... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT '' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '' AS text INDE... - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- indent xml with empty element -SELECT xmlserialize(DOCUMENT '' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '' AS tex... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT '' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '' AS tex... - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- 'no indent' = not using 'no indent' -SELECT xmlserialize(DOCUMENT '42' AS text) = xmlserialize(DOCUMENT '42' AS text NO INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT '42<... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT '42' AS text) = xmlserialize(CONTENT '42' AS text NO INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT '42<... - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- indent xml strings containing blank nodes -SELECT xmlserialize(DOCUMENT ' ' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(DOCUMENT ' '... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlserialize(CONTENT 'text node ' AS text INDENT); -ERROR: unsupported XML feature -LINE 1: SELECT xmlserialize(CONTENT 'text node ... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml 'bar' IS DOCUMENT; -ERROR: unsupported XML feature -LINE 1: SELECT xml 'bar' IS DOCUMENT; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml 'barfoo' IS DOCUMENT; -ERROR: unsupported XML feature -LINE 1: SELECT xml 'barfoo' IS DOCUMENT; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml '' IS NOT DOCUMENT; -ERROR: unsupported XML feature -LINE 1: SELECT xml '' IS NOT DOCUMENT; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml 'abc' IS NOT DOCUMENT; -ERROR: unsupported XML feature -LINE 1: SELECT xml 'abc' IS NOT DOCUMENT; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT '<>' IS NOT DOCUMENT; -ERROR: unsupported XML feature -LINE 1: SELECT '<>' IS NOT DOCUMENT; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlagg(data) FROM xmltest; - xmlagg --------- - -(1 row) - -SELECT xmlagg(data) FROM xmltest WHERE id > 10; - xmlagg --------- - -(1 row) - -SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp; -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. --- Check mapping SQL identifier to XML name -SELECT xmlpi(name ":::_xml_abc135.%-&_"); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmlpi(name "123"); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -PREPARE foo (xml) AS SELECT xmlconcat('', $1); -ERROR: unsupported XML feature -LINE 1: PREPARE foo (xml) AS SELECT xmlconcat('', $1); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SET XML OPTION DOCUMENT; -EXECUTE foo (''); -ERROR: prepared statement "foo" does not exist -EXECUTE foo ('bad'); -ERROR: prepared statement "foo" does not exist -SELECT xml ''; -ERROR: unsupported XML feature -LINE 1: SELECT xml ''; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SET XML OPTION CONTENT; -EXECUTE foo (''); -ERROR: prepared statement "foo" does not exist -EXECUTE foo ('good'); -ERROR: prepared statement "foo" does not exist -SELECT xml ' '; -ERROR: unsupported XML feature -LINE 1: SELECT xml ' '; -ERROR: unsupported XML feature -LINE 1: SELECT xml ' '; -ERROR: unsupported XML feature -LINE 1: SELECT xml ''; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml ' oops '; -ERROR: unsupported XML feature -LINE 1: SELECT xml ' oops '; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml ' '; -ERROR: unsupported XML feature -LINE 1: SELECT xml ' '; - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml ''; -ERROR: unsupported XML feature -LINE 1: SELECT xml ''; - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- Test backwards parsing -CREATE VIEW xmlview1 AS SELECT xmlcomment('test'); -CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you'); -ERROR: unsupported XML feature -LINE 1: CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you'); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -CREATE VIEW xmlview5 AS SELECT xmlparse(content 'x'); -CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -CREATE VIEW xmlview7 AS SELECT xmlroot(xml '', version no value, standalone yes); -ERROR: unsupported XML feature -LINE 1: CREATE VIEW xmlview7 AS SELECT xmlroot(xml '', version... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10)); -ERROR: unsupported XML feature -LINE 1: ...EATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as ... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text); -ERROR: unsupported XML feature -LINE 1: ...EATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as ... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -CREATE VIEW xmlview10 AS SELECT xmlserialize(document '42' AS text indent); -ERROR: unsupported XML feature -LINE 1: ...TE VIEW xmlview10 AS SELECT xmlserialize(document '42' AS character varying no indent); -ERROR: unsupported XML feature -LINE 1: ...TE VIEW xmlview11 AS SELECT xmlserialize(document 'x'::text STRIP WHITESPACE) AS "xmlparse"; -(2 rows) - --- Text XPath expressions evaluation -SELECT xpath('/value', data) FROM xmltest; - xpath -------- -(0 rows) - -SELECT xpath(NULL, NULL) IS NULL FROM xmltest; - ?column? ----------- -(0 rows) - -SELECT xpath('', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath('//text()', 'number one'); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('//text()', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('//loc:piece/@id', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('//loc:piece', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('//loc:piece', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('//@value', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath('''<>''', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('''<>''', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath('count(//*)', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('count(//*)', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath('count(//*)=0', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('count(//*)=0', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath('count(//*)=3', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('count(//*)=3', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath('name(/*)', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('name(/*)', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath('/nosuchtag', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('/nosuchtag', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath('root', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('root', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- Round-trip non-ASCII data through xpath(). -DO $$ -DECLARE - xml_declaration text := ''; - degree_symbol text; - res xml[]; -BEGIN - -- Per the documentation, except when the server encoding is UTF8, xpath() - -- may not work on non-ASCII data. The untranslatable_character and - -- undefined_function traps below, currently dead code, will become relevant - -- if we remove this limitation. - IF current_setting('server_encoding') <> 'UTF8' THEN - RAISE LOG 'skip: encoding % unsupported for xpath', - current_setting('server_encoding'); - RETURN; - END IF; - - degree_symbol := convert_from('\xc2b0', 'UTF8'); - res := xpath('text()', (xml_declaration || - '' || degree_symbol || '')::xml); - IF degree_symbol <> res[1]::text THEN - RAISE 'expected % (%), got % (%)', - degree_symbol, convert_to(degree_symbol, 'UTF8'), - res[1], convert_to(res[1]::text, 'UTF8'); - END IF; -EXCEPTION - -- character with byte sequence 0xc2 0xb0 in encoding "UTF8" has no equivalent in encoding "LATIN8" - WHEN untranslatable_character - -- default conversion function for encoding "UTF8" to "MULE_INTERNAL" does not exist - OR undefined_function - -- unsupported XML feature - OR feature_not_supported THEN - RAISE LOG 'skip: %', SQLERRM; -END -$$; --- Test xmlexists and xpath_exists -SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol'); -ERROR: unsupported XML feature -LINE 1: ...sts('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol'); -ERROR: unsupported XML feature -LINE 1: ...sts('//town[text() = ''Cwmbran'']' PASSING BY REF ''); -ERROR: unsupported XML feature -LINE 1: ...LECT xmlexists('count(/nosuchtag)' PASSING BY REF '')... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml); -ERROR: unsupported XML feature -LINE 1: ...ELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml); -ERROR: unsupported XML feature -LINE 1: ...ELECT xpath_exists('//town[text() = ''Cwmbran'']',''::xml); -ERROR: unsupported XML feature -LINE 1: SELECT xpath_exists('count(/nosuchtag)', ''::xml); - ^ -DETAIL: This functionality requires the server to be built with libxml support. -INSERT INTO xmltest VALUES (4, 'BudvarfreeCarlinglots'::xml); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest VALUES (4, 'BudvarMolsonfreeCarlinglots'::xml); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest VALUES (5, 'MolsonBudvarfreeCarlinglots'::xml); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest VALUES (6, 'MolsonfreeCarlinglots'::xml); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest VALUES (7, 'number one'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml_is_well_formed('bar'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml_is_well_formed('bar'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml_is_well_formed('&'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml_is_well_formed('&idontexist;'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml_is_well_formed(''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml_is_well_formed(''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xml_is_well_formed('&idontexist;'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SET xmloption TO CONTENT; -SELECT xml_is_well_formed('abc'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. --- Since xpath() deals with namespaces, it's a bit stricter about --- what's well-formed and what's not. If we don't obey these rules --- (i.e. ignore namespace-related errors from libxml), xpath() --- fails in subtle ways. The following would for example produce --- the xml value --- --- which is invalid because '<' may not appear un-escaped in --- attribute values. --- Since different libxml versions emit slightly different --- error messages, we suppress the DETAIL in this test. -\set VERBOSITY terse -SELECT xpath('/*', ''); -ERROR: unsupported XML feature at character 20 -\set VERBOSITY default --- Again, the XML isn't well-formed for namespace purposes -SELECT xpath('/*', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('/*', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- XPath deprecates relative namespaces, but they're not supposed to --- throw an error, only a warning. -SELECT xpath('/*', ''); -ERROR: unsupported XML feature -LINE 1: SELECT xpath('/*', ''); - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- External entity references should not leak filesystem information. -SELECT XMLPARSE(DOCUMENT ']>&c;'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT XMLPARSE(DOCUMENT ']>&c;'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. --- This might or might not load the requested DTD, but it mustn't throw error. -SELECT XMLPARSE(DOCUMENT ' '); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. --- XMLPATH tests -CREATE TABLE xmldata(data xml); -INSERT INTO xmldata VALUES(' - - AU - Australia - 3 - - - CN - China - 3 - - - HK - HongKong - 3 - - - IN - India - 3 - - - JP - Japan - 3Sinzo Abe - - - SG - Singapore - 3791 - -'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmldata VALUES(' - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- XMLTABLE with columns -SELECT xmltable.* - FROM (SELECT data FROM xmldata) x, - LATERAL XMLTABLE('/ROWS/ROW' - PASSING data - COLUMNS id int PATH '@id', - _id FOR ORDINALITY, - country_name text PATH 'COUNTRY_NAME/text()' NOT NULL, - country_id text PATH 'COUNTRY_ID', - region_id int PATH 'REGION_ID', - size float PATH 'SIZE', - unit text PATH 'SIZE/@unit', - premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); - id | _id | country_name | country_id | region_id | size | unit | premier_name -----+-----+--------------+------------+-----------+------+------+-------------- -(0 rows) - -CREATE VIEW xmltableview1 AS SELECT xmltable.* - FROM (SELECT data FROM xmldata) x, - LATERAL XMLTABLE('/ROWS/ROW' - PASSING data - COLUMNS id int PATH '@id', - _id FOR ORDINALITY, - country_name text PATH 'COUNTRY_NAME/text()' NOT NULL, - country_id text PATH 'COUNTRY_ID', - region_id int PATH 'REGION_ID', - size float PATH 'SIZE', - unit text PATH 'SIZE/@unit', - premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); -SELECT * FROM xmltableview1; - id | _id | country_name | country_id | region_id | size | unit | premier_name -----+-----+--------------+------------+-----------+------+------+-------------- -(0 rows) - -\sv xmltableview1 -CREATE OR REPLACE VIEW public.xmltableview1 AS - SELECT "xmltable".id, - "xmltable"._id, - "xmltable".country_name, - "xmltable".country_id, - "xmltable".region_id, - "xmltable".size, - "xmltable".unit, - "xmltable".premier_name - FROM ( SELECT xmldata.data - FROM xmldata) x, - LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text)) -EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1; - QUERY PLAN ------------------------------------------ - Nested Loop - -> Seq Scan on xmldata - -> Table Function Scan on "xmltable" -(3 rows) - -EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Nested Loop - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name - -> Seq Scan on public.xmldata - Output: xmldata.data - -> Table Function Scan on "xmltable" - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name - Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text)) -(7 rows) - --- errors -SELECT * FROM XMLTABLE (ROW () PASSING null COLUMNS v1 timestamp) AS f (v1, v2); -ERROR: XMLTABLE function has 1 columns available but 2 columns specified -SELECT * FROM XMLTABLE (ROW () PASSING null COLUMNS v1 timestamp __pg__is_not_null 1) AS f (v1); -ERROR: option name "__pg__is_not_null" cannot be used in XMLTABLE -LINE 1: ...MLTABLE (ROW () PASSING null COLUMNS v1 timestamp __pg__is_n... - ^ --- XMLNAMESPACES tests -SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz), - '/zz:rows/zz:row' - PASSING '10' - COLUMNS a int PATH 'zz:a'); -ERROR: unsupported XML feature -LINE 3: PASSING '10' - COLUMNS a int PATH 'Zz:a'); -ERROR: unsupported XML feature -LINE 3: PASSING '10' - COLUMNS a int PATH 'a'); -ERROR: unsupported XML feature -LINE 3: PASSING '' - COLUMNS a text PATH 'foo/namespace::node()'); -ERROR: unsupported XML feature -LINE 2: PASSING '' - ^ -DETAIL: This functionality requires the server to be built with libxml support. --- used in prepare statements -PREPARE pp AS -SELECT xmltable.* - FROM (SELECT data FROM xmldata) x, - LATERAL XMLTABLE('/ROWS/ROW' - PASSING data - COLUMNS id int PATH '@id', - _id FOR ORDINALITY, - country_name text PATH 'COUNTRY_NAME' NOT NULL, - country_id text PATH 'COUNTRY_ID', - region_id int PATH 'REGION_ID', - size float PATH 'SIZE', - unit text PATH 'SIZE/@unit', - premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); -EXECUTE pp; - id | _id | country_name | country_id | region_id | size | unit | premier_name -----+-----+--------------+------------+-----------+------+------+-------------- -(0 rows) - -SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int); - COUNTRY_NAME | REGION_ID ---------------+----------- -(0 rows) - -SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int); - id | COUNTRY_NAME | REGION_ID -----+--------------+----------- -(0 rows) - -SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int); - id | COUNTRY_NAME | REGION_ID -----+--------------+----------- -(0 rows) - -SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id'); - id ----- -(0 rows) - -SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY); - id ----- -(0 rows) - -SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH '.'); - id | COUNTRY_NAME | REGION_ID | rawdata -----+--------------+-----------+--------- -(0 rows) - -SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH './*'); - id | COUNTRY_NAME | REGION_ID | rawdata -----+--------------+-----------+--------- -(0 rows) - -SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text); -ERROR: unsupported XML feature -LINE 1: SELECT * FROM xmltable('/root' passing 'a1aa1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail -ERROR: unsupported XML feature -LINE 1: SELECT * FROM xmltable('/root' passing 'a1a &"<>!foo]]>2' columns c text); -ERROR: unsupported XML feature -LINE 1: select * from xmltable('d/r' passing ''"&<>' COLUMNS ent text); -ERROR: unsupported XML feature -LINE 1: SELECT * FROM xmltable('/x/a' PASSING '''"&<>' COLUMNS ent xml); -ERROR: unsupported XML feature -LINE 1: SELECT * FROM xmltable('/x/a' PASSING '' Seq Scan on public.xmldata - Output: xmldata.data - -> Table Function Scan on "xmltable" - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name - Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text)) -(7 rows) - --- test qual -SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan'; - COUNTRY_NAME | REGION_ID ---------------+----------- -(0 rows) - -EXPLAIN (VERBOSE, COSTS OFF) -SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan'; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Nested Loop - Output: f."COUNTRY_NAME", f."REGION_ID" - -> Seq Scan on public.xmldata - Output: xmldata.data - -> Table Function Scan on "xmltable" f - Output: f."COUNTRY_NAME", f."REGION_ID" - Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer) - Filter: (f."COUNTRY_NAME" = 'Japan'::text) -(8 rows) - -EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF) -SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan'; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - [ + - { + - "Plan": { + - "Node Type": "Nested Loop", + - "Parallel Aware": false, + - "Async Capable": false, + - "Join Type": "Inner", + - "Disabled": false, + - "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], + - "Inner Unique": false, + - "Plans": [ + - { + - "Node Type": "Seq Scan", + - "Parent Relationship": "Outer", + - "Parallel Aware": false, + - "Async Capable": false, + - "Relation Name": "xmldata", + - "Schema": "public", + - "Alias": "xmldata", + - "Disabled": false, + - "Output": ["xmldata.data"] + - }, + - { + - "Node Type": "Table Function Scan", + - "Parent Relationship": "Inner", + - "Parallel Aware": false, + - "Async Capable": false, + - "Table Function Name": "xmltable", + - "Alias": "f", + - "Disabled": false, + - "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], + - "Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+ - "Filter": "(f.\"COUNTRY_NAME\" = 'Japan'::text)" + - } + - ] + - } + - } + - ] -(1 row) - --- should to work with more data -INSERT INTO xmldata VALUES(' - - CZ - Czech Republic - 2Milos Zeman - - - DE - Germany - 2 - - - FR - France - 2 - -'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmldata VALUES(' - ^ -DETAIL: This functionality requires the server to be built with libxml support. -INSERT INTO xmldata VALUES(' - - EG - Egypt - 1 - - - SD - Sudan - 1 - -'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmldata VALUES(' - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmltable.* - FROM (SELECT data FROM xmldata) x, - LATERAL XMLTABLE('/ROWS/ROW' - PASSING data - COLUMNS id int PATH '@id', - _id FOR ORDINALITY, - country_name text PATH 'COUNTRY_NAME' NOT NULL, - country_id text PATH 'COUNTRY_ID', - region_id int PATH 'REGION_ID', - size float PATH 'SIZE', - unit text PATH 'SIZE/@unit', - premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); - id | _id | country_name | country_id | region_id | size | unit | premier_name -----+-----+--------------+------------+-----------+------+------+-------------- -(0 rows) - -SELECT xmltable.* - FROM (SELECT data FROM xmldata) x, - LATERAL XMLTABLE('/ROWS/ROW' - PASSING data - COLUMNS id int PATH '@id', - _id FOR ORDINALITY, - country_name text PATH 'COUNTRY_NAME' NOT NULL, - country_id text PATH 'COUNTRY_ID', - region_id int PATH 'REGION_ID', - size float PATH 'SIZE', - unit text PATH 'SIZE/@unit', - premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') - WHERE region_id = 2; - id | _id | country_name | country_id | region_id | size | unit | premier_name -----+-----+--------------+------------+-----------+------+------+-------------- -(0 rows) - -EXPLAIN (VERBOSE, COSTS OFF) -SELECT xmltable.* - FROM (SELECT data FROM xmldata) x, - LATERAL XMLTABLE('/ROWS/ROW' - PASSING data - COLUMNS id int PATH '@id', - _id FOR ORDINALITY, - country_name text PATH 'COUNTRY_NAME' NOT NULL, - country_id text PATH 'COUNTRY_ID', - region_id int PATH 'REGION_ID', - size float PATH 'SIZE', - unit text PATH 'SIZE/@unit', - premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') - WHERE region_id = 2; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - Nested Loop - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name - -> Seq Scan on public.xmldata - Output: xmldata.data - -> Table Function Scan on "xmltable" - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name - Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text)) - Filter: ("xmltable".region_id = 2) -(8 rows) - --- should fail, NULL value -SELECT xmltable.* - FROM (SELECT data FROM xmldata) x, - LATERAL XMLTABLE('/ROWS/ROW' - PASSING data - COLUMNS id int PATH '@id', - _id FOR ORDINALITY, - country_name text PATH 'COUNTRY_NAME' NOT NULL, - country_id text PATH 'COUNTRY_ID', - region_id int PATH 'REGION_ID', - size float PATH 'SIZE' NOT NULL, - unit text PATH 'SIZE/@unit', - premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); - id | _id | country_name | country_id | region_id | size | unit | premier_name -----+-----+--------------+------------+-----------+------+------+-------------- -(0 rows) - --- if all is ok, then result is empty --- one line xml test -WITH - x AS (SELECT proname, proowner, procost::numeric, pronargs, - array_to_string(proargnames,',') as proargnames, - case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes - FROM pg_proc WHERE proname = 'f_leak'), - y AS (SELECT xmlelement(name proc, - xmlforest(proname, proowner, - procost, pronargs, - proargnames, proargtypes)) as proc - FROM x), - z AS (SELECT xmltable.* - FROM y, - LATERAL xmltable('/proc' PASSING proc - COLUMNS proname name, - proowner oid, - procost float, - pronargs int, - proargnames text, - proargtypes text)) - SELECT * FROM z - EXCEPT SELECT * FROM x; -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. --- multi line xml test, result should be empty too -WITH - x AS (SELECT proname, proowner, procost::numeric, pronargs, - array_to_string(proargnames,',') as proargnames, - case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes - FROM pg_proc), - y AS (SELECT xmlelement(name data, - xmlagg(xmlelement(name proc, - xmlforest(proname, proowner, procost, - pronargs, proargnames, proargtypes)))) as doc - FROM x), - z AS (SELECT xmltable.* - FROM y, - LATERAL xmltable('/data/proc' PASSING doc - COLUMNS proname name, - proowner oid, - procost float, - pronargs int, - proargnames text, - proargtypes text)) - SELECT * FROM z - EXCEPT SELECT * FROM x; -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -CREATE TABLE xmltest2(x xml, _path text); -INSERT INTO xmltest2 VALUES('1', 'A'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest2 VALUES('1', 'A')... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -INSERT INTO xmltest2 VALUES('2', 'B'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest2 VALUES('2', 'B')... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -INSERT INTO xmltest2 VALUES('3', 'C'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest2 VALUES('3', 'C')... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -INSERT INTO xmltest2 VALUES('2', 'D'); -ERROR: unsupported XML feature -LINE 1: INSERT INTO xmltest2 VALUES('2', 'D')... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c'); - a ---- -(0 rows) - -SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.'); - a ---- -(0 rows) - -SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54); - a ---- -(0 rows) - --- XPath result can be boolean or number too -SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"', e integer PATH 'string-length(.)'); -ERROR: unsupported XML feature -LINE 1: SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml ... - ^ -DETAIL: This functionality requires the server to be built with libxml support. -\x -SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH '/e/n2', y xml PATH '/'); -ERROR: unsupported XML feature -LINE 1: SELECT * FROM XMLTABLE('*' PASSING 'pre"', b xml PATH '""'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmltext(NULL); - xmltext ---------- - -(1 row) - -SELECT xmltext(''); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmltext(' '); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmltext('foo `$_-+?=*^%!|/\()[]{}'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmltext('foo & <"bar">'); -ERROR: unsupported XML feature -DETAIL: This functionality requires the server to be built with libxml support. -SELECT xmltext('x'|| '

73

'::xml || .42 || true || 'j'::char); -ERROR: unsupported XML feature -LINE 1: SELECT xmltext('x'|| '

73

'::xml || .42 || true || 'j':... - ^ -DETAIL: This functionality requires the server to be built with libxml support. +psql: error: connection to server on socket "/tmp/2f2ycaowGU/.s.PGSQL.13722" failed: could not fork new process for connection: Resource temporarily unavailable