On Thu, Sep 14, 2023 at 9:57 AM vignesh C <vignes...@gmail.com> wrote:
> 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. > > We are creating a constraint trigger for DEFERRED check constraint and as per implementation of FOREIGN table we are restricting to have a constraint trigger. I need to do more analysis before reaching to any conclusion, I think we can restrict this gram.y itself. > 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). > > Fixed in V3 patch. > 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. > > Fixed in V3 patch. -- Regards, Himanshu Upadhyaya EnterpriseDB: http://www.enterprisedb.com
