Skip to content

Commit b4bcc6b

Browse files
alvherrealvherre
committed
Fix SET CONSTRAINTS .. DEFERRED on partitioned tables
SET CONSTRAINTS ... DEFERRED failed on partitioned tables, because of a sanity check that ensures that the affected constraints have triggers. On partitioned tables, the triggers are in the leaf partitions, not in the partitioned relations themselves, so the sanity check fails. Removing the sanity check solves the problem, because the code needed to support the case is already there. Backpatch to 11. Note: deferred unique constraints are not affected by this bug, because they do have triggers in the parent partitioned table. I did not add a test for this scenario. Discussion: https://postgr.es/m/20191105212915.GA11324@alvherre.pgsql
1 parent a7145f6 commit b4bcc6b

File tree

3 files changed

+97
-14
lines changed

3 files changed

+97
-14
lines changed

‎src/backend/commands/trigger.c

Lines changed: 0 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -5510,13 +5510,10 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
55105510
foreach(lc, conoidlist)
55115511
{
55125512
Oid conoid = lfirst_oid(lc);
5513-
bool found;
55145513
ScanKeyData skey;
55155514
SysScanDesc tgscan;
55165515
HeapTuple htup;
55175516

5518-
found = false;
5519-
55205517
ScanKeyInit(&skey,
55215518
Anum_pg_trigger_tgconstraint,
55225519
BTEqualStrategyNumber, F_OIDEQ,
@@ -5537,16 +5534,9 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
55375534
*/
55385535
if (pg_trigger->tgdeferrable)
55395536
tgoidlist = lappend_oid(tgoidlist, pg_trigger->oid);
5540-
5541-
found = true;
55425537
}
55435538

55445539
systable_endscan(tgscan);
5545-
5546-
/* Safety check: a deferrable constraint should have triggers */
5547-
if (!found)
5548-
elog(ERROR, "no triggers found for constraint with OID %u",
5549-
conoid);
55505540
}
55515541

55525542
table_close(tgrel, AccessShareLock);

‎src/test/regress/expected/foreign_key.out

Lines changed: 53 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2040,9 +2040,28 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
20402040
alter table fkpart2.fk_part_1 drop constraint fkey; -- ok
20412041
alter table fkpart2.fk_part_1_1 drop constraint my_fkey; -- doesn't exist
20422042
ERROR: constraint "my_fkey" of relation "fk_part_1_1" does not exist
2043-
drop schema fkpart0, fkpart1, fkpart2 cascade;
2044-
NOTICE: drop cascades to 8 other objects
2045-
DETAIL: drop cascades to table fkpart2.pkey
2043+
-- verify constraint deferrability
2044+
create schema fkpart3
2045+
create table pkey (a int primary key)
2046+
create table fk_part (a int, constraint fkey foreign key (a) references fkpart3.pkey deferrable initially immediate) partition by list (a)
2047+
create table fk_part_1 partition of fkpart3.fk_part for values in (1) partition by list (a)
2048+
create table fk_part_1_1 partition of fkpart3.fk_part_1 for values in (1)
2049+
create table fk_part_2 partition of fkpart3.fk_part for values in (2);
2050+
begin;
2051+
set constraints fkpart3.fkey deferred;
2052+
insert into fkpart3.fk_part values (1);
2053+
insert into fkpart3.pkey values (1);
2054+
commit;
2055+
begin;
2056+
set constraints fkpart3.fkey deferred;
2057+
delete from fkpart3.pkey;
2058+
delete from fkpart3.fk_part;
2059+
commit;
2060+
drop schema fkpart0, fkpart1, fkpart2, fkpart3 cascade;
2061+
NOTICE: drop cascades to 10 other objects
2062+
DETAIL: drop cascades to table fkpart3.pkey
2063+
drop cascades to table fkpart3.fk_part
2064+
drop cascades to table fkpart2.pkey
20462065
drop cascades to table fkpart2.fk_part
20472066
drop cascades to table fkpart2.fk_part_1
20482067
drop cascades to table fkpart1.pkey
@@ -2267,6 +2286,37 @@ INSERT INTO fk4 VALUES (50);
22672286
ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES IN (50);
22682287
ERROR: insert or update on table "fk4" violates foreign key constraint "fk_a_fkey"
22692288
DETAIL: Key (a)=(50) is not present in table "pk".
2289+
-- Verify constraint deferrability
2290+
CREATE SCHEMA fkpart9;
2291+
SET search_path TO fkpart9;
2292+
CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY LIST (a);
2293+
CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a);
2294+
CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1);
2295+
CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (3);
2296+
CREATE TABLE fk (a int REFERENCES pk DEFERRABLE INITIALLY IMMEDIATE);
2297+
INSERT INTO fk VALUES (1); -- should fail
2298+
ERROR: insert or update on table "fk" violates foreign key constraint "fk_a_fkey"
2299+
DETAIL: Key (a)=(1) is not present in table "pk".
2300+
BEGIN;
2301+
SET CONSTRAINTS fk_a_fkey DEFERRED;
2302+
INSERT INTO fk VALUES (1);
2303+
COMMIT; -- should fail
2304+
ERROR: insert or update on table "fk" violates foreign key constraint "fk_a_fkey"
2305+
DETAIL: Key (a)=(1) is not present in table "pk".
2306+
BEGIN;
2307+
SET CONSTRAINTS fk_a_fkey DEFERRED;
2308+
INSERT INTO fk VALUES (1);
2309+
INSERT INTO pk VALUES (1);
2310+
COMMIT; -- OK
2311+
BEGIN;
2312+
SET CONSTRAINTS fk_a_fkey DEFERRED;
2313+
DELETE FROM pk WHERE a = 1;
2314+
DELETE FROM fk WHERE a = 1;
2315+
COMMIT; -- OK
2316+
DROP SCHEMA fkpart9 CASCADE;
2317+
NOTICE: drop cascades to 2 other objects
2318+
DETAIL: drop cascades to table pk
2319+
drop cascades to table fk
22702320
-- Verify ON UPDATE/DELETE behavior
22712321
CREATE SCHEMA fkpart6;
22722322
SET search_path TO fkpart6;

‎src/test/regress/sql/foreign_key.sql

Lines changed: 44 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1455,7 +1455,25 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
14551455
alter table fkpart2.fk_part_1 drop constraint fkey; -- ok
14561456
alter table fkpart2.fk_part_1_1 drop constraint my_fkey; -- doesn't exist
14571457

1458-
drop schema fkpart0, fkpart1, fkpart2 cascade;
1458+
-- verify constraint deferrability
1459+
create schema fkpart3
1460+
create table pkey (a int primary key)
1461+
create table fk_part (a int, constraint fkey foreign key (a) references fkpart3.pkey deferrable initially immediate) partition by list (a)
1462+
create table fk_part_1 partition of fkpart3.fk_part for values in (1) partition by list (a)
1463+
create table fk_part_1_1 partition of fkpart3.fk_part_1 for values in (1)
1464+
create table fk_part_2 partition of fkpart3.fk_part for values in (2);
1465+
begin;
1466+
set constraints fkpart3.fkey deferred;
1467+
insert into fkpart3.fk_part values (1);
1468+
insert into fkpart3.pkey values (1);
1469+
commit;
1470+
begin;
1471+
set constraints fkpart3.fkey deferred;
1472+
delete from fkpart3.pkey;
1473+
delete from fkpart3.fk_part;
1474+
commit;
1475+
1476+
drop schema fkpart0, fkpart1, fkpart2, fkpart3 cascade;
14591477

14601478
-- Test a partitioned table as referenced table.
14611479

@@ -1595,6 +1613,31 @@ CREATE TABLE fk4 (LIKE fk);
15951613
INSERT INTO fk4 VALUES (50);
15961614
ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES IN (50);
15971615

1616+
-- Verify constraint deferrability
1617+
CREATE SCHEMA fkpart9;
1618+
SET search_path TO fkpart9;
1619+
CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY LIST (a);
1620+
CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a);
1621+
CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1);
1622+
CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (3);
1623+
CREATE TABLE fk (a int REFERENCES pk DEFERRABLE INITIALLY IMMEDIATE);
1624+
INSERT INTO fk VALUES (1); -- should fail
1625+
BEGIN;
1626+
SET CONSTRAINTS fk_a_fkey DEFERRED;
1627+
INSERT INTO fk VALUES (1);
1628+
COMMIT; -- should fail
1629+
BEGIN;
1630+
SET CONSTRAINTS fk_a_fkey DEFERRED;
1631+
INSERT INTO fk VALUES (1);
1632+
INSERT INTO pk VALUES (1);
1633+
COMMIT; -- OK
1634+
BEGIN;
1635+
SET CONSTRAINTS fk_a_fkey DEFERRED;
1636+
DELETE FROM pk WHERE a = 1;
1637+
DELETE FROM fk WHERE a = 1;
1638+
COMMIT; -- OK
1639+
DROP SCHEMA fkpart9 CASCADE;
1640+
15981641
-- Verify ON UPDATE/DELETE behavior
15991642
CREATE SCHEMA fkpart6;
16001643
SET search_path TO fkpart6;

0 commit comments

Comments
 (0)