RE: Partition Check not updated when insert into a partition - Mailing list pgsql-hackers

From houzj.fnst@fujitsu.com
Subject RE: Partition Check not updated when insert into a partition
Date
Msg-id OS3PR01MB5718E0162180B0466742C98394089@OS3PR01MB5718.jpnprd01.prod.outlook.com
Whole thread Raw
In response to Partition Check not updated when insert into a partition  ("houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>)
List pgsql-hackers
On Wednesday, June 23, 2021 12:16 PM I wrote:
> When directly INSERT INTO partition, postgres will invoke ExecPartitionCheck
> which will execute its parent's and grandparent's partition constraint check.
> From the code, the whole constraint check is saved in relcache::rd_partcheck.
>
> For a multi-level partition, for example: table 'A' is partition of table 'B', and 'B'
> is also partition of table 'C'. After I 'ALTER TABLE C DETACH B', I thought
> partition constraint check of table 'C' does not matter anymore if INSERT INTO
> table 'A'. But it looks like the relcache of 'A' is not invalidated after detaching 'B'.
> And the relcache::rd_partcheck still include the partition constraint of table 'C'.
> Note If I invalidate the table 'A''s relcache manually, then next time the
> relcache::rd_partcheck will be updated to the expected one which does not
> include partition constraint check of table 'C'.
> (ATTACH partition has the same behaviour that relcache::rd_partcheck will not
> be updated immediately)

An DETACH PARTITION example which shows the relcache::rd_partcheck
is not invalidated immediately is:

----- parttable1 -> parttable2-> parttable3
create table parttable1 (a int, b int, c int) partition by list(a);
create table parttable2 (a int, b int, c int) partition by list(b);
create table parttable3 (a int, b int, c int);
alter table parttable1 attach partition parttable2 for values in (1);
alter table parttable2 attach partition parttable3 for values in (1);

-----
-----INSERT a tuple into parttable3 which does not satisfy parttable1's partition constraint
-----we will get an error
-----
insert into parttable3 values(2,1,1);
ERROR:  new row for relation "parttable3" violates partition constraint
DETAIL:  Failing row contains (2, 1, 1).

-----
----- parttable1 is no longer the grandparent of parttable3.
----- I thought the partition constraint of parttable1 does not matter anymore
-----
alter table parttable1 detach partition parttable2;

-----
-----INSERT a tuple into parttable3 which does not satisfy parttable1's partition constraint
----- *** I expect a successful insertion, but it returns an error again. ***
-----
insert into parttable3 values(2,1,1);
ERROR:  new row for relation "parttable3" violates partition constraint
DETAIL:  Failing row contains (2, 1, 1).

RECONNECT
-----
-----Reconnect the postgres which will invalidate the relcache
----- INSERT a tuple into parttable3 which does not satisfy parttable1's partition constraint
----- We succeeded this time as expected.
-----
insert into parttable3 values(2,1,1);
INSERT 0 1


Best regards,
houzj





pgsql-hackers by date:

Previous
From: Nicolas CHAHWEKILIAN
Date:
Subject: Re: [PATCH] Hooks at XactCommand level
Next
From: Michael Paquier
Date:
Subject: Re: [PATCH] Pull general SASL framework out of SCRAM