diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/truncate.out | 54 | ||||
| -rw-r--r-- | src/test/regress/sql/truncate.sql | 21 |
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; |