SPI_ERROR_CONNECT within pl/pgsql, PG 8.4

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.