Skip to content

Commit ba90eac

Browse files
michaelpqmichaelpq
committed
pg_stat_statements: Expand tests for SET statements
There are many grammar flavors that depend on the parse node VariableSetStmt. This closes the gap in pg_stat_statements by providing test coverage for what should be a large majority of them, improving more the work begun in de2aca2. This will be used to ease the evaluation of a path towards more normalization of SET queries with query jumbling. Note that SET NAMES (grammar from the standard, synonym of SET client_encoding) is omitted on purpose, this could use UTF8 with a conditional script where UTF8 is supported, but that does not seem worth the maintenance cost for the sake of these tests. The author has submitted most of these in a TAP test (filled in any holes I could spot), still queries in a SQL file of pg_stat_statements is able to achieve the same goal while being easier to look at when testing normalization patterns. Author: Greg Sabino Mullane, Michael Paquier Discussion: https://postgr.es/m/CAKAnmmJtJY2jzQN91=2QAD2eAJAA-Per61eyO48-TyxEg-q0Rg@mail.gmail.com
1 parent aac2c9b commit ba90eac

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 SESSION CHARACTERISTICS
583+
-- SET SESSION AUTHORIZATION
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
CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
50-
RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL;
50+
RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL
51+
SET work_mem = '256kB';
5152
DROP FUNCTION 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_mem FROM 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_mem FROM 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 SESSION CHARACTERISTICS
308+
-- SET SESSION AUTHORIZATION
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 CHARACTERISTICS AS TRANSACTION READ ONLY;
321+
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ ONLY;
322+
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ WRITE;
323+
-- SET XML OPTION
324+
SET XML OPTION DOCUMENT;
325+
SET XML OPTION CONTENT;
326+
-- SET TIME ZONE
327+
SET TIME ZONE 'America/New_York';
328+
SET TIME ZONE 'Asia/Tokyo';
329+
SET TIME ZONE DEFAULT;
330+
SET TIME ZONE LOCAL;
331+
SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0';
332+
RESET TIME ZONE;
300333

301334
SELECT calls, rows, query FROM pg_stat_statements ORDER 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 NULL AS t;
303338

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

331366
SELECT pg_stat_statements_reset() IS NOT NULL AS 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, query FROM pg_stat_statements ORDER BY query COLLATE "C";
373+
374+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;

0 commit comments

Comments
 (0)