Postgres Escalating Lock based on Blocked Stmts

We have an interesting locking scenario that seems like a bug but I’d like you guys to weigh in…

To reproduce the scenario:

1. Start with a table in a schema that executes a Read Committed Select that may take some time.
2. Issue a DROP SCHEMA stmt to the schema where the SELECT is taking place. (Because the DROP requires Exclusive lock on the schema it blocks as expected until the SELECT is complete).
3. Issue a second SELECT query on the same schema table. In this scenario the stmt is blocked by the DROP Schema stmt which is still blocked by the original SELECT. This seems odd as Postgres has elevated the lock based on a blocked DROP stmt instead of the current executing SELECT stmt. In fact, every stmt is blocked to anything trying to read from the schema.
4. If you manually cancel the DROP stmt to allow the second SELECT to continue the second SELECT query hangs and does not return. A manual restart of the Postgres server must happen to fix the issue.

Current version of Postgres that we are using is v11.2

Eric Alders

Senior Software Architect

Zelis

7007 College Blvd, Suite 650

Overland Park, KS 66211

eric.alders@zelis.com

www.zelis.com