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?


Here is a simple example, using FB 2.5 classic.


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, the system crashes with the message


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