8000 Show values of SET statements as constants in pg_stat_statements · postgres/postgres@dc68515 · GitHub
[go: up one dir, main page]
Skip to content

Commit dc68515

Browse files
michaelpqmichaelpq
committed
Show values of SET statements as constants in pg_stat_statements
This is a continuation of work like 11c34b3, done to reduce the bloat of pg_stat_statements by applying more normalization to query entries. This commit is able to detect and normalize values in VariableSetStmt, resulting in: SET conf_param = $1 Compared to other parse nodes, VariableSetStmt is embedded in much more places in the parser, impacting many query patterns in pg_stat_statements. A custom jumble function is used, with an extra field in the node to decide if arguments should be included in the jumbling or not, a location field being not enough for this purpose. This approach allows for a finer tuning. Clauses relying on one or more keywords are not normalized, for example: * DEFAULT * FROM CURRENT * List of keywords. SET SESSION CHARACTERISTICS AS TRANSACTION, where it is critical to differentiate different sets of options, is a good example of why normalization should not happen. Some queries use VariableSetStmt for some subclauses with SET, that also have their values normalized: - ALTER DATABASE - ALTER ROLE - ALTER SYSTEM - CREATE/ALTER FUNCTION ba90eac has added test coverage for most of the existing SET patterns. The expected output of these tests shows the difference this commit creates. Normalization could be perhaps applied to more portions of the grammar but what is done here is conservative, and good enough as a starting point. Author: Greg Sabino Mullane, Michael Paquier Discussion: https://postgr.es/m/36e5bffe-e989-194f-85c8-06e7bc88e6f7@amazon.com Discussion: https://postgr.es/m/B44FA29D-EBD0-4DD9-ABC2-16F1CB087074@amazon.com Discussion: https://postgr.es/m/CAKAnmmJtJY2jzQN91=2QAD2eAJAA-Per61eyO48-TyxEg-q0Rg@mail.gmail.com
1 parent 559efce commit dc68515

File tree

7 files changed

+71
-19
lines changed
  • utility.out
  • wal.out
  • src

    • 7 files changed

    +71
    -19
    lines changed

    ‎contrib/pg_stat_statements/expected/dml.out

    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -82,7 +82,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
    8282
    2 | 4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a
    8383
    1 | 8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1, $2, $3, $4, $5)
    8484
    1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
    85-
    1 | 0 | SET pg_stat_statements.track_utility = FALSE
    85+
    1 | 0 | SET pg_stat_statements.track_utility = $1
    8686
    6 | 6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2
    8787
    1 | 3 | UPDATE pgss_dml_tab SET b = $1 WHERE a > $2
    8888
    (10 rows)

    ‎contrib/pg_stat_statements/expected/level_tracking.out

    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -64,7 +64,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements
    6464
    | | END; $$
    6565
    f | 1 | SELECT $1::TEXT
    6666
    t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
    67-
    t | 1 | SET pg_stat_statements.track = 'all'
    67+
    t | 1 | SET pg_stat_statements.track = $1
    6868
    (7 rows)
    6969

    7070
    -- Procedure with multiple utility statements.

    ‎contrib/pg_stat_statements/expected/utility.out

    Lines changed: 7 additions & 13 deletions
    Original file line numberDiff line numberDiff line change
    @@ -108,7 +108,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
    108108
    1 | 0 | CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats
    109109
    1 | 0 | CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))+
    110110
    | | RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL +
    111-
    | | SET work_mem = '256kB'
    111+
    | | SET work_mem = $1
    112112
    1 | 0 | CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql +
    113113
    | | AS $$ BEGIN return OLD; end; $$
    114114
    1 | 0 | CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a)
    @@ -620,8 +620,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
    620620
    1 | 0 | SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_1'
    621621
    1 | 0 | SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_2'
    622622
    1 | 0 | SET LOCAL SESSION AUTHORIZATION DEFAULT
    623-
    1 | 0 | SET LOCAL work_mem = '128kB'
    624-
    1 | 0 | SET LOCAL work_mem = '256kB'
    623+
    2 | 0 | SET LOCAL work_mem = $1
    625624
    2 | 0 | SET LOCAL work_mem = DEFAULT
    626625
    1 | 0 | SET LOCAL work_mem FROM CURRENT
    627626
    1 | 0 | SET SESSION AUTHORIZATION 'regress_stat_set_1'
    @@ -630,8 +629,6 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
    630629
    1 | 0 | SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ ONLY
    631630
    1 | 0 | SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ WRITE
    632631
    1 | 0 | SET SESSION SESSION AUTHORIZATION DEFAULT
    633-
    1 | 0 | SET SESSION work_mem = '300kB'
    634-
    1 | 0 | SET SESSION work_mem = '400kB'
    635632
    1 | 0 | SET TIME ZONE 'America/New_York'
    636633
    1 | 0 | SET TIME ZONE 'Asia/Tokyo'
    637634
    1 | 0 | SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0'
    @@ -641,13 +638,11 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
    641638
    1 | 0 | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    642639
    1 | 0 | SET XML OPTION CONTENT
    643640
    1 | 0 | SET XML OPTION DOCUMENT
    644-
    1 | 0 | SET enable_seqscan = off
    645-
    1 | 0 | SET enable_seqscan = on
    646-
    2 | 0 | SET work_mem = '1MB'
    647-
    1 | 0 | SET work_mem = '2MB'
    641+
    2 | 0 | SET enable_seqscan = $1
    642+
    5 | 0 | SET work_mem = $1
    648643
    2 | 0 | SET work_mem = DEFAULT
    649644
    1 | 0 | SET work_mem FROM CURRENT
    650-
    (39 rows)
    645+
    (34 rows)
    651646

    652647
    DROP ROLE regress_stat_set_1;
    653648
    DROP ROLE regress_stat_set_2;
    @@ -733,9 +728,8 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
    733728
    -------+------+----------------------------------------------------
    734729
    1 | 0 | RESET ALL
    735730
    1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
    736-
    1 | 0 | SET SCHEMA 'foo'
    737-
    1 | 0 | SET SCHEMA 'public'
    738-
    (4 rows)
    731+
    2 | 0 | SET SCHEMA $1
    732+
    (3 rows)
    739733

    740734
    SELECT pg_stat_statements_reset() IS NOT NULL AS t;
    741735
    t

    ‎contrib/pg_stat_statements/expected/wal.out

    Copy file name to clipboard
    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -18,7 +18,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
    1818
    DELETE FROM pgss_wal_tab WHERE a > $1 | 1 | 1 | t | t | t
    1919
    INSERT INTO pgss_wal_tab VALUES(generate_series($1, $2), $3) | 1 | 10 | t | t | t
    2020
    SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 | f | f | f
    21-
    SET pg_stat_statements.track_utility = FALSE | 1 | 0 | f | f | t
    21+
    SET pg_stat_statements.track_utility = $1 | 1 | 0 | f | f | t
    2222
    UPDATE pgss_wal_tab SET b = $1 WHERE a > $2 | 1 | 3 | t | t | t
    2323
    (5 rows)
    2424

    ‎src/backend/nodes/queryjumblefuncs.c

    Lines changed: 19 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -57,6 +57,7 @@ static void RecordConstLocation(JumbleState *jstate, int location);
    5757
    static void _jumbleNode(JumbleState *jstate, Node *node);
    5858
    static void _jumbleA_Const(JumbleState *jstate, Node *node);
    5959
    static void _jumbleList(JumbleState *jstate, Node *node);
    60+
    static void _jumbleVariableSetStmt(JumbleState *jstate, Node *node);
    6061

    6162
    /*
    6263
    * Given a possibly multi-statement source string, confine our attention to the
    @@ -352,3 +353,21 @@ _jumbleA_Const(JumbleState *jstate, Node *node)
    352353
    }
    353354
    }
    354355
    }
    356+
    357+
    static void
    358+
    _jumbleVariableSetStmt(JumbleState *jstate, Node *node)
    359+
    {
    360+
    VariableSetStmt *expr = (VariableSetStmt *) node;
    361+
    362+
    JUMBLE_FIELD(kind);
    363+
    JUMBLE_STRING(name);
    364+
    365+
    /*
    366+
    * Account for the list of arguments in query jumbling only if told by the
    367+
    * parser.
    368+
    */
    369+
    if (expr->jumble_args)
    370+
    JUMBLE_NODE(args);
    371+
    JUMBLE_FIELD(is_local);
    372+
    JUMBLE_LOCATION(location);
    373+
    }

    ‎src/backend/parser/gram.y

    Lines changed: 24 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1647,6 +1647,8 @@ set_rest:
    16471647
    n->kind = VAR_SET_MULTI;
    16481648
    n->name = "TRANSACTION";
    16491649
    n->args = $2;
    1650+
    n->jumble_args = true;
    1651+
    n->location = -1;
    16501652
    $$ = n;
    16511653
    }
    16521654
    | SESSION CHARACTERISTICS AS TRANSACTION transaction_mode_list
    @@ -1656,6 +1658,8 @@ set_rest:
    16561658
    n->kind = VAR_SET_MULTI;
    16571659
    n->name = "SESSION CHARACTERISTICS";
    16581660
    n->args = $5;
    1661+
    n->jumble_args = true;
    1662+
    n->location = -1;
    16591663
    $$ = n;
    16601664
    }
    16611665
    | set_rest_more
    @@ -1669,6 +1673,7 @@ generic_set:
    16691673
    n->kind = VAR_SET_VALUE;
    16701674
    n->name = $1;
    16711675
    n->args = $3;
    1676+
    n->location = @3;
    16721677
    $$ = n;
    16731678
    }
    16741679
    | var_name '=' var_list
    @@ -1678,6 +1683,7 @@ generic_set:
    16781683
    n->kind = VAR_SET_VALUE;
    16791684
    n->name = $1;
    16801685
    n->args = $3;
    1686+
    n->location = @3;
    16811687
    $$ = n;
    16821688
    }
    16831689
    | var_name TO DEFAULT
    @@ -1686,6 +1692,7 @@ generic_set:
    16861692

    16871693
    n->kind = VAR_SET_DEFAULT;
    16881694
    n->name = $1;
    1695+
    n->location = -1;
    16891696
    $$ = n;
    16901697
    }
    16911698
    | var_name '=' DEFAULT
    @@ -1694,6 +1701,7 @@ generic_set:
    16941701

    16951702
    n->kind = VAR_SET_DEFAULT;
    16961703
    n->name = $1;
    1704+
    n->location = -1;
    16971705
    $$ = n;
    16981706
    }
    16991707
    ;
    @@ -1706,6 +1714,7 @@ set_rest_more: /* Generic SET syntaxes: */
    17061714

    17071715
    n->kind = VAR_SET_CURRENT;
    17081716
    n->name = $1;
    1717+
    n->location = -1;
    17091718
    $$ = n;
    17101719
    }
    17111720
    /* Special syntaxes mandated by SQL standard: */
    @@ -1715,6 +1724,8 @@ set_rest_more: /* Generic SET syntaxes: */
    17151724

    17161725
    n->kind = VAR_SET_VALUE;
    17171726
    n->name = "timezone";
    1727+
    n->location = -1;
    1728+
    n->jumble_args = true;
    17181729
    if ($3 != NULL)
    17191730
    n->args = list_make1($3);
    17201731
    else
    @@ -1736,6 +1747,7 @@ set_rest_more: /* Generic SET syntaxes: */
    17361747
    n->kind = VAR_SET_VALUE;
    17371748
    n->name = "search_path";
    17381749
    n->args = list_make1(makeStringConst($2, @2));
    1750+
    n->location = @2;
    17391751
    $$ = n;
    17401752
    }
    17411753
    | NAMES opt_encoding
    @@ -1744,6 +1756,7 @@ set_rest_more: /* Generic SET syntaxes: */
    17441756

    17451757
    n->kind = VAR_SET_VALUE;
    17461758
    n->name = "client_encoding";
    1759+
    n->location = @2;
    17471760
    if ($2 != NULL)
    17481761
    n->args = list_make1(makeStringConst($2, @2));
    17491762
    else
    @@ -1757,6 +1770,7 @@ set_rest_more: /* Generic SET syntaxes: */
    17571770
    n->kind = VAR_SET_VALUE;
    17581771
    n->name = "role";
    17591772
    n->args = list_make1(makeStringConst($2, @2));
    1773+
    n->location = @2;
    17601774
    $$ = n;
    17611775
    }
    17621776
    | SESSION AUTHORIZATION NonReservedWord_or_Sconst
    @@ -1766,6 +1780,7 @@ set_rest_more: /* Generic SET syntaxes: */
    17661780
    n->kind = VAR_SET_VALUE;
    17671781
    n->name = "session_authorization";
    17681782
    n->args = list_make1(makeStringConst($3, @3));
    1783+
    n->location = @3;
    17691784
    $$ = n;
    17701785
    }
    17711786
    | SESSION AUTHORIZATION DEFAULT
    @@ -1774,6 +1789,7 @@ set_rest_more: /* Generic SET syntaxes: */
    17741789

    17751790
    n->kind = VAR_SET_DEFAULT;
    17761791
    n->name = "session_authorization";
    1792+
    n->location = -1;
    17771793
    $$ = n;
    17781794
    }
    17791795
    | XML_P OPTION document_or_content
    @@ -1783,6 +1799,8 @@ set_rest_more: /* Generic SET syntaxes: */
    17831799
    n->kind = VAR_SET_VALUE;
    17841800
    n->name = "xmloption";
    17851801
    n->args = list_make1(makeStringConst($3 == XMLOPTION_DOCUMENT ? "DOCUMENT" : "CONTENT", @3));
    1802+
    n->jumble_args = true;
    1803+
    n->location = -1;
    17861804
    $$ = n;
    17871805
    }
    17881806
    /* Special syntaxes invented by PostgreSQL: */
    @@ -1793,6 +1811,7 @@ set_rest_more: /* Generic SET syntaxes: */
    17931811
    n->kind = VAR_SET_MULTI;
    17941812
    n->name = "TRANSACTION SNAPSHOT";
    17951813
    n->args = list_make1(makeStringConst($3, @3));
    1814+
    n->location = @3;
    17961815
    $$ = n;
    17971816
    }
    17981817
    ;
    @@ -1900,6 +1919,7 @@ reset_rest:
    19001919

    19011920
    n->kind = VAR_RESET;
    19021921
    n->name = "timezone";
    1922+
    n->location = -1;
    19031923
    $$ = n;
    19041924
    }
    19051925
    | TRANSACTION ISOLATION LEVEL
    @@ -1908,6 +1928,7 @@ reset_rest:
    19081928

    19091929
    n->kind = VAR_RESET;
    19101930
    n->name = "transaction_isolation";
    1931+
    n->location = -1;
    19111932
    $$ = n;
    19121933
    }
    19131934
    | SESSION AUTHORIZATION
    @@ -1916,6 +1937,7 @@ reset_rest:
    19161937

    19171938
    n->kind = VAR_RESET;
    19181939
    n->name = "session_authorization";
    1940+
    n->location = -1;
    19191941
    $$ = n;
    19201942
    }
    19211943
    ;
    @@ -1927,13 +1949,15 @@ generic_reset:
    19271949

    19281950
    n->kind = VAR_RESET;
    19291951
    n->name = $1;
    1952+
    n->location = -1;
    19301953
    $$ = n;
    19311954
    }
    19321955
    | ALL
    19331956
    {
    19341957
    VariableSetStmt *n = makeNode(VariableSetStmt);
    19351958

    19361959
    n->kind = VAR_RESET_ALL;
    1960+
    n->location = -1;
    19371961
    $$ = n;
    19381962
    }
    19391963
    ;

    ‎src/include/nodes/parsenodes.h

    Lines changed: 18 additions & 3 deletions
    Original file line numberDiff line numberDiff line change
    @@ -2616,11 +2616,26 @@ typedef enum VariableSetKind
    26162616

    26172617
    typedef struct VariableSetStmt
    26182618
    {
    2619+
    pg_node_attr(custom_query_jumble)
    2620+
    26192621
    NodeTag type;
    26202622
    VariableSetKind kind;
    2621-
    char *name; /* variable to be set */
    2622-
    List *args; /* List of A_Const nodes */
    2623-
    bool is_local; /* SET LOCAL? */
    2623+
    /* variable to be set */
    2624+
    char *name;
    2625+
    /* List of A_Const nodes */
    2626+
    List *args;
    2627+
    2628+
    /*
    2629+
    * True if arguments should be accounted for in query jumbling. We use a
    2630+
    * separate flag rather than query_jumble_ignore on "args" as several
    2631+
    * grammar flavors of SET rely on a list of values that are parsed
    2632+
    * directly from the grammar's keywords.
    2633+
    */
    2634+
    bool jumble_args;
    2635+
    /* SET LOCAL? */
    2636+
    bool is_local;
    2637+
    /* token location, or -1 if unknown */
    2638+
    ParseLoc location pg_node_attr(query_jumble_location);
    26242639
    } VariableSetStmt;
    26252640

    26262641
    /* ----------------------

    0 commit comments

    Comments
     (0)
    0