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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: snacktime <snacktime(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: strange problem with pl/pgsql function caching of bad values
Date: 2005-07-11 18:48:22
Message-ID: 11123.1121107702@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message snacktime 2005-07-11 19:04:40 Re: strange problem with pl/pgsql function caching of bad values
Previous Message Thomas F. O'Connell 2005-07-11 18:48:20 Re: PostgreSQL Hosting