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