From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: TRUNCATE tables referenced by FKs on partitioned tables
Date: Tue, 10 Jul 2018 20:06:24 -0400
Message-ID: <20180711000624.zmeizicibxeehhsg@alvherre.pgsql> (raw)
$subject is broken:
create table prim (a int primary key);
create table partfk (a int references prim) partition by range (a);
create table partfk1 partition of partfk for values from (0) to (100);
create table partfk2 partition of partfk for values from (100) to (200);
You can't truncate prim on its own. This is expected.
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.
However, you can't do it even if you try to include partfk in the mix:
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.
Trying to list all the partitions individually is pointless:
alvherre=# truncate table prim, partfk, partfk1, partfk2;
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.
CASCADE is also useless:
alvherre=# truncate table prim cascade;
NOTICE: truncate cascades to table "partfk"
NOTICE: truncate cascades to table "partfk1"
NOTICE: truncate cascades to table "partfk2"
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.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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
Subject: Re: TRUNCATE tables referenced by FKs on partitioned tables
In-Reply-To: <20180711000624.zmeizicibxeehhsg@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