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

PG8.4.7: updating rows leaves duplicate rows violating PK

From: Rainer Pruy <Rainer(dot)Pruy(at)Acrys(dot)COM>
To: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: PG8.4.7: updating rows leaves duplicate rows violating PK
Date: 2011-08-17 10:21:01
Message-ID: 4E4B960D.3020600@acrys.com (view raw or flat)
Thread:
Lists: pgsql-bugs
This is strange and as of now I do not have a reliable way of reproducing.
Nevertheless,
either there is a major blunder on my side that urgently needs being
pointed at and eliminated
or there is something really strange with PG.

Short version:

I update some rows of a table changing non-primary key column values.
Afterwards some of the updated rows are returned from a query with
the version from before and after the update.

Consequently the PK is detected inconsistent later on and errors are
reported accordingly.



Longer Version: please see text attachment


 server_version                  | 8.4.7  
 server_version_num         | 80407

OS: NetBSD 5.99.38

Sizes:
account_item    12 GB    6,8079,402 rows

While the update was executing another process was active that was
issuing a sequence of select.

Running that very sequence on a copy clone of the database (before the
update)
worked without such effect.

I had 3 similar occurrences before.
But those were on a DB instance used for development and I could not
verify the primary key was active during update.
Here it is verified it was in place. So the "bad" entries probably could
have been rejected due to PK violation?

Not much input I can give for decent analysis,
but either someone can point me to the obvious
or it is something thats worth being watched for somehow....

Rainer



Attachment: psql-error.txt
Description: text/plain (19.0 KB)

Responses

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2011-08-17 11:33:58
Subject: Re: PG8.4.7: updating rows leaves duplicate rows violating PK
Previous:From: rafDate: 2011-08-17 05:21:01
Subject: BUG #6165: documentation bug in plpgsql-declarations.html and plpgsql-statements.html (or plpgsql parser bug)

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