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

Commitcd4609e

Browse files
committed
Change TRUNCATE's method for searching for foreign-key references so that
the order in which it visits tables is not dependent on the physical orderof pg_constraint entries, and neither are the error messages it gives.This should correct recently-noticed instability in regression tests.
1 parentbc9b6c2 commitcd4609e

File tree

4 files changed

+115
-76
lines changed

4 files changed

+115
-76
lines changed

‎src/backend/catalog/heap.c‎

Lines changed: 92 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.301 2006/06/27 18:35:05 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.302 2006/06/29 16:07:29 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -75,6 +75,7 @@ static void RelationRemoveInheritance(Oid relid);
7575
staticvoidStoreRelCheck(Relationrel,char*ccname,char*ccbin);
7676
staticvoidStoreConstraints(Relationrel,TupleDesctupdesc);
7777
staticvoidSetRelationNumChecks(Relationrel,intnumchecks);
78+
staticList*insert_ordered_unique_oid(List*list,Oiddatum);
7879

7980

8081
/* ----------------------------------------------------------------
@@ -1990,7 +1991,7 @@ heap_truncate(List *relids)
19901991
/*
19911992
* heap_truncate_check_FKs
19921993
*Check for foreign keys referencing a list of relations that
1993-
*are to be truncated
1994+
*are to be truncated, and raise error if there are any
19941995
*
19951996
* We disallow such FKs (except self-referential ones) since the whole point
19961997
* of TRUNCATE is to not scan the individual rows to be thrown away.
@@ -2004,10 +2005,8 @@ void
20042005
heap_truncate_check_FKs(List*relations,booltempTables)
20052006
{
20062007
List*oids=NIL;
2008+
List*dependents;
20072009
ListCell*cell;
2008-
RelationfkeyRel;
2009-
SysScanDescfkeyScan;
2010-
HeapTupletuple;
20112010

20122011
/*
20132012
* Build a list of OIDs of the interesting relations.
@@ -2030,67 +2029,67 @@ heap_truncate_check_FKs(List *relations, bool tempTables)
20302029
return;
20312030

20322031
/*
2033-
* Otherwise, must scan pg_constraint.Right now, it is a seqscan because
2034-
*there is no available index on confrelid.
2032+
* Otherwise, must scan pg_constraint. We make one pass with all the
2033+
*relations considered; if this finds nothing, then all is well.
20352034
*/
2036-
fkeyRel=heap_open(ConstraintRelationId,AccessShareLock);
2037-
2038-
fkeyScan=systable_beginscan(fkeyRel,InvalidOid, false,
2039-
SnapshotNow,0,NULL);
2035+
dependents=heap_truncate_find_FKs(oids);
2036+
if (dependents==NIL)
2037+
return;
20402038

2041-
while (HeapTupleIsValid(tuple=systable_getnext(fkeyScan)))
2039+
/*
2040+
* Otherwise we repeat the scan once per relation to identify a particular
2041+
* pair of relations to complain about. This is pretty slow, but
2042+
* performance shouldn't matter much in a failure path. The reason for
2043+
* doing things this way is to ensure that the message produced is not
2044+
* dependent on chance row locations within pg_constraint.
2045+
*/
2046+
foreach(cell,oids)
20422047
{
2043-
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tuple);
2044-
2045-
/* Not a foreign key */
2046-
if (con->contype!=CONSTRAINT_FOREIGN)
2047-
continue;
2048+
Oidrelid=lfirst_oid(cell);
2049+
ListCell*cell2;
20482050

2049-
/* Not referencing one of our list of tables */
2050-
if (!list_member_oid(oids,con->confrelid))
2051-
continue;
2051+
dependents=heap_truncate_find_FKs(list_make1_oid(relid));
20522052

2053-
/* The referencer should be in our list too */
2054-
if (!list_member_oid(oids,con->conrelid))
2053+
foreach(cell2,dependents)
20552054
{
2056-
if (tempTables)
2057-
ereport(ERROR,
2058-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2059-
errmsg("unsupported ON COMMIT and foreign key combination"),
2060-
errdetail("Table \"%s\" references \"%s\" via foreign key constraint \"%s\", but they do not have the same ON COMMIT setting.",
2061-
get_rel_name(con->conrelid),
2062-
get_rel_name(con->confrelid),
2063-
NameStr(con->conname))));
2064-
else
2065-
ereport(ERROR,
2066-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2067-
errmsg("cannot truncate a table referenced in a foreign key constraint"),
2068-
errdetail("Table \"%s\" references \"%s\" via foreign key constraint \"%s\".",
2069-
get_rel_name(con->conrelid),
2070-
get_rel_name(con->confrelid),
2071-
NameStr(con->conname)),
2072-
errhint("Truncate table \"%s\" at the same time, "
2073-
"or use TRUNCATE ... CASCADE.",
2074-
get_rel_name(con->conrelid))));
2055+
Oidrelid2=lfirst_oid(cell2);
2056+
2057+
if (!list_member_oid(oids,relid2))
2058+
{
2059+
char*relname=get_rel_name(relid);
2060+
char*relname2=get_rel_name(relid2);
2061+
2062+
if (tempTables)
2063+
ereport(ERROR,
2064+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2065+
errmsg("unsupported ON COMMIT and foreign key combination"),
2066+
errdetail("Table \"%s\" references \"%s\", but they do not have the same ON COMMIT setting.",
2067+
relname2,relname)));
2068+
else
2069+
ereport(ERROR,
2070+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2071+
errmsg("cannot truncate a table referenced in a foreign key constraint"),
2072+
errdetail("Table \"%s\" references \"%s\".",
2073+
relname2,relname),
2074+
errhint("Truncate table \"%s\" at the same time, "
2075+
"or use TRUNCATE ... CASCADE.",
2076+
relname2)));
2077+
}
20752078
}
20762079
}
2077-
2078-
systable_endscan(fkeyScan);
2079-
heap_close(fkeyRel,AccessShareLock);
20802080
}
20812081

20822082
/*
20832083
* heap_truncate_find_FKs
2084-
*Find relations having foreign keys referencing any relations that
2085-
*are to be truncated
2084+
*Find relations having foreign keys referencing any of the given rels
20862085
*
2087-
*This is almost the same code as heap_truncate_check_FKs, but we don't
2088-
*raise an error if we find such relations; instead we return a list of
2089-
*their OIDs. Also note that the input is a list of OIDs not a list
2090-
*of Relations. The result list does *not* include any rels that are
2091-
*already in the input list.
2086+
*Input and result are both lists of relation OIDs. The result contains
2087+
*no duplicates, does *not* include any rels that were already in the input
2088+
*list, and is sorted in OID order. (The last property is enforced mainly
2089+
*to guarantee consistent behavior in the regression tests; we don't want
2090+
*behavior to change depending on chance locations of rows in pg_constraint.)
20922091
*
2093-
* Note: caller should already haveexclusive lock on all rels mentioned
2092+
* Note: caller should already haveappropriate lock on all rels mentioned
20942093
* in relationIds. Since adding or dropping an FK requires exclusive lock
20952094
* on both rels, this ensures that the answer will be stable.
20962095
*/
@@ -2125,11 +2124,51 @@ heap_truncate_find_FKs(List *relationIds)
21252124

21262125
/* Add referencer unless already in input or result list */
21272126
if (!list_member_oid(relationIds,con->conrelid))
2128-
result=list_append_unique_oid(result,con->conrelid);
2127+
result=insert_ordered_unique_oid(result,con->conrelid);
21292128
}
21302129

21312130
systable_endscan(fkeyScan);
21322131
heap_close(fkeyRel,AccessShareLock);
21332132

21342133
returnresult;
21352134
}
2135+
2136+
/*
2137+
* insert_ordered_unique_oid
2138+
*Insert a new Oid into a sorted list of Oids, preserving ordering,
2139+
*and eliminating duplicates
2140+
*
2141+
* Building the ordered list this way is O(N^2), but with a pretty small
2142+
* constant, so for the number of entries we expect it will probably be
2143+
* faster than trying to apply qsort(). It seems unlikely someone would be
2144+
* trying to truncate a table with thousands of dependent tables ...
2145+
*/
2146+
staticList*
2147+
insert_ordered_unique_oid(List*list,Oiddatum)
2148+
{
2149+
ListCell*prev;
2150+
2151+
/* Does the datum belong at the front? */
2152+
if (list==NIL||datum<linitial_oid(list))
2153+
returnlcons_oid(datum,list);
2154+
/* Does it match the first entry? */
2155+
if (datum==linitial_oid(list))
2156+
returnlist;/* duplicate, so don't insert */
2157+
/* No, so find the entry it belongs after */
2158+
prev=list_head(list);
2159+
for (;;)
2160+
{
2161+
ListCell*curr=lnext(prev);
2162+
2163+
if (curr==NULL||datum<lfirst_oid(curr))
2164+
break;/* it belongs after 'prev', before 'curr' */
2165+
2166+
if (datum==lfirst_oid(curr))
2167+
returnlist;/* duplicate, so don't insert */
2168+
2169+
prev=curr;
2170+
}
2171+
/* Insert datum into list after 'prev' */
2172+
lappend_cell_oid(list,prev,datum);
2173+
returnlist;
2174+
}

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -136,4 +136,4 @@ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "temptest3_pkey"
136136
CREATE TEMP TABLE temptest4(col int REFERENCES temptest3);
137137
COMMIT;
138138
ERROR: unsupported ON COMMIT and foreign key combination
139-
DETAIL: Table "temptest4" references "temptest3" via foreign key constraint "temptest4_col_fkey", but they do not have the same ON COMMIT setting.
139+
DETAIL: Table "temptest4" references "temptest3", but they do not have the same ON COMMIT setting.

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

Lines changed: 18 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -39,34 +39,34 @@ CREATE TABLE trunc_d (a int REFERENCES trunc_c);
3939
CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c);
4040
TRUNCATE TABLE truncate_a;-- fail
4141
ERROR: cannot truncate a table referenced in a foreign key constraint
42-
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
42+
DETAIL: Table "trunc_b" references "truncate_a".
4343
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
4444
TRUNCATE TABLE truncate_a,trunc_b;-- fail
4545
ERROR: cannot truncate a table referenced in a foreign key constraint
46-
DETAIL: Table "trunc_e" references "truncate_a" via foreign key constraint "trunc_e_a_fkey".
46+
DETAIL: Table "trunc_e" references "truncate_a".
4747
HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
4848
TRUNCATE TABLE truncate_a,trunc_b,trunc_e;-- ok
4949
TRUNCATE TABLE truncate_a,trunc_e;-- fail
5050
ERROR: cannot truncate a table referenced in a foreign key constraint
51-
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
51+
DETAIL: Table "trunc_b" references "truncate_a".
5252
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
5353
TRUNCATE TABLE trunc_c;-- fail
5454
ERROR: cannot truncate a table referenced in a foreign key constraint
55-
DETAIL: Table "trunc_d" references "trunc_c" via foreign key constraint "trunc_d_a_fkey".
55+
DETAIL: Table "trunc_d" references "trunc_c".
5656
HINT: Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
5757
TRUNCATE TABLE trunc_c,trunc_d;-- fail
5858
ERROR: cannot truncate a table referenced in a foreign key constraint
59-
DETAIL: Table "trunc_e" references "trunc_c" via foreign key constraint "trunc_e_b_fkey".
59+
DETAIL: Table "trunc_e" references "trunc_c".
6060
HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
6161
TRUNCATE TABLE trunc_c,trunc_d,trunc_e;-- ok
6262
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;-- fail
6363
ERROR: cannot truncate a table referenced in a foreign key constraint
64-
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
64+
DETAIL: Table "trunc_b" references "truncate_a".
6565
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
6666
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;-- ok
6767
TRUNCATE TABLE truncate_a RESTRICT; -- fail
6868
ERROR: cannot truncate a table referenced in a foreign key constraint
69-
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
69+
DETAIL: Table "trunc_b" references "truncate_a".
7070
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
7171
TRUNCATE TABLE truncate_a CASCADE; -- ok
7272
NOTICE: truncate cascades to table "trunc_b"
@@ -81,21 +81,21 @@ INSERT INTO trunc_d VALUES (1);
8181
INSERT INTO trunc_e VALUES (1,1);
8282
TRUNCATE TABLE trunc_c;
8383
ERROR: cannot truncate a table referenced in a foreign key constraint
84-
DETAIL: Table "trunc_d" references "trunc_c" via foreign key constraint "trunc_d_a_fkey".
84+
DETAIL: Table "truncate_a" references "trunc_c".
85+
HINT: Truncate table "truncate_a" at the same time, or use TRUNCATE ... CASCADE.
86+
TRUNCATE TABLE trunc_c,truncate_a;
87+
ERROR: cannot truncate a table referenced in a foreign key constraint
88+
DETAIL: Table "trunc_d" references "trunc_c".
8589
HINT: Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
86-
TRUNCATE TABLE trunc_c,trunc_d;
90+
TRUNCATE TABLE trunc_c,truncate_a,trunc_d;
8791
ERROR: cannot truncate a table referenced in a foreign key constraint
88-
DETAIL: Table "trunc_e" references "trunc_c" via foreign key constraint "trunc_e_b_fkey".
92+
DETAIL: Table "trunc_e" references "trunc_c".
8993
HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
90-
TRUNCATE TABLE trunc_c,trunc_d,trunc_e;
91-
ERROR: cannot truncate a table referenced in a foreign key constraint
92-
DETAIL: Table "truncate_a" references "trunc_c" via foreign key constraint "truncate_a_col1_fkey".
93-
HINT: Truncate table "truncate_a" at the same time, or use TRUNCATE ... CASCADE.
94-
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;
94+
TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e;
9595
ERROR: cannot truncate a table referenced in a foreign key constraint
96-
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
96+
DETAIL: Table "trunc_b" references "truncate_a".
9797
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
98-
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;
98+
TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e,trunc_b;
9999
-- Verify that truncating did actually work
100100
SELECT * FROM truncate_a
101101
UNION ALL
@@ -120,9 +120,9 @@ INSERT INTO trunc_b VALUES (1);
120120
INSERT INTO trunc_d VALUES (1);
121121
INSERT INTO trunc_e VALUES (1,1);
122122
TRUNCATE TABLE trunc_c CASCADE; -- ok
123+
NOTICE: truncate cascades to table "truncate_a"
123124
NOTICE: truncate cascades to table "trunc_d"
124125
NOTICE: truncate cascades to table "trunc_e"
125-
NOTICE: truncate cascades to table "truncate_a"
126126
NOTICE: truncate cascades to table "trunc_b"
127127
SELECT * FROM truncate_a
128128
UNION ALL

‎src/test/regress/sql/truncate.sql‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -43,10 +43,10 @@ INSERT INTO trunc_b VALUES (1);
4343
INSERT INTO trunc_dVALUES (1);
4444
INSERT INTO trunc_eVALUES (1,1);
4545
TRUNCATE TABLE trunc_c;
46-
TRUNCATE TABLE trunc_c,trunc_d;
47-
TRUNCATE TABLE trunc_c,trunc_d,trunc_e;
48-
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;
49-
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;
46+
TRUNCATE TABLE trunc_c,truncate_a;
47+
TRUNCATE TABLE trunc_c,truncate_a,trunc_d;
48+
TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e;
49+
TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e,trunc_b;
5050

5151
-- Verify that truncating did actually work
5252
SELECT*FROM truncate_a

0 commit comments

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