Re: CHECK Constraint Deferrable

Thanks for the review comments.

On Tue, Sep 12, 2023 at 2:56 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:

> On Thu, 7 Sept 2023 at 17:26, Himanshu Upadhyaya
> <upadhyaya(dot)himanshu(at)gmail(dot)com> wrote:
> >
> > Attached is v2 of the patch, rebased against the latest HEAD.
>
> Thanks for working on this, few comments:
> 1) "CREATE TABLE check_constr_tbl (i int CHECK(i<>0) DEFERRABLE, t
> text)" is crashing in windows, the same was noticed in CFBot too:
> 2023-09-11 08:11:36.585 UTC [58563][client backend]
> [pg_regress/constraints][13/880:0] LOG: statement: CREATE TABLE
> check_constr_tbl (i int CHECK(i<>0) DEFERRABLE, t text);
> 2023-09-11 08:11:36.586 UTC [58560][client backend]
> [pg_regress/inherit][15/391:0] LOG: statement: drop table c1;
> ../src/backend/commands/trigger.c:220:26: runtime error: member access
> within null pointer of type 'struct CreateTrigStmt'
> ==58563==Using libbacktrace symbolizer.
>
> Will Fix this in my next patch.

> The details of CFBot failure can be seen at [1]
>
> 2) Alter of check constraint deferrable is not handled, is this
> intentional?
> CREATE TABLE check_constr_tbl (i int CHECK(i<>0) DEFERRABLE, t text);
> postgres=# alter table check_constr_tbl alter constraint
> check_constr_tbl_i_check not deferrable;
> ERROR: constraint "check_constr_tbl_i_check" of relation
> "check_constr_tbl" is not a foreign key constraint
>
> ALTER CONSTRAINT is currently only supported for FOREIGN KEY, it's even
not supported for UNIQUE constraint as below:
‘postgres[1271421]=#’CREATE TABLE unique_constr_tbl (i int unique
DEFERRABLE, t text);
CREATE TABLE
‘postgres[1271421]=#’\d unique_constr_tbl;
Table "public.unique_constr_tbl"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |
t | text | | |
Indexes:
"unique_constr_tbl_i_key" UNIQUE CONSTRAINT, btree (i) DEFERRABLE
‘postgres[1271421]=#’alter table unique_constr_tbl alter constraint
unique_constr_tbl_i_key not deferrable;
ERROR: 42809: constraint "unique_constr_tbl_i_key" of relation
"unique_constr_tbl" is not a foreign key constraint
LOCATION: ATExecAlterConstraint, tablecmds.c:11183

I still need to understand the design restriction here, please let me know
if anyone is aware of this?
is it because of dependency on Indexes?

3) Should we handle this scenario for domains too:
> CREATE DOMAIN c1_check AS INT CHECK(VALUE > 10);
> create table test(c1 c1_check);
> alter domain c1_check ADD check (VALUE > 20) DEFERRABLE INITIALLY DEFERRED;
>
> begin;
> -- should this be deffered
> insert into test values(19);
> ERROR: value for domain c1_check violates check constraint
> "c1_check_check1"
>
> Yes, thanks for notifying, I missed this for CREATE DOMAIN, will analyse
and include in next revision.

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