File: pg_stat_kcache--2.1.1.sql

package info (click to toggle)
pg-stat-kcache 2.1.1-2
  • links: PTS, VCS
  • area: main
  • in suites: buster, sid
  • size: 144 kB
  • sloc: ansic: 611; sql: 83; makefile: 36; sh: 2
file content (83 lines) | stat: -rw-r--r-- 3,017 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- This program is open source, licensed under the PostgreSQL License.
-- For license terms, see the LICENSE file.
--
-- Copyright (c) 2014-2017, Dalibo
-- Copyright (c) 2018, The PoWA-team

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_stat_kcache" to load this file. \quit

SET client_encoding = 'UTF8';

CREATE FUNCTION pg_stat_kcache(
    OUT queryid bigint,
    OUT userid      oid,
    OUT dbid        oid,
    OUT reads       bigint,             /* total reads, in bytes */
    OUT writes      bigint,             /* total writes, in bytes */
    OUT user_time   double precision,   /* total user CPU time used */
    OUT system_time double precision,   /* total system CPU time used */
    OUT minflts     bigint,             /* total page reclaims (soft page faults) */
    OUT majflts     bigint,             /* total page faults (hard page faults) */
    OUT nswaps      bigint,             /* total swaps */
    OUT msgsnds     bigint,             /* total IPC messages sent */
    OUT msgrcvs     bigint,             /* total IPC messages received */
    OUT nsignals    bigint,             /* total signals received */
    OUT nvcsws      bigint,             /* total voluntary context switches */
    OUT nivcsws     bigint              /* total involuntary context switches */
)
RETURNS SETOF record
LANGUAGE c COST 1000
AS '$libdir/pg_stat_kcache', 'pg_stat_kcache_2_1';
GRANT ALL ON FUNCTION pg_stat_kcache() TO public;

CREATE FUNCTION pg_stat_kcache_reset()
    RETURNS void
    LANGUAGE c COST 1000
    AS '$libdir/pg_stat_kcache', 'pg_stat_kcache_reset';
REVOKE ALL ON FUNCTION pg_stat_kcache_reset() FROM public;

CREATE VIEW pg_stat_kcache_detail AS
SELECT s.query, d.datname, r.rolname,
       k.user_time,
       k.system_time,
       k.minflts,
       k.majflts,
       k.nswaps,
       k.reads AS reads,
       k.reads/(current_setting('block_size')::integer) AS reads_blks,
       k.writes AS writes,
       k.writes/(current_setting('block_size')::integer) AS writes_blks,
       k.msgsnds,
       k.msgrcvs,
       k.nsignals,
       k.nvcsws,
       k.nivcsws
  FROM pg_stat_kcache() k
  JOIN pg_stat_statements s
    ON k.queryid = s.queryid AND k.dbid = s.dbid AND k.userid = s.userid
  JOIN pg_database d
    ON  d.oid = s.dbid
  JOIN pg_roles r
    ON r.oid = s.userid;
GRANT SELECT ON pg_stat_kcache_detail TO public;

CREATE VIEW pg_stat_kcache AS
SELECT datname,
       SUM(user_time) AS user_time,
       SUM(system_time) AS system_time,
       SUM(minflts) AS minflts,
       SUM(majflts) AS majflts,
       SUM(nswaps) AS nswaps,
       SUM(reads) AS reads,
       SUM(reads_blks) AS reads_blks,
       SUM(writes) AS writes,
       SUM(writes_blks) AS writes_blks,
       SUM(msgsnds) AS msgsnds,
       SUM(msgrcvs) AS msgrcvs,
       SUM(nsignals) AS nsignals,
       SUM(nvcsws) AS nvcsws,
       SUM(nivcsws) AS nivcsws
  FROM pg_stat_kcache_detail
 GROUP BY datname;
GRANT SELECT ON pg_stat_kcache TO public;