Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 22E989F9EDC for ; Mon, 10 Sep 2007 22:54:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 75760-04 for ; Mon, 10 Sep 2007 22:53:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id AE2D59F9E1D for ; Mon, 10 Sep 2007 22:54:07 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l8B1s5qG008900; Mon, 10 Sep 2007 21:54:05 -0400 (EDT) To: Brian Staszewski cc: pgsql-admin@postgresql.org Subject: Re: Close open transactions soon to avoid wraparound problems In-reply-to: <46E58BFD.80300@farheap.com> References: <46E58BFD.80300@farheap.com> Comments: In-reply-to Brian Staszewski message dated "Mon, 10 Sep 2007 11:25:01 -0700" Date: Mon, 10 Sep 2007 21:54:05 -0400 Message-ID: <8899.1189475645@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/57 X-Sequence-Number: 26818 Brian Staszewski writes: > WARNING: oldest xmin is far in the past > HINT: Close open transactions soon to avoid wraparound problems. Hmm ... which PG version is this? I think 8.2 will start to complain about this much earlier than older versions (~ 100M transactions by default, instead of 1000M). Though you've probably got a problem here in any case. > What's been done so far: > -Cluster-wide vacuum (these are done nightly, I ran one again just for gp) Not relevant > -Ran the following query looking for old/suspicious > connections/transactions, didn't find anything useful: > select procpid, datname, client_addr, usename, current_query, > query_start from pg_stat_activity order by query_start asc; Most likely the problem transaction is sitting idle and hence has null query_start. Try "ps auxww" or local equivalent and look for postgres processes that are very old and are in "idle in transaction" state. regards, tom lane