diff options
| author | Peter Eisentraut <peter@eisentraut.org> | 2025-04-02 13:30:13 +0200 |
|---|---|---|
| committer | Peter Eisentraut <peter@eisentraut.org> | 2025-04-02 13:36:44 +0200 |
| commit | eec0040c4bcd650993bb058ebdf61ab94171fda4 (patch) | |
| tree | 5b38744ff96722b149336728c50f0d973dc90bc8 /src/backend/optimizer | |
| parent | 327d987df1e72a9b146f312df0a5ed34ef148720 (diff) | |
| download | postgresql-eec0040c4bcd650993bb058ebdf61ab94171fda4.tar.gz postgresql-eec0040c4bcd650993bb058ebdf61ab94171fda4.zip | |
Add support for NOT ENFORCED in foreign key constraints
This expands the NOT ENFORCED constraint flag, previously only
supported for CHECK constraints (commit ca87c415e2f), to foreign key
constraints.
Normally, when a foreign key constraint is created on a table, action
and check triggers are added to maintain data integrity. With this
patch, if a constraint is marked as NOT ENFORCED, integrity checks are
no longer required, making these triggers unnecessary. Consequently,
when creating a NOT ENFORCED foreign key constraint, triggers will not
be created, and the constraint will be marked as NOT VALID.
Similarly, if an existing foreign key constraint is changed to NOT
ENFORCED, the associated triggers will be dropped, and the constraint
will also be marked as NOT VALID. Conversely, if a NOT ENFORCED
foreign key constraint is changed to ENFORCED, the necessary triggers
will be created, and the will be changed to VALID by performing
necessary validation.
Since not-enforced foreign key constraints have no triggers, the
shortcut used for example in psql and pg_dump to skip looking for
foreign keys if the relation is known not to have triggers no longer
applies. (It already didn't work for partitioned tables.)
Author: Amul Sul <sulamul@gmail.com>
Reviewed-by: Joel Jacobson <joel@compiler.org>
Reviewed-by: Andrew Dunstan <andrew@dunslane.net>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Isaac Morland <isaac.morland@gmail.com>
Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com>
Tested-by: Triveni N <triveni.n@enterprisedb.com>
Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b962c5AcYW9KUt_R_ER5qs3fUGbe4az-SP-vuwPS-w-AGA@mail.gmail.com
Diffstat (limited to 'src/backend/optimizer')
| -rw-r--r-- | src/backend/optimizer/util/plancat.c | 4 |
1 files changed, 4 insertions, 0 deletions
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 0489ad36644..65b7d73bb5e 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -640,6 +640,10 @@ get_relation_foreign_keys(PlannerInfo *root, RelOptInfo *rel, /* conrelid should always be that of the table we're considering */ Assert(cachedfk->conrelid == RelationGetRelid(relation)); + /* skip constraints currently not enforced */ + if (!cachedfk->conenforced) + continue; + /* Scan to find other RTEs matching confrelid */ rti = 0; foreach(lc2, rtable) |