Skip to content

Commit 3430215

Browse files
michaelpqmichaelpq
committed
pg_stat_statements: Add more tests with temp tables and namespaces
These tests provide coverage for RangeTblEntry and how query jumbling works with search_path, as well as the case where relations are re-created, generating a different query ID as the relation OID is used in the computation. A patch is under discussion to switch to a different approach based on the relation name, and there was no test coverage for this area, including how queries are currently grouped with search_path. This is useful to track how the situation changes between HEAD and any patches proposed. Christoph has proposed the test with ON COMMIT DROP temporary tables, and I have written the second part. Author: Christoph Berg <myon@debian.org> Author: Michael Paquier <michael@paquier.xyz> Discussion: https://postgr.es/m/Z9iWXKGwkm8RAC93@msg.df7cb.de
1 parent 626d723 commit 3430215

File tree

2 files changed

+309
-0
lines changed

2 files changed

+309
-0
lines changed

‎contrib/pg_stat_statements/expected/select.out

Lines changed: 240 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -413,3 +413,243 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
413413
t
414414
(1 row)
415415

416+
-- Temporary table with same name, re-created.
417+
BEGIN;
418+
CREATE TEMP TABLE temp_t (id int) ON COMMIT DROP;
419+
SELECT * FROM temp_t;
420+
id
421+
----
422+
(0 rows)
423+
424+
COMMIT;
425+
BEGIN;
426+
CREATE TEMP TABLE temp_t (id int) ON COMMIT DROP;
427+
SELECT * FROM temp_t;
428+
id
429+
----
430+
(0 rows)
431+
432+
COMMIT;
433+
SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
434+
calls | query
435+
-------+------------------------------------------------------------------------
436+
1 | SELECT * FROM temp_t
437+
1 | SELECT * FROM temp_t
438+
0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"
439+
1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
440+
(4 rows)
441+
442+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
443+
t
444+
---
445+
t
446+
(1 row)
447+
448+
-- search_path with various schemas and temporary tables
449+
CREATE SCHEMA pgss_schema_1;
450+
CREATE SCHEMA pgss_schema_2;
451+
-- Same attributes.
452+
CREATE TABLE pgss_schema_1.tab_search_same (a int, b int);
453+
CREATE TABLE pgss_schema_2.tab_search_same (a int, b int);
454+
CREATE TEMP TABLE tab_search_same (a int, b int);
455+
-- Different number of attributes, mapping types
456+
CREATE TABLE pgss_schema_1.tab_search_diff_1 (a int);
457+
CREATE TABLE pgss_schema_2.tab_search_diff_1 (a int, b int);
458+
CREATE TEMP TABLE tab_search_diff_1 (a int, b int, c int);
459+
-- Same number of attributes, different types
460+
CREATE TABLE pgss_schema_1.tab_search_diff_2 (a int);
461+
CREATE TABLE pgss_schema_2.tab_search_diff_2 (a text);
462+
CREATE TEMP TABLE tab_search_diff_2 (a bigint);
463+
-- First permanent schema
464+
SET search_path = 'pgss_schema_1';
465+
SELECT count(*) FROM tab_search_same;
466+
count
467+
-------
468+
0
469+
(1 row)
470+
471+
SELECT a, b FROM tab_search_same;
472+
a | b
473+
---+---
474+
(0 rows)
475+
476+
SELECT count(*) FROM tab_search_diff_1;
477+
count
478+
-------
479+
0
480+
(1 row)
481+
482+
SELECT count(*) FROM tab_search_diff_2;
483+
count
484+
-------
485+
0
486+
(1 row)
487+
488+
SELECT a FROM tab_search_diff_2 AS t1;
489+
a
490+
---
491+
(0 rows)
492+
493+
SELECT a FROM tab_search_diff_2;
494+
a
495+
---
496+
(0 rows)
497+
498+
SELECT a AS a1 FROM tab_search_diff_2;
499+
a1
500+
----
501+
(0 rows)
502+
503+
-- Second permanent schema
504+
SET search_path = 'pgss_schema_2';
505+
SELECT count(*) FROM tab_search_same;
506+
count
507+
-------
508+
0
509+
(1 row)
510+
511+
SELECT a, b FROM tab_search_same;
512+
a | b
513+
---+---
514+
(0 rows)
515+
516+
SELECT count(*) FROM tab_search_diff_1;
517+
count
518+
-------
519+
0
520+
(1 row)
521+
522+
SELECT count(*) FROM tab_search_diff_2;
523+
count
524+
-------
525+
0
526+
(1 row)
527+
528+
SELECT a FROM tab_search_diff_2 AS t1;
529+
a
530+
---
531+
(0 rows)
532+
533+
SELECT a FROM tab_search_diff_2;
534+
a
535+
---
536+
(0 rows)
537+
538+
SELECT a AS a1 FROM tab_search_diff_2;
539+
a1
540+
----
541+
(0 rows)
542+
543+
-- Temporary schema
544+
SET search_path = 'pg_temp';
545+
SELECT count(*) FROM tab_search_same;
546+
count
547+
-------
548+
0
549+
(1 row)
550+
551+
SELECT a, b FROM tab_search_same;
552+
a | b
553+
---+---
554+
(0 rows)
555+
556+
SELECT count(*) FROM tab_search_diff_1;
557+
count
558+
-------
559+
0
560+
(1 row)
561+
562+
SELECT count(*) FROM tab_search_diff_2;
563+
count
564+
-------
565+
0
566+
(1 row)
567+
568+
SELECT a FROM tab_search_diff_2 AS t1;
569+
a
570+
---
571+
(0 rows)
572+
573+
SELECT a FROM tab_search_diff_2;
574+
a
575+
---
576+
(0 rows)
577+
578+
SELECT a AS a1 FROM tab_search_diff_2;
579+
a1
580+
----
581+
(0 rows)
582+
583+
RESET search_path;
584+
-- Schema qualifications
585+
SELECT count(*) FROM pgss_schema_1.tab_search_same;
586+
count
587+
-------
588+
0
589+
(1 row)
590+
591+
SELECT a, b FROM pgss_schema_1.tab_search_same;
592+
a | b
593+
---+---
594+
(0 rows)
595+
596+
SELECT count(*) FROM pgss_schema_2.tab_search_diff_1;
597+
count
598+
-------
599+
0
600+
(1 row)
601+
602+
SELECT count(*) FROM pg_temp.tab_search_diff_2;
603+
count
604+
-------
605+
0
606+
(1 row)
607+
608+
SELECT a FROM pgss_schema_2.tab_search_diff_2 AS t1;
609+
a
610+
---
611+
(0 rows)
612+
613+
SELECT a FROM pgss_schema_2.tab_search_diff_2;
614+
a
615+
---
616+
(0 rows)
617+
618+
SELECT a AS a1 FROM pgss_schema_2.tab_search_diff_2;
619+
a1
620+
----
621+
(0 rows)
622+
623+
SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
624+
calls | query
625+
-------+------------------------------------------------------------------------
626+
3 | SELECT a FROM pgss_schema_2.tab_search_diff_2 AS t1
627+
9 | SELECT a FROM tab_search_diff_2 AS t1
628+
1 | SELECT a, b FROM pgss_schema_1.tab_search_same
629+
3 | SELECT a, b FROM tab_search_same
630+
0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"
631+
1 | SELECT count(*) FROM pgss_schema_1.tab_search_same
632+
1 | SELECT count(*) FROM pgss_schema_2.tab_search_diff_1
633+
3 | SELECT count(*) FROM tab_search_diff_1
634+
4 | SELECT count(*) FROM tab_search_diff_2
635+
3 | SELECT count(*) FROM tab_search_same
636+
1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
637+
(11 rows)
638+
639+
DROP SCHEMA pgss_schema_1 CASCADE;
640+
NOTICE: drop cascades to 3 other objects
641+
DETAIL: drop cascades to table pgss_schema_1.tab_search_same
642+
drop cascades to table pgss_schema_1.tab_search_diff_1
643+
drop cascades to table pgss_schema_1.tab_search_diff_2
644+
DROP SCHEMA pgss_schema_2 CASCADE;
645+
NOTICE: drop cascades to 3 other objects
646+
DETAIL: drop cascades to table pgss_schema_2.tab_search_same
647+
drop cascades to table pgss_schema_2.tab_search_diff_1
648+
drop cascades to table pgss_schema_2.tab_search_diff_2
649+
DROP TABLE tab_search_same, tab_search_diff_1, tab_search_diff_2;
650+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
651+
t
652+
---
653+
t
654+
(1 row)
655+

‎contrib/pg_stat_statements/sql/select.sql

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -148,3 +148,72 @@ SELECT (
148148

149149
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
150150
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
151+
152+
-- Temporary table with same name, re-created.
153+
BEGIN;
154+
CREATE TEMP TABLE temp_t (id int) ON COMMIT DROP;
155+
SELECT * FROM temp_t;
156+
COMMIT;
157+
BEGIN;
158+
CREATE TEMP TABLE temp_t (id int) ON COMMIT DROP;
159+
SELECT * FROM temp_t;
160+
COMMIT;
161+
SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
162+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
163+
164+
-- search_path with various schemas and temporary tables
165+
CREATE SCHEMA pgss_schema_1;
166+
CREATE SCHEMA pgss_schema_2;
167+
-- Same attributes.
168+
CREATE TABLE pgss_schema_1.tab_search_same (a int, b int);
169+
CREATE TABLE pgss_schema_2.tab_search_same (a int, b int);
170+
CREATE TEMP TABLE tab_search_same (a int, b int);
171+
-- Different number of attributes, mapping types
172+
CREATE TABLE pgss_schema_1.tab_search_diff_1 (a int);
173+
CREATE TABLE pgss_schema_2.tab_search_diff_1 (a int, b int);
174+
CREATE TEMP TABLE tab_search_diff_1 (a int, b int, c int);
175+
-- Same number of attributes, different types
176+
CREATE TABLE pgss_schema_1.tab_search_diff_2 (a int);
177+
CREATE TABLE pgss_schema_2.tab_search_diff_2 (a text);
178+
CREATE TEMP TABLE tab_search_diff_2 (a bigint);
179+
-- First permanent schema
180+
SET search_path = 'pgss_schema_1';
181+
SELECT count(*) FROM tab_search_same;
182+
SELECT a, b FROM tab_search_same;
183+
SELECT count(*) FROM tab_search_diff_1;
184+
SELECT count(*) FROM tab_search_diff_2;
185+
SELECT a FROM tab_search_diff_2 AS t1;
186+
SELECT a FROM tab_search_diff_2;
187+
SELECT a AS a1 FROM tab_search_diff_2;
188+
-- Second permanent schema
189+
SET search_path = 'pgss_schema_2';
190+
SELECT count(*) FROM tab_search_same;
191+
SELECT a, b FROM tab_search_same;
192+
SELECT count(*) FROM tab_search_diff_1;
193+
SELECT count(*) FROM tab_search_diff_2;
194+
SELECT a FROM tab_search_diff_2 AS t1;
195+
SELECT a FROM tab_search_diff_2;
196+
SELECT a AS a1 FROM tab_search_diff_2;
197+
-- Temporary schema
198+
SET search_path = 'pg_temp';
199+
SELECT count(*) FROM tab_search_same;
200+
SELECT a, b FROM tab_search_same;
201+
SELECT count(*) FROM tab_search_diff_1;
202+
SELECT count(*) FROM tab_search_diff_2;
203+
SELECT a FROM tab_search_diff_2 AS t1;
204+
SELECT a FROM tab_search_diff_2;
205+
SELECT a AS a1 FROM tab_search_diff_2;
206+
RESET search_path;
207+
-- Schema qualifications
208+
SELECT count(*) FROM pgss_schema_1.tab_search_same;
209+
SELECT a, b FROM pgss_schema_1.tab_search_same;
210+
SELECT count(*) FROM pgss_schema_2.tab_search_diff_1;
211+
SELECT count(*) FROM pg_temp.tab_search_diff_2;
212+
SELECT a FROM pgss_schema_2.tab_search_diff_2 AS t1;
213+
SELECT a FROM pgss_schema_2.tab_search_diff_2;
214+
SELECT a AS a1 FROM pgss_schema_2.tab_search_diff_2;
215+
SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
216+
DROP SCHEMA pgss_schema_1 CASCADE;
217+
DROP SCHEMA pgss_schema_2 CASCADE;
218+
DROP TABLE tab_search_same, tab_search_diff_1, tab_search_diff_2;
219+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;

0 commit comments

Comments
 (0)