Re: [HACKERS] OIDs, CTIDs, updateable cursors and friends

[Shachar Shemesh]

Dave Page wrote:



 

-----Original Message-----
From: Shachar Shemesh [mailto:[EMAIL PROTECTED] 
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/
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings