Re: OID wraparound (was Re: pg_depend)

Tom Lane wrote:
>
> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> > Tom Lane wrote:
> >> Could you use CTID instead of OID?
>
> > I am using both.
> > TIDs for fast access and OIDs for identification.
> > Unfortunately TIDs are transient and they aren't
> > that reliable as for identification.
>
> Hmm ... within a transaction I think they'd be reliable enough,
> but for long-term ID I agree they're not. What behavior do you
> need exactly;do you need to be able to find the updated version
> of a row you originally inserted?

What I was about to do in the case e.g. UPDATE is the following.

1) UPDATE .. set .. where CTID = saved_ctid and OID = saved_oid;
If one row was updated it's OK and return.
2) Otherwise something has changed and the update operation would
fail. However the driver has to try to find the updated
version of the row in case of keyset-driven cursors by the query
SELECT CTID, .. from .. where CTID =
currtid2(table_name, saved_ctid) and OID = saved_oid;
If a row was found, the content of cursors' buffer is
replaced and return.
3) If no row was found, the row may be deleted. Or we could
issue another query
SELECT CTID, .. from .. where OID = saved_oid;
though the performance is doubtful.

The OIDs are (mainly) to prevent updating the wrong records.

> What would it take to use a
> user-defined primary key instead of OID?

Yes it could be. In fact M$ provides the ODBC cursor library
in that way and we have used it(indirectly) for a long time.
It's the reason why ODBC users don't complain about the non-existence
of updatable cursors that often. Must I repeat the implementation ?

regards,
Hiroshi Inoue