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.<p>I made this tool - it’s kind of like “explain” but it tells you about what locks would be required by the query.<p>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.<p>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!<p>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<p><pre><code> PGHOST=db.company.com PGUSER=postgres PGPORT=5432 PGDATABASE=postgres pg_explain_locks --query "SELECT id FROM table"
</code></pre>
[1] <a href="https://www.postgresql.org/docs/current/libpq-envars.html" rel="nofollow">https://www.postgresql.org/docs/current/libpq-envars.html</a>
Not a postgres expert, but it looks like this is checked by <i>executing</i> the query: <a href="https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/blob/master/pg_explain_locks.py#L60">https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/bl...</a><p>Doesn't this mean that checking the locks in a query like:<p><pre><code> Delete from t;commit
</code></pre>
Would kill everything? Or any query that can't be transactional.
If 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.