Re: INSERT ON DUPLICATE KEY UPDATE

On Fri, 2004-06-18 at 16:41, Jim Seymour wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> wrote:
> >
> [snip]
> >
> > IIRC the main downside to the select/update method is it introduces a
> > race condition that can only be solved by locking the table; not an
> > issue for most my$ql apps but would be frowned upon by most postgresql
> > users.
> [snip]
>
> One way around the race condition is to insert and, if the insert
> fails, do an update. If that fails, then something is actually wrong.
>
> One caveat regarding the above solution: Even when an insert fails, if
> there's a column with a sequence in it, that sequence will be
> incremented nonetheless.
>

The other being that you can't do that within a transaction in
postgresql. Course that should change if alvaro gets nested transactions
done for 7.5. (go alvaro go!)

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL