Re: OIDs, CTIDs, updateable cursors and friends

Dave Page wrote:

> 
>
>  
>
>>-----Original Message-----
>>From: Shachar Shemesh [mailto:psql@shemesh.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/