[BUGS] 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_item12 GB6,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 Sequnce of steps: a) As can be seen from the trigger function below. we need a value from a sequence. This is getting such a value. select next_wbuidx(); next_wbuidx - 26121 (1 row) b) perform the update. what was of interest here had been determined earlier with some selects update account_item set receipttype='PY',detail='PY' where accountidx in (8617,8562,8616,8511,8615) and receipttype='EI'; UPDATE 346305 Time: 434837.447 ms c) issue a check query to ensure we did hit all rows that should be affected. This uses a slightly different query to cross check the short-cut values used with the uodate. The actual values are not too interesting. I just left a sample row for reference. The values there indicated it was one of the rows discovered earlier. select * from account_item whore accountidx in (select idx from account where domainidx in (2,3) and contextidx in (8510, 33362709) and accountid not like '%Interest%') and receipttype='EI'; wbuidx | userid | ts |idx| origwbuidx | accountidx | namespace | originatoridx | referenceidx | dedicationidx | groupidx | receipttype | detail | valuedate | effvaluedate | amount | remain | currency | creditdebit | label ++---+---++++---+--+---+--+-+--++--+---+---+--+-+ --- 25880 | 601| 2011-08-16 05:36:25.947873+02 | 215165864 | 25880 | 8615 | Accounting | 8516 |215165861 | | | EI | interest | 2011-08-31 | 2011-08-16 | 1.75 | 1.75 | EUR | -1 | [truncated]... (29 rows) Time: 14916.083 ms d) assuming the update was to scrict, retry targeting the left-overs update account_item set receipttype='PY',detail='PY' where accountidx in (8616) and receipttype='EI'; ERROR: duplicate key value violates unique constraint "pk_account_item" e) check for culprits select idx,count(*) from account_item group by idx having count(*) > 1; idx| count ---+--- 215165864 | 2 215165896 | 2 215165927 | 2 215165959 | 2 215165991 | 2 215166023 | 2 215166155 | 2 215166187 | 2 215166219 | 2 215166251 | 2 215166283 | 2 215166315 | 2 215166347 | 2 215166379 | 2 215166411 | 2 217011495 | 2 217011527 | 2 217011659 | 2 217011691 | 2 217011723 | 2 217011877 | 2 217011909 | 2 217012041 | 2 217012104 | 2 217012136 | 2 217012168 | 2 217012200 | 2 217012232 | 2 217012264 | 2 (29 rows) f) look for details of a sample select * from account_item where idx = 215165864; wbuidx | userid | ts |idx| origwbuidx | accountidx | namespace | originatoridx | referenceidx | dedicationidx | groupidx | receipttype | detail | valuedate | effvaluedate | amount | remain | currency | creditdebit | l abel ++---+---++++---+--+---+--+-+--++--+--+--+--+-+-- - 26121 | pgsql | 2011-08-17 11:13:15.593382+02 | 215165864 | 25880 | 8615 | Accounting | 8516 |215165861 | | | PY | PY | 2011-08-31 | 2011-08-16 |
Re: [BUGS] PG8.4.7: updating rows leaves duplicate rows violating PK
Hello 2011/8/17 Rainer Pruy : > 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 > > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] PG8.4.7: updating rows leaves duplicate rows violating PK
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 : >> 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_item12 GB6,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 >> >> -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] PG8.4.7: updating rows leaves duplicate rows violating PK
2011/8/17 Rainer Pruy : > 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 : >>> 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 >>> >>> > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6166: configure from source fails with 'This platform is not thread-safe.' but was actually /tmp perms
The following bug has been logged online: Bug reference: 6166 Logged by: Alex Soto Email address: aps...@gmail.com PostgreSQL version: 9.0.4 Operating system: Linux (CentOS release 5.0 (Final)) Description:configure from source fails with 'This platform is not thread-safe.' but was actually /tmp perms Details: I was trying to build the 9.0.4 source tarball as the postgres user on a test machine. The configure step failed with the error: This platform is not thread-safe. Check the file 'config.log' or compile and run src/test/thread/thread_test for the exact reason. Use --disable-thread-safety to disable thread safety. As I started looking through the log file I noticed that it failed to write to the /tmp directory. Other configure steps had written to /var/tmp, but this step tried to write to /tmp for some reason. I fixed by correcting the permissions on the /tmp dir, but I thought the error message was a little misleading, so I thought I'd report the problem. The specific configure I ran was: ./configure --disable-integer-datetimes but I don't think the config option likely makes a difference. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6166: configure from source fails with 'This platform is not thread-safe.' but was actually /tmp perms
"Alex Soto" writes: > I was trying to build the 9.0.4 source tarball as the postgres user on a > test machine. > The configure step failed with the error: > This platform is not thread-safe. Check the file 'config.log' or compile > and run src/test/thread/thread_test for the exact reason. > Use --disable-thread-safety to disable thread safety. > As I started looking through the log file I noticed that it failed to write > to the /tmp directory. Other configure steps had written to /var/tmp, but > this step tried to write to /tmp for some reason. > I fixed by correcting the permissions on the /tmp dir, but I thought the > error message was a little misleading, so I thought I'd report the problem. Hmm ... I can't find any explicit reference to either /tmp or /var/tmp in our configure script. It seems like this must be an artifact of your compiler, or some other tool you're using. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6166: configure from source fails with 'This platform is not thread-safe.' but was actually /tmp perms
Alex Soto writes: > Here's the section in the config.log in case it makes a difference > configure:28808: ./conftest > Could not create file in /tmp or > Could not generate failure for create file in /tmp ** > exiting > configure:28812: $? = 1 > configure: program exited with status 1 [ greps... ] Oh, that error is coming from src/test/thread/thread_test.c. I wonder why we have that trying to write to /tmp at all, when all the other transient junk generated by configure is in the current directory. Bruce, do you have a good reason for doing it that way? (The error message seems to be suffering from a bad case of copy-and- paste-itis, too.) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6167: pg_dump fails on table lock
The following bug has been logged online: Bug reference: 6167 Logged by: Jesper Engman Email address: jes...@engman.net PostgreSQL version: 8.3.10 Operating system: Linux Description:pg_dump fails on table lock Details: I have tables that exists for short time periods, sometimes for as short as 5 min. pg_dump is starting to fail due to a problem to lock these tables: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: relation "vehicle_change_partitions.vehicle_change_export_p4368494" does not exist pg_dump: The command was: LOCK TABLE vehicle_change_partitions.vehicle_change_export_p4368494 IN ACCESS SHARE MODE Backup failed: PGPASSWORD=x && export PGPASSWORD && export PGOPTIONS="-c statement_timeout=0 -c maintenance_work_mem=2147483647" && /usr/bin/pg_dump -h xxx.xxx.xxx.xxx -U postgres --ignore-version -Fc -Z 6 > /vol/nfs_backup/postgres_dumps/2011_07_13/_2011_07_13 Account: Backup failed How is this possible - pg_dump is a serializable transaction? It doesn't seem to be tripped up by some other backend function since this actually fails on the lock. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
