Wednesday, October 12, 2016

Postgres global locking problem

It seems that PostgreSQL can't `VACUUM FULL` one database and `pg_dump` another database at the same time. These things were going on in a database I was observing and I saw things back up waiting for an exclusive lock on pg_catalog.pg_db_role_setting. I found in the documentation that `VACUUM FULL` is one of the only things that takes out such an exclusive lock such that nothing else can even do a read-only `SELECT` off the table that's locked. The log file noted "waiting for AccessShareLock on relation ..." and a oid number which turned out to be pg_catalog.pg_db_role_setting. So, that's annoying, and a reason that we're going to have to continue splitting up postgres servers into more single-function servers, because there isn't actually enough internal isolation sometimes.