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