Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group