Skip to content

Instantly share code, notes, and snippets.

@askrana

askrana/postgresql_pg_stat_activity_with_tempfiles.sql

Forked from lure/postgresql_pg_stat_activity_with_tempfiles.sql
Created September 23, 2020 14:15
Show Gist options
  • Save askrana/e6475da6abbe744e59ed05428f46f791 to your computer and use it in GitHub Desktop.
Save askrana/e6475da6abbe744e59ed05428f46f791 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @ng-pe ng-pe revised this gist Feb 2, 2017. 1 changed file with 60 additions and 19 deletions.
    79 changes: 60 additions & 19 deletions postgresql_pg_stat_activity_with_tempfiles.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,25 +1,66 @@
    -- View pg_stat_activity with temporary files informations (file list and total file size)
    -- (Please modify the query when using specific tablespace)
    -- version 0.2
    -- version 0.3

    SELECT a.*,
    b.tmptotalsize,
    b.tmpfilelst
    FROM pg_stat_activity AS a
    SELECT
    pg_stat_activity.pid AS pid,
    CASE WHEN LENGTH(pg_stat_activity.datname) > 16
    THEN SUBSTRING(pg_stat_activity.datname FROM 0 FOR 6)||'...'||SUBSTRING(pg_stat_activity.datname FROM '........$')
    ELSE pg_stat_activity.datname
    END
    AS database,
    pg_stat_activity.client_addr AS client,
    EXTRACT(epoch FROM (NOW() - pg_stat_activity.query_start)) AS duration,
    pg_stat_activity.wait_event IS NOT NULL AS wait,
    pg_stat_activity.usename AS user,
    pg_stat_activity.state AS state,
    pg_size_pretty(pg_temp_files.sum) as temp_file_size, pg_temp_files.count as temp_file_num,
    pg_stat_activity.query AS query
    FROM
    pg_stat_activity AS pg_stat_activity
    INNER JOIN
    (SELECT filepid,
    tmptotalsize,
    (array_to_string(
    (array(SELECT FILE
    FROM pg_ls_dir('./base/pgsql_tmp/') AS ls(FILE)
    GROUP BY FILE
    HAVING FILE ~ ('pgsql_tmp'|| filepid ||'\.[0-9]*'))
    ), ',')) AS tmpfilelst
    (
    SELECT unnest(regexp_matches(agg.tmpfile, 'pgsql_tmp([0-9]*)')) AS pid,
    SUM((pg_stat_file(agg.dir||'/'||agg.tmpfile)).size),
    count(*)
    FROM
    (SELECT ls.oid,
    ls.spcname,
    ls.dir||'/'||ls.sub AS dir,
    CASE gs.i
    WHEN 1 THEN ''
    ELSE pg_ls_dir(dir||'/'||ls.sub)
    END AS tmpfile
    FROM
    (SELECT DISTINCT unnest(regexp_matches(FILE, 'pgsql_tmp([0-9]+)\.[0-9]+', 'g')) AS filepid,
    sum((pg_stat_file('./base/pgsql_tmp/' || FILE)).size) AS tmptotalsize
    FROM pg_ls_dir('./base/pgsql_tmp/') AS ls(FILE)
    GROUP BY filepid) AS tmppidlst) AS b ON (a.pid = b.filepid::int)
    ;
    (SELECT sr.oid,
    sr.spcname,
    'pg_tblspc/'||sr.oid||'/'||sr.spc_root AS dir,
    pg_ls_dir('pg_tblspc/'||sr.oid||'/'||sr.spc_root) AS sub
    FROM
    (SELECT spc.oid,
    spc.spcname,
    pg_ls_dir('pg_tblspc/'||spc.oid) AS spc_root,
    trim(TRAILING E'\n '
    FROM pg_read_file('PG_VERSION')) AS v
    FROM
    (SELECT oid,
    spcname
    FROM pg_tablespace
    WHERE spcname !~ '^pg_') AS spc) sr
    WHERE sr.spc_root ~ ('^PG_'||sr.v)
    UNION ALL
    SELECT 0,
    'pg_default',
    'base' AS dir,
    'pgsql_tmp' AS sub
    FROM pg_ls_dir('base') AS l WHERE l='pgsql_tmp' ) AS ls,

    (SELECT generate_series(1,2) AS i) AS gs
    WHERE ls.sub = 'pgsql_tmp') agg
    GROUP BY 1
    ) as pg_temp_files on (pg_stat_activity.pid = pg_temp_files.pid::int)
    WHERE
    pg_stat_activity.pid <> pg_backend_pid()
    ORDER BY
    EXTRACT(epoch FROM (NOW() - pg_stat_activity.query_start)) DESC;

    -- => http://blog.ng.pe/
  2. @ng-pe ng-pe created this gist Jan 4, 2017.
    25 changes: 25 additions & 0 deletions postgresql_pg_stat_activity_with_tempfiles.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    -- View pg_stat_activity with temporary files informations (file list and total file size)
    -- (Please modify the query when using specific tablespace)
    -- version 0.2

    SELECT a.*,
    b.tmptotalsize,
    b.tmpfilelst
    FROM pg_stat_activity AS a
    INNER JOIN
    (SELECT filepid,
    tmptotalsize,
    (array_to_string(
    (array(SELECT FILE
    FROM pg_ls_dir('./base/pgsql_tmp/') AS ls(FILE)
    GROUP BY FILE
    HAVING FILE ~ ('pgsql_tmp'|| filepid ||'\.[0-9]*'))
    ), ',')) AS tmpfilelst
    FROM
    (SELECT DISTINCT unnest(regexp_matches(FILE, 'pgsql_tmp([0-9]+)\.[0-9]+', 'g')) AS filepid,
    sum((pg_stat_file('./base/pgsql_tmp/' || FILE)).size) AS tmptotalsize
    FROM pg_ls_dir('./base/pgsql_tmp/') AS ls(FILE)
    GROUP BY filepid) AS tmppidlst) AS b ON (a.pid = b.filepid::int)
    ;

    -- => http://blog.ng.pe/