Show HN: Postgres query lock explainer

[admtal]

https://github.com/AdmTal/PostgreSQL-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.

[fovc]

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.

[LunaSea → fovc]

As far as I know, truncate can be rolled back but it is also visible, even before the transaction is commited, to other transactions that started before:

https://www.postgresql.org/docs/current/sql-truncate.html

[fovc → LunaSea]

Ah thank you! Quoting the link for others;

> TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. See Section 13.6 for more details.

> TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.

[admtal → fovc]

Great suggestion, thank you!

Done - https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/co...

[mad0 → fovc]

Maybe emitting a warning would be good. I'm no DBA so remembering all operations that cannot be rolledback might be tough. (Though maybe I should know all of them if they aren't rollbackable :P )

[efxhoy]

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

    PGHOST=db.company.com PGUSER=postgres PGPORT=5432 PGDATABASE=postgres pg_explain_locks --query "SELECT id FROM table"

[1] https://www.postgresql.org/docs/current/libpq-envars.html

[thwarted → efxhoy]

Even with that command line, shell history needs to be disabled, or the shell needs to be configured to filter when it saves history (for example, the bash ignorespace HISTCONTROL option is useful here). This is, of course, shell specific.

[0xbadcafebee → thwarted]

Actually they didn't pass a password so there's nothing secret in the shell history that needs hiding. You can also set the password as an environment variable in a file and load it at runtime. The benefit there is that if you use a shell script to load your settings before running your application, you have a universal method of setting and passing variables, because the script can call any program to retrieve the password in any way, and then pass the value via environment variable. Bonus that any application-deploying tools can now override the value too.

Security concerns around env vars being visible in process lists are mostly red herrings. If you can list the processes you've already got a memory leak, RCE, etc. Even if you can get the password it should be impossible to connect to the database from anywhere other than the application anyway. If you can read memory and connect to a database from where an application is running, you've got bigger problems.

[hn92726819]

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:

    Delete from t;commit

Would kill everything? Or any query that can't be transactional.

[admtal → hn92726819]

The command won't work if you include "commit" in the input :)

[runeks → admtal]

Link to relevant source code: https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/bl...

[irrational]

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?

[miohtama]

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.

[dboreham → miohtama]

The deadlocking pids will be logged and then if you have/had query logging turned on you can see what the pids were executing prior to that. Tracing back to the application involves using the available logged information: client IP, client name (possibly just the default from the pq driver, so the same everywhere), content of the actual query.