Re: Replaceing records

On Thu, Sep 04, 2003 at 12:29:17PM -0700, Stephan Szabo wrote:
>
> On Thu, 4 Sep 2003, Richard Ellis wrote:
>
> > On Thu, Sep 04, 2003 at 12:17:35PM +0200, Csaba Nagy wrote:
> > > [philosophical post regarding a missing feature of Postgres]
> > >
> > > I found there's no way to avoid failed inserts because of
> > > unique constraint violations, causing automatic roll-back of
> > > the running transaction.
> > >
> > > Now contention on insert has a quite high probability for this
> > > operation in our application.
> >
> > Did you ever try this:
> >
> > insert into test (a, b, c, d)
> > (select 1, 2, 3, 4 where not exists
> > (select 1 from test where a=1 and b=2 and c=3 and d=4)
> > );
> >
> > If your table contains a=1, b=2, c=3, and d=4, nothing will
> > happen, and there will be no failed transaction. If your table
> > does not contain a=1, b=2, c=3, and d=4, you'll get an insert of
> > a row containing 1, 2, 3, 4.
>
> Unfortunately that doesn't work if two transactions want to insert
> a row containing 1,2,3,4 that are running concurrently.

True, if the row does not already exist. But in that situation,
because of the unique constraint premise in the original quote, there
is always going to be at least one failed transaction. So the battle
is already lost before it's even begun.

If, however, the same row already exists in the table, then both of these
inserts will silently do nothing, and both transactions will continue
without aborting.