From: Michael Paquier <michael@paquier.xyz>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: TRUNCATE tables referenced by FKs on partitioned tables
Date: Wed, 11 Jul 2018 16:16:47 +0900
Message-ID: <20180711071647.GC14301@paquier.xyz> (raw)
In-Reply-To: <20180711000624.zmeizicibxeehhsg@alvherre.pgsql>
References: <20180711000624.zmeizicibxeehhsg@alvherre.pgsql>
On Tue, Jul 10, 2018 at 08:06:24PM -0400, Alvaro Herrera wrote:
> 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.
You mean that instead:
=# truncate table prim;
ERROR: 0A000: cannot truncate a table referenced in a foreign key
constraint
DETAIL: Table "partfk" references "prim".
HINT: Truncate table "partfk" at the same time, or use TRUNCATE
... CASCADE.
LOCATION: heap_truncate_check_FKs, heap.c:3245
I agree that this should be an error.
> 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.
Your first and second queries are the same :)
And those ones work:
=# truncate table partfk;
TRUNCATE TABLE
=# truncate table partfk, partfk1;
TRUNCATE TABLE
=# truncate table partfk, partfk1, partfk2;
TRUNCATE TABLE
=# truncate table partfk, partfk2;
TRUNCATE TABLE
> 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.
Yes, I would expect this one to pass.
> 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.
And this one as well.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
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: michael@paquier.xyz, alvherre@2ndquadrant.com
Subject: Re: TRUNCATE tables referenced by FKs on partitioned tables
In-Reply-To: <20180711071647.GC14301@paquier.xyz>
* 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