Re: Revisited: Transactions, insert unique.

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

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.