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

Commitba90eac

Browse files
committed
pg_stat_statements: Expand tests for SET statements
There are many grammar flavors that depend on the parse nodeVariableSetStmt. This closes the gap in pg_stat_statements by providingtest coverage for what should be a large majority of them, improving morethe work begun inde2aca2. This will be used to ease theevaluation of a path towards more normalization of SET queries withquery jumbling.Note that SET NAMES (grammar from the standard, synonym of SETclient_encoding) is omitted on purpose, this could use UTF8 with aconditional script where UTF8 is supported, but that does not seem worththe maintenance cost for the sake of these tests.The author has submitted most of these in a TAP test (filled in anyholes I could spot), still queries in a SQL file of pg_stat_statementsis able to achieve the same goal while being easier to look at whentesting normalization patterns.Author: Greg Sabino Mullane, Michael PaquierDiscussion:https://postgr.es/m/CAKAnmmJtJY2jzQN91=2QAD2eAJAA-Per61eyO48-TyxEg-q0Rg@mail.gmail.com
1 parentaac2c9b commitba90eac

File tree

2 files changed

+132
-10
lines changed

2 files changed

+132
-10
lines changed

‎contrib/pg_stat_statements/expected/utility.out

Lines changed: 87 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,8 @@ DROP SERVER server_stats;
6666
DROP FOREIGN DATA WRAPPER wrapper_stats;
6767
-- Functions
6868
CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
69-
RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL;
69+
RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL
70+
SET work_mem = '256kB';
7071
DROP FUNCTION func_stats;
7172
-- Rules
7273
CREATE TABLE tab_rule_stats (a int, b int);
@@ -106,7 +107,8 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
106107
1 | 0 | CREATE FOREIGN DATA WRAPPER wrapper_stats
107108
1 | 0 | CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats
108109
1 | 0 | CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))+
109-
| | RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL
110+
| | RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL +
111+
| | SET work_mem = '256kB'
110112
1 | 0 | CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql +
111113
| | AS $$ BEGIN return OLD; end; $$
112114
1 | 0 | CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a)
@@ -551,46 +553,104 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
551553

552554
-- SET statements.
553555
-- These use two different strings, still they count as one entry.
556+
CREATE ROLE regress_stat_set_1;
557+
CREATE ROLE regress_stat_set_2;
554558
SET work_mem = '1MB';
555559
Set work_mem = '1MB';
556560
SET work_mem = '2MB';
561+
SET work_mem = DEFAULT;
562+
SET work_mem TO DEFAULT;
563+
SET work_mem FROM CURRENT;
564+
BEGIN;
565+
SET LOCAL work_mem = '128kB';
566+
SET LOCAL work_mem = '256kB';
567+
SET LOCAL work_mem = DEFAULT;
568+
SET LOCAL work_mem TO DEFAULT;
569+
SET LOCAL work_mem FROM CURRENT;
570+
COMMIT;
557571
RESET work_mem;
558572
SET enable_seqscan = off;
559573
SET enable_seqscan = on;
574+
SET SESSION work_mem = '300kB';
575+
SET SESSION work_mem = '400kB';
560576
RESET enable_seqscan;
561577
-- SET TRANSACTION ISOLATION
562578
BEGIN;
563579
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
564580
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
565581
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
566582
COMMIT;
567-
-- SET SESSIONCHARACTERISTICS
583+
-- SET SESSIONAUTHORIZATION
568584
SET SESSION SESSION AUTHORIZATION DEFAULT;
585+
SET SESSION AUTHORIZATION 'regress_stat_set_1';
586+
SET SESSION AUTHORIZATION 'regress_stat_set_2';
569587
RESET SESSION AUTHORIZATION;
570588
BEGIN;
571589
SET LOCAL SESSION AUTHORIZATION DEFAULT;
590+
SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_1';
591+
SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_2';
572592
RESET SESSION AUTHORIZATION;
573593
COMMIT;
594+
-- SET SESSION CHARACTERISTICS
595+
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
596+
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ ONLY;
597+
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ WRITE;
598+
-- SET XML OPTION
599+
SET XML OPTION DOCUMENT;
600+
SET XML OPTION CONTENT;
601+
-- SET TIME ZONE
602+
SET TIME ZONE 'America/New_York';
603+
SET TIME ZONE 'Asia/Tokyo';
604+
SET TIME ZONE DEFAULT;
605+
SET TIME ZONE LOCAL;
606+
SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0';
607+
RESET TIME ZONE;
574608
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
575-
calls | rows | query
576-
-------+------+----------------------------------------------------
577-
2 | 0 | BEGIN
578-
2 | 0 | COMMIT
609+
calls | rows | query
610+
-------+------+------------------------------------------------------------------
611+
3 | 0 | BEGIN
612+
3 | 0 | COMMIT
613+
1 | 0 | CREATE ROLE regress_stat_set_1
614+
1 | 0 | CREATE ROLE regress_stat_set_2
579615
2 | 0 | RESET SESSION AUTHORIZATION
616+
1 | 0 | RESET TIME ZONE
580617
1 | 0 | RESET enable_seqscan
581618
1 | 0 | RESET work_mem
582619
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
620+
1 | 0 | SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_1'
621+
1 | 0 | SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_2'
583622
1 | 0 | SET LOCAL SESSION AUTHORIZATION DEFAULT
623+
1 | 0 | SET LOCAL work_mem = '128kB'
624+
1 | 0 | SET LOCAL work_mem = '256kB'
625+
2 | 0 | SET LOCAL work_mem = DEFAULT
626+
1 | 0 | SET LOCAL work_mem FROM CURRENT
627+
1 | 0 | SET SESSION AUTHORIZATION 'regress_stat_set_1'
628+
1 | 0 | SET SESSION AUTHORIZATION 'regress_stat_set_2'
629+
1 | 0 | SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY
630+
1 | 0 | SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ ONLY
631+
1 | 0 | SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ WRITE
584632
1 | 0 | SET SESSION SESSION AUTHORIZATION DEFAULT
633+
1 | 0 | SET SESSION work_mem = '300kB'
634+
1 | 0 | SET SESSION work_mem = '400kB'
635+
1 | 0 | SET TIME ZONE 'America/New_York'
636+
1 | 0 | SET TIME ZONE 'Asia/Tokyo'
637+
1 | 0 | SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0'
638+
2 | 0 | SET TIME ZONE DEFAULT
585639
1 | 0 | SET TRANSACTION ISOLATION LEVEL READ COMMITTED
586640
1 | 0 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
587641
1 | 0 | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
642+
1 | 0 | SET XML OPTION CONTENT
643+
1 | 0 | SET XML OPTION DOCUMENT
588644
1 | 0 | SET enable_seqscan = off
589645
1 | 0 | SET enable_seqscan = on
590646
2 | 0 | SET work_mem = '1MB'
591647
1 | 0 | SET work_mem = '2MB'
592-
(15 rows)
648+
2 | 0 | SET work_mem = DEFAULT
649+
1 | 0 | SET work_mem FROM CURRENT
650+
(39 rows)
593651

652+
DROP ROLE regress_stat_set_1;
653+
DROP ROLE regress_stat_set_2;
594654
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
595655
t
596656
---
@@ -664,3 +724,22 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
664724
t
665725
(1 row)
666726

727+
-- Special cases. Keep these ones at the end to avoid conflicts.
728+
SET SCHEMA 'foo';
729+
SET SCHEMA 'public';
730+
RESET ALL;
731+
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
732+
calls | rows | query
733+
-------+------+----------------------------------------------------
734+
1 | 0 | RESET ALL
735+
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)
739+
740+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
741+
t
742+
---
743+
t
744+
(1 row)
745+

‎contrib/pg_stat_statements/sql/utility.sql

Lines changed: 45 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,8 @@ DROP FOREIGN DATA WRAPPER wrapper_stats;
4747

4848
-- Functions
4949
CREATEFUNCTIONfunc_stats(atext DEFAULT'a_data', btext DEFAULTlower('b_data'))
50-
RETURNStextAS $$SELECT $1::text||'_'|| $2::text; $$ LANGUAGE SQL;
50+
RETURNStextAS $$SELECT $1::text||'_'|| $2::text; $$ LANGUAGE SQL
51+
SET work_mem='256kB';
5152
DROPFUNCTION func_stats;
5253

5354
-- Rules
@@ -277,28 +278,62 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
277278

278279
-- SET statements.
279280
-- These use two different strings, still they count as one entry.
281+
CREATE ROLE regress_stat_set_1;
282+
CREATE ROLE regress_stat_set_2;
280283
SET work_mem='1MB';
281284
Set work_mem='1MB';
282285
SET work_mem='2MB';
286+
SET work_mem= DEFAULT;
287+
SET work_mem TO DEFAULT;
288+
SET work_memFROM CURRENT;
289+
BEGIN;
290+
SET LOCAL work_mem='128kB';
291+
SET LOCAL work_mem='256kB';
292+
SET LOCAL work_mem= DEFAULT;
293+
SET LOCAL work_mem TO DEFAULT;
294+
SET LOCAL work_memFROM CURRENT;
295+
COMMIT;
283296
RESET work_mem;
284297
SET enable_seqscan= off;
285298
SET enable_seqscan=on;
299+
SET SESSION work_mem='300kB';
300+
SET SESSION work_mem='400kB';
286301
RESET enable_seqscan;
287302
-- SET TRANSACTION ISOLATION
288303
BEGIN;
289304
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
290305
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
291306
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
292307
COMMIT;
293-
-- SET SESSIONCHARACTERISTICS
308+
-- SET SESSIONAUTHORIZATION
294309
SET SESSION SESSION AUTHORIZATION DEFAULT;
310+
SET SESSION AUTHORIZATION'regress_stat_set_1';
311+
SET SESSION AUTHORIZATION'regress_stat_set_2';
295312
RESET SESSION AUTHORIZATION;
296313
BEGIN;
297314
SET LOCAL SESSION AUTHORIZATION DEFAULT;
315+
SET LOCAL SESSION AUTHORIZATION'regress_stat_set_1';
316+
SET LOCAL SESSION AUTHORIZATION'regress_stat_set_2';
298317
RESET SESSION AUTHORIZATION;
299318
COMMIT;
319+
-- SET SESSION CHARACTERISTICS
320+
SET SESSION CHARACTERISTICSAS TRANSACTION READ ONLY;
321+
SET SESSION CHARACTERISTICSAS TRANSACTION READ ONLY, READ ONLY;
322+
SET SESSION CHARACTERISTICSAS TRANSACTION READ ONLY, READ WRITE;
323+
-- SET XML OPTION
324+
SET XML OPTION DOCUMENT;
325+
SET XML OPTION CONTENT;
326+
-- SET TIME ZONE
327+
SETTIME ZONE'America/New_York';
328+
SETTIME ZONE'Asia/Tokyo';
329+
SETTIME ZONE DEFAULT;
330+
SETTIME ZONE LOCAL;
331+
SETTIME ZONE'CST7CDT,M4.1.0,M10.5.0';
332+
RESETTIME ZONE;
300333

301334
SELECT calls, rows, queryFROM pg_stat_statementsORDER BY query COLLATE"C";
335+
DROP ROLE regress_stat_set_1;
336+
DROP ROLE regress_stat_set_2;
302337
SELECT pg_stat_statements_reset()IS NOT NULLAS t;
303338

304339
--
@@ -329,3 +364,11 @@ DROP TABLE pgss_ctas;
329364
DROPTABLE pgss_select_into;
330365

331366
SELECT pg_stat_statements_reset()IS NOT NULLAS t;
367+
368+
-- Special cases. Keep these ones at the end to avoid conflicts.
369+
SET SCHEMA'foo';
370+
SET SCHEMA'public';
371+
RESET ALL;
372+
SELECT calls, rows, queryFROM pg_stat_statementsORDER BY query COLLATE"C";
373+
374+
SELECT pg_stat_statements_reset()IS NOT NULLAS t;

0 commit comments

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