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

Commita13b018

Browse files
committed
Disallow foreign-key references from temp tables to permanent tables.
Per recent discussion, this does not work because other backends can'treliably see tuples in a temp table and so cannot run the RI checkscorrectly. Seems better to disallow this case than go back to accessingtemp tables through shared buffers. Also, disallow FK references toON COMMIT DELETE ROWS tables. We already caught this problem for normalTRUNCATE, but the path used by ON COMMIT didn't check.
1 parentfc6b7c5 commita13b018

File tree

5 files changed

+107
-49
lines changed

5 files changed

+107
-49
lines changed

‎doc/src/sgml/ref/truncate.sgml

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/truncate.sgml,v 1.13 2003/09/12 00:12:47 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/truncate.sgml,v 1.14 2003/09/19 21:04:19 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -50,6 +50,16 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable>
5050
</variablelist>
5151
</refsect1>
5252

53+
<refsect1>
54+
<title>Notes</title>
55+
56+
<para>
57+
<command>TRUNCATE</> cannot be used if there are foreign-key references
58+
to the table from other tables. Checking validity in such cases would
59+
require table scans, and the whole point is not to do one.
60+
</para>
61+
</refsect1>
62+
5363
<refsect1>
5464
<title>Examples</title>
5565

‎src/backend/catalog/heap.c

Lines changed: 67 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.251 2003/08/04 02:39:58 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.252 2003/09/19 21:04:19 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -1966,9 +1966,11 @@ RelationTruncateIndexes(Oid heapId)
19661966
/*
19671967
* heap_truncate
19681968
*
1969-
* This routine is used to truncate the data from the
1970-
* storage manager of any data within the relation handed
1971-
* to this routine. This is not transaction-safe!
1969+
* This routine deletes all data within the specified relation.
1970+
*
1971+
* This is not transaction-safe! There is another, transaction-safe
1972+
* implementation in commands/cluster.c. We now use this only for
1973+
* ON COMMIT truncation of temporary tables, where it doesn't matter.
19721974
*/
19731975
void
19741976
heap_truncate(Oidrid)
@@ -1979,6 +1981,9 @@ heap_truncate(Oid rid)
19791981
/* Open relation for processing, and grab exclusive access on it. */
19801982
rel=heap_open(rid,AccessExclusiveLock);
19811983

1984+
/* Don't allow truncate on tables that are referenced by foreign keys */
1985+
heap_truncate_check_FKs(rel);
1986+
19821987
/*
19831988
* Release any buffers associated with this relation. If they're
19841989
* dirty, they're just dropped without bothering to flush to disk.
@@ -2003,3 +2008,61 @@ heap_truncate(Oid rid)
20032008
*/
20042009
heap_close(rel,NoLock);
20052010
}
2011+
2012+
/*
2013+
* heap_truncate_check_FKs
2014+
*Check for foreign keys referencing a relation that's to be truncated
2015+
*
2016+
* We disallow such FKs (except self-referential ones) since the whole point
2017+
* of TRUNCATE is to not scan the individual rows to be thrown away.
2018+
*
2019+
* This is split out so it can be shared by both implementations of truncate.
2020+
* Caller should already hold a suitable lock on the relation.
2021+
*/
2022+
void
2023+
heap_truncate_check_FKs(Relationrel)
2024+
{
2025+
Oidrelid=RelationGetRelid(rel);
2026+
ScanKeyDatakey;
2027+
RelationfkeyRel;
2028+
SysScanDescfkeyScan;
2029+
HeapTupletuple;
2030+
2031+
/*
2032+
* Fast path: if the relation has no triggers, it surely has no FKs
2033+
* either.
2034+
*/
2035+
if (rel->rd_rel->reltriggers==0)
2036+
return;
2037+
2038+
/*
2039+
* Otherwise, must scan pg_constraint. Right now, this is a seqscan
2040+
* because there is no available index on confrelid.
2041+
*/
2042+
fkeyRel=heap_openr(ConstraintRelationName,AccessShareLock);
2043+
2044+
ScanKeyEntryInitialize(&key,0,
2045+
Anum_pg_constraint_confrelid,
2046+
F_OIDEQ,
2047+
ObjectIdGetDatum(relid));
2048+
2049+
fkeyScan=systable_beginscan(fkeyRel,NULL, false,
2050+
SnapshotNow,1,&key);
2051+
2052+
while (HeapTupleIsValid(tuple=systable_getnext(fkeyScan)))
2053+
{
2054+
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tuple);
2055+
2056+
if (con->contype==CONSTRAINT_FOREIGN&&con->conrelid!=relid)
2057+
ereport(ERROR,
2058+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2059+
errmsg("cannot truncate a table referenced in a foreign key constraint"),
2060+
errdetail("Table \"%s\" references \"%s\" via foreign key constraint \"%s\".",
2061+
get_rel_name(con->conrelid),
2062+
RelationGetRelationName(rel),
2063+
NameStr(con->conname))));
2064+
}
2065+
2066+
systable_endscan(fkeyScan);
2067+
heap_close(fkeyRel,AccessShareLock);
2068+
}

‎src/backend/commands/tablecmds.c

Lines changed: 25 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.81 2003/09/15 00:26:31 petere Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.82 2003/09/19 21:04:20 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -365,15 +365,9 @@ void
365365
TruncateRelation(constRangeVar*relation)
366366
{
367367
Relationrel;
368-
Oidrelid;
369-
ScanKeyDatakey;
370-
RelationfkeyRel;
371-
SysScanDescfkeyScan;
372-
HeapTupletuple;
373368

374369
/* Grab exclusive lock in preparation for truncate */
375370
rel=heap_openrv(relation,AccessExclusiveLock);
376-
relid=RelationGetRelid(rel);
377371

378372
/* Only allow truncate on regular tables */
379373
if (rel->rd_rel->relkind!=RELKIND_RELATION)
@@ -383,7 +377,7 @@ TruncateRelation(const RangeVar *relation)
383377
RelationGetRelationName(rel))));
384378

385379
/* Permissions checks */
386-
if (!pg_class_ownercheck(relid,GetUserId()))
380+
if (!pg_class_ownercheck(RelationGetRelid(rel),GetUserId()))
387381
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_CLASS,
388382
RelationGetRelationName(rel));
389383

@@ -405,35 +399,7 @@ TruncateRelation(const RangeVar *relation)
405399
/*
406400
* Don't allow truncate on tables which are referenced by foreign keys
407401
*/
408-
fkeyRel=heap_openr(ConstraintRelationName,AccessShareLock);
409-
410-
ScanKeyEntryInitialize(&key,0,
411-
Anum_pg_constraint_confrelid,
412-
F_OIDEQ,
413-
ObjectIdGetDatum(relid));
414-
415-
fkeyScan=systable_beginscan(fkeyRel,0, false,
416-
SnapshotNow,1,&key);
417-
418-
/*
419-
* First foreign key found with us as the reference should throw an
420-
* error.
421-
*/
422-
while (HeapTupleIsValid(tuple=systable_getnext(fkeyScan)))
423-
{
424-
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tuple);
425-
426-
if (con->contype=='f'&&con->conrelid!=relid)
427-
ereport(ERROR,
428-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
429-
errmsg("cannot truncate a table referenced in a foreign key constraint"),
430-
errdetail("Table \"%s\" references this one via foreign key constraint \"%s\".",
431-
get_rel_name(con->conrelid),
432-
NameStr(con->conname))));
433-
}
434-
435-
systable_endscan(fkeyScan);
436-
heap_close(fkeyRel,AccessShareLock);
402+
heap_truncate_check_FKs(rel);
437403

438404
/*
439405
* Do the real work using the same technique as cluster, but without
@@ -3137,11 +3103,28 @@ AlterTableAddForeignKeyConstraint(Relation rel, FkConstraint *fkconstraint)
31373103
aclcheck_error(aclresult,ACL_KIND_CLASS,
31383104
RelationGetRelationName(rel));
31393105

3140-
if (isTempNamespace(RelationGetNamespace(pkrel))&&
3141-
!isTempNamespace(RelationGetNamespace(rel)))
3142-
ereport(ERROR,
3143-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
3144-
errmsg("cannot reference temporary table from permanent table constraint")));
3106+
/*
3107+
* Disallow reference from permanent table to temp table or vice versa.
3108+
* (The ban on perm->temp is for fairly obvious reasons. The ban on
3109+
* temp->perm is because other backends might need to run the RI triggers
3110+
* on the perm table, but they can't reliably see tuples the owning
3111+
* backend has created in the temp table, because non-shared buffers
3112+
* are used for temp tables.)
3113+
*/
3114+
if (isTempNamespace(RelationGetNamespace(pkrel)))
3115+
{
3116+
if (!isTempNamespace(RelationGetNamespace(rel)))
3117+
ereport(ERROR,
3118+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
3119+
errmsg("cannot reference temporary table from permanent table constraint")));
3120+
}
3121+
else
3122+
{
3123+
if (isTempNamespace(RelationGetNamespace(rel)))
3124+
ereport(ERROR,
3125+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
3126+
errmsg("cannot reference permanent table from temporary table constraint")));
3127+
}
31453128

31463129
/*
31473130
* Look up the referencing attributes to make sure they exist, and

‎src/include/catalog/heap.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: heap.h,v 1.61 2003/08/04 02:40:10 momjian Exp $
10+
* $Id: heap.h,v 1.62 2003/09/19 21:04:20 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -48,6 +48,8 @@ extern void heap_drop_with_catalog(Oid rid);
4848

4949
externvoidheap_truncate(Oidrid);
5050

51+
externvoidheap_truncate_check_FKs(Relationrel);
52+
5153
externvoidAddRelationRawConstraints(Relationrel,
5254
List*rawColDefaults,
5355
List*rawConstraints);

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,7 @@ SELECT * FROM truncate_a;
4242

4343
TRUNCATE truncate_a;
4444
ERROR: cannot truncate a table referenced in a foreign key constraint
45-
DETAIL: Table "truncate_b" referencesthis one via foreign key constraint "$1".
45+
DETAIL: Table "truncate_b" references"truncate_a" via foreign key constraint "$1".
4646
SELECT * FROM truncate_a;
4747
col1
4848
------

0 commit comments

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