X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 80E379FA140 for ; Sat, 1 Jul 2006 11:28:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 27484-03 for ; Sat, 1 Jul 2006 11:28:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 25F449FA395 for ; Sat, 1 Jul 2006 11:28:25 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k61ESLg8023914; Sat, 1 Jul 2006 10:28:21 -0400 (EDT) To: "Gregory S. Williamson" cc: pgsql-admin@postgresql.org Subject: Re: vacuum can't find clog In-reply-to: <71E37EF6B7DCC1499CEA0316A256832802B3E1DE@loki.wc.globexplorer.net> References: <71E37EF6B7DCC1499CEA0316A256832802B3E1DE@loki.wc.globexplorer.net> Comments: In-reply-to "Gregory S. Williamson" message dated "Sat, 01 Jul 2006 03:40:55 -0700" Date: Sat, 01 Jul 2006 10:28:21 -0400 Message-ID: <23913.1151764101@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/2 X-Sequence-Number: 22248 "Gregory S. Williamson" writes: > INFO: vacuuming "public.weekly_log_data" > ERROR: could not access status of transaction 1081340514 > DETAIL: could not open file "/data/postgres/psql8_test/pg_clog/0407": No such file or directory This is a data-corruption problem, ie, something has stomped on the xmin or xmax field of a tuple. (It may or may not be relevant that the high order half of that number looks like ASCII "s@" ...) Since xmin/xmax are the first fields looked at that can be checked with any specificity, "could not access status of transaction" is a common symptom even when most of a page has been trashed :-( I don't recall whether we've fixed any bugs post-8.0.3 that might explain this. My experience is that if you inquire in any detail you tend to find symptoms that look more like OS- or hardware-level problems than anything that looks like it could have come from a Postgres bug ... for instance, I recall finding a block of mail-message text smack in the middle of a Postgres file in one case. If you care to investigate exactly what happened, you could break out pg_filedump and other implements of destruction and see if you can determine exactly what's bogus in that table. You could probably also recover whatever data hasn't been overwritten, but the extent of damage is impossible to guess at this point. Otherwise, you might just consider this a wake-up call to update your Postgres and kernel, and maybe run some memory and disk testing. regards, tom lane