Re: VACUUM FULL not working with persistent connections

On Mon, 28 Oct 2002, Juan Jose Comellas wrote:

> The connections are staying idle in a transaction (both in v7.1.3 and v7.2),
> but I'm not sure why. We've developed a Java application that uses a database
> connection pool (developed by us) that runs a rollback transaction whenever a
> connection is returned to the pool. We do this to ensure that each connection
> is in a clean state when inside the pool. Somehow the JDBC drivers interpret
> this in a strange way and leave a transaction open. Other than that,
> everything seems to be working perfectly.
>
> It is very strange, because under v7.2 Postgres seems to stop vacuuming before
> deleting all the useless tuples (see example below). If I run a verbose
> VACUUM you will notice that it moves some tuples instead of removing them. Is
> this the expected behavior? We are seeing a large performance drop with
> PostgreSQL v7.2 when having a lot of updates on one table.

If one of the open transactions is earlier than the updates, then I
believe vacuum will not delete the old rows because they might be visible
to some transaction.

Like I think:
T1: begin; -- this one's just hanging out
T2: begin;
T2: update t set a=2;
T2: commit;
T3: vacuum full verbose t;
-- I don't believe this vacuum will remove the old value of row(s) in t.
-- Since there are circumstances where T1 might be able to see said rows.