5 ms·
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.
- miohtama 3y agoRelated 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 3y agoThe 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.
- efxhoy 3y agoNeat 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 https://www.postgresql.org/docs/current/libpq-envars.html
- thwarted 3y agoEven 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 3y agoActually 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.
- fovc 3y agoIt 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.
- admtal 3y agoGreat suggestion, thank you! Done - https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/commit/da1c5fcfe7b1d5f8755c0203ad66ea0e3828c00f https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/co...
- LunaSea 3y agoAs 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 https://www.postgresql.org/docs/current/sql-truncate.html
- fovc 3y agoAh 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.
- mad0 3y agoMaybe 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 )
- hn92726819 3y agoNot a postgres expert, but it looks like this is checked by executing the query: https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/blob/master/pg_explain_locks.py#L60 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 3y agoThe command won't work if you include "commit" in the input :)
- runeks 3y agoLink to relevant source code: https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/blob/da1c5fcfe7b1d5f8755c0203ad66ea0e3828c00f/pg_explain_locks.py#L148-L153 https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/bl...
- irrational 3y agoIf 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?