RE: Partition Check not updated when insert into a partition

On Tuesday, July 13, 2021 2:52 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> On 2021-Jun-23, houzj(dot)fnst(at)fujitsu(dot)com wrote:
>
> > 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'.
>
> Hmm, if I understand correctly, this means that we need to invalidate relcache
> for all partitions of the partition being detached. Maybe like in the attached
> WIP ("XXX VERY CRUDE XXX DANGER EATS DATA") patch, which solves what
> you complained about, but I didn't run any other tests.
> (Also, in the concurrent case I think this should be done during the first
> transaction, so this patch is wrong for it.)
>
> Did you have a misbehaving test for the ATTACH case?

Thanks for the response.

Yes, I think the following example of ATTACH doesn't work as expected.

---------------------------------------------------------------
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 parttable2 attach partition parttable3 for values in (1);

-----
----- INSERT a tuple into parttable3
----- Cache the partitioncheck in relcache::rd_partcheck
-----
insert into parttable3 values(1, 1, 0);

----- Attach a new top parent
alter table parttable1 attach partition parttable2 for values in (1);

-----
----- INSERT a tuple which doesn't satisfy the new top parent(parttable1)'s partitioncheck
----- But the INSERT will succeed which looks not as expected.
-----
insert into parttable3 values(999, 1, 0);

-----
----- And when I reconnect to clean the cache
----- INSERT a tuple which doesn't satisfy the new top parent(parttable1)'s partitioncheck
----- INSERT will fail due to partition check violation.
-----
insert into parttable3 values(999, 1, 0);

Best regards,
Hou zhijie