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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: "T(dot)J(dot) Ferraro" <tjtoocool(at)phreaker(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2393: update fails with unique constraint violation
Date: 2006-04-15 14:04:56
Message-ID: 200604151404.k3FE4uM11878@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Volkan YAZICI 2006-04-15 15:32:58 Re: BUG #2395: Can't get right type oid by PQftype.
Previous Message T.J. Ferraro 2006-04-15 13:56:13 Re: BUG #2393: update fails with unique constraint violation