8000 Track statement entry timestamp in contrib/pg_stat_statements · postgres/postgres@dc9f8a7 · GitHub
[go: up one dir, main page]
Skip to content

Commit dc9f8a7

Browse files
akorotkovakorotkov
committed
Track statement entry timestamp in contrib/pg_stat_statements
This patch adds 'stats_since' and 'minmax_stats_since' columns to the pg_stat_statements view and pg_stat_statements() function. The new min/max reset mode for the pg_stat_stetments_reset() function is controlled by the parameter minmax_only. 'stat_since' column is populated with the current timestamp when a new statement is added to the pg_stat_statements hashtable. It provides clean information about statistics collection time intervals for each statement. Besides it can be used by sampling solutions to detect situations when a statement was evicted and stored again between samples. Such a sampling solution could derive any pg_stat_statements statistic values for an interval between two samples with the exception of all min/max statistics. To address this issue this patch adds the ability to reset min/max statistics independently of the statement reset using the new minmax_only parameter of the pg_stat_statements_reset(userid oid, dbid oid, queryid bigint, minmax_only boolean) function. The timestamp of such reset is stored in the minmax_stats_since field for each statement. pg_stat_statements_reset() function now returns the timestamp of a reset as the result. Discussion: https://postgr.es/m/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru Author: Andrei Zubkov Reviewed-by: Julien Rouhaud, Hayato Kuroda, Yuki Seino, Chengxi Sun Reviewed-by: Anton Melnikov, Darren Rush, Michael Paquier, Sergei Kornilov Reviewed-by: Alena Rybakina, Andrei Lepikhov
1 parent 6ab1dbd commit dc9f8a7

File tree

9 files changed

+511
-96
lines changed
  • contrib/pg_stat_statements
  • doc/src/sgml

    • 9 files changed

    +511
    -96
    lines changed

    ‎contrib/pg_stat_statements/Makefile

    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -19,7 +19,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
    1919

    2020
    REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
    2121
    REGRESS = select dml cursors utility level_tracking planning \
    22-
    user_activity wal cleanup oldextversions
    22+
    user_activity wal entry_timestamp cleanup oldextversions
    2323
    # Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
    2424
    # which typical installcheck users do not have (e.g. buildfarm clients).
    2525
    NO_INSTALLCHECK = 1

    ‎contrib/pg_stat_statements/expected/entry_timestamp.out

    Lines changed: 159 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -0,0 +1,159 @@
    1+
    --
    2+
    -- statement timestamps
    3+
    --
    4+
    -- planning time is needed during tests
    5+
    SET pg_stat_statements.track_planning = TRUE;
    6+
    SELECT 1 AS "STMTTS1";
    7+
    STMTTS1
    8+
    ---------
    9+
    1
    10+
    (1 row)
    11+
    12+
    SELECT now() AS ref_ts \gset
    13+
    SELECT 1,2 AS "STMTTS2";
    14+
    ?column? | STMTTS2
    15+
    ----------+---------
    16+
    1 | 2
    17+
    (1 row)
    18+
    19+
    SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
    20+
    WHERE query LIKE '%STMTTS%'
    21+
    GROUP BY stats_since >= :'ref_ts'
    22+
    ORDER BY stats_since >= :'ref_ts';
    23+
    ?column? | count
    24+
    ----------+-------
    25+
    f | 1
    26+
    t | 1
    27+
    (2 rows)
    28+
    29+
    SELECT now() AS ref_ts \gset
    30+
    SELECT
    31+
    count(*) as total,
    32+
    count(*) FILTER (
    33+
    WHERE min_plan_time + max_plan_time = 0
    34+
    ) as minmax_plan_zero,
    35+
    count(*) FILTER (
    36+
    WHERE min_exec_time + max_exec_time = 0
    37+
    ) as minmax_exec_zero,
    38+
    count(*) FILTER (
    39+
    WHERE minmax_stats_since >= :'ref_ts'
    40+
    ) as minmax_stats_since_after_ref,
    41+
    count(*) FILTER (
    42+
    WHERE stats_since >= :'ref_ts'
    43+
    ) as stats_since_after_ref
    44+
    FROM pg_stat_statements
    45+
    WHERE query LIKE '%STMTTS%';
    46+
    total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
    47+
    -------+------------------+------------------+------------------------------+-----------------------
    48+
    2 | 0 | 0 | 0 | 0
    49+
    (1 row)
    50+
    51+
    -- Perform single min/max reset
    52+
    SELECT pg_stat_statements_reset(0, 0, queryid, true) AS minmax_reset_ts
    53+
    FROM pg_stat_statements
    54+
    WHERE query LIKE '%STMTTS1%' \gset
    55+
    -- check
    56+
    SELECT
    57+
    count(*) as total,
    58+
    count(*) FILTER (
    59+
    WHERE min_plan_time + max_plan_time = 0
    60+
    ) as minmax_plan_zero,
    61+
    count(*) FILTER (
    62+
    WHERE min_exec_time + max_exec_time = 0
    63+
    ) as minmax_exec_zero,
    64+
    count(*) FILTER (
    65+
    WHERE minmax_stats_since >= :'ref_ts'
    66+
    ) as minmax_stats_since_after_ref,
    67+
    count(*) FILTER (
    68+
    WHERE stats_since >= :'ref_ts'
    69+
    ) as stats_since_after_ref
    70+
    FROM pg_stat_statements
    71+
    WHERE query LIKE '%STMTTS%';
    72+
    total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
    73+
    -------+------------------+------------------+------------------------------+-----------------------
    74+
    2 | 1 | 1 | 1 | 0
    75+
    (1 row)
    76+
    77+
    -- check minmax reset timestamps
    78+
    SELECT
    79+
    query, minmax_stats_since = :'minmax_reset_ts' AS reset_ts_match
    80+
    FROM pg_stat_statements
    81+
    WHERE query LIKE '%STMTTS%'
    82+
    ORDER BY query COLLATE "C";
    83+
    query | reset_ts_match
    84+
    ---------------------------+----------------
    85+
    SELECT $1 AS "STMTTS1" | t
    86+
    SELECT $1,$2 AS "STMTTS2" | f
    87+
    (2 rows)
    88+
    89+
    -- check that minmax reset does not set stats_reset
    90+
    SELECT
    91+
    stats_reset = :'minmax_reset_ts' AS stats_reset_ts_match
    92+
    FROM pg_stat_statements_info;
    93+
    stats_reset_ts_match
    94+
    ----------------------
    95+
    f
    96+
    (1 row)
    97+
    98+
    -- Perform common min/max reset
    99+
    SELECT pg_stat_statements_reset(0, 0, 0, true) AS minmax_reset_ts \gset
    100+
    -- check again
    101+
    SELECT
    102+
    count(*) as total,
    103+
    count(*) FILTER (
    104+
    WHERE min_plan_time + max_plan_time = 0
    105+
    ) as minmax_plan_zero,
    106+
    count(*) FILTER (
    107+
    WHERE min_exec_time + max_exec_time = 0
    108+
    ) as minmax_exec_zero,
    109+
    count(*) FILTER (
    110+
    WHERE minmax_stats_since >= :'ref_ts'
    111+
    ) as minmax_ts_after_ref,
    112+
    count(*) FILTER (
    113+
    WHERE minmax_stats_since = :'minmax_reset_ts'
    114+
    ) as minmax_ts_match,
    115+
    count(*) FILTER (
    116+
    WHERE stats_since >= :'ref_ts'
    117+
    ) as stats_since_after_ref
    118+
    FROM pg_stat_statements
    119+
    WHERE query LIKE '%STMTTS%';
    120+
    total | minmax_plan_zero | minmax_exec_zero | minmax_ts_after_ref | minmax_ts_match | stats_since_after_ref
    121+
    -------+------------------+------------------+---------------------+-----------------+-----------------------
    122+
    2 | 2 | 2 | 2 | 2 | 0
    123+
    (1 row)
    124+
    125+
    -- Execute first query once more to check stats update
    126+
    SELECT 1 AS "STMTTS1";
    127+
    STMTTS1
    128+
    ---------
    129+
    1
    130+
    (1 row)
    131+
    132+
    -- check
    133+
    -- we don't check planing times here to be independent of
    134+
    -- plan caching approach
    135+
    SELECT
    136+
    count(*) as total,
    137+
    count(*) FILTER (
    138+
    WHERE min_exec_time + max_exec_time = 0
    139+
    ) as minmax_exec_zero,
    140+
    count(*) FILTER (
    141+
    WHERE minmax_stats_since >= :'ref_ts'
    142+
    ) as minmax_ts_after_ref,
    143+
    count(*) FILTER (
    144+
    WHERE stats_since >= :'ref_ts'
    145+
    ) as stats_since_after_ref
    146+
    FROM pg_stat_statements
    147+
    WHERE query LIKE '%STMTTS%';
    148+
    total | minmax_exec_zero | minmax_ts_after_ref | stats_since_after_ref
    149+
    -------+------------------+---------------------+-----------------------
    150+
    2 | 1 | 2 | 0
    151+
    (1 row)
    152+
    153+
    -- Cleanup
    154+
    SELECT pg_stat_statements_reset() IS NOT NULL AS t;
    155+
    t
    156+
    ---
    157+
    t
    158+
    (1 row)
    159+

    ‎contrib/pg_stat_statements/expected/oldextversions.out

    Lines changed: 65 additions & 51 deletions
    Original file line numberDiff line numberDiff line change
    @@ -250,64 +250,78 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
    250250
    t
    251251
    (1 row)
    252252

    253-
    -- New views for pg_stat_statements in 1.11
    253+
    -- New functions and views for pg_stat_statements in 1.11
    254254
    AlTER EXTENSION pg_stat_statements UPDATE TO '1.11';
    255255
    \d pg_stat_statements
    256-
    View "public.pg_stat_statements"
    257-
    Column | Type | Collation | Nullable | Default
    258-
    ------------------------+------------------+-----------+----------+---------
    259-
    userid | oid | | |
    260-
    dbid | oid | | |
    261-
    toplevel | boolean | | |
    262-
    queryid | bigint | | |
    263-
    query | text | | |
    264-
    plans | bigint | | |
    265-
    total_plan_time | double precision | | |
    266-
    min_plan_time | double precision | | |
    267-
    max_plan_time | double precision | | |
    268-
    mean_plan_time | double precision | | |
    269-
    stddev_plan_time | double precision | | |
    270-
    calls | bigint | | |
    271-
    total_exec_time | double precision | | |
    272-
    min_exec_time | double precision | | |
    273-
    max_exec_time | double precision | | |
    274-
    mean_exec_time | double precision | | |
    275-
    stddev_exec_time | double precision | | |
    276-
    rows | bigint | | |
    277-
    shared_blks_hit | bigint | | |
    278-
    shared_blks_read | bigint | | |
    279-
    shared_blks_dirtied | bigint | | |
    280-
    shared_blks_written | bigint | | |
    281-
    local_blks_hit | bigint | | |
    282-
    local_blks_read | bigint | | |
    283-
    local_blks_dirtied | bigint | | |
    284-
    local_blks_written | bigint | | |
    285-
    temp_blks_read | bigint | | |
    286-
    temp_blks_written | bigint | | |
    287-
    shared_blk_read_time | double precision | | |
    288-
    shared_blk_write_time | double precision | | |
    289-
    local_blk_read_time | double precision | | |
    290-
    local_blk_write_time | double precision | | |
    291-
    temp_blk_read_time | double precision | | |
    292-
    temp_blk_write_time | double precision | | |
    293-
    wal_records | bigint | | |
    294-
    wal_fpi | bigint | | |
    295-
    wal_bytes | numeric | | |
    296-
    jit_functions | bigint | | |
    297-
    jit_generation_time | double precision | | |
    298-
    jit_inlining_count | bigint | | |
    299-
    jit_inlining_time | double precision | | |
    300-
    jit_optimization_count | bigint | | |
    301-
    jit_optimization_time | double precision | | |
    302-
    jit_emission_count | bigint | | |
    303-
    jit_emission_time | double precision | | |
    304-
    jit_deform_count | bigint | | |
    305-
    jit_deform_time | double precision | | |
    256+
    View "public.pg_stat_statements"
    257+
    Column | Type | Collation | Nullable | Default
    258+
    ------------------------+--------------------------+-----------+----------+---------
    259+
    userid | oid | | |
    260+
    dbid | oid | | |
    261+
    toplevel | boolean | | |
    262+
    queryid | bigint | | |
    263+
    query | text | | |
    264+
    plans | bigint | | |
    265+
    total_plan_time | double precision | | |
    266+
    min_plan_time | double precision | | |
    267+
    max_plan_time | double precision | | |
    268+
    mean_plan_time | double precision | | |
    269+
    stddev_plan_time | double precision | | |
    270+
    calls | bigint | | |
    271+
    total_exec_time | double precision | | |
    272+
    min_exec_time | double precision | | |
    273+
    max_exec_time | double precision | | |
    274+
    mean_exec_time | double precision | | |
    275+
    stddev_exec_time | double precision | | |
    276+
    rows | bigint | | |
    277+
    shared_blks_hit | bigint | | |
    278+
    shared_blks_read | bigint | | |
    279+
    shared_blks_dirtied | bigint | | |
    280+
    shared_blks_written | bigint | | |
    281+
    local_blks_hit | bigint | | |
    282+
    local_blks_read | bigint | | |
    283+
    local_blks_dirtied | bigint | | |
    284+
    local_blks_written | bigint | | |
    285+
    temp_blks_read | bigint | | |
    286+
    temp_blks_written | bigint | | |
    287+
    shared_blk_read_time | double precision | | |
    288+
    shared_blk_write_time | double precision | | |
    289+
    local_blk_read_time | double precision | | |
    290+
    local_blk_write_time | double precision | | |
    291+
    temp_blk_read_time | double precision | | |
    292+
    temp_blk_write_time | double precision | | |
    293+
    wal_records | bigint | | |
    294+
    wal_fpi | bigint | | |
    295+
    wal_bytes | numeric | | |
    296+
    jit_functions | bigint | | |
    297+
    jit_generation_time | double precision | | |
    298+
    jit_inlining_count | bigint | | |
    299+
    jit_inlining_time | double precision | | |
    300+
    jit_optimization_count | bigint | | |
    301+
    jit_optimization_time | double precision | | |
    302+
    jit_emission_count | bigint | | |
    303+
    jit_emission_time | double precision | | |
    304+
    jit_deform_count | bigint | | |
    305+
    jit_deform_time | double precision | | |
    306+
    stats_since | timestamp with time zone | | |
    307+
    minmax_stats_since | timestamp with time zone | | |
    306308

    307309
    SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
    308310
    has_data
    309311
    ----------
    310312
    t
    311313
    (1 row)
    312314

    315+
    -- New parameter minmax_only of pg_stat_statements_reset function
    316+
    SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
    317+
    pg_get_functiondef
    318+
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
    319+
    CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0, minmax_only boolean DEFAULT false)+
    320+
    RETURNS timestamp with time zone +
    321+
    LANGUAGE c +
    322+
    PARALLEL SAFE STRICT +
    323+
    AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_11$function$ +
    324+
    325+
    (1 row)
    326+
    313327
    DROP EXTENSION pg_stat_statements;

    ‎contrib/pg_stat_statements/meson.build

    Lines changed: 1 addition & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -49,6 +49,7 @@ tests += {
    4949
    'planning',
    5050
    'user_activity',
    5151
    'wal',
    52+
    'entry_timestamp',
    5253
    'cleanup',
    5354
    'oldextversions',
    5455
    ],

    ‎contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql

    Lines changed: 17 additions & 6 deletions
    Original file line numberDiff line numberDiff line change
    @@ -3,13 +3,10 @@
    33
    -- complain if script is sourced in psql, rather than via ALTER EXTENSION
    44
    \echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.11'" to load this file. \quit
    55

    6-
    /* First we have to remove them from the extension */
    7-
    ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
    8-
    ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
    9-
    10-
    /* Then we can drop them */
    6+
    /* Drop old versions */
    117
    DROP VIEW pg_stat_statements;
    128
    DROP FUNCTION pg_stat_statements(boolean);
    9+
    DROP FUNCTION pg_stat_statements_reset(Oid, Oid, bigint);
    1310

    1411
    /* Now redefine */
    1512
    CREATE FUNCTION pg_stat_statements(IN showtext boolean,
    @@ -59,7 +56,9 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
    5956
    OUT jit_emission_count int8,
    6057
    OUT jit_emission_time float8,
    6158
    OUT jit_deform_count int8,
    62-
    OUT jit_deform_time float8
    59+
    OUT jit_deform_time float8,
    60+
    OUT stats_since timestamp with time zone,
    61+
    OUT minmax_stats_since timestamp with time zone
    6362
    )
    6463
    RETURNS SETOF record
    6564
    AS 'MODULE_PATHNAME', 'pg_stat_statements_1_11'
    @@ -69,3 +68,15 @@ CREATE VIEW pg_stat_statements AS
    6968
    SELECT * FROM pg_stat_statements(true);
    7069

    7170
    GRANT SELECT ON pg_stat_statements TO PUBLIC;
    71+
    72+
    CREATE FUNCTION pg_stat_statements_reset(IN userid Oid DEFAULT 0,
    73+
    IN dbid Oid DEFAULT 0,
    74+
    IN queryid bigint DEFAULT 0,
    75+
    IN minmax_only boolean DEFAULT false
    76+
    )
    77+
    RETURNS timestamp with time zone
    78+
    AS 'MODULE_PATHNAME', 'pg_stat_statements_reset_1_11'
    79+
    LANGUAGE C STRICT PARALLEL SAFE;
    80+
    81+
    -- Don't want this to be available to non-superusers.
    82+
    REVOKE ALL ON FUNCTION pg_stat_statements_reset(Oid, Oid, bigint, boolean) FROM PUBLIC;

    0 commit comments

    Comments
     (0)
    0