Обсуждение: strange problem with pl/pgsql function caching of bad values

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

snacktime <snacktime@gmail.com> writes:
> I have a very strange issue that I'm not sure how to debug.

Hm, are you certain there is always only one row for each value of
s_oid?  This command:

> status := active from recurbilling_transactions where s_oid = in_s_oid;

is going to give you a random one of the matching rows if there's more
than one match.  Another possibility worth considering is that it's
matching no rows (so that status ends up null).

> 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.

I would spend some more time trying to figure out what the pattern is
that distinguishes sessions that work from those that don't.

> 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'.

Mph.  Are you expecting the function to work for more than one such path
value over the life of a connection?  Maybe you need to do the selection
part with an EXECUTE not only the update.  As-is, the first execution
will latch down which copy of recurbilling_transactions will be used
for the selection, regardless of later changes in search_path.

            regards, tom lane

> > 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'.
>
> Mph.  Are you expecting the function to work for more than one such path
> value over the life of a connection?  Maybe you need to do the selection
> part with an EXECUTE not only the update.  As-is, the first execution
> will latch down which copy of recurbilling_transactions will be used
> for the selection, regardless of later changes in search_path.
>

Argh...  I knew better.  Yes i need to use EXECUTE because it is used
across multiple paths.

Chris