Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4

From: Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
Date: 2009-07-14 14:34:48
Message-ID: 200907141634.49128.ftm.van.vugt@foxi.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

> Hmph. So evidently the unexpected recursion into SPI is happening when
> a cached plan has to be recomputed. I suspected something of the sort,
> but now the question is why you didn't see the same problem in 8.3 ...

Just as an additional confirmation: nothing else but the db changed when we
migrated from 8.3.7 to 8.4.0 last weekend

Anything else I can do to assist?

FYI:

- the production site today ran into this problem just over a thousand times
resulting in the same number of rollbacks and reconnects (since that solves it
for a little while again), so needless to say there's some additional
motivation to help ;)

- with relation to recomputation of cached plans: our usage patterns basically
do not include DDL, with the exception of temp tables; the functions that bail
are all using a single table each, the records of which are not changing in
any way

- we use before/after and deferred triggers, all in sql or pgsql; we use a
single after-trigger written in C for keeping track of history, which does its
own SPI_connect/finish calls, but as stated earlier, all of this was in place
a long time when we were running 8.3.7, though obviously the C-trigger has
been recompiled

- the distribution over triggers / functions that run into this error seems
uneven, basically they all boil down to a couple of custom functions, the
specifics of which I described earlier (same structure, immutable, use
exception thus an extra begin/end block, etc), but since we have loads of
other simular functions that don't come up, it's hard to see what would be
special here

Depending on the other idea's you might have, we could fairly easily
temporarily redefine one or more of the pgsql functions that suffer from this
error as plain sql functions, just to see what would happen.......?
(I'm reaching, I know ;) )

--
Best,

Frank.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2009-07-14 14:42:09 Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
Previous Message Tom Lane 2009-07-14 14:01:51 Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4