Postgres utility to show what locks will be acquired by a given query.
Project description
PostgreSQL Query Lock Explainer
Utility to show what locks will be acquired by a given query.
Query is executed but not committed.
Installation instructions
pip install pg_explain_locks
Example Usage
Examples shown against dvdrental sample database
pg_explain_locks \
--user DB_USER \
--password DB_PASSWORD \
--database DATABASE \
--host HOST \
--query "DROP table actor CASCADE"
+-------------+----------------------------+---------------------+
| Relation ID | Relation Name | Lock Type |
+-------------+----------------------------+---------------------+
| 16422 | actor | AccessExclusiveLock |
| 16448 | film_actor | AccessExclusiveLock |
| 16456 | actor_info | AccessExclusiveLock |
| 16487 | film_list | AccessExclusiveLock |
| 16506 | nicer_but_slower_film_list | AccessExclusiveLock |
| 16557 | actor_pkey | AccessExclusiveLock |
| 16588 | idx_actor_last_name | AccessExclusiveLock |
+-------------+----------------------------+---------------------+
pg_explain_locks \
--user DB_USER \
--password DB_PASSWORD \
--database DATABASE \
--host HOST \
--query "SELECT * FROM film_actor fa JOIN actor a on a.actor_id=fa.actor_id FOR UPDATE"
+-------------+---------------------+-----------------+
| Relation ID | Relation Name | Lock Type |
+-------------+---------------------+-----------------+
| 16422 | actor | RowShareLock |
| 16448 | film_actor | RowShareLock |
| 16557 | actor_pkey | AccessShareLock |
| 16569 | film_actor_pkey | AccessShareLock |
| 16588 | idx_actor_last_name | AccessShareLock |
| 16593 | idx_fk_film_id | AccessShareLock |
+-------------+---------------------+-----------------+
pg_explain_locks \
--user DB_USER \
--password DB_PASSWORD \
--database DATABASE \
--host HOST \
--query "ALTER TABLE customer ADD COLUMN deleted BOOLEAN"
+-------------+---------------+---------------------+
| Relation ID | Relation Name | Lock Type |
+-------------+---------------+---------------------+
| 16411 | customer | AccessExclusiveLock |
+-------------+---------------+---------------------+
Example usage with settings file
Create a settings file at ~/.pg_explain_locks_settings
in order to use the same DB settings every time.
Settings file contents :
USER=your_user
PASSWORD=your_password
DATABASE=your_database
HOST=your_host
PORT=your_post
Usage:
pg_explain_locks "ALTER TABLE customer ADD COLUMN deleted BOOLEAN"
+-------------+---------------+---------------------+
| Relation ID | Relation Name | Lock Type |
+-------------+---------------+---------------------+
| 16411 | customer | AccessExclusiveLock |
+-------------+---------------+---------------------+
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
pg_explain_locks-0.0.3.tar.gz
(3.4 kB
view hashes)
Built Distribution
Close
Hashes for pg_explain_locks-0.0.3-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 33349ada6096a8f14b3b3995e4cf2cb86b715c025ed75ee40566f4a7686574fc |
|
MD5 | 435b5306408c3a845bb7f96299bacf5a |
|
BLAKE2b-256 | 77ca3878f9df91d21ec84d12ff5cdcb62b059b55a3382316c895ad1a0ff8c9f4 |