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

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
>
>