From: | Haroldo Stenger <hstenger(at)adinet(dot)com(dot)uy> |
---|---|
To: | Ed Loehr <eloehr(at)austin(dot)rr(dot)com>, postgres general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Revisited: Transactions, insert unique. |
Date: | 2000-04-24 16:39:05 |
Message-ID: | 390478A9.7B494969@adinet.com.uy |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ross J. Reedstrom | 2000-04-24 16:42:24 | Re: storing large amounts of text |
Previous Message | Ed Loehr | 2000-04-24 15:58:30 | Re: Revisited: Transactions, insert unique. |