Thread: 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@lewczuk.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@sss.pgh.pa.us>: > Marek Lewczuk <marek@lewczuk.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@lewczuk.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@sss.pgh.pa.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@sss.pgh.pa.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