Re: BUG #2393: update fails with unique constraint violation

T.J. Ferraro wrote:
> Isn't that expected? Your query will try to update row 3 first and set
> the primary key to 5, which in fact would violate the primary key
> constraint on that table.

While the error is expected, it isn't valid based on the SQL spec.  The
spec requires checks to happen at statement conclusion, not during
statement execution.  But because we use unique indexes to check the
constraint, we check during the statement, leading to an error.  We have
in TODO:

    * Allow DEFERRABLE UNIQUE constraints?

but the question mark is there because we don't know how to fix this
without causing terrible performance.

---------------------------------------------------------------------------

>
> Laurence Dawson wrote:
> > And then try an update:
> > lstore=> select * from test.test;
> >  a
> > ----
> >   1
> >   2
> >   3
> >   4
> >   5
> >   6
> >   7
> >   8
> >   9
> >  10
> > (10 rows)
> >
> > lstore=> update test.test set a = a + 2 where a >= 3;
> > ERROR:  duplicate key violates unique constraint "pk"
> > lstore=>
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +