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

2011/8/17 Rainer Pruy <Rainer.Pruy@acrys.com>:
> Hallo,
> this is strange for sure. The database is in heavy use regularly.
> So it is - if at all - a rare occurrence.
>
> The update statement reported 346305 updated rows.
> And I could verify that this is the number of rows that hat there value
> change
> (where afterwards there was a "new" version in the table.
> Thus, the update statement actually performed its operation.
>
> After eliminating the "offending" rows the index is operational again
> and does not complaining about violations.
> Thus, it is not likely a plain bad PK index.
>
> I already tried to come up with something that could create a false
> positive here,
> but am out of ideas now.

it should be a race condition too.

you can try to use a triggers for identification of place where value
is modified back.

Pavel

>
> Up to now this only happened with said table.
> May be something is bad with the table?
> However, the current instance is nearly a fresh installation of PG
> with data loaded from a pg_dumpall from another instance
> (for some special testing and analysis).
> Thus, I have events with two different instances of PG.
> Something being imported by plain DML operations?
> A strange idea by itself anyway.....
>
> Still clueless...
>
> Rainer
>
>
> Am 17.08.2011 13:33, schrieb Pavel Stehule:
>> Hello
>>
>> 2011/8/17 Rainer Pruy <Rainer.Pruy@acrys.com>:
>>> This is strange and as of now I do not have a reliable way of reproduci=
ng.
>>> 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
>>>
>>>
>>> =C2=A0server_version =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0| 8.4.7
>>> =C2=A0server_version_num =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 80407
>>>
>>> OS: NetBSD 5.99.38
>>>
>>> Sizes:
>>> account_item =C2=A0 =C2=A012 GB =C2=A0 =C2=A06,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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>>
>>>
>