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
