Re: CHECK Constraint Deferrable

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

--
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com