On Thu, 14 Sept 2023 at 15:33, Himanshu Upadhyaya <upadhyaya.himan...@gmail.com> wrote: > > > > 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
I think we should be able to defer one constraint like in the case of foreign key constraint: create table t1(c1 int primary key); insert into t1 values(10); create table t2(c1 int primary key); insert into t2 values(10); create table t3(c1 int, c2 int references t1(c1) deferrable, c3 int references t2(c1) deferrable); -- Set only one constraint as deferred begin; set CONSTRAINTS t3_c2_fkey deferred; -- c2 column constraint is deferred, we need not set all constraints deferred in this case, insert was successful postgres=*# insert into t3 values(1,11,10); INSERT 0 1 -- Throws error for the constraint that is not deferred postgres=*# insert into t3 values(1,10,11); ERROR: insert or update on table "t3" violates foreign key constraint "t3_c3_fkey" DETAIL: Key (c3)=(11) is not present in table "t2". Thoughts? Regards, Vignesh