diff -U3 /home/postgres/pgsql/src/test/regress/expected/alter_table.out /home/postgres/pgsql/src/test/regress/results/alter_table.out --- /home/postgres/pgsql/src/test/regress/expected/alter_table.out 2019-07-05 13:52:24.628980105 -0400 +++ /home/postgres/pgsql/src/test/regress/results/alter_table.out 2019-07-17 14:14:07.868058584 -0400 @@ -6,6 +6,8 @@ DROP ROLE IF EXISTS regress_alter_table_user1; RESET client_min_messages; CREATE USER regress_alter_table_user1; +-- Useful to check that things are actually doing what we expect +SET client_min_messages = debug1; -- -- add attribute -- @@ -19,6 +21,7 @@ ALTER TABLE attmp ADD COLUMN a int4 default 3; ALTER TABLE attmp ADD COLUMN b name; ALTER TABLE attmp ADD COLUMN c text; +DEBUG: building index "pg_toast_68110_index" on table "pg_toast_68110" serially ALTER TABLE attmp ADD COLUMN d float8; ALTER TABLE attmp ADD COLUMN e float4; ALTER TABLE attmp ADD COLUMN f int2; @@ -61,6 +64,7 @@ ALTER TABLE attmp ADD COLUMN a int4; ALTER TABLE attmp ADD COLUMN b name; ALTER TABLE attmp ADD COLUMN c text; +DEBUG: building index "pg_toast_68402_index" on table "pg_toast_68402" serially ALTER TABLE attmp ADD COLUMN d float8; ALTER TABLE attmp ADD COLUMN e float4; ALTER TABLE attmp ADD COLUMN f int2; @@ -96,6 +100,7 @@ (1 row) CREATE INDEX attmp_idx ON attmp (a, (d + e), b); +DEBUG: building index "attmp_idx" on table "attmp" serially 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; @@ -138,6 +143,9 @@ ALTER TABLE attmp RENAME TO attmp_new2; SELECT * FROM attmp; -- should fail +DEBUG: relation "attmp" does not exist +LINE 1: SELECT * FROM attmp; + ^ ERROR: relation "attmp" does not exist LINE 1: SELECT * FROM attmp; ^ @@ -155,7 +163,10 @@ DROP TABLE attmp_new2; -- check rename of partitioned tables and indexes also CREATE TABLE part_attmp (a int primary key) partition by range (a); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "part_attmp_pkey" for table "part_attmp" CREATE TABLE part_attmp1 PARTITION OF part_attmp FOR VALUES FROM (0) TO (100); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "part_attmp1_pkey" for table "part_attmp1" +DEBUG: building index "part_attmp1_pkey" on table "part_attmp1" serially 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; @@ -223,8 +234,10 @@ -- 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; +DEBUG: relation "__onek_unique1" does not exist NOTICE: relation "__onek_unique1" does not exist, skipping ALTER INDEX IF EXISTS __attmp_onek_unique1 RENAME TO onek_unique1; +DEBUG: relation "__attmp_onek_unique1" does not exist 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; @@ -261,14 +274,19 @@ 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); +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "onek" +DEBUG: building index "onek_unique1_constraint" on table "onek" serially 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); +DEBUG: verifying table "onek" 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); +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "onek" +DEBUG: building index "onek_unique1_constraint" on table "onek" serially 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. @@ -333,6 +351,7 @@ Inherits: constraint_rename_test ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT; +DEBUG: verifying table "constraint_rename_test" 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 @@ -360,6 +379,10 @@ Inherits: constraint_rename_test ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "con3" for table "constraint_rename_test" +DEBUG: building index "con3" on table "constraint_rename_test" serially +DEBUG: verifying table "constraint_rename_test" +DEBUG: verifying table "constraint_rename_test2" ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok \d constraint_rename_test Table "public.constraint_rename_test" @@ -390,19 +413,25 @@ DROP TABLE constraint_rename_test2; DROP TABLE constraint_rename_test; ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok +DEBUG: relation "constraint_not_exist" does not exist NOTICE: relation "constraint_not_exist" does not exist, skipping ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a); +DEBUG: relation "constraint_rename_test" does not exist 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)); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "constraint_rename_cache_pkey" for table "constraint_rename_cache" +DEBUG: building index "constraint_rename_cache_pkey" on table "constraint_rename_cache" serially 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); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "like_constraint_rename_cache_pkey" for table "like_constraint_rename_cache" +DEBUG: building index "like_constraint_rename_cache_pkey" on table "like_constraint_rename_cache" serially \d like_constraint_rename_cache Table "public.like_constraint_rename_cache" Column | Type | Collation | Nullable | Default @@ -417,8 +446,12 @@ DROP TABLE like_constraint_rename_cache; -- FOREIGN KEY CONSTRAINT adding TEST CREATE TABLE attmp2 (a int primary key); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "attmp2_pkey" for table "attmp2" +DEBUG: building index "attmp2_pkey" on table "attmp2" serially CREATE TABLE attmp3 (a int, b int); CREATE TABLE attmp4 (a int, b int, unique(a,b)); +DEBUG: CREATE TABLE / UNIQUE will create implicit index "attmp4_a_b_key" for table "attmp4" +DEBUG: building index "attmp4_a_b_key" on table "attmp4" serially CREATE TABLE attmp5 (a int, b int); -- Insert rows into attmp2 (pktable) INSERT INTO attmp2 values (1); @@ -437,26 +470,31 @@ 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; +DEBUG: validating foreign key 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) ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full; +DEBUG: validating foreign key constraint "attmpconstr" 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; +DEBUG: validating foreign key 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; +DEBUG: validating foreign key 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 +DEBUG: verifying table "attmp3" ERROR: check constraint "b_greater_than_ten" is violated by some row ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails @@ -483,6 +521,7 @@ 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)); +DEBUG: verifying table "attmp7" NOTICE: boo: 18 ALTER TABLE attmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID; NOTICE: merging constraint "identity" with inherited definition @@ -606,8 +645,11 @@ -- 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); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" +DEBUG: building index "pktable_pkey" on table "pktable" serially INSERT INTO PKTABLE VALUES(42); CREATE TEMP TABLE FKTABLE (ftest1 inet); +DEBUG: building index "pg_toast_69835_index" on table "pg_toast_69835" serially -- 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 @@ -622,6 +664,7 @@ -- 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; +DEBUG: validating foreign key constraint "fktable_ftest1_fkey" -- Check it actually works INSERT INTO FKTABLE VALUES(42); -- should succeed INSERT INTO FKTABLE VALUES(43); -- should fail @@ -632,6 +675,7 @@ -- not an implicit coercion (or use numeric=numeric, but that's not part -- of the integer opfamily) CREATE TEMP TABLE FKTABLE (ftest1 numeric); +DEBUG: building index "pg_toast_69849_index" on table "pg_toast_69849" serially ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer. @@ -640,9 +684,13 @@ -- 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); +DEBUG: building index "pg_toast_69856_index" on table "pg_toast_69856" serially +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" +DEBUG: building index "pktable_pkey" on table "pktable" serially INSERT INTO PKTABLE VALUES(42); CREATE TEMP TABLE FKTABLE (ftest1 int); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; +DEBUG: validating foreign key constraint "fktable_ftest1_fkey" -- Check it actually works INSERT INTO FKTABLE VALUES(42); -- should succeed INSERT INTO FKTABLE VALUES(43); -- should fail @@ -652,14 +700,19 @@ DROP TABLE PKTABLE; CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2)); +DEBUG: building index "pg_toast_69877_index" on table "pg_toast_69877" serially +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" +DEBUG: building index "pktable_pkey" on table "pktable" serially -- This should fail, because we just chose really odd types CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); +DEBUG: building index "pg_toast_69887_index" on table "pg_toast_69887" serially 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" and "ptest1" are of incompatible types: cidr and integer. DROP TABLE FKTABLE; -- Again, so should this... CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); +DEBUG: building index "pg_toast_69894_index" on table "pg_toast_69894" serially ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1, ptest2); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented @@ -667,6 +720,7 @@ DROP TABLE FKTABLE; -- This fails because we mixed up the column ordering CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet); +DEBUG: building index "pg_toast_69901_index" on table "pg_toast_69901" serially ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2, ptest1); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented @@ -680,21 +734,29 @@ DROP TABLE PKTABLE; -- Test that ALTER CONSTRAINT updates trigger deferrability properly CREATE TEMP TABLE PKTABLE (ptest1 int primary key); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" +DEBUG: building index "pktable_pkey" on table "pktable" serially 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; +DEBUG: validating foreign key constraint "fknd" ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; +DEBUG: validating foreign key constraint "fkdd" ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE; +DEBUG: validating foreign key constraint "fkdi" ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; +DEBUG: validating foreign key constraint "fknd2" 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; +DEBUG: validating foreign key constraint "fkdd2" 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; +DEBUG: validating foreign key constraint "fkdi2" 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 @@ -741,6 +803,7 @@ create table atacc1 ( test int ); -- add a check constraint alter table atacc1 add constraint atacc_test1 check (test>3); +DEBUG: verifying table "atacc1" -- should fail insert into atacc1 (test) values (2); ERROR: new row for relation "atacc1" violates check constraint "atacc_test1" @@ -754,6 +817,7 @@ insert into atacc1 (test) values (2); -- add a check constraint (fails) alter table atacc1 add constraint atacc_test1 check (test>3); +DEBUG: verifying table "atacc1" ERROR: check constraint "atacc_test1" is violated by some row insert into atacc1 (test) values (4); drop table atacc1; @@ -768,6 +832,7 @@ 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); +DEBUG: verifying table "atacc1" -- should fail for $2 insert into atacc1 (test2, test) values (3, 4); ERROR: new row for relation "atacc1" violates check constraint "atacc1_check" @@ -788,6 +854,8 @@ create table atacc2 (test2 int); create table atacc3 (test3 int) inherits (atacc1, atacc2); alter table atacc2 add constraint foo check (test2>0); +DEBUG: verifying table "atacc2" +DEBUG: verifying table "atacc3" -- fail and then succeed on atacc2 insert into atacc2 (test2) values (-3); ERROR: new row for relation "atacc2" violates check constraint "foo" @@ -818,6 +886,7 @@ -- fail due to missing constraint alter table atacc2 add constraint foo check (test2>0); +DEBUG: verifying table "atacc2" alter table atacc3 inherit atacc2; ERROR: child table is missing constraint "foo" -- fail due to missing column @@ -833,6 +902,7 @@ alter table atacc3 add test2 int; update atacc3 set test2 = 4 where test2 is null; alter table atacc3 add constraint foo check (test2>0); +DEBUG: verifying table "atacc3" alter table atacc3 inherit atacc2; -- fail due to duplicates and circular inheritance alter table atacc3 inherit atacc2; @@ -858,6 +928,7 @@ create table atacc2 (test2 int) inherits (atacc1); -- ok: alter table atacc1 add constraint foo check (test>0) no inherit; +DEBUG: verifying table "atacc1" -- check constraint is not there on child insert into atacc2 (test) values (-3); -- check constraint is there on parent @@ -867,6 +938,7 @@ insert into atacc1 (test) values (3); -- fail, violating row: alter table atacc2 add constraint foo check (test>0) no inherit; +DEBUG: verifying table "atacc2" ERROR: check constraint "foo" is violated by some row drop table atacc2; drop table atacc1; @@ -874,6 +946,8 @@ create table atacc1 ( test int ) ; -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test); +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" +DEBUG: building index "atacc_test1" on table "atacc1" serially -- insert first value insert into atacc1 (test) values (2); -- should fail @@ -884,6 +958,8 @@ 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; +DEBUG: rewriting table "atacc1" +DEBUG: building index "atacc_test1" on table "atacc1" serially ERROR: could not create unique index "atacc_test1" DETAIL: Key (test)=(0) is duplicated. drop table atacc1; @@ -894,6 +970,8 @@ insert into atacc1 (test) values (2); -- add a unique constraint (fails) alter table atacc1 add constraint atacc_test1 unique (test); +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" +DEBUG: building index "atacc_test1" on table "atacc1" serially ERROR: could not create unique index "atacc_test1" DETAIL: Key (test)=(2) is duplicated. insert into atacc1 (test) values (3); @@ -909,6 +987,8 @@ create table atacc1 ( test int, test2 int); -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test, test2); +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" +DEBUG: building index "atacc_test1" on table "atacc1" serially -- insert initial value insert into atacc1 (test,test2) values (4,4); -- should fail @@ -922,7 +1002,11 @@ drop table atacc1; -- lets do some naming tests create table atacc1 (test int, test2 int, unique(test)); +DEBUG: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1" +DEBUG: building index "atacc1_test_key" on table "atacc1" serially alter table atacc1 add unique (test2); +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1" +DEBUG: building index "atacc1_test2_key" on table "atacc1" serially -- should fail for @@ second one @@ insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); @@ -931,8 +1015,12 @@ drop table atacc1; -- test primary key constraint adding create table atacc1 ( id serial, test int) ; +DEBUG: CREATE TABLE will create implicit sequence "atacc1_id_seq" for serial column "atacc1.id" -- add a primary key constraint alter table atacc1 add constraint atacc_test1 primary key (test); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" +DEBUG: building index "atacc_test1" on table "atacc1" serially +DEBUG: verifying table "atacc1" -- insert first value insert into atacc1 (test) values (2); -- should fail @@ -952,6 +1040,8 @@ 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); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1" +DEBUG: building index "atacc_oid1" on table "atacc1" serially drop table atacc1; -- let's do one where the primary key constraint fails when added create table atacc1 ( test int ); @@ -960,6 +1050,8 @@ insert into atacc1 (test) values (2); -- add a primary key (fails) alter table atacc1 add constraint atacc_test1 primary key (test); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" +DEBUG: building index "atacc_test1" on table "atacc1" serially ERROR: could not create unique index "atacc_test1" DETAIL: Key (test)=(2) is duplicated. insert into atacc1 (test) values (3); @@ -970,6 +1062,9 @@ insert into atacc1 (test) values (NULL); -- add a primary key (fails) alter table atacc1 add constraint atacc_test1 primary key (test); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" +DEBUG: building index "atacc_test1" on table "atacc1" serially +DEBUG: verifying table "atacc1" ERROR: column "test" contains null values insert into atacc1 (test) values (3); drop table atacc1; @@ -986,9 +1081,14 @@ insert into atacc1 (test) values (0); -- add a primary key column without a default (fails). alter table atacc1 add column test2 int primary key; +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +DEBUG: building index "atacc1_pkey" on table "atacc1" serially +DEBUG: verifying table "atacc1" ERROR: column "test2" contains null values -- now add a primary key column with a default (succeeds). alter table atacc1 add column test2 int default 0 primary key; +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +DEBUG: building index "atacc1_pkey" on table "atacc1" serially drop table atacc1; -- this combination used to have order-of-execution problems (bug #15580) create table atacc1 (a int); @@ -996,11 +1096,17 @@ alter table atacc1 add column b float8 not null default random(), add primary key(a); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +DEBUG: rewriting table "atacc1" +DEBUG: building index "atacc1_pkey" on table "atacc1" serially 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); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" +DEBUG: building index "atacc_test1" on table "atacc1" serially +DEBUG: verifying table "atacc1" -- 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 @@ -1026,6 +1132,8 @@ drop table atacc1; -- lets do some naming tests create table atacc1 (test int, test2 int, primary key(test)); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +DEBUG: building index "atacc1_pkey" on table "atacc1" serially -- only first should succeed insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); @@ -1050,15 +1158,19 @@ -- 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); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +DEBUG: building index "atacc1_pkey" on table "atacc1" serially alter table atacc1 alter column test drop not null; ERROR: column "test" is in a primary key alter table atacc1 drop constraint "atacc1_pkey"; alter table atacc1 alter column test drop not null; insert into atacc1 values (null); alter table atacc1 alter test set not null; +DEBUG: verifying table "atacc1" ERROR: column "test" contains null values delete from atacc1; alter table atacc1 alter test set not null; +DEBUG: verifying table "atacc1" -- 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 @@ -1077,40 +1189,54 @@ 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); +DEBUG: verifying table "atacc1" alter table atacc1 alter test_a set not null; +DEBUG: verifying table "atacc1" ERROR: column "test_a" 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; +DEBUG: verifying table "atacc1" ERROR: column "test_a" 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); +DEBUG: verifying table "atacc1" alter table atacc1 alter test_a set not null; +DEBUG: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls 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; +DEBUG: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls +DEBUG: verifying table "atacc1" ERROR: column "test_b" contains null values -- commands order has no importance alter table atacc1 alter test_b set not null, alter test_a set not null; +DEBUG: verifying table "atacc1" ERROR: column "test_b" 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; +DEBUG: verifying table "atacc1" 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); +DEBUG: verifying table "atacc1" alter table atacc1 alter test_b set not null, alter test_a set not null; +DEBUG: existing constraints on column "atacc1"."test_b" are sufficient to prove that it does not contain nulls +DEBUG: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls drop table atacc1; -- test inheritance create table parent (a int); create table child (b varchar(255)) inherits (parent); alter table parent alter a set not null; +DEBUG: verifying table "parent" +DEBUG: verifying table "child" insert into parent values (NULL); ERROR: null value in column "a" violates not-null constraint DETAIL: Failing row contains (null). @@ -1121,15 +1247,19 @@ insert into parent values (NULL); insert into child (a, b) values (NULL, 'foo'); alter table only parent alter a set not null; +DEBUG: verifying table "parent" ERROR: column "a" contains null values alter table child alter a set not null; +DEBUG: verifying table "child" ERROR: column "a" contains null values delete from parent; alter table only parent alter a set not null; +DEBUG: verifying table "parent" insert into parent values (NULL); ERROR: null value in column "a" violates not-null constraint DETAIL: Failing row contains (null). alter table child alter a set not null; +DEBUG: verifying table "child" insert into child (a, b) values (NULL, 'foo'); ERROR: null value in column "a" violates not-null constraint DETAIL: Failing row contains (null, foo). @@ -1145,6 +1275,7 @@ c1 int4 default 5, c2 text default 'initial_default' ); +DEBUG: building index "pg_toast_70165_index" on table "pg_toast_70165" serially insert into def_test default values; alter table def_test alter column c1 drop default; insert into def_test default values; @@ -1423,6 +1554,8 @@ alter table atacc1 add check ("........pg.dropped.1........" > 3); ERROR: column "........pg.dropped.1........" does not exist create table atacc2 (id int4 unique); +DEBUG: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2" +DEBUG: building index "atacc2_id_key" on table "atacc2" serially 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); @@ -1465,11 +1598,15 @@ drop table atacc1; -- test constraint error reporting in presence of dropped columns create table atacc1 (id serial primary key, value int check (value < 10)); +DEBUG: CREATE TABLE will create implicit sequence "atacc1_id_seq" for serial column "atacc1.id" +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +DEBUG: building index "atacc1_pkey" on table "atacc1" serially 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); +DEBUG: verifying table "atacc1" insert into atacc1(value) values (100); ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check" DETAIL: Failing row contains (2, 100). @@ -1514,6 +1651,7 @@ drop table parent; -- check error cases for inheritance column merging create table parent (a float8, b numeric(10,4), c text collate "C"); +DEBUG: building index "pg_toast_70227_index" on table "pg_toast_70227" serially create table child (a float4) inherits (parent); -- fail NOTICE: merging column "a" with inherited definition ERROR: column "a" has a type conflict @@ -1531,6 +1669,7 @@ 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 +DEBUG: building index "pg_toast_70233_index" on table "pg_toast_70233" serially drop table child; drop table parent; -- test copy in/out @@ -1602,8 +1741,10 @@ alter table renameColumnChild rename column b to a; -- these should work alter table if exists doesnt_exist_tab rename column a to d; +DEBUG: relation "doesnt_exist_tab" does not exist NOTICE: relation "doesnt_exist_tab" does not exist, skipping alter table if exists doesnt_exist_tab rename column b to a; +DEBUG: relation "doesnt_exist_tab" does not exist NOTICE: relation "doesnt_exist_tab" does not exist, skipping -- this should work alter table renameColumn add column w int; @@ -1669,10 +1810,14 @@ drop table p1 cascade; NOTICE: drop cascades to table c1 create table p1(id int, name text); +DEBUG: building index "pg_toast_70285_index" on table "pg_toast_70285" serially create table p2(id2 int, name text, height int); +DEBUG: building index "pg_toast_70291_index" on table "pg_toast_70291" serially create table c1(age int) inherits(p1,p2); NOTICE: merging multiple inherited definitions of column "name" +DEBUG: building index "pg_toast_70297_index" on table "pg_toast_70297" serially create table gc1() inherits (c1); +DEBUG: building index "pg_toast_70303_index" on table "pg_toast_70303" serially 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 @@ -1739,9 +1884,13 @@ -- test attinhcount tracking with merged columns create table depth0(); create table depth1(c text) inherits (depth0); +DEBUG: building index "pg_toast_70316_index" on table "pg_toast_70316" serially create table depth2() inherits (depth1); +DEBUG: building index "pg_toast_70322_index" on table "pg_toast_70322" serially alter table depth0 add c text; NOTICE: merging definition of column "c" for child "depth1" +DEBUG: rehashing catalog cache id 58 for pg_statistic; 257 tups, 128 buckets +DEBUG: building index "pg_toast_70313_index" on table "pg_toast_70313" serially select attrelid::regclass, attname, attinhcount, attislocal from pg_attribute where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2') @@ -1756,9 +1905,13 @@ -- test renumbering of child-table columns in inherited operations create table p1 (f1 int); create table c1 (f2 text, f3 int) inherits (p1); +DEBUG: building index "pg_toast_70335_index" on table "pg_toast_70335" serially alter table p1 add column a1 int check (a1 > 0); +DEBUG: verifying table "p1" +DEBUG: verifying table "c1" alter table p1 add column f2 text; NOTICE: merging definition of column "f2" for child "c1" +DEBUG: building index "pg_toast_70332_index" on table "pg_toast_70332" serially 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" @@ -1785,6 +1938,7 @@ -- its datatype create domain mytype as text; create temp table foo (f1 text, f2 mytype, f3 text); +DEBUG: building index "pg_toast_70352_index" on table "pg_toast_70352" serially insert into foo values('bb','cc','dd'); select * from foo; f1 | f2 | f3 @@ -1827,8 +1981,11 @@ 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); +DEBUG: building index "pg_toast_70359_index" on table "pg_toast_70359" serially +DEBUG: rewriting table "foo" create table anothertab (atcol1 serial8, atcol2 boolean, constraint anothertab_chk check (atcol1 <= 3)); +DEBUG: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1" insert into anothertab (atcol1, atcol2) values (default, true); insert into anothertab (atcol1, atcol2) values (default, false); select * from anothertab; @@ -1845,6 +2002,7 @@ 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; +DEBUG: rewriting table "anothertab" select * from anothertab; atcol1 | atcol2 --------+-------- @@ -1868,6 +2026,9 @@ using case when atcol2 is true then 'IT WAS TRUE' when atcol2 is false then 'IT WAS FALSE' else 'IT WAS NULL!' end; +DEBUG: building index "pg_toast_70368_index" on table "pg_toast_70368" serially +DEBUG: building index "pg_toast_70385_index" on table "pg_toast_70385" serially +DEBUG: rewriting table "anothertab" select * from anothertab; atcol1 | atcol2 --------+-------------- @@ -1891,6 +2052,8 @@ 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; +DEBUG: building index "pg_toast_70391_index" on table "pg_toast_70391" serially +DEBUG: rewriting table "anothertab" select * from anothertab; atcol1 | atcol2 --------+-------------- @@ -1900,19 +2063,34 @@ (3 rows) drop table anothertab; +DEBUG: rehashing catalog cache id 74 for pg_type; 129 tups, 64 buckets -- 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); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "anothertab_pkey" for table "anothertab" +DEBUG: building index "anothertab_pkey" on table "anothertab" serially +DEBUG: CREATE TABLE / UNIQUE will create implicit index "anothertab_f2_key" for table "anothertab" +DEBUG: building index "anothertab_f2_key" on table "anothertab" serially alter table anothertab add exclude using btree (f3 with =); +DEBUG: ALTER TABLE / ADD EXCLUDE will create implicit index "anothertab_f3_excl" for table "anothertab" +DEBUG: building index "anothertab_f3_excl" on table "anothertab" serially alter table anothertab add exclude using btree (f4 with =) where (f4 is not null); +DEBUG: ALTER TABLE / ADD EXCLUDE will create implicit index "anothertab_f4_excl" for table "anothertab" +DEBUG: building index "anothertab_f4_excl" on table "anothertab" serially alter table anothertab add exclude using btree (f4 with =) where (f5 > 0); +DEBUG: ALTER TABLE / ADD EXCLUDE will create implicit index "anothertab_f4_excl1" for table "anothertab" +DEBUG: building index "anothertab_f4_excl1" on table "anothertab" serially alter table anothertab add unique(f1,f4); +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "anothertab_f1_f4_key" for table "anothertab" +DEBUG: building index "anothertab_f1_f4_key" on table "anothertab" serially create index on anothertab(f2,f3); +DEBUG: building index "anothertab_f2_f3_idx" on table "anothertab" serially create unique index on anothertab(f4); +DEBUG: building index "anothertab_f4_idx" on table "anothertab" serially \d anothertab Table "public.anothertab" Column | Type | Collation | Nullable | Default @@ -1933,11 +2111,39 @@ "anothertab_f4_idx" UNIQUE, btree (f4) alter table anothertab alter column f1 type bigint; +DEBUG: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets +DEBUG: rewriting table "anothertab" +DEBUG: building index "anothertab_f2_key" on table "anothertab" serially +DEBUG: building index "anothertab_f3_excl" on table "anothertab" serially +DEBUG: building index "anothertab_f4_excl" on table "anothertab" serially +DEBUG: building index "anothertab_f4_excl1" on table "anothertab" serially +DEBUG: building index "anothertab_f2_f3_idx" on table "anothertab" serially +DEBUG: building index "anothertab_f4_idx" on table "anothertab" serially +DEBUG: building index "anothertab_pkey" on table "anothertab" serially +DEBUG: building index "anothertab_f1_f4_key" on table "anothertab" serially alter table anothertab alter column f2 type bigint, alter column f3 type bigint, alter column f4 type bigint; +DEBUG: rewriting table "anothertab" +DEBUG: building index "anothertab_pkey" on table "anothertab" serially +DEBUG: building index "anothertab_f2_key" on table "anothertab" serially +DEBUG: building index "anothertab_f3_excl" on table "anothertab" serially +DEBUG: building index "anothertab_f4_excl" on table "anothertab" serially +DEBUG: building index "anothertab_f4_excl1" on table "anothertab" serially +DEBUG: building index "anothertab_f1_f4_key" on table "anothertab" serially +DEBUG: building index "anothertab_f2_f3_idx" on table "anothertab" serially +DEBUG: building index "anothertab_f4_idx" on table "anothertab" serially alter table anothertab alter column f5 type bigint; +DEBUG: rewriting table "anothertab" +DEBUG: building index "anothertab_pkey" on table "anothertab" serially +DEBUG: building index "anothertab_f2_key" on table "anothertab" serially +DEBUG: building index "anothertab_f3_excl" on table "anothertab" serially +DEBUG: building index "anothertab_f4_excl" on table "anothertab" serially +DEBUG: building index "anothertab_f1_f4_key" on table "anothertab" serially +DEBUG: building index "anothertab_f2_f3_idx" on table "anothertab" serially +DEBUG: building index "anothertab_f4_idx" on table "anothertab" serially +DEBUG: building index "anothertab_f4_excl1" on table "anothertab" serially \d anothertab Table "public.anothertab" Column | Type | Collation | Nullable | Default @@ -1959,6 +2165,7 @@ drop table anothertab; create table another (f1 int, f2 text); +DEBUG: building index "pg_toast_70477_index" on table "pg_toast_70477" serially insert into another values(1, 'one'); insert into another values(2, 'two'); insert into another values(3, 'three'); @@ -1973,6 +2180,8 @@ alter table another alter f1 type text using f2 || ' more', alter f2 type bigint using f1 * 10; +DEBUG: building index "pg_toast_70484_index" on table "pg_toast_70484" serially +DEBUG: rewriting table "another" select * from another; f1 | f2 ------------+---- @@ -1984,17 +2193,23 @@ drop table another; -- table's row type create table tab1 (a int, b text); +DEBUG: building index "pg_toast_70491_index" on table "pg_toast_70491" serially create table tab2 (x int, y tab1); +DEBUG: building index "pg_toast_70497_index" on table "pg_toast_70497" serially alter table tab1 alter column b type varchar; -- fails ERROR: cannot alter table "tab1" because column "tab2.y" uses its row type -- 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); +DEBUG: building index "pg_toast_70507_index" on table "pg_toast_70507" serially insert into at_partitioned values (512, '0.123'); create table at_part_2 (b text, a int); +DEBUG: building index "pg_toast_70513_index" on table "pg_toast_70513" serially insert into at_part_2 values ('1.234', 1024); create index on at_partitioned (b); +DEBUG: building index "at_part_1_b_idx" on table "at_part_1" serially create index on at_partitioned (a); +DEBUG: building index "at_part_1_a_idx" on table "at_part_1" serially \d at_part_1 Table "public.at_part_1" Column | Type | Collation | Nullable | Default @@ -2014,6 +2229,9 @@ a | integer | | | alter table at_partitioned attach partition at_part_2 for values from (1000) to (2000); +DEBUG: building index "at_part_2_b_idx" on table "at_part_2" serially +DEBUG: building index "at_part_2_a_idx" on table "at_part_2" serially +DEBUG: verifying table "at_part_2" \d at_part_2 Table "public.at_part_2" Column | Type | Collation | Nullable | Default @@ -2026,6 +2244,14 @@ "at_part_2_b_idx" btree (b) alter table at_partitioned alter column b type numeric using b::numeric; +DEBUG: building index "pg_toast_70530_index" on table "pg_toast_70530" serially +DEBUG: rewriting table "at_part_1" +DEBUG: building index "at_part_1_a_idx" on table "at_part_1" serially +DEBUG: building index "at_part_1_b_idx" on table "at_part_1" serially +DEBUG: building index "pg_toast_70538_index" on table "pg_toast_70538" serially +DEBUG: rewriting table "at_part_2" +DEBUG: building index "at_part_2_a_idx" on table "at_part_2" serially +DEBUG: building index "at_part_2_b_idx" on table "at_part_2" serially \d at_part_1 Table "public.at_part_1" Column | Type | Collation | Nullable | Default @@ -2053,14 +2279,22 @@ -- Also check that comments are preserved create table at_partitioned(id int, name varchar(64), unique (id, name)) partition by hash(id); +DEBUG: CREATE TABLE / UNIQUE will create implicit index "at_partitioned_id_name_key" for table "at_partitioned" 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); +DEBUG: CREATE TABLE / UNIQUE will create implicit index "at_partitioned_0_id_name_key" for table "at_partitioned_0" +DEBUG: building index "at_partitioned_0_id_name_key" on table "at_partitioned_0" serially 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); +DEBUG: rehashing catalog cache id 49 for pg_class; 257 tups, 128 buckets +LINE 1: create table at_partitioned_1 partition of at_partitioned + ^ +DEBUG: CREATE TABLE / UNIQUE will create implicit index "at_partitioned_1_id_name_key" for table "at_partitioned_1" +DEBUG: building index "at_partitioned_1_id_name_key" on table "at_partitioned_1" serially 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'); @@ -2101,6 +2335,8 @@ (3 rows) alter table at_partitioned alter column name type varchar(127); +DEBUG: building index "at_partitioned_0_id_name_key" on table "at_partitioned_0" serially +DEBUG: building index "at_partitioned_1_id_name_key" on table "at_partitioned_1" serially -- Note: these tests currently show the wrong behavior for comments :-( select relname, c.oid = oldoid as orig_oid, @@ -2146,6 +2382,7 @@ 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); +DEBUG: building index "pg_toast_70583_index" on table "pg_toast_70583" serially 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; @@ -2153,6 +2390,7 @@ 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); +DEBUG: building index "pg_toast_70589_index" on table "pg_toast_70589" serially alter table test_storage alter a set storage plain; alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table alter table test_storage alter a set storage extended; -- re-add TOAST table @@ -2198,6 +2436,12 @@ (2 rows) ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric; +DEBUG: building index "pg_toast_70597_index" on table "pg_toast_70597" serially +DEBUG: building index "pg_toast_70601_index" on table "pg_toast_70601" serially +DEBUG: building index "pg_toast_70619_index" on table "pg_toast_70619" serially +DEBUG: rewriting table "test_inh_check" +DEBUG: building index "pg_toast_70630_index" on table "pg_toast_70630" serially +DEBUG: rewriting table "test_inh_check_child" \d test_inh_check Table "public.test_inh_check" Column | Type | Collation | Nullable | Default @@ -2230,10 +2474,14 @@ -- also try noinherit, local, and local+inherited cases ALTER TABLE test_inh_check ADD CONSTRAINT bnoinherit CHECK (b > 100) NO INHERIT; +DEBUG: verifying table "test_inh_check" ALTER TABLE test_inh_check_child ADD CONSTRAINT blocal CHECK (b < 1000); +DEBUG: verifying table "test_inh_check_child" ALTER TABLE test_inh_check_child ADD CONSTRAINT bmerged CHECK (b > 1); +DEBUG: verifying table "test_inh_check_child" ALTER TABLE test_inh_check ADD CONSTRAINT bmerged CHECK (b > 1); NOTICE: merging constraint "bmerged" with inherited definition +DEBUG: verifying table "test_inh_check" \d test_inh_check Table "public.test_inh_check" Column | Type | Collation | Nullable | Default @@ -2274,6 +2522,10 @@ ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric; NOTICE: merging constraint "bmerged" with inherited definition +DEBUG: building index "pg_toast_70647_index" on table "pg_toast_70647" serially +DEBUG: rewriting table "test_inh_check" +DEBUG: building index "pg_toast_70654_index" on table "pg_toast_70654" serially +DEBUG: rewriting table "test_inh_check_child" \d test_inh_check Table "public.test_inh_check" Column | Type | Collation | Nullable | Default @@ -2319,6 +2571,8 @@ 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; +DEBUG: rewriting table "test_type_diff" +DEBUG: rewriting table "test_type_diff_c" 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); @@ -2330,13 +2584,21 @@ 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; +DEBUG: rewriting table "test_type_diff2" +DEBUG: rewriting table "test_type_diff2_c1" +DEBUG: rewriting table "test_type_diff2_c2" +DEBUG: rewriting table "test_type_diff2_c3" -- 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); +DEBUG: building index "pg_toast_70699_index" on table "pg_toast_70699" serially +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "check_fk_presence_1_pkey" for table "check_fk_presence_1" +DEBUG: building index "check_fk_presence_1_pkey" on table "check_fk_presence_1" serially CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text); +DEBUG: building index "pg_toast_70707_index" on table "pg_toast_70707" serially BEGIN; ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey; ANALYZE check_fk_presence_2; @@ -2353,6 +2615,7 @@ 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); +DEBUG: building index "pg_toast_70719_index" on table "pg_toast_70719" serially 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; @@ -2453,6 +2716,7 @@ ,'AccessExclusiveLock' ); drop view my_locks; +DEBUG: relation "my_locks" does not exist 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 @@ -2466,8 +2730,13 @@ and c.relname != 'my_locks' group by c.relname; create table alterlock (f1 int primary key, f2 text); +DEBUG: building index "pg_toast_70762_index" on table "pg_toast_70762" serially +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "alterlock_pkey" for table "alterlock" +DEBUG: building index "alterlock_pkey" on table "alterlock" serially insert into alterlock values (1, 'foo'); create table alterlock2 (f3 int primary key, f1 int); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "alterlock2_pkey" for table "alterlock2" +DEBUG: building index "alterlock2_pkey" on table "alterlock2" serially insert into alterlock2 values (1, 1); begin; alter table alterlock alter column f2 set statistics 150; select * from my_locks order by 1; @@ -2584,6 +2853,7 @@ (0 rows) alter table alterlock2 add foreign key (f1) references alterlock (f1); +DEBUG: validating foreign key constraint "alterlock2_f1_fkey" select * from my_locks order by 1; relname | max_lockmode -----------------+----------------------- @@ -2607,6 +2877,7 @@ commit; begin; alter table alterlock2 validate constraint alterlock2nv; +DEBUG: validating foreign key constraint "alterlock2nv" select * from my_locks order by 1; relname | max_lockmode -----------------+-------------------------- @@ -2704,6 +2975,9 @@ create schema alter1; create schema alter2; create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0)); +DEBUG: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" +DEBUG: building index "t1_pkey" on table "t1" serially 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); @@ -2848,11 +3122,14 @@ DROP TYPE test_type; CREATE TYPE test_type1 AS (a int, b text); CREATE TABLE test_tbl1 (x int, y test_type1); +DEBUG: building index "pg_toast_70865_index" on table "pg_toast_70865" serially ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails ERROR: cannot alter type "test_type1" because column "test_tbl1.y" uses it CREATE TYPE test_type2 AS (a int, b text); CREATE TABLE test_tbl2 OF test_type2; +DEBUG: building index "pg_toast_70874_index" on table "pg_toast_70874" serially CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2); +DEBUG: building index "pg_toast_70880_index" on table "pg_toast_70880" serially \d test_type2 Composite type "public.test_type2" Column | Type | Collation | Nullable | Default @@ -2964,6 +3241,7 @@ DROP TABLE test_tbl2_subclass; CREATE TYPE test_typex AS (a int, b text); CREATE TABLE test_tblx (x int, y test_typex check ((y).a > 0)); +DEBUG: building index "pg_toast_70919_index" on table "pg_toast_70919" serially 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 @@ -2984,6 +3262,7 @@ -- 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; +DEBUG: building index "pg_toast_70929_index" on table "pg_toast_70929" serially ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int; CREATE TYPE test_type_empty AS (); DROP TYPE test_type_empty; @@ -3000,6 +3279,7 @@ 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)); +DEBUG: building index "pg_toast_70962_index" on table "pg_toast_70962" serially ALTER TABLE tt7 DROP q; -- OK ALTER TABLE tt0 OF tt_t0; ALTER TABLE tt1 OF tt_t0; @@ -3027,7 +3307,9 @@ -- 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)); +DEBUG: building index "pg_toast_70971_index" on table "pg_toast_70971" serially CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent); +DEBUG: building index "pg_toast_70978_index" on table "pg_toast_70978" serially 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); @@ -3039,22 +3321,32 @@ -- IF EXISTS test -- ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; +DEBUG: relation "tt8" does not exist NOTICE: relation "tt8" does not exist, skipping ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f); +DEBUG: relation "tt8" does not exist NOTICE: relation "tt8" does not exist, skipping ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); +DEBUG: relation "tt8" does not exist NOTICE: relation "tt8" does not exist, skipping ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0; +DEBUG: relation "tt8" does not exist NOTICE: relation "tt8" does not exist, skipping ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1; +DEBUG: relation "tt8" does not exist NOTICE: relation "tt8" does not exist, skipping ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2; +DEBUG: relation "tt8" does not exist 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); +DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "xxx" for table "tt8" +DEBUG: building index "xxx" on table "tt8" serially +DEBUG: verifying table "tt8" ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); +DEBUG: verifying table "tt8" 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; @@ -3076,20 +3368,32 @@ -- CREATE TABLE tt9(c integer); ALTER TABLE tt9 ADD CHECK(c > 1); +DEBUG: verifying table "tt9" ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name +DEBUG: verifying table "tt9" ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3); +DEBUG: verifying table "tt9" 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); +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "tt9_c_key" for table "tt9" +DEBUG: building index "tt9_c_key" on table "tt9" serially ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "tt9_c_key1" for table "tt9" +DEBUG: building index "tt9_c_key1" on table "tt9" serially ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "tt9_c_key" for table "tt9" ERROR: relation "tt9_c_key" already exists ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "foo" for table "tt9" 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); +DEBUG: verifying table "tt9" ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name +DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "tt9_c_key3" for table "tt9" +DEBUG: building index "tt9_c_key3" on table "tt9" serially \d tt9 Table "public.tt9" Column | Type | Collation | Nullable | Default @@ -3112,7 +3416,10 @@ positive_col int CHECK (positive_col > 0), indexed_col int, CONSTRAINT comment_test_pk PRIMARY KEY (id)); +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "comment_test_pk" for table "comment_test" +DEBUG: building index "comment_test_pk" on table "comment_test" serially CREATE INDEX comment_test_index ON comment_test(indexed_col); +DEBUG: building index "comment_test_index" on table "comment_test" serially 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'; @@ -3144,10 +3451,24 @@ -- 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; +DEBUG: building index "pg_toast_71006_index" on table "pg_toast_71006" serially +DEBUG: building index "pg_toast_71018_index" on table "pg_toast_71018" serially +DEBUG: rewriting table "comment_test" +DEBUG: building index "comment_test_pk" on table "comment_test" serially +DEBUG: building index "comment_test_index" on table "comment_test" serially ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int; ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; +DEBUG: building index "pg_toast_71030_index" on table "pg_toast_71030" serially +DEBUG: rewriting table "comment_test" +DEBUG: building index "comment_test_index" on table "comment_test" serially +DEBUG: building index "comment_test_pk" on table "comment_test" serially ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int; +DEBUG: verifying table "comment_test" ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint; +DEBUG: building index "pg_toast_71040_index" on table "pg_toast_71040" serially +DEBUG: rewriting table "comment_test" +DEBUG: building index "comment_test_index" on table "comment_test" serially +DEBUG: building index "comment_test_pk" on table "comment_test" serially -- Check that the comments are intact. SELECT col_description('comment_test'::regclass, 1) as comment; comment @@ -3174,7 +3495,9 @@ -- to an error and would reduce the test scope. CREATE TABLE comment_test_child ( id text CONSTRAINT comment_test_child_fk REFERENCES comment_test); +DEBUG: building index "pg_toast_71048_index" on table "pg_toast_71048" serially CREATE INDEX comment_test_child_fk ON comment_test_child(id); +DEBUG: building index "comment_test_child_fk" on table "comment_test_child" serially 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'; @@ -3213,6 +3536,9 @@ FROM pg_class, pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid WHERE relkind IN ('r', 'i', 'S', 't', 'm') AND mapped_oid IS DISTINCT FROM oid; +DEBUG: rehashing catalog cache id 50 for pg_class; 257 tups, 128 buckets +DEBUG: rehashing catalog cache id 50 for pg_class; 513 tups, 256 buckets +DEBUG: rehashing catalog cache id 50 for pg_class; 1025 tups, 512 buckets 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 @@ -3237,6 +3563,10 @@ 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); +DEBUG: CREATE TABLE will create implicit sequence "new_system_table_id_seq" for serial column "new_system_table.id" +DEBUG: building index "pg_toast_71075_index" on table "pg_toast_71075" serially +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "new_system_table_pkey" for table "new_system_table" +DEBUG: building index "new_system_table_pkey" on table "new_system_table" serially 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; @@ -3244,15 +3574,23 @@ 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); +DEBUG: building index "old_system_table__othercol" on table "old_system_table" serially 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; +DEBUG: building index "new_system_table_pkey" on table "old_system_table" serially +DEBUG: building index "old_system_table__othercol" on table "old_system_table" serially +DEBUG: building index "pg_toast_71075_index" on table "pg_toast_71075" serially 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); +DEBUG: CREATE TABLE will create implicit sequence "unlogged1_f1_seq" for serial column "unlogged1.f1" +DEBUG: building index "pg_toast_71094_index" on table "pg_toast_71094" serially +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "unlogged1_pkey" for table "unlogged1" +DEBUG: building index "unlogged1_pkey" on table "unlogged1" serially -- check relpersistence of an unlogged table SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1' UNION ALL @@ -3270,11 +3608,22 @@ (5 rows) CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key +DEBUG: CREATE TABLE will create implicit sequence "unlogged2_f1_seq" for serial column "unlogged2.f1" +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "unlogged2_pkey" for table "unlogged2" +DEBUG: building index "unlogged2_pkey" on table "unlogged2" serially CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key +DEBUG: CREATE TABLE will create implicit sequence "unlogged3_f1_seq" for serial column "unlogged3.f1" +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "unlogged3_pkey" for table "unlogged3" +DEBUG: building index "unlogged3_pkey" on table "unlogged3" serially ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key +DEBUG: rewriting table "unlogged3" +DEBUG: building index "unlogged3_pkey" on table "unlogged3" serially 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; +DEBUG: building index "pg_toast_71136_index" on table "pg_toast_71136" serially +DEBUG: rewriting table "unlogged1" +DEBUG: building index "unlogged1_pkey" on table "unlogged1" serially -- check relpersistence of an unlogged table after changing to permanent SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1' UNION ALL @@ -3293,10 +3642,15 @@ ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing DROP TABLE unlogged3; +DEBUG: rehashing catalog cache id 74 for pg_type; 257 tups, 128 buckets DROP TABLE unlogged2; DROP TABLE unlogged1; -- set unlogged CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT); +DEBUG: CREATE TABLE will create implicit sequence "logged1_f1_seq" for serial column "logged1.f1" +DEBUG: building index "pg_toast_71155_index" on table "pg_toast_71155" serially +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "logged1_pkey" for table "logged1" +DEBUG: building index "logged1_pkey" on table "logged1" serially -- check relpersistence of a permanent table SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1' UNION ALL @@ -3314,12 +3668,25 @@ (5 rows) CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key +DEBUG: CREATE TABLE will create implicit sequence "logged2_f1_seq" for serial column "logged2.f1" +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "logged2_pkey" for table "logged2" +DEBUG: building index "logged2_pkey" on table "logged2" serially CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key +DEBUG: CREATE TABLE will create implicit sequence "logged3_f1_seq" for serial column "logged3.f1" +DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "logged3_pkey" for table "logged3" +DEBUG: building index "logged3_pkey" on table "logged3" serially 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 +DEBUG: rewriting table "logged3" +DEBUG: building index "logged3_pkey" on table "logged3" serially ALTER TABLE logged2 SET UNLOGGED; +DEBUG: rewriting table "logged2" +DEBUG: building index "logged2_pkey" on table "logged2" serially ALTER TABLE logged1 SET UNLOGGED; +DEBUG: building index "pg_toast_71204_index" on table "pg_toast_71204" serially +DEBUG: rewriting table "logged1" +DEBUG: building index "logged1_pkey" on table "logged1" serially -- check relpersistence of a permanent table after changing to unlogged SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1' UNION ALL @@ -3501,6 +3868,7 @@ CREATE TABLE owned_by_me ( a int ) PARTITION BY LIST (a); +DEBUG: rehashing catalog cache id 22 for pg_default_acl; 17 tups, 8 buckets 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; @@ -3548,6 +3916,7 @@ 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"; +DEBUG: rewriting table "fail_part" 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; @@ -3560,6 +3929,7 @@ 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); +DEBUG: verifying table "fail_part" 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; @@ -3570,6 +3940,7 @@ CONSTRAINT check_a CHECK (a > 0) ); ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1); +DEBUG: verifying table "part_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 @@ -3592,6 +3963,7 @@ -- 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; +DEBUG: verifying table "def_part" -- check attaching default partition fails if a default partition already -- exists CREATE TABLE fail_def_part (LIKE part_1 INCLUDING CONSTRAINTS); @@ -3606,19 +3978,25 @@ 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); +DEBUG: verifying table "part_2" ERROR: partition constraint 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); +DEBUG: verifying table "part_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); +DEBUG: verifying table "part_3" +DEBUG: verifying table "list_parted2_def" ERROR: updated partition constraint for default partition 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); +DEBUG: verifying table "part_3" +DEBUG: verifying table "list_parted2_def" -- adding constraints that describe the desired partition constraint -- (or more restrictive) will help skip the validation scan CREATE TABLE part_3_4 ( @@ -3629,13 +4007,18 @@ -- 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); +DEBUG: verifying table "part_3_4" +DEBUG: verifying table "list_parted2_def" -- 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; +DEBUG: verifying table "part_3_4" ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); INFO: partition constraint for table "part_3_4" is implied by existing constraints +DEBUG: verifying table "list_parted2_def" -- check if default partition scan skipped ALTER TABLE list_parted2_def ADD CONSTRAINT check_a CHECK (a IN (5, 6)); +DEBUG: verifying table "list_parted2_def" CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66); INFO: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints -- check validation when attaching range partitions @@ -3651,10 +4034,12 @@ 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); +DEBUG: verifying table "part1" ERROR: partition constraint 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); +DEBUG: verifying table "part1" -- adding constraints that describe the desired partition constraint -- (or more restrictive) will help skip the validation scan CREATE TABLE part2 ( @@ -3673,9 +4058,13 @@ 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); +DEBUG: verifying table "part3" +DEBUG: verifying table "partr_def1" ERROR: updated partition constraint for default partition 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); +DEBUG: verifying table "part3" +DEBUG: verifying table "partr_def1" -- check that leaf partitions are scanned when attaching a partitioned -- table CREATE TABLE part_5 ( @@ -3685,18 +4074,23 @@ 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); +DEBUG: verifying table "part_5_a" ERROR: partition constraint 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); +DEBUG: verifying table "part_5_a" ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); INFO: partition constraint for table "part_5" is implied by existing constraints +DEBUG: verifying table "list_parted2_def" 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; +DEBUG: verifying table "part_5_a" ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); INFO: partition constraint for table "part_5" is implied by existing constraints +DEBUG: verifying table "list_parted2_def" -- 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. @@ -3708,6 +4102,7 @@ ALTER TABLE part_6 DROP c; ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6); INFO: partition constraint for table "part_6" is implied by existing constraints +DEBUG: verifying table "list_parted2_def" -- Similar to above, but the table being attached is a partitioned table -- whose partition has still different attnos for the root partitioning -- columns. @@ -3743,6 +4138,7 @@ ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7); INFO: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints +DEBUG: verifying table "part_7_a_null" ERROR: partition constraint is violated by some row -- check that leaf partitions of default partition are scanned when -- attaching a partitioned table. @@ -3752,10 +4148,14 @@ 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'); +DEBUG: verifying table "part5_p1" +DEBUG: verifying table "part5_def_p1" ERROR: updated partition constraint for default partition 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'); +DEBUG: verifying table "part5_p1" +DEBUG: verifying table "part5_def_p1" -- 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 @@ -3775,16 +4175,25 @@ CREATE TABLE quuux_default1 PARTITION OF quuux_default ( CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1) ) FOR VALUES IN ('b'); +DEBUG: building index "pg_toast_71391_index" on table "pg_toast_71391" serially CREATE TABLE quuux1 (a int, b text); +DEBUG: building index "pg_toast_71398_index" on table "pg_toast_71398" serially ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate! +DEBUG: rehashing catalog cache id 49 for pg_class; 513 tups, 256 buckets +DEBUG: verifying table "quuux1" +DEBUG: verifying table "quuux_default1" CREATE TABLE quuux2 (a int, b text); +DEBUG: building index "pg_toast_71404_index" on table "pg_toast_71404" serially ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints +DEBUG: verifying table "quuux2" DROP TABLE quuux1, quuux2; -- should validate for quuux1, but not for quuux2 CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1); +DEBUG: building index "pg_toast_71410_index" on table "pg_toast_71410" serially CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2); INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints +DEBUG: building index "pg_toast_71416_index" on table "pg_toast_71416" serially DROP TABLE quuux; -- check validation when attaching hash partitions -- Use hand-rolled hash functions and operator class to get predictable result @@ -3806,10 +4215,12 @@ 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); +DEBUG: verifying table "hpart_2" ERROR: partition constraint 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); +DEBUG: verifying table "hpart_2" -- check that leaf partitions are scanned when attaching a partitioned -- table CREATE TABLE hpart_5 ( @@ -3819,10 +4230,12 @@ 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); +DEBUG: verifying table "hpart_5_a" ERROR: partition constraint 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); +DEBUG: verifying table "hpart_5_a" -- 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); @@ -3910,10 +4323,26 @@ ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); ERROR: constraint must be added to child tables too ALTER TABLE list_parted2 ALTER b SET NOT NULL; +DEBUG: verifying table "part_2" +DEBUG: verifying table "list_parted2_def" +DEBUG: verifying table "part_3" +DEBUG: verifying table "part_55_66" +DEBUG: verifying table "part_6" +DEBUG: verifying table "part_5_a" +DEBUG: verifying table "part5_p1" +DEBUG: verifying table "part5_def_p1" ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL; ERROR: cannot remove constraint from only the partitioned table when partitions exist HINT: Do not specify the ONLY keyword. ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); +DEBUG: verifying table "part_2" +DEBUG: verifying table "list_parted2_def" +DEBUG: verifying table "part_3" +DEBUG: verifying table "part_55_66" +DEBUG: verifying table "part_5_a" +DEBUG: verifying table "part5_def_p1" +DEBUG: verifying table "part5_p1" +DEBUG: verifying table "part_6" ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b; ERROR: cannot remove constraint from only the partitioned table when partitions exist HINT: Do not specify the ONLY keyword. @@ -3926,6 +4355,14 @@ 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); +DEBUG: verifying table "part_2" +DEBUG: verifying table "list_parted2_def" +DEBUG: verifying table "part_3" +DEBUG: verifying table "part_55_66" +DEBUG: verifying table "part_6" +DEBUG: verifying table "part_5_a" +DEBUG: verifying table "part5_p1" +DEBUG: verifying table "part5_def_p1" 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; @@ -3967,6 +4404,7 @@ alter table p11 add a int; alter table p11 drop a; alter table p11 add a int not null; +DEBUG: verifying table "p11" -- attnum for key attribute 'a' is different in p, p1, and p11 select attrelid::regclass, attname, attnum from pg_attribute @@ -3983,9 +4421,11 @@ (3 rows) alter table p1 attach partition p11 for values from (2) to (5); +DEBUG: verifying table "p11" 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); +DEBUG: verifying table "p11" ERROR: partition constraint is violated by some row -- cleanup drop table p; @@ -4014,9 +4454,11 @@ -- 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; +DEBUG: verifying table "defpart_attach_test_d" ERROR: partition constraint is violated by some row delete from defpart_attach_test_d where a = 1; alter table defpart_attach_test_d add check (a > 1); +DEBUG: verifying table "defpart_attach_test_d" -- should be attached successfully and without needing to be scanned alter table defpart_attach_test attach partition defpart_attach_test_d default; INFO: partition constraint for table "defpart_attach_test_d" is implied by existing constraints @@ -4025,6 +4467,8 @@ -- 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); +DEBUG: verifying table "defpart_attach_test_2" +DEBUG: verifying table "defpart_attach_test_d" ERROR: updated partition constraint for default partition would be violated by some row drop table defpart_attach_test; -- check combinations of temporary and permanent relations when attaching @@ -4070,6 +4514,7 @@ 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); +DEBUG: verifying table "at_test_sql_partop_1" drop table at_test_sql_partop; drop operator class at_test_sql_partop using btree; drop function at_test_sql_partop;