Postgres Escalating Lock based on Blocked Stmts - Mailing list pgsql-bugs
We have an interesting locking scenario that seems like a bug but I’d like you guys to weigh in…
To reproduce the scenario:
- Start with a table in a schema that executes a Read Committed Select that may take some time.
- 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).
- 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.
- 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
Confidentiality Notice
This email and the attachments may contain information which is privileged and/or confidential and is intended for the business and/or confidential use of the recipient only. Such information may be protected by applicable State and/or Federal laws from disclosure or unauthorized use. If you are not the intended recipient, you are hereby notified that any disclosure is strictly prohibited. If you have received this email in error, please contact the sender immediately.
Attachment
pgsql-bugs by date: