Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitdc68515

Browse files
committed
Show values of SET statements as constants in pg_stat_statements
This is a continuation of work like11c34b3, done to reduce thebloat of pg_stat_statements by applying more normalization to queryentries. This commit is able to detect and normalize values inVariableSetStmt, resulting in:SET conf_param = $1Compared to other parse nodes, VariableSetStmt is embedded in much moreplaces in the parser, impacting many query patterns inpg_stat_statements. A custom jumble function is used, with an extrafield in the node to decide if arguments should be included in thejumbling 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 agood example of why normalization should not happen.Some queries use VariableSetStmt for some subclauses with SET, that alsohave their values normalized:- ALTER DATABASE- ALTER ROLE- ALTER SYSTEM- CREATE/ALTER FUNCTIONba90eac has added test coverage for most of the existing SETpatterns. The expected output of these tests shows the difference thiscommit creates. Normalization could be perhaps applied to more portionsof the grammar but what is done here is conservative, and good enough asa starting point.Author: Greg Sabino Mullane, Michael PaquierDiscussion:https://postgr.es/m/36e5bffe-e989-194f-85c8-06e7bc88e6f7@amazon.comDiscussion:https://postgr.es/m/B44FA29D-EBD0-4DD9-ABC2-16F1CB087074@amazon.comDiscussion:https://postgr.es/m/CAKAnmmJtJY2jzQN91=2QAD2eAJAA-Per61eyO48-TyxEg-q0Rg@mail.gmail.com
1 parent559efce commitdc68515

File tree

7 files changed

+71
-19
lines changed

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

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
staticvoid_jumbleNode(JumbleState*jstate,Node*node);
5858
staticvoid_jumbleA_Const(JumbleState*jstate,Node*node);
5959
staticvoid_jumbleList(JumbleState*jstate,Node*node);
60+
staticvoid_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+
staticvoid
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
|SESSIONCHARACTERISTICSASTRANSACTIONtransaction_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_nameTODEFAULT
@@ -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
|NAMESopt_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
|SESSIONAUTHORIZATIONNonReservedWord_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
|SESSIONAUTHORIZATIONDEFAULT
@@ -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_POPTIONdocument_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
|TRANSACTIONISOLATIONLEVEL
@@ -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
|SESSIONAUTHORIZATION
@@ -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
typedefstructVariableSetStmt
26182618
{
2619+
pg_node_attr(custom_query_jumble)
2620+
26192621
NodeTagtype;
26202622
VariableSetKindkind;
2621-
char*name;/* variable to be set */
2622-
List*args;/* List of A_Const nodes */
2623-
boolis_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+
booljumble_args;
2635+
/* SET LOCAL? */
2636+
boolis_local;
2637+
/* token location, or -1 if unknown */
2638+
ParseLoclocationpg_node_attr(query_jumble_location);
26242639
}VariableSetStmt;
26252640

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp