Re: Close open transactions soon to avoid wraparound problems

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brian Staszewski <brian(dot)staszewski(at)farheap(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Close open transactions soon to avoid wraparound problems
Date: 2007-09-11 01:54:05
Message-ID: 8899.1189475645@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Brian Staszewski <brian(dot)staszewski(at)farheap(dot)com> 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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Mateus Interciso 2007-09-11 11:15:54 Idle transactions
Previous Message Tom Lane 2007-09-11 00:20:12 Re: reindexdb hangs