Re: Autocommit, isolation level, and vacuum behavior

Martijn van Oosterhout wrote:
> 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

Application requirement. We need to do something for each row retrieved from BIG
and the something is expensive. We do the scan slowly (30 second sleep inside
the loop) to amortize the cost.

> 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.

If the slow scan is done with autocommit = true, then the transactions updating
BIG and TINY run with no degradation in performance (as long as TINY is vacuumed
frequently).

If the slow scan is done with autocommit = false, then the transactions updating
BIG and TINY get slower and slower and the TINY table's file bloats.

I guess the question is this: What are the transaction boundaries for a scan
done with autocommit = false? (The connection has autcommit false, and the
connection is used for nothing but the scan.)

Jack