I'll have a look at that. How would updateable cursors do it? By locking the row?
-----Original Message-----
From: Shachar Shemesh [mailto:[EMAIL PROTECTED] Sent: 18 February 2004 14:10
To: Dave Page
Cc: Hackers; PostgreSQL OLE DB development
Subject: Re: [HACKERS] OIDs, CTIDs, updateable cursors and friends
I would, except I'm not sure how many queries I would need in order to find what the primary key is.
Well this is the only safe way to update a specific record. To find the pkey, look for an index on the table in pg_index with indisprimary = true. The indkey column holds an array of pg_attribute.attnum's that are in the index iirc.
So, basically, I would not be able to update a table that has no primary key?Also, what happens if the primary key is not a part of the fields returned by the query?
Add them as you proprosed to do with the OID, or fall back to the next
method. ADO etc. normally fail to update rows if the programmer hasn't
included a suitable key in the recordset.
What happens if I check how many would be updated, and get "1" as a result. I then actually do it, but between asking and performing, someone added a second row that matches the criteria?
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.
Should be perfectly safe.
But what if someone else changes some of the known values of my row?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?
You might find a new row that wasn;t there before but is now, or vice versa.
Then it's not a good enough solution, even if the driver did have the prorogative to change the table.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?
It is not the drivers place to mess with peoples schemas, but yes, it could cause an insert to fail following wraparound.
Regards, Dave.Ok, it seems to me there are several options here.
1. Find out which is the primary key for the table. What happens if the primary key is a multi-row thing? What happens if there is no primary key?
2. If I'm in a transaction, use OID for the insert after checking with a select that I'm only affecting one row. If I'm not in a transaction - perform the update in a generated transaction, and roll it back if there is more than one row affected.
I like 1 better, frankly. Dillemas dillemas dillemas.
-- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
