You can also do a ps -x to see current connections and whether they are
performing a query, idle or idle in transaction.
Greg
----- Original Message -----
From: "David Esposito" <dvesposito@newnetco.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Jeffrey W. Baker" <jwbaker@acm.org>; "Postgres general mailing list"
<pgsql-general@postgresql.org>
Sent: Monday, April 08, 2002 3:47 PM
Subject: Re: [GENERAL] vacuuming not working?
> i took a peek at pg_stat_activity and it doesn't appear as though any of
the
> processes are doing anything ... they all have blanks for "current_query"
> ... (when i'm vacuuming and when i'm running the populate/clear queries)
>
> Is there any way to get timestamps turned on in the log file? Is there a
> chance that this "defunct" transaction is cleaned when the transaction
logs
> rotate? I see this in my log file but i haven't got the foggiest idea when
> it happened ... ;) ... and whether it resulted in me being able to vacuum
> the table successfully ...
>
> DEBUG: recycled transaction log file 0000000100000039
>
>
>
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: Monday, April 08, 2002 3:38 PM
> > To: David Esposito
> > Cc: Jeffrey W. Baker; Postgres general mailing list
> > Subject: Re: [GENERAL] vacuuming not working?
> >
> >
> > "David Esposito" <dvesposito@newnetco.com> writes:
> > > That's why I isolated it down to a standalone example ... There
> > is no other
> > > process looking at that table ... no foreign keys or other
> > things that could
> > > cause those records to be "in use" ...
> >
> > Whether there *is* something looking at that table is not the issue.
> > The issue is whether there is an open transaction old enough that if
> > it chose to look at the table, it would see now-deleted rows. If so,
> > VACUUM can't remove those rows, since it doesn't have any way to know
> > whether the old transaction will later choose to look.
> >
> > Given your later message, I suspect there was such an open transaction
> > and it exited. There is not any really good way to look for this
> > situation, although the pg_stat_activity view (in 7.2) is better than
> > nothing. Perhaps in future releases, we should add columns to
> > pg_stat_activity that would indicate how old each backend's open
> > transaction is.
> >
> > regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>