Re: strange problem with pl/pgsql function caching of bad values

snacktime <snacktime(at)gmail(dot)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