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 failure :-(
Anyway, this patch seems to fix it, and adds what I think is appropriate
test coverage.
--
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 07500a1d7d7e6e2a79514672a7b6441781baa1da Mon Sep 17 00:00:00 2001
From: Alvaro Herrera
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)
{
Relationrel = 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
