Re: CHECK Constraint Deferrable
| От | Himanshu Upadhyaya |
|---|---|
| Тема | Re: CHECK Constraint Deferrable |
| Дата | |
| Msg-id | CAPF61jAvRYEe3oHvHV_h6Keuy40=gZRt4p8Gkoj6A_4CFCrdHg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: CHECK Constraint Deferrable (vignesh C <vignesh21@gmail.com>) |
| Список | pgsql-hackers |
Thanks for the review comments.
On Tue, Sep 12, 2023 at 2:56 PM vignesh C <vignesh21@gmail.com> wrote:
On Thu, 7 Sept 2023 at 17:26, Himanshu Upadhyaya
<upadhyaya.himanshu@gmail.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
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?
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.
В списке pgsql-hackers по дате отправления: