snippetsqlModerate
How to view full blocking postgres queries?
Viewed 0 times
fullpostgresviewblockinghowqueries
Problem
I'm using a statement from this page to view blocking queries in Postgres
However the queries are truncated so I typically can't see what's in the
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
ka.query AS blocking_statement,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.query AS blocked_statement,
now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;However the queries are truncated so I typically can't see what's in the
WHERE clause which makes debugging these blocking issues very hard. Is there a setting where I can tell postgres to log the full queries to pg_stat_activitySolution
The length of the query tracked can be found in the parameter
18.8.1. Query and Index Statistics Collector
Specifies the number of bytes reserved to track the currently executing command for each active session, for the
parameter can only be set at server start.
e.g. if you had
track_activity_query_size.18.8.1. Query and Index Statistics Collector
track_activity_query_size (integer)Specifies the number of bytes reserved to track the currently executing command for each active session, for the
pg_stat_activity.current_query field. The default value is 1024. Thisparameter can only be set at server start.
e.g. if you had
track_activity_query_size=16384 in your config, then this would mean that query text up to 16k would be collected.Code Snippets
track_activity_query_size (integer)Context
StackExchange Database Administrators Q#90343, answer score: 11
Revisions (0)
No revisions yet.