Re: NOT ENFORCED constraint feature

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: Amul Sul <sulamul(at)gmail(dot)com>
Cc: Peter Eisentraut <peter(at)eisentraut(dot)org>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Joel Jacobson <joel(at)compiler(dot)org>
Subject: Re: NOT ENFORCED constraint feature
Date: 2025-02-01 15:01:22
Message-ID: CACJufxG1bX=7ZoOR9Gb7_0pPg7w5iwB_JZ=v104T2Ut2j64aAA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.
after applying the v11-0002 to v11-0006.
there is a bug in ATExecAlterConstrRecurse, i think.

in ATExecAlterConstrRecurse, after applying the patch, the code is

if (currcon->condeferrable != cmdcon->deferrable ||
currcon->condeferred != cmdcon->initdeferred ||
currcon->conenforced != cmdcon->is_enforced)
{
}
if (currcon->conenforced != cmdcon->is_enforced)
{
ATExecAlterConstrEnforceability
}
else
{
AlterConstrTriggerDeferrability...
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
get_rel_relkind(refrelid) == RELKIND_PARTITIONED_TABLE)
ATExecAlterChildConstr(cmdcon, conrel, tgrel, fkrelid, pkrelid,
contuple, otherrelids, lockmode);
}

drop table if exists PKTABLE, fktable cascade;
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY, ptest2 text);
CREATE TABLE FKTABLE (ftest1 int REFERENCES PKTABLE MATCH FULL ON
DELETE CASCADE ON UPDATE CASCADE NOT ENFORCED,
ftest2 int);
ALTER TABLE fktable ALTER CONSTRAINT fktable_ftest1_fkey deferrable;

\d fktable
Table "public.fktable"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
ftest1 | integer | | |
ftest2 | integer | | |
Foreign-key constraints:
"fktable_ftest1_fkey" FOREIGN KEY (ftest1) REFERENCES
pktable(ptest1) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
DEFERRABLE NOT VALID

Currently "ALTER TABLE fktable ALTER CONSTRAINT fktable_ftest1_fkey deferrable;"
imply the constraint fktable_ftest1_fkey is changing from "not
enforced" to "enforced".
but here we didn't explicitly mean to change the "enforced" status.
We only want to change the deferriability.

So the code should only call AlterConstrTriggerDeferrability,
not call ATExecAlterConstrEnforceability?

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2025-02-01 15:50:37 Re: hash_search_with_hash_value is high in "perf top" on a replica
Previous Message Ants Aasma 2025-02-01 14:43:41 Re: hash_search_with_hash_value is high in "perf top" on a replica