Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 8539E9FB6AD for ; Thu, 5 Jul 2007 14:53:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 82004-05-2 for ; Thu, 5 Jul 2007 14:53:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 19B609FB7A7 for ; Thu, 5 Jul 2007 14:53:11 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-218-82.bk12-dsl.surnet.cl [201.221.218.82]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l65Hr7eb014937; Thu, 5 Jul 2007 10:53:09 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id C281B47C44; Thu, 5 Jul 2007 13:53:04 -0400 (CLT) Date: Thu, 5 Jul 2007 13:53:04 -0400 From: Alvaro Herrera To: Tom Lane Cc: James Wilford , pgsql-admin@postgresql.org Subject: Re: More than one pg_database entry for database Message-ID: <20070705175304.GL5500@alvh.no-ip.org> References: <11565.1183647202@sss.pgh.pa.us> <18569.1183652804@sss.pgh.pa.us> <19238.1183657129@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <19238.1183657129@sss.pgh.pa.us> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV 0.90.3/3604/Thu Jul 5 03:33:34 2007 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Thu, 05 Jul 2007 10:53:09 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/47 X-Sequence-Number: 26028 Tom Lane wrote: > "James Wilford" writes: > > It's a redhat version of 7.3 - rh-postgresql-server-7.3.8-2 is the > > package. > > That was obsoleted by Red Hat well over two years ago; you are overdue > for an update. > > > xmin | cmin | xmax | cmax | oid | ctid | datname > > ----------+------------+------------+------+----------+--------+-------- > > 59254726 | 0 | 0 | 0 | 31238435 | (0,28) | misp > > 2 | 2430588419 | 2430588419 | 0 | 6790290 | (0,37) | misp > > It definitely looks to me like you have a problem with XID wraparound :-( > The (0,37) row was apparently deleted long ago, but never vacuumed away, > and now its xmax has wrapped around past the 2-billion-transaction event > horizon, causing it to appear visible to MVCC-using queries. So there's > something broken about your routine vacuuming procedures; you'd better > take a look at that. > > As far as getting out of the situation, the only really good answer is a > dump and reload. I can't think of any simple way of getting rid of the > bogus row, but what you should be able to do to let pg_dump work is to > rename misp to something else. You can rename it back after getting > through the dump/reload, of course. Or roll the XID counter back, vacuum the table, and restore the XID to the original value. This is done with pg_resetxlog, though I am not sure if we shipped it in 7.3. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "The West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do." (Samuel P. Huntington)