Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb4bcc6b

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

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
Oidconoid=lfirst_oid(lc);
5513-
boolfound;
55145513
ScanKeyDataskey;
55155514
SysScanDesctgscan;
55165515
HeapTuplehtup;
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
altertablefkpart2.fk_part_1 dropconstraint fkey;-- ok
14561456
altertablefkpart2.fk_part_1_1 dropconstraint my_fkey;-- doesn't exist
14571457

1458-
dropschema fkpart0, fkpart1, fkpart2 cascade;
1458+
-- verify constraint deferrability
1459+
createschemafkpart3
1460+
createtablepkey (aintprimary key)
1461+
createtablefk_part (aint,constraint fkeyforeign key (a)referencesfkpart3.pkey deferrable initially immediate) partition by list (a)
1462+
createtablefk_part_1 partition offkpart3.fk_part forvaluesin (1) partition by list (a)
1463+
createtablefk_part_1_1 partition offkpart3.fk_part_1 forvaluesin (1)
1464+
createtablefk_part_2 partition offkpart3.fk_part forvaluesin (2);
1465+
begin;
1466+
set constraintsfkpart3.fkey deferred;
1467+
insert intofkpart3.fk_partvalues (1);
1468+
insert intofkpart3.pkeyvalues (1);
1469+
commit;
1470+
begin;
1471+
set constraintsfkpart3.fkey deferred;
1472+
deletefromfkpart3.pkey;
1473+
deletefromfkpart3.fk_part;
1474+
commit;
1475+
1476+
dropschema 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 fk4VALUES (50);
15961614
ALTERTABLE fk ATTACH PARTITION fk4 FORVALUESIN (50);
15971615

1616+
-- Verify constraint deferrability
1617+
CREATESCHEMAfkpart9;
1618+
SET search_path TO fkpart9;
1619+
CREATETABLEpk (aintPRIMARY KEY) PARTITION BY LIST (a);
1620+
CREATETABLEpk1 PARTITION OF pk FORVALUESIN (1,2) PARTITION BY LIST (a);
1621+
CREATETABLEpk11 PARTITION OF pk1 FORVALUESIN (1);
1622+
CREATETABLEpk3 PARTITION OF pk FORVALUESIN (3);
1623+
CREATETABLEfk (aintREFERENCES pk DEFERRABLE INITIALLY IMMEDIATE);
1624+
INSERT INTO fkVALUES (1);-- should fail
1625+
BEGIN;
1626+
SET CONSTRAINTS fk_a_fkey DEFERRED;
1627+
INSERT INTO fkVALUES (1);
1628+
COMMIT;-- should fail
1629+
BEGIN;
1630+
SET CONSTRAINTS fk_a_fkey DEFERRED;
1631+
INSERT INTO fkVALUES (1);
1632+
INSERT INTO pkVALUES (1);
1633+
COMMIT;-- OK
1634+
BEGIN;
1635+
SET CONSTRAINTS fk_a_fkey DEFERRED;
1636+
DELETEFROM pkWHERE a=1;
1637+
DELETEFROM fkWHERE a=1;
1638+
COMMIT;-- OK
1639+
DROPSCHEMA fkpart9 CASCADE;
1640+
15981641
-- Verify ON UPDATE/DELETE behavior
15991642
CREATESCHEMAfkpart6;
16001643
SET search_path TO fkpart6;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp