Thread: How to update rows from a cursor in PostgreSQL

I was trying something like:

select * from t1, t2, t3, t4
where ...
for update of t1

while(fetch...)
{
    update
    where current
}

Since "FOR UPDATE" cursors are not supported in PostgreSQL, can I update
the current row of table t1?

Thanks a lot!

Ruben writes:

> Since "FOR UPDATE" cursors are not supported in PostgreSQL, can I update
> the current row of table t1?

There is no direct way, but some interfaces (e.g., ODBC, JDBC) emulate
updatable cursors in the client.

--
Peter Eisentraut   peter_e@gmx.net

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.

            regards, tom lane

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.

However, it should be noted that this may fail, especially if you work on
views. I've been bitten by this.

                    Emmanuel Charpentier

--
Emmanuel Charpentier

On Saturday 22 Feb 2003 2:08 pm, you wrote:
> 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.
>
> However, it should be noted that this may fail, especially if you work on
> views. I've been bitten by this.

Just wondering, is updatable views a TODO for postgresql?

 Shridhar

Thanks a lot Tom:

Shouldn't it be better then to use "oid" instead of ctid?

Ruben.



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.

Ruben <ruben12@superguai.com> writes:
> Shouldn't it be better then to use "oid" instead of ctid?

Only if you (a) have an OID column and (b) have a unique index created
on the OID column.  ctid is nice because it doesn't incur any indexing
overhead ...

            regards, tom lane

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