Re: simplifying foreign key/RI checks

Kuroda-san,

On Mon, Jan 25, 2021 at 6:06 PM Keisuke Kuroda
<keisuke.kuroda.3862@gmail.com> wrote:
> Hi, Amit-san,
>
> Nice patch. I have confirmed that this solves the problem in [1] with
> INSERT/UPDATE.

Thanks for testing.

> HEAD + patch
>        name       | bytes | pg_size_pretty
> ------------------+-------+----------------
>  CachedPlanQuery  | 10280 | 10 kB
>  CachedPlanSource | 14616 | 14 kB
>  CachedPlan       | 13168 | 13 kB ★ 710MB -> 13kB
> (3 rows)

If you only tested insert/update on the referencing table, I would've
expected to see nothing in the result of that query, because the patch
eliminates all use of SPI in that case.  I suspect the CachedPlan*
memory contexts you are seeing belong to some early activity in the
session.  So if you try the insert/update in a freshly started
session, you would see 0 rows in the result of that query.

> > > This patch completely sidesteps the DELETE case, which has more insidious performance implications, but is also
farless common, and whose solution will likely be very different. 
> >
> > Yeah, we should continue looking into the ways to make referenced-side
> > RI checks be less bloated.
>
> However, as already mentioned, the problem of memory bloat on DELETE remains.
> This can be solved by the patch in [1], but I think it is too much to apply
> this patch only for DELETE. What do you think?
>
> [1] https://www.postgresql.org/message-id/flat/cab4b85d-9292-967d-adf2-be0d803c3e23%40nttcom.co.jp_1

Hmm, the patch tries to solve a general problem that SPI plans are not
being shared among partitions whereas they should be.   So I don't
think that it's necessarily specific to DELETE.  Until we have a
solution like the patch on this thread for DELETE, it seems fine to
consider the other patch as a stopgap solution.

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