Re: Partition Check not updated when insert into a partition

Sorry that I missed this thread.

On Wed, Jul 14, 2021 at 11:16 AM houzj(dot)fnst(at)fujitsu(dot)com
<houzj(dot)fnst(at)fujitsu(dot)com> wrote:
> 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.

Thank you both.

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

Yeah, need the fix for the ATTACH case too.

Couple more things:

* We must invalidate not just the "direct" partitions of the table
being attached/detached, but also any indirect ones, because all of
their partition constraints would need to contain (or no longer
contain) the root parent's partition constraint.

* I think we should lock the partitions before sending the
invalidation. The ATTACH code already locks the descendents for a
different purpose, but DETACH doesn't, so the latter needs to be fixed
to match.

I've updated Alvaro's patch to address these points. Maybe, we should
also add these cases to the regression and isolation suites?

--
Amit Langote
EDB: http://www.enterprisedb.com