Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4

Hello,
I have made an upgrade to PG 8.4 and following error was thrown during
execution of some pl/pgsql function:
ERROR: XX000: SPI_connect failed: SPI_ERROR_CONNECT
CONTEXT: PL/pgSQL function "price_aftertrigger" line 30 at IF
SQL statement "update price set validFrom = $1 , validTo =
$2 where id = $3 [ $4 ][1]::Integer"
PL/pgSQL function "price_rebuildpricelistvalidity" line 54 at
SQL statement
SQL statement "SELECT price_rebuildPriceListValidity( $1 )"
PL/pgSQL function "price_aftertrigger" line 54 at PERFORM
LOCATION: plpgsql_call_handler, pl_handler.c:77

As I said it was thrown within PG8.4, but when it was executed within
PG8.3.5 there were no problems at all. Second execution of same
statement didn't throw an error too. Can anyone explain what it means.

Best regards,
ML

Marek Lewczuk <marek(at)lewczuk(dot)com> writes:
> I have made an upgrade to PG 8.4 and following error was thrown during
> execution of some pl/pgsql function:
> ERROR: XX000: SPI_connect failed: SPI_ERROR_CONNECT

Really? Could we see a self-contained example?

regards, tom lane

2009/7/10 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Marek Lewczuk <marek(at)lewczuk(dot)com> writes:
>> I have made an upgrade to PG 8.4 and following error was thrown during
>> execution of some pl/pgsql function:
>> ERROR:  XX000: SPI_connect failed: SPI_ERROR_CONNECT
>
> Really?  Could we see a self-contained example?
Really... And what is very problematic it doesn't happen always. Right
now this error is thrown in another trigger/function as well:
ERROR: XX000: SPI_connect failed: SPI_ERROR_CONNECT
CONTEXT: PL/pgSQL function
"configurationvsibundle_updateconfigurationbundlestrigger" line 116 at
assignment
LOCATION: plpgsql_call_handler, pl_handler.c:77
STATEMENT: update configurationvsibundle set newbundle = cast($1 as
vsibundle) where id = $2

I can't prepare an example, cause I can't reproduce this error in an
example, but in production database that error occurs. As I wrote
before, the problem is also that second try of executing a statement,
that cause an error works just fine, but I don't know whether this is
executed in the same connection or in the different one, cause my
application uses connection pooling. How can it be, that pl/pgsql
looses SPI connection within the middle of some function ?

Thanks for help.

ML

Marek Lewczuk <newsy(at)lewczuk(dot)com> writes:
>>> I have made an upgrade to PG 8.4 and following error was thrown during
>>> execution of some pl/pgsql function:
>>> ERROR: XX000: SPI_connect failed: SPI_ERROR_CONNECT
>>
> I can't prepare an example, cause I can't reproduce this error in an
> example, but in production database that error occurs. As I wrote
> before, the problem is also that second try of executing a statement,
> that cause an error works just fine, but I don't know whether this is
> executed in the same connection or in the different one, cause my
> application uses connection pooling. How can it be, that pl/pgsql
> looses SPI connection within the middle of some function ?

No, you're misinterpreting the message. What that code likely means
is that something is trying to use SPI and finding plpgsql already
connected. In other words, plpgsql forgets to do a SPI_push() before
calling something that might try to use SPI re-entrantly. It should be
perfectly deterministic and it definitely doesn't have anything to do
with the states of other sessions. But we're going to need a test
case to fix it.

regards, tom lane

2009/7/13 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> No, you're misinterpreting the message.  What that code likely means
> is that something is trying to use SPI and finding plpgsql already
> connected.  In other words, plpgsql forgets to do a SPI_push() before
> calling something that might try to use SPI re-entrantly.  It should be
> perfectly deterministic and it definitely doesn't have anything to do
> with the states of other sessions.  But we're going to need a test
> case to fix it.
I will try to prepare a test case, but as I said it is very
difficult... I've notice one thing - when that error occured I've
tried to repeat execution of the query but it didn't work - query was
executed in different connections from the pool. However in totaly new
connection (not taken from the connection pool) same query was
executed without any problem. So it seems, that for connections that
are in the pool SPI connection was not closed ??

--
Pozdrawiam
Marek Lewczuk

2009/7/13 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> No, you're misinterpreting the message.  What that code likely means
> is that something is trying to use SPI and finding plpgsql already
> connected.  In other words, plpgsql forgets to do a SPI_push() before
> calling something that might try to use SPI re-entrantly.  It should be
> perfectly deterministic and it definitely doesn't have anything to do
> with the states of other sessions.  But we're going to need a test
> case to fix it.
>
Tom,
I'm not able to prepare a test case - the error is thrown exactly in
two queries, but those queries can be executed without that error -
there is no rule, when it will be thrown. When the error was thrown
both queries cannot be executed (in the same connection of course, but
in other connections they can be executed without problem). Before the
error is thrown both queries were executed successfully (I'm logging
all "mod" queries), but at some moment (unknown for me) the error is
thrown and as I wrote those queries cannot be executed in current
connection anymore - what is really strange is the fact that other
queries (both selects and updates) can be executed in that connection
and there some of those queries fires plpgsql triggers which updates
db data (so SPI is used??).

What can I do ? I don't want to do the downgrade :-( How can I track
the moment, when plpgsql forgets to do SPI_push() ?

I'm really appreciated for your help.

ML