TRUNCATE tables referenced by FKs on partitioned tables
$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
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
On 2018-Jul-11, Michael Paquier wrote:
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 :)
Yeah, C&P failure :-(
Anyway, this patch seems to fix it, and adds what I think is appropriate
test coverage.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-fix-truncate.patchtext/plain; charset=us-asciiDownload+128-4
On Wed, Jul 11, 2018 at 06:59:16PM -0400, Alvaro Herrera wrote:
Anyway, this patch seems to fix it, and adds what I think is appropriate
test coverage.
This looks good to me. I am noticing that the documentation of TRUNCATE
does not mention that when running the command on a partitioned table
then it automatically gets to the children even if CASCADE is not used
and each child partition is not listed.
What is the filler column added in truncpart used for?
--
Michael
On 2018-Jul-12, Michael Paquier wrote:
On Wed, Jul 11, 2018 at 06:59:16PM -0400, Alvaro Herrera wrote:
Anyway, this patch seems to fix it, and adds what I think is appropriate
test coverage.This looks good to me. I am noticing that the documentation of TRUNCATE
does not mention that when running the command on a partitioned table
then it automatically gets to the children even if CASCADE is not used
and each child partition is not listed.
Hmm ... well, that's not new -- I think that came in with pg10.
What is the filler column added in truncpart used for?
Nothing. Also column b -- I had an additional different test, but then
I discovered it wasn't testing anything new. Removed both.
Pushed, thanks for looking!
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services