Re: OIDs, CTIDs, updateable cursors and friends

"Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> writes:
> Ignoring potential OID wraparound problems (which we do in pgAdmin) this
> should work, assuming there is an OID column. I would suggest trying the
> following methods in sequence:

> 1) Use the tables primary key.
> 2) Use the OID (and check that only one record will be affected).
> 3) Build a where clause based on all known original values (and check
> that only one record will be affected).
> 4) Fail with an appropriate error.

I don't think it's a good idea for driver-level code to depend on OIDs
for this; to do that you need the knowledge and cooperation of the
database designer. The OID column may not exist at all (CREATE TABLE
... WITHOUT OIDS). If it does exist, it's not guaranteed to be unique
unless someone put a unique index on it (and I agree with Dave that a
driver has no business installing such an index). Furthermore, if
there's not an index on OID then an update specifying "WHERE oid = nnn"
is going to be very slow because it will have to seqscan the whole
table.

I believe the ODBC driver uses CTID for this sort of problem. CTID is
guaranteed to exist and to be fast to access (since it's a physical
locator). Against this you have the problem that concurrent updates
of the record will move it, leaving your CTID invalid. However, that
could be a good thing, as it's debatable that you want to blindly apply
your update in such a case anyway.

If you are willing to hold open a transaction while the user edits the
record, you can lock the record with SELECT FOR UPDATE, and then your
CTID is guaranteed good for the duration of the transaction.

If you don't want to do that, I'd suggest reading both CTID and XMIN
when you initially read the tuple. When you are ready to commit
changes, do this:

BEGIN;
SELECT xmin FROM table WHERE ctid = whatever FOR UPDATE;
-- check that you get a record and its xmin matches
-- what you had; if so, you can go ahead and do
UPDATE table SET ... WHERE ctid = whatever;
COMMIT;

If some other client changed the record behind your back, you'll know it
because the xmin changes, and you can then do whatever seems best to
cope.

regards, tom lane