Re: Revisited: Transactions, insert unique.

Ed Loehr wrote:
>
> Lincoln Yeoh wrote:
> >
> > Hi,
> >
> > Previously I wanted to ensure that I am inserting something unique into a
> > table, the answer was to create a unique index on the relevant columns.
> >
> > But what if I don't want to get an error which would force a rollback? Say
> > I want to insert something if it doesn't already exist, but update it if it
> > does.

I think you could SELECT from etc using the key value, before trying to
insert anything; if it returns 0 rows, then you insert, else you update.
>
> I think the best answer to this is to correct the non-std error-handling to
> abort only the current statement and not the entire transaction.  IIRC,
> Peter Eisenstraut recently posted a one-line patch to facilitate this,
> though I don't know how well it's working for those who tried it.  I have
> not seen anything that indicated that the core developers were ready to
> adopt this, though recent discussions appeared to be heading that way.
>

I tested the mentioned patch. I worked fine as far as I could try. I
agree with you in that this is the way to go, including what Bruce
suggested of using a SET statement to select behaviour ...

> Regards,
> Ed Loehr
>
> >
> > Do I have to lock the whole table?
> >
> > Would it be a good idea to be able to request a lock on an arbitrary string
> > like in MySQL? Then I could perhaps do something like
> >
> > LOCK HANDLE('max255charstring',TimeoutInSeconds)
> > e.g.
> > LOCK HANDLE('mytable,field1=x,field2=y',10)
> >
> > Then I could control access to a row that may not even exist, or do other
> > snazzy transaction stuff.
> >
> > Cheerio,
> > Link.