Re: strange OOM errors with EXECUTE in PL/pgSQL

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> On 12/20/2012 4:47 PM, Dimitri Fontaine wrote:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> The reason this fails is that you've got a half-megabyte source string,
>>> and each of the 11000 plans that are due to be created from it saves
>>> its own copy of the source string. Hence, 5500 megabytes needed just
>>> for source strings.
>>>
>>> We could possibly fix this by inventing some sort of reference-sharing
>>> arrangement (which'd be complicated and fragile) or by not storing the
>>> source strings with the plans (which'd deal a serious blow to our
>>> ability to provide helpful error messages). Neither answer seems
>>> appealing.

> Don't all the plans result as a plan list from a multi-statement query
> string, which was parsed into a query tree "list" and each single query
> tree then planned? I don't think there is any way that a single one of
> those trees (parse or plan) will be free'd separately. If that is true,
> then proper usage of memory contexts would make reference counting
> obsolete, even though all plans refer to the same copy.

The issue is that a multi-statement string gives rise to multiple
CachedPlanSources, which could be freed independently so far as
plancache.c knows. (spi.c wouldn't actually attempt to do so.)
So you'd really need reference counting, or else some explicit
connection between the CachedPlanSources, neither of which seems
exactly trivial to me.

As of HEAD this particular complaint is moot anyway, because SPI_execute
now goes through the "one-shot CachedPlan" facility, and so it makes no
(zero) copies of the source string.

It'd still be possible to hit the problem when trying to SPI_prepare a
very-many-statement string, but I think the use case for that is pretty
darn small. So I'm not excited about adding complication to fix it.

regards, tom lane