Обсуждение: pgsql: Fix plpgsql to release SPI plans when a function or DO block is

Fix plpgsql to release SPI plans when a function or DO block is freed.

This fixes the gripe I made a few months ago about DO blocks getting
slower with repeated use.  At least, it fixes it for the case where
the DO block isn't aborted by an error.  We could try running
plpgsql_free_function_memory() even during error exit, but that seems
a bit scary since it makes a lot of presumptions about the data
structures being in good shape.  It's probably reasonable to assume
that repeated failures of DO blocks isn't a performance-critical case.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/87f2ad1326bff5cd37dde6fbf024137a2243efea

Modified Files
--------------
src/pl/plpgsql/src/pl_comp.c    |    7 +-
src/pl/plpgsql/src/pl_funcs.c   |  398 +++++++++++++++++++++++++++++++++++++++
src/pl/plpgsql/src/pl_handler.c |   10 +
src/pl/plpgsql/src/plpgsql.h    |    1 +
4 files changed, 411 insertions(+), 5 deletions(-)

On 27.03.2011 19:51, Tom Lane wrote:
> Fix plpgsql to release SPI plans when a function or DO block is freed.
>
> This fixes the gripe I made a few months ago about DO blocks getting
> slower with repeated use.  At least, it fixes it for the case where
> the DO block isn't aborted by an error.  We could try running
> plpgsql_free_function_memory() even during error exit, but that seems
> a bit scary since it makes a lot of presumptions about the data
> structures being in good shape.  It's probably reasonable to assume
> that repeated failures of DO blocks isn't a performance-critical case.

I was quite surprised by the way you did this. Instead of adding all
that code to traverse the PLpgSQL_stmt tree (that we'll have to remember
to keep up-to-date), can't we just have a list of cached plans in
PLpgSQL_function? As attached.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> On 27.03.2011 19:51, Tom Lane wrote:
>> Fix plpgsql to release SPI plans when a function or DO block is freed.

> I was quite surprised by the way you did this. Instead of adding all
> that code to traverse the PLpgSQL_stmt tree (that we'll have to remember
> to keep up-to-date), can't we just have a list of cached plans in
> PLpgSQL_function? As attached.

The code I added is a direct rip-off of the adjacent function tree
dumping code, which we've had no trouble keeping up-to-date.  (I checked
it as I went through, and it did not appear to be missing any cases.)
I'm unconvinced that adding a separate new mechanism to track the
function's SPI plans would be more easily maintainable or less prone to
oversights.  Also, what if we come up with another resource that has to
be explicitly released here?  The tree walk won't require additional
mechanism for that.

            regards, tom lane