github.com
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.
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.
> 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.
Done - https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/co...
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"
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.
Doesn't this mean that checking the locks in a query like:
Delete from t;commit