Re: OIDs, CTIDs, updateable cursors and friends

Dave Page wrote:

>
>
>
>
>>-----Original Message-----
>>From: Shachar Shemesh [mailto:psql(at)shemesh(dot)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/