PROBLEM SOLVED: LOCK TABLE oddness in PLpgSQL function called via JDBC

At 06:36 PM 10/2/2001, Hiroshi Inoue wrote:
>The cause is that the stored function uses a common
>snapshot throughout the function execution. As I've
>complained many times, the current implementaion is
>far from intuition and this case seems to show that
>it isn't proper at all either.

Bravo! That indeed seems to have been the problem. To solve it, I simply
moved the LOCK TABLE out of the PLpgSQL function and into the JDBC code.
While this isn't *ideal* as it leaves the table locked across two JDBC
calls (the function and the following commit), it achieves the desired
result (synchronous access to the idfactory table across all clients), and
as I said, the function won't be called very often. It's far more important
that it work as expected rather than it work in sub-millisecond time.

To illustrate then what seems to have been occurring:

Time Thread A Thread B
1 snapshot
2 lock
3 read 1, 1
4 write 11, 2
5 snapshot
6 return 1
7 commit
8 lock
9 read 1, 1
10 write 11, 2
11 FAIL

As long as thread B takes its snapshot any time before the commit at (7),
its write at (10) will not affect any rows because ...

>The *update* statement
>find the row matching the where clause using the common
>snapshot but will find the row was already updated and
>the updated row doesn't satisfy the condition any longer.

Ouch. So querying for select, update, delete, whatever goes against the
snapshot to *locate* rows, but then applies the where clause to the *new
values* not seen in the snapshot? If that's the case, that's extremely
confusing.

Anyway, many thanks to everyone for keeping me from going totally insane.
Luckily the other stored procedures we need to write won't require such
strict access to table data. :)

Peace,
Dave