Skip to content

Commit 47b37a6

Browse files
bmomjianbmomjian
committed
# Disallow TRUNCATE on tables that are involved in referential
constraints The issue with finding and removing foreign key constraints is no longer an issue, so please apply the attached. It does NOT check for rules or on delete triggers (old style foreign keys) as those are difficult to deal with (remove, truncate, re-add). Rod Taylor
1 parent dac22ee commit 47b37a6

File tree

5 files changed

+93
-3
lines changed

5 files changed

+93
-3
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 35 additions & 1 deletion
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.30 2002/08/19 15:08:46 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.31 2002/08/22 04:51:05 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -332,6 +332,10 @@ TruncateRelation(const RangeVar *relation)
332332
{
333333
Relation rel;
334334
Oid relid;
335+
ScanKeyData key;
336+
Relation fkeyRel;
337+
SysScanDesc fkeyScan;
338+
HeapTuple tuple;
335339

336340
/* Grab exclusive lock in preparation for truncate */
337341
rel = heap_openrv(relation, AccessExclusiveLock);
@@ -356,6 +360,36 @@ TruncateRelation(const RangeVar *relation)
356360
if (!pg_class_ownercheck(relid, GetUserId()))
357361
aclcheck_error(ACLCHECK_NOT_OWNER, RelationGetRelationName(rel));
358362

363+
/*
364+
* Don't allow truncate on tables which are referenced
365+
* by foreign keys
366+
*/
367+
fkeyRel = heap_openr(ConstraintRelationName, AccessShareLock);
368+
369+
ScanKeyEntryInitialize(&key, 0,
370+
Anum_pg_constraint_confrelid,
371+
F_OIDEQ,
372+
ObjectIdGetDatum(relid));
373+
374+
fkeyScan = systable_beginscan(fkeyRel, 0, false,
375+
SnapshotNow, 1, &key);
376+
377+
/*
378+
* First foriegn key found with us as the reference
379+
* should throw an error.
380+
*/
381+
while (HeapTupleIsValid(tuple = systable_getnext(fkeyScan)))
382+
{
383+
Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
384+
385+
if (con->contype == 'f')
386+
elog(ERROR, "TRUNCATE cannot be used as other tables reference this one via foreign key constraint %s",
387+
NameStr(con->conname));
388+
}
389+
390+
systable_endscan(fkeyScan);
391+
heap_close(fkeyRel, AccessShareLock);
392+
359393
/* Keep the lock until transaction commit */
360394
heap_close(rel, NoLock);
361395

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

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
-- Test basic TRUNCATE functionality.
2+
CREATE TABLE truncate_a (col1 integer primary key);
3+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'truncate_a_pkey' for table 'truncate_a'
4+
INSERT INTO truncate_a VALUES (1);
5+
INSERT INTO truncate_a VALUES (2);
6+
SELECT * FROM truncate_a;
7+
col1
8+
------
9+
1
10+
2
11+
(2 rows)
12+
13+
TRUNCATE truncate_a;
14+
SELECT * FROM truncate_a;
15+
col1
16+
------
17+
(0 rows)
18+
19+
-- Test foreign constraint check
20+
CREATE TABLE truncate_b(col1 integer references truncate_a);
21+
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
22+
INSERT INTO truncate_a VALUES (1);
23+
SELECT * FROM truncate_a;
24+
col1
25+
------
26+
1
27+
(1 row)
28+
29+
TRUNCATE truncate_a;
30+
ERROR: TRUNCATE cannot be used as other tables reference this one via foreign key constraint $1
31+
SELECT * FROM truncate_a;
32+
col1
33+
------
34+
1
35+
(1 row)
36+
37+
DROP TABLE truncate_b;
38+
DROP TABLE truncate_a;

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -74,4 +74,4 @@ test: select_views alter_table portals_p2 rules foreign_key cluster
7474
# The sixth group of parallel test
7575
# ----------
7676
# "plpgsql" cannot run concurrently with "rules"
77-
test: limit plpgsql temp domain rangefuncs copy2 conversion without_oid
77+
test: limit plpgsql temp domain rangefuncs copy2 conversion without_oid truncate

‎src/test/regress/serial_schedule

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.15 2002/08/11 02:06:32 tgl Exp $
1+
# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.16 2002/08/22 04:51:06 momjian Exp $
22
# This should probably be in an order similar to parallel_schedule.
33
test: boolean
44
test: char
@@ -88,3 +88,4 @@ test: domain
8888
test: rangefuncs
8989
test: without_oid
9090
test: conversion
91+
test: truncate

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
-- Test basic TRUNCATE functionality.
2+
CREATE TABLE truncate_a (col1 integer primary key);
3+
INSERT INTO truncate_a VALUES (1);
4+
INSERT INTO truncate_a VALUES (2);
5+
SELECT * FROM truncate_a;
6+
TRUNCATE truncate_a;
7+
SELECT * FROM truncate_a;
8+
9+
-- Test foreign constraint check
10+
CREATE TABLE truncate_b(col1 integer references truncate_a);
11+
INSERT INTO truncate_a VALUES (1);
12+
SELECT * FROM truncate_a;
13+
TRUNCATE truncate_a;
14+
SELECT * FROM truncate_a;
15+
16+
DROP TABLE truncate_b;
17+
DROP TABLE truncate_a;

0 commit comments

Comments
 (0)