Re: CHECK Constraint Deferrable - Mailing list pgsql-hackers
From | vignesh C |
---|---|
Subject | Re: CHECK Constraint Deferrable |
Date | |
Msg-id | CALDaNm18D0uoKzkyL+Db5gcKKcmkRfiBDuny_2tXsnnEjpD+-g@mail.gmail.com Whole thread Raw |
In response to | Re: CHECK Constraint Deferrable (Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>) |
Responses |
Re: CHECK Constraint Deferrable
Re: CHECK Constraint Deferrable |
List | pgsql-hackers |
On Thu, 7 Sept 2023 at 17:26, Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com> wrote: > > Attached is v2 of the patch, rebased against the latest HEAD. Few issues: 1) Create domain fails but alter domain is successful, I feel we should support create domain too: postgres=# create domain d1 as int check(value<>0) deferrable; ERROR: specifying constraint deferrability not supported for domains postgres=# create domain d1 as int check(value<>0); CREATE DOMAIN postgres=# alter domain d1 add constraint con_2 check(value<>1) deferrable; ALTER DOMAIN 2) I was not sure, if the error message change was intentional: 2a) In Head: CREATE FOREIGN TABLE t9(a int CHECK(a<>0) DEFERRABLE) SERVER s1; ERROR: misplaced DEFERRABLE clause LINE 1: CREATE FOREIGN TABLE t9(a int CHECK(a<>0) DEFERRABLE) SERVER... ^ postgres=# CREATE FOREIGN TABLE t9(a int CHECK(a<>0) DEFERRABLE) SERVER s1; ERROR: "t9" is a foreign table DETAIL: Foreign tables cannot have constraint triggers. 2b) In Head: postgres=# CREATE FOREIGN TABLE t2(a int CHECK(a<>0)) SERVER s1; CREATE FOREIGN TABLE postgres=# ALTER FOREIGN TABLE t2 ADD CONSTRAINT t2_chk_1 CHECK(a<>1) DEFERRABLE; ERROR: CHECK constraints cannot be marked DEFERRABLE With patch: postgres=# ALTER FOREIGN TABLE t8 ADD CONSTRAINT t8_chk_1 CHECK(a<>1) DEFERRABLE; ERROR: "t8" is a foreign table DETAIL: Foreign tables cannot have constraint triggers. 3) Insert check is not deferred to commit: This insert check here is deferred to commit: postgres=# CREATE TABLE tbl (i int ) partition by range (i); CREATE TABLE tbl_1 PARTITION OF tbl FOR VALUES FROM (0) TO (10); CREATE TABLE tbl_2 PARTITION OF tbl FOR VALUES FROM (20) TO (30); CREATE TABLE CREATE TABLE CREATE TABLE postgres=# ALTER TABLE tbl ADD CONSTRAINT tbl_chk_1 CHECK(i<>1) DEFERRABLE; ALTER TABLE postgres=# begin; BEGIN postgres=*# SET CONSTRAINTS tbl_chk_1 DEFERRED; SET CONSTRAINTS postgres=*# INSERT INTO tbl values (1); INSERT 0 1 postgres=*# commit; ERROR: new row for relation "tbl_1" violates check constraint "tbl_chk_1" DETAIL: Failing row contains (1). But the check here is not deferred to commit: postgres=# CREATE TABLE tbl (i int check(i<>0) DEFERRABLE) partition by range (i); CREATE TABLE tbl_1 PARTITION OF tbl FOR VALUES FROM (0) TO (10); CREATE TABLE tbl_2 PARTITION OF tbl FOR VALUES FROM (20) TO (30); CREATE TABLE CREATE TABLE CREATE TABLE postgres=# ALTER TABLE tbl ADD CONSTRAINT tbl_chk_1 CHECK(i<>1) DEFERRABLE; ALTER TABLE postgres=# begin; BEGIN postgres=*# SET CONSTRAINTS tbl_chk_1 DEFERRED; SET CONSTRAINTS postgres=*# INSERT INTO tbl values (1); ERROR: new row for relation "tbl_1" violates check constraint "tbl_chk_1" DETAIL: Failing row contains (1). 4) There is a new warning popping up now: CREATE TABLE tbl_new_3 (i int check(i<>0)) partition by range (i); CREATE FOREIGN TABLE ftbl_new_3 PARTITION OF tbl_new_3 FOR VALUES FROM (40) TO (50) server s1; postgres=# ALTER TABLE tbl_new_3 ADD CONSTRAINT tbl_new_3_chk CHECK(i<>1) DEFERRABLE; WARNING: unexpected pg_constraint record found for relation "tbl_new_3" ERROR: "ftbl_new_3" is a foreign table DETAIL: Foreign tables cannot have constraint triggers. Regards, Vignesh
pgsql-hackers by date: