Re: CHECK Constraint Deferrable

On Thu, 14 Sept 2023 at 15:33, Himanshu Upadhyaya
<upadhyaya(dot)himanshu(at)gmail(dot)com> wrote:
>
>
>
> On Thu, Sep 14, 2023 at 9:57 AM vignesh C <vignesh21(at)gmail(dot)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