Re: TRUNCATE tables referenced by FKs on partitioned tables

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