Horton

Re: NOT ENFORCED constraint feature

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Amul Sul <sulamul@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>
Date: 2025-02-01T15:01:22Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add support for NOT ENFORCED in foreign key constraints

  2. Expand test a bit

  3. refactor: Pass relation OID instead of Relation to createForeignKeyCheckTriggers()

  4. refactor: Split ATExecAlterConstraintInternal()

  5. refactor: Move some code that updates pg_constraint to a separate function

  6. Move RemoveInheritedConstraint() call slightly earlier

  7. refactor: Split tryAttachPartitionForeignKey()

  8. refactor: re-add ATExecAlterChildConstr()

  9. Add ATAlterConstraint struct for ALTER .. CONSTRAINT

  10. refactor: split ATExecAlterConstrRecurse()

  11. Add support for NOT ENFORCED in CHECK constraints

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?