Re: How to update rows from a cursor in PostgreSQL

Tom Lane wrote:
> Ruben <ruben12(at)superguai(dot)com> writes:
> > Since "FOR UPDATE" cursors are not supported in PostgreSQL, can I update
> > the current row of table t1?
>
> The usual hack for this is to select the table's "ctid" system column as
> part of the cursor output, and then say
>
> UPDATE t1 SET ... WHERE ctid = 'what-you-got-from-the-cursor';
>
> This is quite fast because the ctid is essentially a physical locator.
> Note however that it will fail (do nothing) if someone else has already
> updated the same row since your transaction started. This may or may
> not be what you want. I think ODBC has some hack to find the ctid of
> the latest version of the row.

We do have this in TODO:

o Allow UPDATE/DELETE WHERE CURRENT OF cursor using per-cursor tid
stored in the backend

Tom, if they do FOR UPDATE in the cursor, no one else can modify the row
until the transaction commits, right? I assume FOR UPDATE it required
for this functionality.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073