public inbox for [email protected]  

help / color / mirror / Atom feed

From: Himanshu Upadhyaya <[email protected]>
To: vignesh C <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: CHECK Constraint Deferrable
Date: Mon, 2 Oct 2023 20:31:22 +0530
Message-ID: <CAPF61jB8H9Q_D3_Oyx2S-spdMhbvQWonbcqyqb+iZbXJe2D_sw@mail.gmail.com> (raw)
In-Reply-To: <CALDaNm18D0uoKzkyL+Db5gcKKcmkRfiBDuny_2tXsnnEjpD+-g@mail.gmail.com>
References: <CAPF61jBFT6iGv-Af=NrZx3vhqLYqx8WDvfjqVuRVYv2DFbbY4A@mail.gmail.com>
	<CAFiTN-tRjms-23L7FyxACJHcxcYr8yo=KP8JN4XC2gtukaXT3A@mail.gmail.com>
	<CAPF61jCRXxCo9wX=RW2mqc+bnB=DPijU1ruGDki55TYYiSu7yw@mail.gmail.com>
	<CAKFQuwZf0We7p-7Df-Fyxrfv+cS=meF0DorEtuBWx5nhLZ+j9A@mail.gmail.com>
	<CAPF61jAH7NhgoQkzuv+eBE8UyLorB8uQB0_hgmAYWj783GnxRQ@mail.gmail.com>
	<CALDaNm18D0uoKzkyL+Db5gcKKcmkRfiBDuny_2tXsnnEjpD+-g@mail.gmail.com>

On Thu, Sep 14, 2023 at 9:57 AM vignesh C <[email protected]> wrote:

> 2) I was not sure, if the error message change was intentional:
> 2a)
> In Head:
> CREATE FOREIGN TABLE t9(a int CHECK(a<>0) DEFERRABLE) SERVER s1;
> ERROR:  misplaced DEFERRABLE clause
> LINE 1: CREATE FOREIGN TABLE t9(a int CHECK(a<>0) DEFERRABLE) SERVER...
>                                                   ^
> postgres=# CREATE FOREIGN TABLE t9(a int CHECK(a<>0) DEFERRABLE) SERVER s1;
> ERROR:  "t9" is a foreign table
> DETAIL:  Foreign tables cannot have constraint triggers.
>
> 2b)
> In Head:
> postgres=# CREATE FOREIGN TABLE t2(a int CHECK(a<>0)) SERVER s1;
> CREATE FOREIGN TABLE
> postgres=# ALTER FOREIGN TABLE t2 ADD CONSTRAINT t2_chk_1 CHECK(a<>1)
> DEFERRABLE;
> ERROR:  CHECK constraints cannot be marked DEFERRABLE
>
> With patch:
> postgres=# ALTER FOREIGN TABLE t8 ADD CONSTRAINT t8_chk_1 CHECK(a<>1)
> DEFERRABLE;
> ERROR:  "t8" is a foreign table
> DETAIL:  Foreign tables cannot have constraint triggers.
>
> We are creating a constraint trigger for DEFERRED check constraint and as
per implementation of FOREIGN table we are restricting to have a constraint
trigger. I need to do more analysis before reaching to any conclusion, I
think we can restrict this gram.y itself.

> 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).
>
> Fixed in V3 patch.

> 4) There is a new warning popping up now:
> CREATE TABLE tbl_new_3 (i int check(i<>0)) partition by range (i);
> CREATE FOREIGN TABLE ftbl_new_3 PARTITION OF tbl_new_3 FOR VALUES FROM
> (40) TO (50) server s1;
> postgres=# ALTER TABLE tbl_new_3 ADD CONSTRAINT tbl_new_3_chk
> CHECK(i<>1) DEFERRABLE;
> WARNING:  unexpected pg_constraint record found for relation "tbl_new_3"
> ERROR:  "ftbl_new_3" is a foreign table
> DETAIL:  Foreign tables cannot have constraint triggers.
>
> Fixed in V3 patch.

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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: CHECK Constraint Deferrable
  In-Reply-To: <CAPF61jB8H9Q_D3_Oyx2S-spdMhbvQWonbcqyqb+iZbXJe2D_sw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox