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. Dan -- Dan R. K. Ports MIT CSAIL http://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
