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: CAFj8pRD1enH13Cerdo=OgNkm-2zxzk_cJYt1DWSPW-+95C96rw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello

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?

Regards

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:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>

In response to

Responses

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-2014 The PostgreSQL Global Development Group