aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/commands/trigger.c10
-rw-r--r--src/test/regress/expected/foreign_key.out56
-rw-r--r--src/test/regress/sql/foreign_key.sql45
3 files changed, 97 insertions, 14 deletions
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 0b84de59437..cef6d02f517 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -5510,13 +5510,10 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
foreach(lc, conoidlist)
{
Oid conoid = lfirst_oid(lc);
- bool found;
ScanKeyData skey;
SysScanDesc tgscan;
HeapTuple htup;
- found = false;
-
ScanKeyInit(&skey,
Anum_pg_trigger_tgconstraint,
BTEqualStrategyNumber, F_OIDEQ,
@@ -5537,16 +5534,9 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
*/
if (pg_trigger->tgdeferrable)
tgoidlist = lappend_oid(tgoidlist, pg_trigger->oid);
-
- found = true;
}
systable_endscan(tgscan);
-
- /* Safety check: a deferrable constraint should have triggers */
- if (!found)
- elog(ERROR, "no triggers found for constraint with OID %u",
- conoid);
}
table_close(tgrel, AccessShareLock);
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 894084f94f1..9f2dda499ef 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -2040,9 +2040,28 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
alter table fkpart2.fk_part_1 drop constraint fkey; -- ok
alter table fkpart2.fk_part_1_1 drop constraint my_fkey; -- doesn't exist
ERROR: constraint "my_fkey" of relation "fk_part_1_1" does not exist
-drop schema fkpart0, fkpart1, fkpart2 cascade;
-NOTICE: drop cascades to 8 other objects
-DETAIL: drop cascades to table fkpart2.pkey
+-- verify constraint deferrability
+create schema fkpart3
+ create table pkey (a int primary key)
+ create table fk_part (a int, constraint fkey foreign key (a) references fkpart3.pkey deferrable initially immediate) partition by list (a)
+ create table fk_part_1 partition of fkpart3.fk_part for values in (1) partition by list (a)
+ create table fk_part_1_1 partition of fkpart3.fk_part_1 for values in (1)
+ create table fk_part_2 partition of fkpart3.fk_part for values in (2);
+begin;
+set constraints fkpart3.fkey deferred;
+insert into fkpart3.fk_part values (1);
+insert into fkpart3.pkey values (1);
+commit;
+begin;
+set constraints fkpart3.fkey deferred;
+delete from fkpart3.pkey;
+delete from fkpart3.fk_part;
+commit;
+drop schema fkpart0, fkpart1, fkpart2, fkpart3 cascade;
+NOTICE: drop cascades to 10 other objects
+DETAIL: drop cascades to table fkpart3.pkey
+drop cascades to table fkpart3.fk_part
+drop cascades to table fkpart2.pkey
drop cascades to table fkpart2.fk_part
drop cascades to table fkpart2.fk_part_1
drop cascades to table fkpart1.pkey
@@ -2267,6 +2286,37 @@ INSERT INTO fk4 VALUES (50);
ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES IN (50);
ERROR: insert or update on table "fk4" violates foreign key constraint "fk_a_fkey"
DETAIL: Key (a)=(50) is not present in table "pk".
+-- Verify constraint deferrability
+CREATE SCHEMA fkpart9;
+SET search_path TO fkpart9;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY LIST (a);
+CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a);
+CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1);
+CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (3);
+CREATE TABLE fk (a int REFERENCES pk DEFERRABLE INITIALLY IMMEDIATE);
+INSERT INTO fk VALUES (1); -- should fail
+ERROR: insert or update on table "fk" violates foreign key constraint "fk_a_fkey"
+DETAIL: Key (a)=(1) is not present in table "pk".
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+INSERT INTO fk VALUES (1);
+COMMIT; -- should fail
+ERROR: insert or update on table "fk" violates foreign key constraint "fk_a_fkey"
+DETAIL: Key (a)=(1) is not present in table "pk".
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+INSERT INTO fk VALUES (1);
+INSERT INTO pk VALUES (1);
+COMMIT; -- OK
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+DELETE FROM pk WHERE a = 1;
+DELETE FROM fk WHERE a = 1;
+COMMIT; -- OK
+DROP SCHEMA fkpart9 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table pk
+drop cascades to table fk
-- Verify ON UPDATE/DELETE behavior
CREATE SCHEMA fkpart6;
SET search_path TO fkpart6;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index b67bef01df5..6f94bf88851 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1455,7 +1455,25 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
alter table fkpart2.fk_part_1 drop constraint fkey; -- ok
alter table fkpart2.fk_part_1_1 drop constraint my_fkey; -- doesn't exist
-drop schema fkpart0, fkpart1, fkpart2 cascade;
+-- verify constraint deferrability
+create schema fkpart3
+ create table pkey (a int primary key)
+ create table fk_part (a int, constraint fkey foreign key (a) references fkpart3.pkey deferrable initially immediate) partition by list (a)
+ create table fk_part_1 partition of fkpart3.fk_part for values in (1) partition by list (a)
+ create table fk_part_1_1 partition of fkpart3.fk_part_1 for values in (1)
+ create table fk_part_2 partition of fkpart3.fk_part for values in (2);
+begin;
+set constraints fkpart3.fkey deferred;
+insert into fkpart3.fk_part values (1);
+insert into fkpart3.pkey values (1);
+commit;
+begin;
+set constraints fkpart3.fkey deferred;
+delete from fkpart3.pkey;
+delete from fkpart3.fk_part;
+commit;
+
+drop schema fkpart0, fkpart1, fkpart2, fkpart3 cascade;
-- Test a partitioned table as referenced table.
@@ -1595,6 +1613,31 @@ CREATE TABLE fk4 (LIKE fk);
INSERT INTO fk4 VALUES (50);
ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES IN (50);
+-- Verify constraint deferrability
+CREATE SCHEMA fkpart9;
+SET search_path TO fkpart9;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY LIST (a);
+CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a);
+CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1);
+CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (3);
+CREATE TABLE fk (a int REFERENCES pk DEFERRABLE INITIALLY IMMEDIATE);
+INSERT INTO fk VALUES (1); -- should fail
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+INSERT INTO fk VALUES (1);
+COMMIT; -- should fail
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+INSERT INTO fk VALUES (1);
+INSERT INTO pk VALUES (1);
+COMMIT; -- OK
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+DELETE FROM pk WHERE a = 1;
+DELETE FROM fk WHERE a = 1;
+COMMIT; -- OK
+DROP SCHEMA fkpart9 CASCADE;
+
-- Verify ON UPDATE/DELETE behavior
CREATE SCHEMA fkpart6;
SET search_path TO fkpart6;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-23 03:25:15 +0000