Re: CURRENT OF cursor without OIDs

Ian Lance Taylor wrote:
>
> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
>
> > > Ian Lance Taylor <ian(at)airs(dot)com> writes:
> > > > Anyhow, I see that there is a move afoot to eliminate mandatory OIDs.
> > > > My question now is: if there is no OID, is there any comparable way to
> > > > implement CURRENT OF cursor? Basically what is needed is some way to
> > > > identify a particular row between a SELECT and an UPDATE.
> > >
> > > I'd look at using TID. Seems like that is more efficient anyway (no
> > > index needed). Hiroshi has opined that TID is not sufficient for ODBC
> > > cursors, but it seems to me that it is sufficient for SQL cursors.
> > >
> >
> > Yes TID is available and I introduced Tid Scan in order
> > to support this kind of implementation. However there
> > are some notices.
> > 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ?
> > (It doesn't seem easy for me).
>
> No, it is not supported right now.
>
> Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause
> and turn it into an explicit LOCK statement.

It's impossible to realize *FOR UPDATE* using LOCK statement.
Each row must be locked individually to prevent UPDATE/DELETE
operations for the row. You could acquire an EXCLUSIVE
LOCK on the table but it doesn't seem preferable.

I'm planning to implement updatable cursors with no lock
using TID and OID. TID is for the fast access and OID is
to verify the identity. OID doesn't provide a specific
access method in the first place and the access would be
veeery slow for large tables unless there's an index on OID.

regards,
Hiroshi Inoue