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

Re: PG8.4.7: updating rows leaves duplicate rows violating PK

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Rainer Pruy <Rainer(dot)Pruy(at)acrys(dot)com>
Cc: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: PG8.4.7: updating rows leaves duplicate rows violating PK
Date: 2011-08-17 11:33:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs

2011/8/17 Rainer Pruy <Rainer(dot)Pruy(at)acrys(dot)com>:
> 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.

It is strange - are you sure, so UPDATE statement doesn't fail? Are
you sure, so UPDATE statement really modified rows?
Are you sure, so you are has not a broken index on PK?


Pavel Stehule

> 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
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:

In response to


pgsql-bugs by date

Next:From: Rainer PruyDate: 2011-08-17 12:05:36
Subject: Re: PG8.4.7: updating rows leaves duplicate rows violating PK
Previous:From: Rainer PruyDate: 2011-08-17 10:21:01
Subject: PG8.4.7: updating rows leaves duplicate rows violating PK

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