Ok, some further experimentation has proven that this problem does not
manifest itself if there are ZERO connections to the database (aside from
the actual connection doing the insert/delete/vacuum) ... The vacuum command
correctly reclaims the space and the on-disk file never grows beyond a
certain size ...
So, here's the million dollar question ... and one that may or may not be a
postgres question ... I normally have a pool of connections connected to
postgres from my application server (jboss) ... they are all normally in the
"idle in transaction" state (if i do a ps -ax, they are all listed as "idle
in transaction") ... the question is, are these other connections actually
holding open a transaction that would prevent the table from being properly
vacuumed? if so, is there anything that i can do about it ... (yes, i
suppose i need to figure out why the JDBC connections have transactions
started before they're actually needed) ... and was this the case with
postgres 7.1.3?
but i figured i'd run it by the experts too ... ;)
-dave
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Monday, April 08, 2002 3:53 PM
> To: David Esposito
> Cc: Jeffrey W. Baker; Postgres general mailing list
> Subject: Re: [GENERAL] vacuuming not working?
>
>
> "David Esposito" <dvesposito@newnetco.com> writes:
> > 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"
>
> Read
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monito
> ring-stats.html
>
> > Is there any way to get timestamps turned on in the log file?
>
> There's a flag in postgresql.conf. You might wanna turn on log_pid too.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org