On Fri, Mar 04, 2011 at 05:52:29PM +0200, Heikki Linnakangas wrote: > Hmm, if these all came from the same database, then it looks OldestXmin > has moved backwards. That would explain the warnings. First one vacuum > determines that all the tuples are visible to everyone and sets the > flag. Then another vacuum runs with an older OldestXmin, and determines > that there's a tuple on the page with an xmin that is not yet visible to > everyone, hence it thinks that the flag should not have been set yet. > > Looking at the code, I don't see how that situation could arise, though. > The value calculated by GetOldestXmin() should never move backwards. And > GetOldestXmin() is called in lazy_vacuum_rel(), after it has acquired a > lock on the table, which should protect from a race condition where two > vacuums could run on the table one after another, in a way where the > later vacuum runs with an OldestXmin calculated before the first vacuum. > > Hmm, fiddling with vacuum_defer_cleanup_age on the fly could cause that, > though. You don't do that, do you? No.
I've updated the patch to collect db and schema and added Merlins patch as well and run it for a while. The attached log is all the debug messages for pg_statistic page 333 from one database. I've also attached the two most recent page images for that particular page, the last digits in the filename are the hour and minute of when the page was saved. What else can I be doing to help figure this out? Thanks -dg -- David Gould da...@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects.
2011-03-05 00:50:03.238 PST 13304 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 337186202) 2011-03-05 21:55:00.004 PST 10702 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 341342282) 2011-03-05 23:28:06.368 PST 16660 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 342000646) 2011-03-06 05:24:03.461 PST 12850 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 343420345) 2011-03-06 06:23:40.174 PST 21650 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 343773970) 2011-03-06 07:50:49.921 PST 3847 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 344373260) 2011-03-06 09:11:35.662 PST 10820 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 344660206) 2011-03-07 01:23:57.124 PST 14121 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 347873961) 2011-03-07 01:25:01.129 PST 14266 WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "c57.pg_catalog.pg_statistic" page 333 via OldestXmin (OldestXmin 347186993) 2011-03-07 01:26:01.143 PST 14356 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 347879220) 2011-03-07 01:27:01.158 PST 14456 WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "c57.pg_catalog.pg_statistic" page 333 via OldestXmin (OldestXmin 347186993) 2011-03-07 01:33:05.353 PST 15108 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 347705144) 2011-03-07 01:38:09.298 PST 15869 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 347705144) 2011-03-07 01:51:17.622 PST 18829 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 348042728) 2011-03-07 02:24:41.994 PST 22594 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 348116608) 2011-03-07 06:33:47.460 PST 18384 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 349004767) 2011-03-07 07:01:51.400 PST 23896 WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "c57.pg_catalog.pg_statistic" page 333 via OldestXmin (OldestXmin 347705144) 2011-03-07 10:23:31.416 PST 10654 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 349660381)
pageimage_c57_pg_catalog_pg_statistic_333.0127
Description: Binary data
pageimage_c57_pg_catalog_pg_statistic_333.0701
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
