From: | Michael Paquier <michael(at)paquier(dot)xyz> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: TRUNCATE tables referenced by FKs on partitioned tables |
Date: | 2018-07-11 07:16:47 |
Message-ID: | 20180711071647.GC14301@paquier.xyz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2018-07-11 07:26:49 | Re: In pageinspect, perform clean-up after testing gin-related functions |
Previous Message | Kuntal Ghosh | 2018-07-11 07:07:04 | In pageinspect, perform clean-up after testing gin-related functions |