Oracle FOR-over-cursor vs WHERE CURRENT OF?

Started by Tom Laneabout 18 years ago2 messageshackers
Jump to latest
#1Tom Lane
tgl@sss.pgh.pa.us

I'm working over Pavel's FOR-over-cursor patch:
http://archives.postgresql.org/pgsql-patches/2007-12/msg00001.php

(Although I previously complained that this was a bit pointless,
the argument that it's important for porting Oracle code won me over.)

I noticed that because the patch uses the same prefetch logic that
regular plpgsql FOR loops do, the "current row" as seen by the cursor
itself is typically not in sync with the current row of the loop.
This would mean in particular that UPDATE/DELETE WHERE CURRENT OF would
affect the "wrong" row of the table. This is fixable by not
prefetching, but at a performance cost. I wonder whether anyone knows
if Oracle users expect this combination to work at all?

regards, tom lane

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tom Lane (#1)
Re: Oracle FOR-over-cursor vs WHERE CURRENT OF?

I wrote:

I wonder whether anyone knows
if Oracle users expect this combination to work at all?

Nevermind that ... I found an example of this in Oracle's own docs,
so clearly it had better work. Patch applied with appropriate fixes.

regards, tom lane