Re: OIDs, CTIDs, updateable cursors and friends - Mailing list pgsql-hackers

Dave Page wrote:

> 
>
>  
>
>>-----Original Message-----
>>From: Shachar Shemesh [mailto:psql@shemesh.biz] 
>>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.
>
>  
>
I'll have a look at that. How would updateable cursors do it? By locking 
the row?

>>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.
>  
>
So, basically, I would not be able to update a table that has no primary 
key?

>  
>
>>>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.
>
>  
>
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?

>>>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.
>
>  
>
But what if someone else changes some of the known values of my row?

>>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.
>
>  
>
Then it's not a good enough solution, even if the driver did have the 
prorogative to change the table.

>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/