PostgreSQL Blocking Queries – How to View Full Blocking Queries

concurrencylogspostgresql

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

Best Answer

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.

SUGGESTION #1

If you are just looking to take an EXPLAIN plan and instantly present it, here is a SQL-Fiddle like site called explain.depesz.com. Just copy-and-paste the explain and hit Submit.

SUGGESTION #2

You could try pgAdminIII which comes with Graphical Explains

See the following links to how to use pgAdmin and other methods

PostgreSQL – Why WHERE Clause is Not Pushed Down in View’s Query

You can create a function like this;

CREATE OR REPLACE FUNCTION public.get_messages_by_timestamp (
  time_a timestamp,
  time_b timestamp
)
RETURNS TABLE (
  recipient varchar,
  "timestamp" timestamp
) AS
$$
BEGIN
  RETURN QUERY
    SELECT DISTINCT ON (m.recipient) 
        m.recipient,
        m."timestamp"
      FROM messages m
      LEFT JOIN identities i ON m.recipient = i.name
      WHERE 
        m."timestamp" BETWEEN time_a AND time_b
      ORDER BY 
        m.recipient,
        m."timestamp" DESC;
END;
$$
LANGUAGE 'plpgsql';

Then you can use the function like a table

  SELECT *
         FROM get_messages_by_timestamp('2015-01-01', '2015-01-02')

Best Answer

Related Solutions

Postgres Auto_Explain Parser – Easier Reading and Understanding

SUGGESTION #1

SUGGESTION #2

PostgreSQL – Why WHERE Clause is Not Pushed Down in View’s Query

Related Question