TRUNCATE tables referenced by FKs on partitioned tables
Alvaro Herrera <alvherre@2ndquadrant.com>
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2018-07-11T00:06:24Z
Lists: pgsql-hackers
$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
Commits
-
Fix FK checks of TRUNCATE involving partitioned tables
- 727196ced242 11.0 landed
- cd073d8f7043 12.0 landed