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
