I have a very strange issue that I'm not sure how to debug. This is
on postgresql 8.0.0rc5, Freebsd 5.4. Yes I know I should be upgrading
this version and it's scheduled, but it can't happen for another week
and for all I know this might still be an issue in current versions of
postgresql.
First the function in question:
CREATE OR REPLACE FUNCTION cancel_subscription_bysubid(varchar)
returns integer AS '
DECLARE
in_s_oid varchar;
in_active varchar;
status integer;
BEGIN
in_s_oid := $1;
in_active := 0;
status := active from recurbilling_transactions where s_oid = in_s_oid;
IF status = 0 THEN
RETURN 0;
ELSIF status = 1 THEN
EXECUTE ''update recurbilling_transactions set active= ''
||in_active|| '' WHERE s_oid ='' || quote_literal(in_s_oid);
RETURN 1;
ELSE
RETURN 2;
END IF;
END
' LANGUAGE 'plpgsql' SECURITY DEFINER;
The 'active' column is an integer NOT NULL. s_oid is a varchar.
Every few days the database gets into a state where this function
starts returning a value of 2 even though the value of 'active' is 1
or 0. Even stranger is that not all sessions will do this. We used
cached connections via the perl DBI, and once this starts happening
some sessions return the bad value while others work correctly. Once
the database is in this state testing the function via psql at the
command line will always result in the function returning a value of
2, while some of the perl DBI connections will still be returning 0 or
1. I'm assuming that at some point all new sessions get hosed and
it's the older sessions which still work.
Restarting the database puts things back to normal and the function
then works correctly again for a while.
One other thing about our particular setup is that we use separate
schema's for all user data and the functions go in the public schema.
So before executing this function we issue something like 'set_path to
username,public'.
Chris
