Oracle FOR-over-cursor vs WHERE CURRENT OF?

Lists: pgsql-hackers
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Oracle FOR-over-cursor vs WHERE CURRENT OF?
Date: 2008-04-06 22:29:33
Message-ID: 12079.1207520973@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Oracle FOR-over-cursor vs WHERE CURRENT OF?
Date: 2008-04-06 23:44:51
Message-ID: 15091.1207525491@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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