Re: Autocommit, isolation level, and vacuum behavior

On 2008-09-11 17:21, Jack Orenstein wrote:

>> Then do the processing in separate transactions like this (in pseudocode):
> The id > last_id trick doesn't work for me -- I don't have an index that would
> support it efficiently.
>
> Turning on autocommit seems to work, I'm just not clear on the reason why.

Not knowing would bite you some time.

Please provide some (pseudo-)code on what you do. Do you mark rows as
processed? Do you save output of processing to a database? IMHO without
it it is hard to solve a mystery but I'll try below.

> 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

When you do:
result = query("select something from sometable")
then all rows of a result will be cached by a client program. To see
effects of serialization modes you have to issue another query in the
same transaction or use a cursor.

Check memory usage of your client program - you'll see that it needs a
lot of memory for query results.

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

I think after caching a result of a query you start processing your
rows. When you finish processing your first row you update your database
to save results. In autocommit mode a transaction in which you do this
update is automatically commited and ended. When autocommit is turned
off a transaction is not ended so from now on vacuum is not working
until you finish processing all rows.

Regards
Tometzky

PS. Please keep a CC to the list.
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh