Re: simplifying foreign key/RI checks

Kuroda-san,

On Mon, Jan 25, 2021 at 6:06 PM Keisuke Kuroda
<keisuke(dot)kuroda(dot)3862(at)gmail(dot)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 far less 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