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

Commit47b37a6

Browse files
committed
# Disallow TRUNCATE on tables that are involved in referential
constraintsThe issue with finding and removing foreign key constraints is no longeran issue, so please apply the attached.It does NOT check for rules or on delete triggers (old style foreignkeys) as those are difficult to deal with (remove, truncate, re-add).Rod Taylor
1 parentdac22ee commit47b37a6

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
Relationrel;
334334
Oidrelid;
335+
ScanKeyDatakey;
336+
RelationfkeyRel;
337+
SysScanDescfkeyScan;
338+
HeapTupletuple;
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_constraintcon= (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

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+
CREATETABLEtruncate_a (col1integerprimary key);
3+
INSERT INTO truncate_aVALUES (1);
4+
INSERT INTO truncate_aVALUES (2);
5+
SELECT*FROM truncate_a;
6+
TRUNCATE truncate_a;
7+
SELECT*FROM truncate_a;
8+
9+
-- Test foreign constraint check
10+
CREATETABLEtruncate_b(col1integerreferences truncate_a);
11+
INSERT INTO truncate_aVALUES (1);
12+
SELECT*FROM truncate_a;
13+
TRUNCATE truncate_a;
14+
SELECT*FROM truncate_a;
15+
16+
DROPTABLE truncate_b;
17+
DROPTABLE truncate_a;

0 commit comments

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