Re: OIDs, CTIDs, updateable cursors and friends

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/