Hi!
A couple of years ago I was working on PostgreSQL
features and extensions. Some of them was in ECPG,
mainly in the area of improving Informix compatibility.
One of the patchsets I invented was the cursor readahead
support to improve performance in ECPG which used FETCH N.
This work was published at
https://github.com/zboszor/ecpg-readahead/commits
However, there was one use case for this patchset actually
decreased performance, namely if the cursor was used to
modify rows via UPDATE WHERE CURRENT OF. It was because
the cursor position on the server side was different from
the application so the ECPG readahead code had to use
MOVE then UPDATE WHERE CURRENT OF.
I brewed this idea for a while and yesterday I decided
to do something about it and here's the (admittedly, limited)
result. I added a new PostgreSQL extension to the UPDATE syntax:
UPDATE ... WHERE OFFSET n IN cursor;
This new syntax changes the feature disparity between
FETCH N and UPDATE WHERE CURRENT OF that existed in
PostgreSQL forever.
I only implemented this for cursors that use SELECT FOR UPDATE.
This part was quite straightforward to implement.
The behaviour of this syntax is as follows:
* the offset value may be 0 or negative, since it is a virtual
index into the rows returned by the last FETCH statement
and negative indexes are felt natural relative to the
cursor position and the cursor direction
* for offset value 0, the behaviour is identical to WHERE CURRENT OF
* negative indexes allow UPDATEs on previous rows even if
the cursor reached the end of the result set
I need clues for how to extend this for cursors that aren't
using FOR UPDATE queries, if it's possible at all.
Please, review.
Best regards,
Zoltán Böszörményi