Skip to content

Commit a13b018

Browse files
tglsfdctglsfdc
committed
Disallow foreign-key references from temp tables to permanent tables.
Per recent discussion, this does not work because other backends can't reliably see tuples in a temp table and so cannot run the RI checks correctly. Seems better to disallow this case than go back to accessing temp tables through shared buffers. Also, disallow FK references to ON COMMIT DELETE ROWS tables. We already caught this problem for normal TRUNCATE, but the path used by ON COMMIT didn't check.
1 parent fc6b7c5 commit a13b018

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(Oid rid)
@@ -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(Relation rel)
2024+
{
2025+
Oid relid = RelationGetRelid(rel);
2026+
ScanKeyData key;
2027+
Relation fkeyRel;
2028+
SysScanDesc fkeyScan;
2029+
HeapTuple tuple;
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_constraint con = (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(const RangeVar *relation)
366366
{
367367
Relation rel;
368-
Oid relid;
369-
ScanKeyData key;
370-
Relation fkeyRel;
371-
SysScanDesc fkeyScan;
372-
HeapTuple tuple;
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_constraint con = (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
extern void heap_truncate(Oid rid);
5050

51+
extern void heap_truncate_check_FKs(Relation rel);
52+
5153
extern void AddRelationRawConstraints(Relation rel,
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" references this 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)