Dan Ports <drkp@csail.mit.edu> wrote:
> I was doing some tests recently with default_transaction_isolation
> set to 'serializable' in postgresql.conf when I noticed pg_locks
> filling up with SIReadLocks rather more quickly than I expected.
>
> After some investigation, I found that an autovacuum worker was
> starting a transaction at the default isolation level. While using
> a serializable transaction doesn't affect its behavior (because
> it's not using a MVCC snapshot), having a serializable transaction
> open prevents other concurrent serializable transactions and their
> predicate locks from being cleaned up. Since VACUUM on a large
> table can take a long time, this could affect many concurrent
> transactions.
>
> My one-liner fix for this was to set
> DefaultXactIsoLevel = XACT_READ_COMMITTED;
> in AutoVacWorkerMain.
Ouch! I think this needs to be considered a significant bug! Is it
too late to get this into 9.1.2?
-Kevin