On 2018-Jul-11, Michael Paquier wrote:
> > alvherre=# truncate table prim, partfk;
> > ERROR: cannot truncate a table referenced in a foreign key constraint
> > DETALLE: Table "partfk" references "prim".
> > SUGERENCIA: Truncate table "partfk" at the same time, or use TRUNCATE ...
> > CASCADE.
>
> Your first and second queries are the same :)
Yeah, C&P failure :-(
Anyway, this patch seems to fix it, and adds what I think is appropriate
test coverage.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 07500a1d7d7e6e2a79514672a7b6441781baa1da Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Wed, 11 Jul 2018 18:54:00 -0400
Subject: [PATCH] fix truncate
---
src/backend/catalog/heap.c | 7 +++-
src/backend/commands/tablecmds.c | 2 +-
src/test/regress/expected/truncate.out | 75 ++++++++++++++++++++++++++++++++++
src/test/regress/sql/truncate.sql | 47 +++++++++++++++++++++
4 files changed, 128 insertions(+), 3 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index d223ba8537..4cfc0c8911 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -3181,13 +3181,16 @@ heap_truncate_check_FKs(List *relations, bool
tempTables)
* Build a list of OIDs of the interesting relations.
*
* If a relation has no triggers, then it can neither have FKs nor be
- * referenced by a FK from another table, so we can ignore it.
+ * referenced by a FK from another table, so we can ignore it. For
+ * partitioned tables, FKs have no triggers, so we must include them
+ * anyway.
*/
foreach(cell, relations)
{
Relation rel = lfirst(cell);
- if (rel->rd_rel->relhastriggers)
+ if (rel->rd_rel->relhastriggers ||
+ rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
oids = lappend_oid(oids, RelationGetRelid(rel));
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 7c0cf0d7ee..22e81e712d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1421,7 +1421,7 @@ ExecuteTruncateGuts(List *explicit_rels, List *relids,
List *relids_logged,
Oid *logrelids;
/*
- * Open, exclusive-lock, and check all the explicitly-specified
relations
+ * Check the explicitly-specified relations.
*
* In CASCADE mode, suck in all referencing relations as well. This
* requires multiple iterations to find indirectly-dependent relations.
At
diff --git a/src/test/regress/expected/truncate.out
b/src/test/regress/expected/truncate.out
index 735d0e862d..0c2ba173db 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -464,3 +464,78 @@ ERROR: cannot truncate only a partitioned table
HINT: Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions
directly.
TRUNCATE truncparted;
DROP TABLE truncparted;
+-- foreign key on partitioned table: partition key is referencing column.
+-- Make sure truncate did execute on all tables
+CREATE FUNCTION tp_ins_data() RETURNS void LANGUAGE plpgsql AS $$
+ BEGIN
+ INSERT INTO truncprim VALUES (1), (100), (150);
+ INSERT INTO truncpart VALUES (1, 1), (100, 100), (150, 150);
+ END
+$$;
+CREATE FUNCTION tp_chk_data(OUT pktb regclass, OUT pkval int, OUT fktb
regclass, OUT fkval int)
+ RETURNS SETOF record LANGUAGE plpgsql AS $$
+ BEGIN
+ RETURN QUERY SELECT
+ pk.tableoid::regclass, pk.a, fk.tableoid::regclass, fk.a
+ FROM truncprim pk FULL JOIN truncpart fk USING (a)
+ ORDER BY 2, 4;
+ END
+$$;
+CREATE TABLE truncprim (a int PRIMARY KEY);
+CREATE TABLE truncpart (a int REFERENCES truncprim, b int, filler text)
+ PARTITION BY RANGE (a);
+CREATE TABLE truncpart_1 PARTITION OF truncpart FOR VALUES FROM (0) TO (100);
+CREATE TABLE truncpart_2 PARTITION OF truncpart FOR VALUES FROM (100) TO (200)
+ PARTITION BY RANGE (a);
+CREATE TABLE truncpart_2_1 PARTITION OF truncpart_2 FOR VALUES FROM (100) TO
(150);
+CREATE TABLE truncpart_2_d PARTITION OF truncpart_2 DEFAULT;
+TRUNCATE TABLE truncprim; -- should fail
+ERROR: cannot truncate a table referenced in a foreign key constraint
+DETAIL: Table "truncpart" references "truncprim".
+HINT: Truncate table "truncpart" at the same time, or use TRUNCATE ...
CASCADE.
+select tp_ins_data();
+ tp_ins_data
+-------------
+
+(1 row)
+
+-- should truncate everything
+TRUNCATE TABLE truncprim, truncpart;
+select * from tp_chk_data();
+ pktb | pkval | fktb | fkval
+------+-------+------+-------
+(0 rows)
+
+select tp_ins_data();
+ tp_ins_data
+-------------
+
+(1 row)
+
+-- should truncate everything
+SET client_min_messages TO WARNING; -- suppress cascading notices
+TRUNCATE TABLE truncprim CASCADE;
+RESET client_min_messages;
+SELECT * FROM tp_chk_data();
+ pktb | pkval | fktb | fkval
+------+-------+------+-------
+(0 rows)
+
+SELECT tp_ins_data();
+ tp_ins_data
+-------------
+
+(1 row)
+
+-- should truncate all partitions
+TRUNCATE TABLE truncpart;
+SELECT * FROM tp_chk_data();
+ pktb | pkval | fktb | fkval
+-----------+-------+------+-------
+ truncprim | 1 | |
+ truncprim | 100 | |
+ truncprim | 150 | |
+(3 rows)
+
+DROP TABLE truncprim, truncpart;
+DROP FUNCTION tp_ins_data(), tp_chk_data();
diff --git a/src/test/regress/sql/truncate.sql
b/src/test/regress/sql/truncate.sql
index fbd1d1a8a5..bfa73f043b 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -244,3 +244,50 @@ INSERT INTO truncparted VALUES (1, 'a');
TRUNCATE ONLY truncparted;
TRUNCATE truncparted;
DROP TABLE truncparted;
+
+-- foreign key on partitioned table: partition key is referencing column.
+-- Make sure truncate did execute on all tables
+CREATE FUNCTION tp_ins_data() RETURNS void LANGUAGE plpgsql AS $$
+ BEGIN
+ INSERT INTO truncprim VALUES (1), (100), (150);
+ INSERT INTO truncpart VALUES (1, 1), (100, 100), (150, 150);
+ END
+$$;
+CREATE FUNCTION tp_chk_data(OUT pktb regclass, OUT pkval int, OUT fktb
regclass, OUT fkval int)
+ RETURNS SETOF record LANGUAGE plpgsql AS $$
+ BEGIN
+ RETURN QUERY SELECT
+ pk.tableoid::regclass, pk.a, fk.tableoid::regclass, fk.a
+ FROM truncprim pk FULL JOIN truncpart fk USING (a)
+ ORDER BY 2, 4;
+ END
+$$;
+CREATE TABLE truncprim (a int PRIMARY KEY);
+CREATE TABLE truncpart (a int REFERENCES truncprim, b int, filler text)
+ PARTITION BY RANGE (a);
+CREATE TABLE truncpart_1 PARTITION OF truncpart FOR VALUES FROM (0) TO (100);
+CREATE TABLE truncpart_2 PARTITION OF truncpart FOR VALUES FROM (100) TO (200)
+ PARTITION BY RANGE (a);
+CREATE TABLE truncpart_2_1 PARTITION OF truncpart_2 FOR VALUES FROM (100) TO
(150);
+CREATE TABLE truncpart_2_d PARTITION OF truncpart_2 DEFAULT;
+
+TRUNCATE TABLE truncprim; -- should fail
+
+select tp_ins_data();
+-- should truncate everything
+TRUNCATE TABLE truncprim, truncpart;
+select * from tp_chk_data();
+
+select tp_ins_data();
+-- should truncate everything
+SET client_min_messages TO WARNING; -- suppress cascading notices
+TRUNCATE TABLE truncprim CASCADE;
+RESET client_min_messages;
+SELECT * FROM tp_chk_data();
+
+SELECT tp_ins_data();
+-- should truncate all partitions
+TRUNCATE TABLE truncpart;
+SELECT * FROM tp_chk_data();
+DROP TABLE truncprim, truncpart;
+DROP FUNCTION tp_ins_data(), tp_chk_data();
--
2.11.0
