Re: Autocommit, isolation level, and vacuum behavior

On Wed, Sep 10, 2008 at 09:45:04AM -0400, Jack Orenstein wrote:
> Am I on the right track -- does autocommit = false for the BIG scan force
> versions of TINY to accumulate? I played around with a JDBC test program,
> and so far cannot see how the autocommit mode causes variations in what is
> seen by the scan. The behavior I've observed is consistent with the
> SERIALIZABLE isolation level, but 1) I thought the default was READ
> COMMITTED, and 2) why does the accumulation of row versions have anything
> to do with autocommit mode (as opposed to isolation level) on a connection
> used for the scan?

Vacuum can only clean up stuff older than the oldest open transaction.
So if you have a transaction which is open for hours then stuff made
since then it can't be vacuumed. The solution is: don't do that.

What I don't understand from your description is why your scan is slow
and how the autocommit relates to this. Postgresql only cares about
when you start and commit transactions, and I can't get from your
description when exactly that happens.

Rule of thumb: don't hold transaction open unnessarily long.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.