Thread: PG8.4.7: updating rows leaves duplicate rows violating PK
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
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 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 > > > =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 > >
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. 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 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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs >> >>
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 >>> >>> >