L.S.
After an upgrade to v8.4 one of our clients is experiencing heaps of problems,
they get errors like "ERROR: SPI_connect failed: SPI_ERROR_CONNECT".
I refer to this earlier post, it looks a lot like it:
http://archives.postgresql.org/pgsql-general/2009-07/msg00388.php
We too had no problem when running v8.3
Tom, you stated:
Really? Could we see a self-contained example?
We're working on that, but its very difficult. We have a way to reproduce it
in our application, but this works only in the production environment, not in
our development situation (the more users, the more faster the problem
appears). Some users are able to work 15 minutes without problems and then
they get errors. Restarting the app and thus the connection silences it again
for a while, etc.
Though a reproducable 'psql' testcase is not yet available, I do have a bit of
additional information that might serve as a clue to some:
* when they happen, the code triggering the errors seems to be fairly limited
* a small part are all triggered in before triggers and all are in a custom
function session_userid()
* the bulk part are triggered in both after as well as deferred triggers that
in turn call a couple of custom functions
_all_ of these custom function have the following structure in common:
CREATE OR REPLACE FUNCTION xxxxxxxxx()
RETURNS int
LANGUAGE 'plpgsql'
IMMUTABLE
STRICT
SECURITY INVOKER
AS ' DECLARE
result INT := 0;
BEGIN
BEGIN
SELECT id INTO STRICT result FROM xxxxxxxxx WHERE xxxxxxxxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION ''Unknown (%)...!!'', xxxxxxxxx
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION ''More than one found (%)...!!'', xxxxxxxxx;
END;
RETURN result;
END;';
=> mind the fact that it is marked 'immutable'
=> mind the fact that due to the exception usage, it contains an inner
begin/end block
Though the frequency / localisation of these errors seems too high to be a
coincidence, I do have to point out that these are not the only functions with
the same structure. I have a whole set of _status() and _type() functions that
look exactly the same, but who never come up in an error. However, this might
simply be due to the usage-pattern of my application.
Any clues as to how to gather additional information that might bring us
closer to a solution is appreciated also. I'd have no problem with applying
some patch as long as it's safe enough for a production environment ;)
--
Best,
Frank.
