Skip site navigation (1) Skip section navigation (2)

SPI_ERROR_CONNECT within pl/pgsql, PG 8.4

From: Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
Date: 2009-07-13 13:56:29
Message-ID: 200907131556.30868.ftm.van.vugt@foxi.nl (view raw or flat)
Thread:
Lists: pgsql-bugs
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.


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-07-13 14:26:08
Subject: Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
Previous:From: Chris SpottsDate: 2009-07-13 13:09:49
Subject: Re: Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group