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
