Re: OIDs, CTIDs, updateable cursors and friends

> -----Original Message-----
> From: Shachar Shemesh [mailto:psql(at)shemesh(dot)biz]
> Sent: 18 February 2004 13:18
> To: Hackers; PostgreSQL OLE DB development
> Subject: [HACKERS] OIDs, CTIDs, updateable cursors and friends
>
> Would adding "OID" to the rows returned by each "Select"
> call, and then doing "update blah where oid=xxx" when I'm
> requested to update the row sound like a reasonable stategy,
> in lieu of updateable cursors? Can anyone suggest a better way?
>

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.

2 & 3 can potentially affect more than one record, but even Microsoft
code runs into that problem from time to time and fails with an
appropriate error message. In pgAdmin II we used to ask the user if they
wanted to update all matching rows, but of course that is not
appropriate in a driver.

Regards, Dave.

Dave Page wrote:

>
>
>
>
>>-----Original Message-----
>>From: Shachar Shemesh [mailto:psql(at)shemesh(dot)biz]
>>Sent: 18 February 2004 13:18
>>To: Hackers; PostgreSQL OLE DB development
>>Subject: [HACKERS] OIDs, CTIDs, updateable cursors and friends
>>
>>Would adding "OID" to the rows returned by each "Select"
>>call, and then doing "update blah where oid=xxx" when I'm
>>requested to update the row sound like a reasonable stategy,
>>in lieu of updateable cursors? Can anyone suggest a better way?
>>
>>
>>
>
>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.
>
>
I would, except I'm not sure how many queries I would need in order to
find what the primary key is. Also, what happens if the primary key is
not a part of the fields returned by the query?

>2) Use the OID (and check that only one record will be affected).
>
>
That may work. Do a query for "how many would be affected". Then again,
I'm currently not inside a transaction. The plan was not to be inside a
transaction unless I needed to. I'm not sure how safe this is to perform
many queries.

>3) Build a where clause based on all known original values (and check
>that only one record will be affected).
>
>
Again - what happens when I'm not inside a transaction?

>4) Fail with an appropriate error.
>
>2 & 3 can potentially affect more than one record, but even Microsoft
>code runs into that problem from time to time and fails with an
>appropriate error message. In pgAdmin II we used to ask the user if they
>wanted to update all matching rows, but of course that is not
>appropriate in a driver.
>
>Regards, Dave.
>
>
The doc mentions something about making the OID column unique. Would
that not cause other problems? What happens if I define the OID field as
unique, and I get a wraparound and an attempt to put a new field in with
existing value? Would the OID skip to the next unique per table, or
would the insert fail?

Shachar

--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/

"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