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.


Here is a simple example, using FB 2.5 classic. The example is unrealistic, and can be easily avoided, but equivalent updates to records already open in cursors can easily happen when triggers operate recusrively.


CREATE TABLE MY_TABLE (A INTEGER, B INTEGER,C INTEGER);
INSERT INTO MY_TABLE(A,B,C) VALUES (1,1,1);

set term ^ ;

EXECUTE BLOCK AS

DECLARE MY_CURSOR CURSOR FOR
(SELECT B FROM MY_TABLE
WHERE A = 1 /* FOR UPDATE OF B WITH LOCK */ ) ;
DECLARE B INTEGER;

BEGIN
OPEN MY_CURSOR;
FETCH MY_CURSOR INTO :B;

UPDATE MY_TABLE SET C = 2
WHERE A = 1;

UPDATE MY_TABLE SET B = 2
WHERE CURRENT OF MY_CURSOR;
END^

SELECT * FROM MY_TABLE gives the result 1,2,1

If "FOR_UPDATE OF B WITH LOCK" is uncommented, Flamerobin crashes with the
message


Engine Code : 335544333
Engine Message :
internal Firebird consistency check (cannot find record back version (291),
file: vio.cpp line: 5024)