Re: [BUGS] BUG #3891: Multiple UPDATE doesn't handle UNIQUE constraint correctly

[David Fetter]

On Mon, Jan 21, 2008 at 08:17:47AM +, Adriaan van Os wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:  3891
> Logged by:  Adriaan van Os
> Email address:  [EMAIL PROTECTED]
> PostgreSQL version: 8.2.4
> Operating system:   Mac OS X 10.5
> Description:Multiple UPDATE doesn't handle UNIQUE constraint
> correctly
> Details: 
> 
> Suppose we have a table A with a UNIQUE column Name of type VARCHAR, a
> PRIMARY KEY record_id of type INT4 and the following records
> 
> A
> record_id   Name
> 1  X
> 2  Y
> 
> Furthermore, we have a temporary table temp_A with a UNIQUE column Name of
> type VARCHAR, a PRIMARY KEY record_id of type INT4 and the following
> records
> 
> temp_A
> record_id   Name
> 1  Y
> 2  X
> 
> Now, we update table A with values from temporary table temp_A doing
> something like
> 
> UPDATE "A" SET "Name" = "temp_A"."Name"  FROM "temp_A" WHERE "A".record_id =
> "temp_A".record_id

You can do something like FROM (SELECT * FROM "temp_A" ORDER BY record_id) AS 
"A"

so it won't collide.

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [BUGS] BUG #3891: Multiple UPDATE doesn't handle UNIQUE constraint correctly

[Euler Taveira de Oliveira]

Adriaan van Os wrote:


This will cause a UNIQUE violation in de middle of the UPDATE statement,
although after completion of the statement there is no such violation.


This is not a bug.


Although deferred constraints are not yet implemented for Postgres, the docs
at 
state:


But I forgot to read this:

"Check and unique constraints are always effectively not deferrable."

It's a known "problem" (TODO item). See Greg's elegant solution [1].

[1] 
http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html



--
  Euler Taveira de Oliveira
  http://www.timbira.com/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[BUGS] BUG #3891: Multiple UPDATE doesn't handle UNIQUE constraint correctly

[Adriaan van Os]

The following bug has been logged online:

Bug reference:  3891
Logged by:  Adriaan van Os
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system:   Mac OS X 10.5
Description:Multiple UPDATE doesn't handle UNIQUE constraint
correctly
Details: 

Suppose we have a table A with a UNIQUE column Name of type VARCHAR, a
PRIMARY KEY record_id of type INT4 and the following records

A
record_id   Name
1  X
2  Y

Furthermore, we have a temporary table temp_A with a UNIQUE column Name of
type VARCHAR, a PRIMARY KEY record_id of type INT4 and the following
records

temp_A
record_id   Name
1  Y
2  X

Now, we update table A with values from temporary table temp_A doing
something like

UPDATE "A" SET "Name" = "temp_A"."Name"  FROM "temp_A" WHERE "A".record_id =
"temp_A".record_id

This will cause a UNIQUE violation in de middle of the UPDATE statement,
although after completion of the statement there is no such violation.

Although deferred constraints are not yet implemented for Postgres, the docs
at 
state:

   IMMEDIATE constraints are checked at the end of each statement.

Apparently, this is not correct, as in the above example constraints are
checked in the middle of a statement, rather than at the end.

I feel the docs are right here and the software wrong.

Sincerely,

Adriaan van Os

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org