Horton

Thread

Commits

  1. Fix FK checks of TRUNCATE involving partitioned tables

  1. TRUNCATE tables referenced by FKs on partitioned tables

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-07-11T00:06:24Z

    $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

  2. Re: TRUNCATE tables referenced by FKs on partitioned tables

    Michael Paquier <michael@paquier.xyz> — 2018-07-11T07:16:47Z

    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

  3. Re: TRUNCATE tables referenced by FKs on partitioned tables

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-07-11T22:59:16Z

    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

  4. Re: TRUNCATE tables referenced by FKs on partitioned tables

    Michael Paquier <michael@paquier.xyz> — 2018-07-12T02:53:08Z

    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

  5. Re: TRUNCATE tables referenced by FKs on partitioned tables

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-07-12T16:29:48Z

    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