> -----Original Message-----
> From: Andrew Sullivan [mailto:andrew@libertyrms.com]On Behalf Of Andrew
> Sullivan
> Sent: Tuesday, April 09, 2002 3:39 PM
> To: David Esposito
> Subject: Re: [GENERAL] vacuuming not working?
>
>
> On Mon, Apr 08, 2002 at 05:32:44PM -0400, David Esposito wrote:
>
> > 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")
>
> That's your problem.
>
> > ... the question is, are these other connections actually
> > holding open a transaction that would prevent the table from
> being properly
> > vacuumed?
>
> It _is_ being properly vacuumed. Nothing that was in the table at
> the time the transaction started can be removed (because the idle
> transaction _could_ be looking for it).
>
disclaimer: i've never looked at the source code and, unfortunately, do not
have the time to do so ... so please, don't flog me ...
but it seems as though the VACCUMING process is intended to bring the
physical data storage in sync with the logical data storage ... that is, it
removes pages on the disk that are no longer used by the database to store
data ... so then, why would vacuum give a damn about other transactions that
might be in progress? if everyone is using SERIALIZABLE for their isolation
level, i could see why this might be necessary ... but since all of my
transactions are using READ_COMMITTED, it shouldn't matter if i do a logical
delete (DELETE FROM) followed by a vacuum (physical cleanup of the unused
pages) as far as the other transactions are concerned ... there is no
guarantee made to those transactions that the rows in my table will still
exist when they attempt to query them ... (SELECT, UPDATE, DELETE, etc...)
am I missing something here? ...
-dave