 

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

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

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

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

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

Wed, 18 Feb 2004 14:36:43 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: OIDs, CTIDs, updateable cursors and friends
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/


Wed, 18 Feb 2004 16:56:07 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: OIDs, CTIDs, updateable cursors and friends