We've observed a 9.0 database have undetected deadlocks repeatedly in
hot standby mode.
I think what's happening is that autovacuum is kicking off a VACUUM of
some system catalogs -- it seems to usually be pg_statistics' toast
table actually. At the end of the vacuum it briefly gets the exclusive
lock to truncate the table. On the standby it replays that and records
the exclusive lock being taken. It then sees a cleanup record that
pauses replay because a HS standby transaction is running that can see
the xid being cleaned up. That transaction then blocks against the
exclusive lock and deadlocks against recovery.
We expect upgrading to 9.3 to fix the problem for us due to the xid
feedback mechanism. But is this still a known problem when feedback is
not enabled? And is it a problem we should try to find a backpatchable
fix for?
I'm pondering whether we really need to log the exclusive lock taken
by vacuum when truncating. Worst case is a scan is in progress,
perhaps we can make scans understand how to handle tables that have
been truncated concurrently? We could always make the truncate replay
command acquire the lock and release it itself right away.
--
greg