Re: How to update rows from a cursor in PostgreSQL

Tom Lane wrote:
> Ruben <ruben12@superguai.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073