Обсуждение: BUG #9447: still bug in autovacuum after upgrading to 9.1.11?
The following bug has been logged on the website: Bug reference: 9447 Logged by: Pius Chan Email address: pchan@contigo.com PostgreSQL version: 9.1.11 Operating system: Linux version 2.6.18-371.3.1.el5 Description: After upgrading to 9.1.11, I vacuum the database as instructed in the release note (i.e. set vacuum_freeze_table_age=0, then run vacuum). I also run the utility as described in this blog: http://www.depesz.com/2013/12/06/what-does-fix-vacuums-tests-to-see-whether-it-can-update-relfrozenxid-really-mean/ However, after a month upgrading to 9.1.11, I run the utility again and still find rows with strange xmax. The current txid in the system is 1922768288 but I find several rows in a table with xmax <= 5000 and I am sure some of them were updated after upgrade to 9.1.11. For example: MY_DB=# select xmin,xmax,* from my_table where xmax=3389; -[ RECORD 1 ]---+------------------- xmin | 2 xmax | 3389 id | 35461 name | Speed Over 75 kph MY_DB=# select relname, relfrozenxid from pg_class where relname='my_table'; -[ RECORD 1 ]+----------- relname | my_table relfrozenxid | 1872725454 I usually fix the bad rows by updating them. However, when I run the utility every two weeks, I always find rows with exceptionally low xmax again. Thanks a lot for your help. Pius
pchan@contigo.com wrote: > PostgreSQL version: 9.1.11 > Operating system: Linux version 2.6.18-371.3.1.el5 > Description: > > After upgrading to 9.1.11, I vacuum the database as instructed in the > release note (i.e. set vacuum_freeze_table_age=0, then run vacuum). I also > run the utility as described in this blog: > > http://www.depesz.com/2013/12/06/what-does-fix-vacuums-tests-to-see-whether-it-can-update-relfrozenxid-really-mean/ Uh, that article has very odd formatting and is rather hard to read and also long, so I'm not gonna read it. It might contain errors, if you're going from what it says. > However, after a month upgrading to 9.1.11, I run the utility again and > still find rows with strange xmax. The current txid in the system is > 1922768288 but I find several rows in a table with xmax <= 5000 and I am > sure some of them were updated after upgrade to 9.1.11. For example: It's normal to have low numbers used in Xmax. This just means that they used a multixact instead of a plain transaction ID. You can check that by using the pageinspect extension and seeing the flag set in the t_infomask field. I'm not saying there is no bug here, only that you have provided no evidence that there is. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services