Thread: Change ereport level for QueuePartitionConstraintValidation
Hello Per discussion started here: https://www.postgresql.org/message-id/CA%2BTgmoZWSLUjVcc9KBSVvbn%3DU5QRgW1O-MgUX0y5CnLZOA2qyQ%40mail.gmail.com We have INFO ereport messages in alter table attach partition like this: > partition constraint for table \"%s\" is implied by existing constraints Personally I like this message and not want remove it. But recently my colleague noticed that INFO level is written to stderr by psql. For example, simple command > psql -c "alter table measurement attach partition measurement_y2006m04 for values from ('2006-04-01') to ('2006-05-01');" can produce stderr output like error, but this is expected behavior from successful execution. And INFO level always sent to client regardless of client_min_messages as clearly documented in src/include/utils/elog.h So now I am +1 to idea of change error level for this messages. I attach patch to lower such ereport to DEBUG1 level thanks PS: possible we can change level to NOTICE but I doubt we will choose this way regards, Sergei
Attachment
On Fri, Mar 15, 2019 at 12:55:36PM +0300, Sergei Kornilov wrote: > We have INFO ereport messages in alter table attach partition like this: > > partition constraint for table \"%s\" is implied by existing constraints > > So now I am +1 to idea of change error level for this messages. I attach patch to lower such ereport to DEBUG1 level +1 I reviewed existing logging behavior and now I agree. Also, I wondered if it was worth considering a way to configure logging which scales better than boolean GUCs: log_duration log_checkpoints log_(dis)connections log_lock_waits log_replication_commands ..plus a bunch more developer ones: https://www.postgresql.org/docs/current/runtime-config-developer.html I'm (very tentatively) thinking of a string GUC which is split on whitespace and is parsed into a bitmap which is consulted instead of the existing vars, as in: if (logging_bits & LOG_CHECKPOINTS) ... which could be either an enum or #define.. If there's an entry in logging_bits which isn't recognized, I guess it'd be logged at NOTICE or WARNING. I'd also request this be conditional promoted from DEBUG1 to LOG depending a new logging_bit for LOG_PARALLEL_WORKER: |starting background worker process "parallel worker for PID... When written to csvlog (and when using log_min_error_severity=notice or similar), that would help answering questions like: "what queries are my max_parallel_workers(_per_process) being used for (at the possible exclusion of other queries)?". I noticed on our servers that a query running possibly every ~10sec had been using parallel query, which not only hurt that query, but also meant that works may have been unavailable for report queries which could have benefited from their use. It'd be nice to know if there were other such issues. Justin
Hello This change is discussed as open item for pg12. Seems we have nor objections nor agreement. I attached updated version duemerge conflict. > Per discussion started here: https://www.postgresql.org/message-id/CA%2BTgmoZWSLUjVcc9KBSVvbn%3DU5QRgW1O-MgUX0y5CnLZOA2qyQ%40mail.gmail.com regards, Sergei
Attachment
On Tue, Jul 2, 2019 at 12:17 AM Sergei Kornilov <sk@zsrv.org> wrote: > This change is discussed as open item for pg12. Seems we have nor objections nor agreement. I attached updated versiondue merge conflict. > > > Per discussion started here: https://www.postgresql.org/message-id/CA%2BTgmoZWSLUjVcc9KBSVvbn%3DU5QRgW1O-MgUX0y5CnLZOA2qyQ%40mail.gmail.com I took the liberty of setting this to "Ready for Committer" to see if we can get a decision one way or another and clear both a Commitfest item and a PG12 Open Item. No committer is signed up, but it looks like Amit L wrote the messages in question, Robert committed them, and David made arguments for AND against on the referenced thread, so I'm CCing them, and retreating to a safe distance. -- Thomas Munro https://enterprisedb.com
On Mon, 15 Jul 2019 at 11:46, Thomas Munro <thomas.munro@gmail.com> wrote: > > On Tue, Jul 2, 2019 at 12:17 AM Sergei Kornilov <sk@zsrv.org> wrote: > > This change is discussed as open item for pg12. Seems we have nor objections nor agreement. I attached updated versiondue merge conflict. > > > > > Per discussion started here: https://www.postgresql.org/message-id/CA%2BTgmoZWSLUjVcc9KBSVvbn%3DU5QRgW1O-MgUX0y5CnLZOA2qyQ%40mail.gmail.com > > I took the liberty of setting this to "Ready for Committer" to see if > we can get a decision one way or another and clear both a Commitfest > item and a PG12 Open Item. No committer is signed up, but it looks > like Amit L wrote the messages in question, Robert committed them, and > David made arguments for AND against on the referenced thread, so I'm > CCing them, and retreating to a safe distance. I think the only argument against it was around lack of ability to test if the constraint was used to verify no row breaks the partition bound during the ATTACH PARTITION. Does anyone feel strongly that we need to the test to confirm that the constraint was used for this? If nobody feels so strongly about that then I say we can just push this. It seems something that's unlikely to get broken, but then you could probably say that for most things our tests test for. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Sun, Jul 14, 2019 at 7:46 PM Thomas Munro <thomas.munro@gmail.com> wrote: > ... and retreating to a safe distance. Is that measure in, like, light-years? I vote for changing it to NOTICE instead of DEBUG1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2019-Jul-15, David Rowley wrote: > I think the only argument against it was around lack of ability to > test if the constraint was used to verify no row breaks the partition > bound during the ATTACH PARTITION. Would it work to set client_min_messages to DEBUG1 for the duration of the test, or does that have too much unrelated noise? > Does anyone feel strongly that we need to the test to confirm that the > constraint was used for this? Well, IME if we don't test it, we're sure to break it in the future. The only questions are 1) when, 2) how long till we notice, 3) how difficult is it to fix at that point. I think breakage is easily noticed by users, and a fix is unlikely to require hard measures such as ABI breaks or catversion bumps. I'd like more than zero tests, but it doesn't seem *that* severe. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, 16 Jul 2019 at 03:13, Robert Haas <robertmhaas@gmail.com> wrote: > I vote for changing it to NOTICE instead of DEBUG1. Well, there are certainly other DDL commands that spit out NOTICES. postgres=# create table z (a int); CREATE TABLE postgres=# create table x (a int) inherits(z); NOTICE: merging column "a" with inherited definition CREATE TABLE However, we did get rid of a few of those a while back. In 9.2 we used to have: postgres=# create table a (a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" I'm pretty keen for consistency. Having ATTACH PARTITION spit out an INFO and merge attributes a NOTICE, and SET NOT NULL just a DEBUG1 is pretty far from consistent. I wouldn't object to making them all NOTICE. I've only seen complaints about the INFO one. Would anyone complain if we made them all INFO? If we do that should we backpatch the change into PG12. SET NOT NULL using a constraint was new there. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
David Rowley <david.rowley@2ndquadrant.com> writes:
> Would anyone complain if we made them all INFO?
That would be remarkably horrid, because that makes them unsuppressable.
I'm generally for having these be less in-your-face, not more so.
regards, tom lane
On Tue, Jul 16, 2019 at 10:15 AM David Rowley <david.rowley@2ndquadrant.com> wrote: > On Tue, 16 Jul 2019 at 03:13, Robert Haas <robertmhaas@gmail.com> wrote: > > I vote for changing it to NOTICE instead of DEBUG1. > > Well, there are certainly other DDL commands that spit out NOTICES. > > postgres=# create table z (a int); > CREATE TABLE > postgres=# create table x (a int) inherits(z); > NOTICE: merging column "a" with inherited definition > CREATE TABLE > > However, we did get rid of a few of those a while back. In 9.2 we used to have: > > postgres=# create table a (a int primary key); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "a_pkey" for table "a" > > I'm pretty keen for consistency. Having ATTACH PARTITION spit out an > INFO and merge attributes a NOTICE, and SET NOT NULL just a DEBUG1 is > pretty far from consistent. I wouldn't object to making them all > NOTICE. I've only seen complaints about the INFO one. Fwiw, I'm leaning toward NOTICE for all. It's helpful for users to know a certain action was taken. Thanks, Amit
Hello Here is two patches with NOTICE ereport: one for partitions operations and one for "set not null" (for consistency) regards, Sergei
Attachment
On 2019-Jul-15, David Rowley wrote: > I think the only argument against it was around lack of ability to > test if the constraint was used to verify no row breaks the partition > bound during the ATTACH PARTITION. Would it work to set client_min_messages to DEBUG1 for the duration of the test, or does that have too much unrelated noise? > Does anyone feel strongly that we need to the test to confirm that the > constraint was used for this? Well, IME if we don't test it, we're sure to break it in the future. The only questions are 1) when, 2) how long till we notice, 3) how difficult is it to fix at that point. I think breakage is easily noticed by users, and a fix is unlikely to require hard measures such as ABI breaks or catversion bumps. I'd like more than zero tests, but it doesn't seem *that* severe. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2019-Jul-15, David Rowley wrote:
>> I think the only argument against it was around lack of ability to
>> test if the constraint was used to verify no row breaks the partition
>> bound during the ATTACH PARTITION.
> Would it work to set client_min_messages to DEBUG1 for the duration of
> the test, or does that have too much unrelated noise?
It's not awful. I tried inserting "set client_min_messages = debug1"
into alter_table.sql, and got the attached diffs. Evidently we
could not keep it on throughout that test script, because of the
variable OIDs in some of the toast table names. But in the areas
where we're currently emitting INFO messages, we could have it on
and not have any other noise except some "verifying table" messages,
which actually seem like a good thing for this test.
So right at the moment my vote is to downgrade all of these to DEBUG1
and fix the test-coverage complaint by adjusting client_min_messages
as needed in the test scripts.
A potential objection is that this'd constrain people's ability to add
DEBUG1 messages in code reachable from ALTER TABLE --- but we can
cross that bridge when we come to it.
regards, tom lane
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+test2<test3*4);
+DEBUG: verifying table "atacc1"
-- should fail
insert into atacc1 (test,test2,test3) values (4,4,2);
ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
@@ -778,6 +843,7 @@
-- lets do some naming tests
create table atacc1 (test int check (test>3), 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;
Hi > It's not awful. I tried inserting "set client_min_messages = debug1" > into alter_table.sql We already did this in March. And this change was reverted in 5655565c077c53b6e9b4b9bfcdf96439cf3af065 because this willnot work on buildfarm animals with log_statement = 'all' regards, Sergei
Sergei Kornilov <sk@zsrv.org> writes:
>> It's not awful. I tried inserting "set client_min_messages = debug1"
>> into alter_table.sql
> We already did this in March. And this change was reverted in 5655565c077c53b6e9b4b9bfcdf96439cf3af065 because this
willnot work on buildfarm animals with log_statement = 'all'
Oh :-(.
Seems like maybe what we need is to transpose the tests at issue into
a TAP test? That could grep for the messages we care about and disregard
other ones.
regards, tom lane
On Thu, 18 Jul 2019 at 07:01, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Seems like maybe what we need is to transpose the tests at issue into > a TAP test? That could grep for the messages we care about and disregard > other ones. That seems like a good idea. I guess that's a vote in favour of having DEBUG1 for ATTACH PARTITION and SET NOT NULL too? I don't know my way around the tap tests that well, but I started to look at this and ended up a bit stuck on where the test should be located. I see src/test/modules/brin has some brin related tests, so I thought that src/test/modules/alter_table might be the spot, but after looking at src/test/README I see it mentions that only tests that are themselves an extension should be located within: modules/ Extensions used only or mainly for test purposes, generally not suitable for installing in production databases There are a few others in the same situation as brin; commit_ts, snapshot_too_old, unsafe_tests. I see unsafe_tests does mention the lack of module in the README file. Is there a better place to do the alter_table ones? Or are the above ones in there because there's no better place? Also, if I'm not wrong, the votes so far appear to be: NOTICE: Robert, Amit DEBUG1: Tom, Alvaro (I'm entirely basing this on the fact that they mentioned possible ways to test with DEBUG1) I'll be happy with DEBUG1 if we can get tests to test it. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 2019-Jul-23, David Rowley wrote: > Also, if I'm not wrong, the votes so far appear to be: > > NOTICE: Robert, Amit > DEBUG1: Tom, Alvaro (I'm entirely basing this on the fact that they > mentioned possible ways to test with DEBUG1) > > I'll be happy with DEBUG1 if we can get tests to test it. Well, I think the user doesn't *care* to see a message about the optimization. They just want the command to be fast. *We* (developers) want the message in order to ensure the command remains fast. So some DEBUG level seems the right thing. Another way to reach the same conclusion is to think about the "building index ... serially" messages, which are are pretty much in the same category and are using DEBUG1. (I do think the TOAST ones are just noise though, and since they disrupt potential testing with client_min_messages=debug1, another way to go about this is to reduce those to DEBUG2 or just elide them.) -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2019-Jul-23, David Rowley wrote: > I don't know my way around the tap tests that well, but I started to > look at this and ended up a bit stuck on where the test should be > located. I see src/test/modules/brin has some brin related tests, so > I thought that src/test/modules/alter_table might be the spot, but > after looking at src/test/README I see it mentions that only tests > that are themselves an extension should be located within: > > modules/ > Extensions used only or mainly for test purposes, generally not suitable > for installing in production databases > > There are a few others in the same situation as brin; commit_ts, > snapshot_too_old, unsafe_tests. I see unsafe_tests does mention the > lack of module in the README file. The readme in src/test/modules says "extensions or libraries", and I see no reason to think that a TAP test would be totally out of place there. I think the alter_table/ subdir is a perfect place. Sergei, can we enlist you to submit a patch for this? Namely reduce the log level to DEBUG1 and add a TAP test in src/test/modules/alter_table/ that verifies that the message is or isn't emitted, as appropriate. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi > Sergei, can we enlist you to submit a patch for this? Namely reduce the > log level to DEBUG1 and add a TAP test in src/test/modules/alter_table/ > that verifies that the message is or isn't emitted, as appropriate. Yes, will do. Probably in few days. regards, Sergei
Hello > Sergei, can we enlist you to submit a patch for this? Namely reduce the > log level to DEBUG1 and add a TAP test in src/test/modules/alter_table/ > that verifies that the message is or isn't emitted, as appropriate. I created this patch. I test message existence. Also I check message "verifying table" (generated on DEBUG1 from ATRewriteTable). So with manuallydamaged logic in NotNullImpliedByRelConstraints or ConstraintImpliedByRelConstraint "make check" may works but failson new test during "make check-world". As we want. regards, Sergei
Attachment
Hello I noticed appveyor build on windows is not happy: > perl buildsetup.pl > Could not determine contrib module type for alter_table > at buildsetup.pl line 38. But I have no idea why. I can't check on windows. Possible I miss some change while adding new module to tree. Will check.Please let me know if root of such error is known. https://ci.appveyor.com/project/postgresql-cfbot/postgresql/builds/26831382 https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.53294 regards, Sergei
Sergei Kornilov <sk@zsrv.org> writes: > I noticed appveyor build on windows is not happy: >> perl buildsetup.pl >> Could not determine contrib module type for alter_table >> at buildsetup.pl line 38. > But I have no idea why. I can't check on windows. Possible I miss some change while adding new module to tree. Will check.Please let me know if root of such error is known. > https://ci.appveyor.com/project/postgresql-cfbot/postgresql/builds/26831382 > https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.53294 grep is your friend: that message is coming out of Mkvcbuild.pm's AddContrib. (No idea why perl is fingering someplace else.) Apparently you need to have one of MODULE_big, MODULES, or PROGRAM defined, unless you add the module to @contrib_excludes to keep it from being built. regards, tom lane
Hello Thank you! It seems the most appropriate option for this test is to change @contrib_excludes Done in attached patch, will check appveyor reaction. regards, Sergei
Attachment
Sergei Kornilov <sk@zsrv.org> writes:
> Thank you! It seems the most appropriate option for this test is to change @contrib_excludes
> Done in attached patch, will check appveyor reaction.
Appveyor seems happy, so I took a look through this. There's little
to say about 0001: if we're going to drop the elevel, that's what
results. 0002 is slightly more interesting. I did not like calling
the module "alter_table": we should encourage any future similar needs
to add more tests in this same directory, not invent whole new ones.
I went with "test_misc", but that's still open for bikeshedding of course.
I did a minor amount of cleanup in the test script, including running
it through pgperltidy, but no substantive changes. Also added a README.
I think there are basically two objections that might be raised to
committing this:
1. Making this a src/test/modules/ subdirectory, when there is no
actual extension module in it, is a triumph of expediency over
good file-tree structure. If there were no other constraints
I'd want to call it src/test/misc/ or src/test/tap/ or something
like that. The expediency angle is that if we do that, the
buildfarm client script will need changes to know about it.
Is it better to go with the long-term view and accept that we
won't have full buildfarm coverage right away?
2. It seems kind of expensive and redundant to duplicate all these
test cases from the core tests. On my machine the new test script
took close to 2.5 seconds as-submitted. I was able to knock that
down to 2.1 by the expedient of combining adjacent psql invocations
that we didn't need to examine the results of. But it still is
adding a noticeable amount of time to check-world, which takes only
circa 100s overall (with parallelism). Should we think about
deleting some of these test cases from the core tests?
(An argument not to do so is that the test conditions are a bit
different: since the TAP test starts a new session for each
query, it fails to exercise carry-over of relcache entries,
which might possibly be interesting in this area.)
Or, of course, we could forget the whole thing and switch the output
level for these messages to NOTICE instead. I'm not for that, but
now that we see what it'll cost us to have them better hidden, we can
at least have an informed debate.
Thoughts?
regards, tom lane
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cceefbd..30f8d53 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -15402,11 +15402,11 @@ QueuePartitionConstraintValidation(List **wqueue, Relation scanrel,
if (PartConstraintImpliedByRelConstraint(scanrel, partConstraint))
{
if (!validate_default)
- ereport(INFO,
+ ereport(DEBUG1,
(errmsg("partition constraint for table \"%s\" is implied by existing constraints",
RelationGetRelationName(scanrel))));
else
- ereport(INFO,
+ ereport(DEBUG1,
(errmsg("updated partition constraint for default partition \"%s\" is implied by existing
constraints",
RelationGetRelationName(scanrel))));
return;
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 46d03f3..318d8ec 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -1253,7 +1253,7 @@ check_default_partition_contents(Relation parent, Relation default_rel,
*/
if (PartConstraintImpliedByRelConstraint(default_rel, def_part_constraints))
{
- ereport(INFO,
+ ereport(DEBUG1,
(errmsg("updated partition constraint for default partition \"%s\" is implied by existing
constraints",
RelationGetRelationName(default_rel))));
return;
@@ -1304,7 +1304,7 @@ check_default_partition_contents(Relation parent, Relation default_rel,
if (PartConstraintImpliedByRelConstraint(part_rel,
def_part_constraints))
{
- ereport(INFO,
+ ereport(DEBUG1,
(errmsg("updated partition constraint for default partition \"%s\" is implied by existing
constraints",
RelationGetRelationName(part_rel))));
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index e5407bb..23d4265 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3633,11 +3633,9 @@ ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
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);
-INFO: partition constraint for table "part_3_4" is implied by existing constraints
-- 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);
-INFO: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints
-- check validation when attaching range partitions
CREATE TABLE range_parted (
a int,
@@ -3662,7 +3660,6 @@ CREATE TABLE part2 (
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);
-INFO: partition constraint for table "part2" is implied by existing constraints
-- Create default partition
CREATE TABLE partr_def1 PARTITION OF range_parted DEFAULT;
-- Only one default partition is allowed, hence, following should give error
@@ -3690,13 +3687,11 @@ ERROR: partition constraint is violated by some row
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);
-INFO: partition constraint for table "part_5" is implied by existing constraints
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);
-INFO: partition constraint for table "part_5" is implied by existing constraints
-- 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.
@@ -3707,7 +3702,6 @@ CREATE TABLE part_6 (
);
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
-- Similar to above, but the table being attached is a partitioned table
-- whose partition has still different attnos for the root partitioning
-- columns.
@@ -3725,10 +3719,7 @@ CREATE TABLE part_7_a_null (
);
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);
-INFO: partition constraint for table "part_7_a_null" is implied by existing constraints
ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
-INFO: partition constraint for table "part_7" is implied by existing constraints
-INFO: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints
-- Same example, but check this time that the constraint correctly detects
-- violating rows
ALTER TABLE list_parted2 DETACH PARTITION part_7;
@@ -3742,7 +3733,6 @@ SELECT tableoid::regclass, a, b FROM part_7 order by a;
(2 rows)
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
ERROR: partition constraint is violated by some row
-- check that leaf partitions of default partition are scanned when
-- attaching a partitioned table.
@@ -3779,12 +3769,10 @@ 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
-INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints
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);
-INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints
DROP TABLE quuux;
-- check validation when attaching hash partitions
-- Use hand-rolled hash functions and operator class to get predictable result
@@ -4019,7 +4007,6 @@ 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;
-INFO: partition constraint for table "defpart_attach_test_d" is implied by existing constraints
-- 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
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 3c30271..ce2484f 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1163,7 +1163,6 @@ alter table defcheck_def drop c;
alter table defcheck attach partition defcheck_def default;
alter table defcheck_def add check (b <= 0 and b is not null);
create table defcheck_1 partition of defcheck for values in (1, null);
-INFO: updated partition constraint for default partition "defcheck_def" is implied by existing constraints
-- test that complex default partition constraints are enforced correctly
insert into defcheck_def values (0, 0);
create table defcheck_0 partition of defcheck for values in (0);
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 953905d..0e4e53d 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -14,6 +14,7 @@ SUBDIRS = \
test_extensions \
test_ginpostinglist \
test_integerset \
+ test_misc \
test_parser \
test_pg_dump \
test_predtest \
diff --git a/src/test/modules/test_misc/.gitignore b/src/test/modules/test_misc/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/src/test/modules/test_misc/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/test_misc/Makefile b/src/test/modules/test_misc/Makefile
new file mode 100644
index 0000000..39c6c20
--- /dev/null
+++ b/src/test/modules/test_misc/Makefile
@@ -0,0 +1,14 @@
+# src/test/modules/test_misc/Makefile
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/test_misc
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/test_misc/README b/src/test/modules/test_misc/README
new file mode 100644
index 0000000..4876733
--- /dev/null
+++ b/src/test/modules/test_misc/README
@@ -0,0 +1,4 @@
+This directory doesn't actually contain any extension module.
+
+What it is is a home for otherwise-unclassified TAP tests that exercise core
+server features. We might equally well have called it, say, src/test/misc.
diff --git a/src/test/modules/test_misc/t/001_constraint_validation.pl
b/src/test/modules/test_misc/t/001_constraint_validation.pl
new file mode 100644
index 0000000..f762bc2
--- /dev/null
+++ b/src/test/modules/test_misc/t/001_constraint_validation.pl
@@ -0,0 +1,310 @@
+# Verify that ALTER TABLE optimizes certain operations as expected
+
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More tests => 42;
+
+# Initialize a test cluster
+my $node = get_new_node('master');
+$node->init();
+# Turn message level up to DEBUG1 so that we get the messages we want to see
+$node->append_conf('postgresql.conf', 'client_min_messages = DEBUG1');
+$node->start;
+
+# Run a SQL command and return psql's stderr (including debug messages)
+sub run_sql_command
+{
+ my $sql = shift;
+ my $stderr;
+
+ $node->psql(
+ 'postgres',
+ $sql,
+ stderr => \$stderr,
+ on_error_die => 1,
+ on_error_stop => 1);
+ return $stderr;
+}
+
+# Check whether result of run_sql_command shows that we did a verify pass
+sub is_table_verified
+{
+ my $output = shift;
+ return index($output, 'DEBUG: verifying table') != -1;
+}
+
+my $output;
+
+note "test alter table set not null";
+
+run_sql_command(
+ 'create table atacc1 (test_a int, test_b int);
+ insert into atacc1 values (1, 2);');
+
+$output = run_sql_command('alter table atacc1 alter test_a set not null;');
+ok(is_table_verified($output),
+ 'column test_a without constraint will scan table');
+
+run_sql_command(
+ 'alter table atacc1 alter test_a drop not null;
+ alter table atacc1 add constraint atacc1_constr_a_valid
+ check(test_a is not null);');
+
+# normal run will verify table data
+$output = run_sql_command('alter table atacc1 alter test_a set not null;');
+ok(!is_table_verified($output), 'with constraint will not scan table');
+ok( $output =~
+ m/existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls/,
+ 'test_a proved by constraints');
+
+run_sql_command('alter table atacc1 alter test_a drop not null;');
+
+# we have check only for test_a column, so we need verify table for test_b
+$output = run_sql_command(
+ 'alter table atacc1 alter test_b set not null, alter test_a set not null;'
+);
+ok(is_table_verified($output), 'table was scanned');
+# we may miss debug message for test_a constraint because we need verify table due test_b
+ok( !( $output =~
+ m/existing constraints on column "atacc1"."test_b" are sufficient to prove that it does not contain nulls/
+ ),
+ 'test_b not proved by wrong constraints');
+run_sql_command(
+ 'alter table atacc1 alter test_a drop not null, alter test_b drop not null;'
+);
+
+# test with both columns having check constraints
+run_sql_command(
+ 'alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);'
+);
+$output = run_sql_command(
+ 'alter table atacc1 alter test_b set not null, alter test_a set not null;'
+);
+ok(!is_table_verified($output), 'table was not scanned for both columns');
+ok( $output =~
+ m/existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls/,
+ 'test_a proved by constraints');
+ok( $output =~
+ m/existing constraints on column "atacc1"."test_b" are sufficient to prove that it does not contain nulls/,
+ 'test_b proved by constraints');
+run_sql_command('drop table atacc1;');
+
+note "test alter table attach partition";
+
+run_sql_command(
+ 'CREATE TABLE list_parted2 (
+ a int,
+ b char
+ ) PARTITION BY LIST (a);
+ CREATE TABLE part_3_4 (
+ LIKE list_parted2,
+ CONSTRAINT check_a CHECK (a IN (3)));');
+
+# need NOT NULL to skip table scan
+$output = run_sql_command(
+ 'ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);'
+);
+ok(is_table_verified($output), 'table part_3_4 scanned');
+
+run_sql_command(
+ 'ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
+ ALTER TABLE part_3_4 ALTER a SET NOT NULL;');
+
+$output = run_sql_command(
+ 'ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);'
+);
+ok(!is_table_verified($output), 'table part_3_4 not scanned');
+ok( $output =~
+ m/partition constraint for table "part_3_4" is implied by existing constraints/,
+ 'part_3_4 verified by existing constraints');
+
+# test attach default partition
+run_sql_command(
+ 'CREATE TABLE list_parted2_def (
+ LIKE list_parted2,
+ CONSTRAINT check_a CHECK (a IN (5, 6)));');
+$output = run_sql_command(
+ 'ALTER TABLE list_parted2 ATTACH PARTITION list_parted2_def default;');
+ok(!is_table_verified($output), 'table list_parted2_def not scanned');
+ok( $output =~
+ m/partition constraint for table "list_parted2_def" is implied by existing constraints/,
+ 'list_parted2_def verified by existing constraints');
+
+$output = run_sql_command(
+ 'CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66);'
+);
+ok(!is_table_verified($output), 'table list_parted2_def not scanned');
+ok( $output =~
+ m/updated partition constraint for default partition "list_parted2_def" is implied by existing constraints/,
+ 'updated partition constraint for default partition list_parted2_def');
+
+# test attach another partitioned table
+run_sql_command(
+ 'CREATE TABLE part_5 (
+ LIKE list_parted2
+ ) PARTITION BY LIST (b);
+ CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN (\'a\');
+ ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5);'
+);
+$output = run_sql_command(
+ 'ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);');
+ok(!($output =~ m/verifying table "part_5"/), 'table part_5 not scanned');
+ok($output =~ m/verifying table "list_parted2_def"/,
+ 'list_parted2_def scanned');
+ok( $output =~
+ m/partition constraint for table "part_5" is implied by existing constraints/,
+ 'part_5 verified by existing constraints');
+
+run_sql_command(
+ '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
+run_sql_command(
+ 'ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)),
+ ALTER a SET NOT NULL;'
+);
+$output = run_sql_command(
+ 'ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);');
+ok(!($output =~ m/verifying table "part_5"/), 'table part_5 not scanned');
+ok($output =~ m/verifying table "list_parted2_def"/,
+ 'list_parted2_def scanned');
+ok( $output =~
+ m/partition constraint for table "part_5" is implied by existing constraints/,
+ 'part_5 verified by existing constraints');
+
+# 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.
+run_sql_command(
+ '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;');
+$output = run_sql_command(
+ 'ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6);');
+ok(!($output =~ m/verifying table "part_6"/), 'table part_6 not scanned');
+ok($output =~ m/verifying table "list_parted2_def"/,
+ 'list_parted2_def scanned');
+ok( $output =~
+ m/partition constraint for table "part_6" is implied by existing constraints/,
+ 'part_6 verified by existing constraints');
+
+# Similar to above, but the table being attached is a partitioned table
+# whose partition has still different attnos for the root partitioning
+# columns.
+run_sql_command(
+ '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;');
+
+$output = run_sql_command(
+ 'ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN (\'a\', null);'
+);
+ok(!is_table_verified($output), 'table not scanned');
+ok( $output =~
+ m/partition constraint for table "part_7_a_null" is implied by existing constraints/,
+ 'part_7_a_null verified by existing constraints');
+$output = run_sql_command(
+ 'ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);');
+ok(!is_table_verified($output), 'tables not scanned');
+ok( $output =~
+ m/partition constraint for table "part_7" is implied by existing constraints/,
+ 'part_7 verified by existing constraints');
+ok( $output =~
+ m/updated partition constraint for default partition "list_parted2_def" is implied by existing constraints/,
+ 'updated partition constraint for default partition list_parted2_def');
+
+run_sql_command(
+ 'CREATE TABLE range_parted (
+ a int,
+ b int
+ ) PARTITION BY RANGE (a, b);
+ CREATE TABLE range_part1 (
+ a int NOT NULL CHECK (a = 1),
+ b int NOT NULL);');
+
+$output = run_sql_command(
+ 'ALTER TABLE range_parted ATTACH PARTITION range_part1 FOR VALUES FROM (1, 1) TO (1, 10);'
+);
+ok(is_table_verified($output), 'table range_part1 scanned');
+ok( !( $output =~
+ m/partition constraint for table "range_part1" is implied by existing constraints/
+ ),
+ 'range_part1 not verified by existing constraints');
+
+run_sql_command(
+ 'CREATE TABLE range_part2 (
+ a int NOT NULL CHECK (a = 1),
+ b int NOT NULL CHECK (b >= 10 and b < 18)
+);');
+$output = run_sql_command(
+ 'ALTER TABLE range_parted ATTACH PARTITION range_part2 FOR VALUES FROM (1, 10) TO (1, 20);'
+);
+ok(!is_table_verified($output), 'table range_part2 not scanned');
+ok( $output =~
+ m/partition constraint for table "range_part2" is implied by existing constraints/,
+ 'range_part2 verified by existing constraints');
+
+# 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.
+run_sql_command(
+ '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);');
+
+$output = run_sql_command(
+ 'ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1);');
+ok(is_table_verified($output), 'quuux1 table scanned');
+ok( !( $output =~
+ m/partition constraint for table "quuux1" is implied by existing constraints/
+ ),
+ 'quuux1 verified by existing constraints');
+
+run_sql_command('CREATE TABLE quuux2 (a int, b text);');
+$output = run_sql_command(
+ 'ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2);');
+ok(!($output =~ m/verifying table "quuux_default1"/),
+ 'quuux_default1 not scanned');
+ok($output =~ m/verifying table "quuux2"/, 'quuux2 scanned');
+ok( $output =~
+ m/updated partition constraint for default partition "quuux_default1" is implied by existing constraints/,
+ 'updated partition constraint for default partition quuux_default1');
+run_sql_command('DROP TABLE quuux1, quuux2;');
+
+# should validate for quuux1, but not for quuux2
+$output = run_sql_command(
+ 'CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1);');
+ok(!is_table_verified($output), 'tables not scanned');
+ok( !( $output =~
+ m/partition constraint for table "quuux1" is implied by existing constraints/
+ ),
+ 'quuux1 verified by existing constraints');
+$output = run_sql_command(
+ 'CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);');
+ok(!is_table_verified($output), 'tables not scanned');
+ok( $output =~
+ m/updated partition constraint for default partition "quuux_default1" is implied by existing constraints/,
+ 'updated partition constraint for default partition quuux_default1');
+run_sql_command('DROP TABLE quuux;');
+
+$node->stop('fast');
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index 2eab635..a09263d 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -47,8 +47,9 @@ my @contrib_excludes = (
'jsonb_plperl', 'jsonb_plpython',
'ltree_plpython', 'pgcrypto',
'sepgsql', 'brin',
- 'test_extensions', 'test_pg_dump',
- 'snapshot_too_old', 'unsafe_tests');
+ 'test_extensions', 'test_misc',
+ 'test_pg_dump', 'snapshot_too_old',
+ 'unsafe_tests');
# Set of variables for frontend modules
my $frontend_defines = { 'initdb' => 'FRONTEND' };
I wrote:
> Or, of course, we could forget the whole thing and switch the output
> level for these messages to NOTICE instead. I'm not for that, but
> now that we see what it'll cost us to have them better hidden, we can
> at least have an informed debate.
> Thoughts?
Hearing no comments, I've pushed that patch, and marked the v12
open item closed.
regards, tom lane
Re: Change ereport level for QueuePartitionConstraintValidation
From
Alvaro Herrera from 2ndQuadrant
Date:
On 2019-Sep-07, Tom Lane wrote: > I wrote: > > Or, of course, we could forget the whole thing and switch the output > > level for these messages to NOTICE instead. I'm not for that, but > > now that we see what it'll cost us to have them better hidden, we can > > at least have an informed debate. > > Thoughts? > > Hearing no comments, I've pushed that patch, and marked the v12 > open item closed. I've marked https://commitfest.postgresql.org/24/2076/ committed also. Thanks, -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera from 2ndQuadrant <alvherre@alvh.no-ip.org> writes: > I've marked https://commitfest.postgresql.org/24/2076/ committed also. Yeah, I just remembered about doing that, and saw you'd beat me to it. regards, tom lane
Hello > Hearing no comments, I've pushed that patch, and marked the v12 > open item closed. Thank you! regards, Sergei