On Thu, Sep 14, 2023 at 9:57 AM vignesh C <vignes...@gmail.com> wrote:
> 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). > > I dont think it's a problem, in the second case there are two DEFERRABLE CHECK constraints and you are marking one as DEFERRED but other one will be INITIALLY IMMEDIATE. so we can use "SET CONSTRAINTS ALL DEFERRED;". ‘postgres[1271421]=#’CREATE TABLE tbl (i int check(i<>0) DEFERRABLE) partition ‘...>’by range (i); CREATE TABLE ‘postgres[1271421]=#’CREATE TABLE tbl_1 PARTITION OF tbl FOR VALUES FROM (0) TO (10); CREATE TABLE ‘postgres[1271421]=#’CREATE TABLE tbl_2 PARTITION OF tbl FOR VALUES FROM (20) TO (30); CREATE TABLE ‘postgres[1271421]=#’ALTER TABLE tbl ADD CONSTRAINT tbl_chk_1 CHECK(i<>1) DEFERRABLE; ALTER TABLE ‘postgres[1271421]=#’\d tbl Partitioned table "public.tbl" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- i | integer | | | Partition key: RANGE (i) Check constraints: "tbl_chk_1" CHECK (i <> 1) DEFERRABLE "tbl_i_check" CHECK (i <> 0) DEFERRABLE Number of partitions: 2 (Use \d+ to list them.) ‘postgres[1271421]=#’begin; BEGIN ‘postgres[1271421]=#*’SET CONSTRAINTS ALL DEFERRED; SET CONSTRAINTS ‘postgres[1271421]=#*’INSERT INTO tbl values (1); INSERT 0 1 ‘postgres[1271421]=#*’commit; ERROR: 23514: new row for relation "tbl_1" violates check constraint "tbl_chk_1" DETAIL: Failing row contains (1). SCHEMA NAME: public TABLE NAME: tbl_1 CONSTRAINT NAME: tbl_chk_1 LOCATION: ExecConstraints, execMain.c:2077 -- Regards, Himanshu Upadhyaya EnterpriseDB: http://www.enterprisedb.com