Calendario dell'Avvento ITPUG: 6 Dicembre

Ecco una "piccola" query che dovrebbe dare uno sguardo piu' facilmente leggibile sui lock attualmente presenti nel sistema. Parto da un possibile output della query per meglio far capire:

-[ RECORD 1 ]---+-------------------------

--------------------------------------
pid             | 1492
locking_summary | RowExclusiveLock GRANTED on table public.listini
user_query      | insert into listini(prezzo) select generate_series(1,1000000);
by              | bsdmag started on 2014-12-05 13:15:02.759257+00 (running)

ovvero si riporta quale processo (pid) ha quali lock (locking_summary) per l'utente (by) che sta eseguendo la query (user_query) a partire dal tempo indicato (by). Altro non è che una reinterpretazione del join fra pg_locks e
pg_stat_activity.

Inserire la query in una vista, denominata verbose_locck, consente di eseguirla piu' facilmente; di seguito la definizione della query/vista:

CREATE VIEW verbose_locks
AS

WITH full_table_name AS
(
   SELECT 'table ' || nsp.nspname || '.' || clz.relname AS name, clz.oid
   FROM pg_class clz JOIN pg_namespace nsp ON clz.relnamespace = nsp.oid
   WHERE clz.relkind = 'r'
)

SELECT
l.pid   -- process identifier
-- locking information
, l.mode
  || CASE WHEN l.granted THEN ' GRANTED'
          ELSE ' WAITING'
     END
  || ' on ' ||
  CASE WHEN l.locktype = 'relation'
            THEN (SELECT name
                  FROM full_table_name
                  WHERE oid = l.relation )
       WHEN l.locktype = 'tuple'                THEN    ' row '
       WHEN l.locktype = 'transactionid'
            THEN        ' transaction ' || l.transactionid
       WHEN l.locktype = 'virtualxid'
            THEN    ' virtual transaction ' || l.virtualtransaction
       ELSE l.locktype
  END
  AS locking_summary

-- current query launched from the user
, a.current_query AS user_query
-- username and some other details about the query
, a.usename || ' started on ' || a.query_start || CASE WHEN a.waiting
THEN ' (waiting) '
                                                       ELSE ' (running) '
                                                  END
  AS by

FROM pg_locks l LEFT JOIN pg_stat_activity a ON l.pid = a.procpid
WHERE l.pid <> pg_backend_pid() -- do not show myself