Show HN: Postgres query lock explainer
This is like 4 years old, but I’m braver now and ready to share stuff with this community. I’ve been a lurker for a while.
I made this tool - it’s kind of like “explain” but it tells you about what locks would be required by the query.
I was making it as part of a larger tool that would try to prevent deadlocks during migrations at my last company, I never finished it.
It would be nice if you added a how it works section to the readme. Knowing that it does `begin; ${query}; rollback;` under the hood is important. The example with truncate in fact made me think the opposite was true, since truncate violates MVCC and cannot be rolled back.
I’m often worrying about locks in migrations that could be long running, so executing the query to figure out the locks defeats the purpose. Or at least I need to know to use a test DB.
Neat tool!
I prefer using environment variables for my connection info to avoid passwords in my shell history or plaintext config files. The standard[1] ones from libpq work in psycopg2 if you pass an empty connection string. Then you can keep them in the environment variables or do
[1] https://www.postgresql.org/docs/current/libpq-envars.htmlPGHOST=db.company.com PGUSER=postgres PGPORT=5432 PGDATABASE=postgres pg_explain_locks --query "SELECT id FROM table"Not a postgres expert, but it looks like this is checked by executing the query: https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/bl...
Doesn't this mean that checking the locks in a query like:
Would kill everything? Or any query that can't be transactional.Delete from t;commitIf the query causes a trigger to fire, will any additional locks that are created by the trigger(s) (that is, any functions called by the triggers) be noted?
Related to this: would anyone know how to figure out which 1) applications and 2) queries where holidng locks after a dead lock happened, post mortem? As the default PSQL deadlock log output is very sparse to the point being useless.