Re: Release SPI plans for referential integrity with DISCARD ALL

From: yuzuko <yuzukohosoya(at)gmail(dot)com>
To: Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Release SPI plans for referential integrity with DISCARD ALL
Date: 2021-03-10 08:19:13
Message-ID: CAKkQ508aO3=xCabb1=0z95Kz9Mu=VBYWFN2R=jgrOWw3Us8grg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I thought about this suggestion again.

Amit's patch suggested in the thread [1] can eliminate SPI plans from
INSERT/UPDATE triggers, so our memory pressure issue would be solved.
But as far as I can see that thread, Amit's patch doesn't cover DELETE case.
It is not a common case, but there is a risk of pressing memory
capacity extremely.
Considering that, this suggestion might still have good value as Corey
said before.

I improved a patch according to Peter's following comment :
> but I think the
> solution of dropping all cached plans as part of DISCARD ALL seems a bit
> too extreme of a solution. In the context of connection pooling,
> getting a new session with pre-cached plans seems like a good thing, and
> this change could potentially have a performance impact without a
> practical way to opt out.

In a new patch, I separated discarding SPI Plan caches for RI from DISCARD ALL
and added a new option "RIPLANS" to DISCARD command to do that. Also a new
function, ResetRIPlanCache() which clears SPI plan caches is called by
DISCARD ALL
or DISCARD RIPLANS. The amount of modification is small and this option can be
removed instantly when it is no longer needed, so I think we can use
this patch as
a provisional solution.

Any thoughts?

[1] https://www.postgresql.org/message-id/flat/CA%2BHiwqGkfJfYdeq5vHPh6eqPKjSbfpDDY%2Bj-kXYFePQedtSLeg%40mail.gmail.com

--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center

Attachment Content-Type Size
v2_discard_ri_spi_plans.patch application/octet-stream 6.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2021-03-10 08:21:53 Re: cryptohash: missing locking functions for OpenSSL <= 1.0.2?
Previous Message 杨逸存 2021-03-10 08:09:49 Queries for PostgreSQL DDL convert