From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: TRUNCATE tables referenced by FKs on partitioned tables
Date: Wed, 11 Jul 2018 18:59:16 -0400
Message-ID: <20180711225916.domrlbkmukhrg6jg@alvherre.pgsql> (raw)
In-Reply-To: <20180711071647.GC14301@paquier.xyz>
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
Attachments:0001-fix-truncate.patch (text/plain, 6.7K) download
From 07500a1d7d7e6e2a79514672a7b6441781baa1da Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@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.cindex 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.cindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, michael@paquier.xyz
Subject: Re: TRUNCATE tables referenced by FKs on partitioned tables
In-Reply-To: <20180711225916.domrlbkmukhrg6jg@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox