Add CASCADE option to TRUNCATE. Joachim Wieland

2 files changed, 65 insertions, 10 deletions

diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index c03deef1e8..263c5c8e08 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -40,30 +40,37 @@ CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c);
 TRUNCATE TABLE truncate_a;		-- fail
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
-HINT:  Truncate table "trunc_b" at the same time.
+HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE truncate_a,trunc_b;		-- fail
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_e" references "truncate_a" via foreign key constraint "trunc_e_a_fkey".
-HINT:  Truncate table "trunc_e" at the same time.
+HINT:  Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE truncate_a,trunc_b,trunc_e;	-- ok
 TRUNCATE TABLE truncate_a,trunc_e;		-- fail
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
-HINT:  Truncate table "trunc_b" at the same time.
+HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE trunc_c;		-- fail
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_d" references "trunc_c" via foreign key constraint "trunc_d_a_fkey".
-HINT:  Truncate table "trunc_d" at the same time.
+HINT:  Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE trunc_c,trunc_d;		-- fail
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_e" references "trunc_c" via foreign key constraint "trunc_e_b_fkey".
-HINT:  Truncate table "trunc_e" at the same time.
+HINT:  Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE trunc_c,trunc_d,trunc_e;	-- ok
 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;	-- fail
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
-HINT:  Truncate table "trunc_b" at the same time.
+HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;	-- ok
+TRUNCATE TABLE truncate_a RESTRICT; -- fail
+ERROR:  cannot truncate a table referenced in a foreign key constraint
+DETAIL:  Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
+HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
+TRUNCATE TABLE truncate_a CASCADE;  -- ok
+NOTICE:  truncate cascades to table "trunc_b"
+NOTICE:  truncate cascades to table "trunc_e"
 -- circular references
 ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
 -- Add some data to verify that truncating actually works ...
@@ -75,19 +82,19 @@ INSERT INTO trunc_e VALUES (1,1);
 TRUNCATE TABLE trunc_c;
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_d" references "trunc_c" via foreign key constraint "trunc_d_a_fkey".
-HINT:  Truncate table "trunc_d" at the same time.
+HINT:  Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE trunc_c,trunc_d;
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_e" references "trunc_c" via foreign key constraint "trunc_e_b_fkey".
-HINT:  Truncate table "trunc_e" at the same time.
+HINT:  Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE trunc_c,trunc_d,trunc_e;
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "truncate_a" references "trunc_c" via foreign key constraint "truncate_a_col1_fkey".
-HINT:  Truncate table "truncate_a" at the same time.
+HINT:  Truncate table "truncate_a" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;
 ERROR:  cannot truncate a table referenced in a foreign key constraint
 DETAIL:  Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
-HINT:  Truncate table "trunc_b" at the same time.
+HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;
 -- Verify that truncating did actually work
 SELECT * FROM truncate_a
@@ -106,6 +113,33 @@ SELECT * FROM trunc_e;
 ---+---
 (0 rows)
 
+-- Add data again to test TRUNCATE ... CASCADE
+INSERT INTO trunc_c VALUES (1);
+INSERT INTO truncate_a VALUES (1);
+INSERT INTO trunc_b VALUES (1);
+INSERT INTO trunc_d VALUES (1);
+INSERT INTO trunc_e VALUES (1,1);
+TRUNCATE TABLE trunc_c CASCADE;  -- ok
+NOTICE:  truncate cascades to table "trunc_d"
+NOTICE:  truncate cascades to table "trunc_e"
+NOTICE:  truncate cascades to table "truncate_a"
+NOTICE:  truncate cascades to table "trunc_b"
+SELECT * FROM truncate_a
+   UNION ALL
+ SELECT * FROM trunc_c
+   UNION ALL
+ SELECT * FROM trunc_b
+   UNION ALL
+ SELECT * FROM trunc_d;
+ col1 
+------
+(0 rows)
+
+SELECT * FROM trunc_e;
+ a | b 
+---+---
+(0 rows)
+
 DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
 NOTICE:  drop cascades to constraint trunc_e_a_fkey on table trunc_e
 NOTICE:  drop cascades to constraint trunc_b_a_fkey on table trunc_b
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index 09a08e5652..a4d27d01e1 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -30,6 +30,9 @@ TRUNCATE TABLE trunc_c,trunc_d,trunc_e;	-- ok
 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;	-- fail
 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;	-- ok
 
+TRUNCATE TABLE truncate_a RESTRICT; -- fail
+TRUNCATE TABLE truncate_a CASCADE;  -- ok
+
 -- circular references
 ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
 
@@ -55,4 +58,22 @@ SELECT * FROM truncate_a
  SELECT * FROM trunc_d;
 SELECT * FROM trunc_e;
 
+-- Add data again to test TRUNCATE ... CASCADE
+INSERT INTO trunc_c VALUES (1);
+INSERT INTO truncate_a VALUES (1);
+INSERT INTO trunc_b VALUES (1);
+INSERT INTO trunc_d VALUES (1);
+INSERT INTO trunc_e VALUES (1,1);
+
+TRUNCATE TABLE trunc_c CASCADE;  -- ok
+
+SELECT * FROM truncate_a
+   UNION ALL
+ SELECT * FROM trunc_c
+   UNION ALL
+ SELECT * FROM trunc_b
+   UNION ALL
+ SELECT * FROM trunc_d;
+SELECT * FROM trunc_e;
+
 DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;