Roger,

> If, in PSQL, I open a named cursor on a record, and some other
> operation changes a field in that record, then the change is
> lost when I post using the cursor, even if the cursor does not
> fetch the changed field.
>
> If I define the cursor as FOR UPDATE OF MY_FIELD WITH LOCK, then
> the system crashes if, after some other operation changes the
> record, I try to post using the cursor.
>
> How do I maintain concurrency when using named cursors? Am I
> doing something wrong?

The problem is that positioned updates/deletes (aka "where current of")
write the originally fetched record and they cannot see the changes
happened in the meantime (the record is already in memory and thus is
not affected). If those changes would be performed by a concurrent
transaction, then the update conflict would be thrown. But in your case,
the changes are not really concurrent (transaction is the same) and they
get ignored.

And it's not exclusive to the explicit PSQL cursors, it can be
reproduced with FOR SELECT AS CURSOR as well. In fact, this is a design
pitfall existing since the InterBase era.

Could you please post this into the tracker? I suppose some solution
could be possible to implement.


Dmitry