Re: CHECK Constraint Deferrable

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