4
GitHub - AdmTal/PostgreSQL-Query-Lock-Explainer: Command line utility to show wh...
source link: https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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 |
+-------------+---------------+---------------------+
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK