9

I'm using a statement from this page to view blocking queries in Postgres

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_activity

1 Answer 1

11

The length of the query tracked can be found in the parameter 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. This parameter 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.

1
  • 2
    Just to make sure it's clear (as stated in the quote, which might go overlooked) .. changing that config option will require a server restart for it to take effect. Jan 27, 2015 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.