Re: OIDs, CTIDs, updateable cursors and friends

> -----Original Message-----
> From: Shachar Shemesh [mailto:psql(at)shemesh(dot)biz]
> Sent: 18 February 2004 14:56
> To: Dave Page
> Cc: Hackers; PostgreSQL OLE DB development
> Subject: Re: [HACKERS] OIDs, CTIDs, updateable cursors and friends
>
> I'll have a look at that. How would updateable cursors do it?
> By locking the row?

Dunno, we don't have them!

> So, basically, I would not be able to update a table that has
> no primary key?

Yes, unless you feel back to the value matching type update.

Realistically though, how can anyone expect to edit data successfully
unless they have defined a key to identify rows with? Whilst it's nice
to get it to work 100% of the time no matter how brain dead the schema
it's not that practical.

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

It'll update both rows unless you do it in one transaction.

> But what if someone else changes some of the known values of my row?

The update will fail to find any rows. This is almost certainly what
happens when MS Access starts pinting #DELETED# in rows of a linked
table.

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

I guess you mean multicolumn? No different, you just need all columns in
your WHERE clause. If there is no pkey (and I would be inclined to say
if there is none in the user's query and not try to add it yourself)
then you fail with an error.

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

1 is definitely better and is the only way that is guaranteed to be
safe. Thinking about it more, that is almost certainly the position a
driver should take. In pgAdmin we can afford a little artistic licence
(no pun intended) because no one will be using pgAdmin as a driver to
connect another program to a database, plus we can ask the user what
action to take if we don't know if the result will be exactly what was
intended. You do not have that luxury in a driver of course.

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?
>
> I guess you mean multicolumn? No different, you just
> need all columns in
> your WHERE clause. If there is no pkey (and I would
> be inclined to say
> if there is none in the user's query and not try to
> add it yourself)
> then you fail with an error.
>
> > 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.
>
> 1 is definitely better and is the only way that is
> guaranteed to be
> safe. Thinking about it more, that is almost
> certainly the position a
> driver should take. In pgAdmin we can afford a
> little artistic licence
> (no pun intended) because no one will be using
> pgAdmin as a driver to
> connect another program to a database, plus we can
> ask the user what
> action to take if we don't know if the result will
> be exactly what was
> intended. You do not have that luxury in a driver of
> course.

Just as another datapoint, pgaccess does the same
thing (finding the pkey(s), and using those to
uniquely identify the row). It is kind of a PITA, but
as Dave says, it's the best way to do this.

HTH,

--brett

__________________________________
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools