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

Commit3430215

Browse files
committed
pg_stat_statements: Add more tests with temp tables and namespaces
These tests provide coverage for RangeTblEntry and how query jumblingworks with search_path, as well as the case where relations arere-created, generating a different query ID as the relation OID is usedin the computation.A patch is under discussion to switch to a different approach based onthe relation name, and there was no test coverage for this area,including how queries are currently grouped with search_path. This isuseful to track how the situation changes between HEAD and any patchesproposed.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 parent626d723 commit3430215

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
SELECTCOUNT(*)FROM pg_stat_statementsWHERE queryLIKE'%SELECT GROUPING%';
150150
SELECT pg_stat_statements_reset()IS NOT NULLAS t;
151+
152+
-- Temporary table with same name, re-created.
153+
BEGIN;
154+
CREATE TEMP TABLE temp_t (idint)ONCOMMIT DROP;
155+
SELECT*FROM temp_t;
156+
COMMIT;
157+
BEGIN;
158+
CREATE TEMP TABLE temp_t (idint)ONCOMMIT DROP;
159+
SELECT*FROM temp_t;
160+
COMMIT;
161+
SELECT calls, queryFROM pg_stat_statementsORDER BY query COLLATE"C";
162+
SELECT pg_stat_statements_reset()IS NOT NULLAS t;
163+
164+
-- search_path with various schemas and temporary tables
165+
CREATESCHEMApgss_schema_1;
166+
CREATESCHEMApgss_schema_2;
167+
-- Same attributes.
168+
CREATETABLEpgss_schema_1.tab_search_same (aint, bint);
169+
CREATETABLEpgss_schema_2.tab_search_same (aint, bint);
170+
CREATE TEMP TABLE tab_search_same (aint, bint);
171+
-- Different number of attributes, mapping types
172+
CREATETABLEpgss_schema_1.tab_search_diff_1 (aint);
173+
CREATETABLEpgss_schema_2.tab_search_diff_1 (aint, bint);
174+
CREATE TEMP TABLE tab_search_diff_1 (aint, bint, cint);
175+
-- Same number of attributes, different types
176+
CREATETABLEpgss_schema_1.tab_search_diff_2 (aint);
177+
CREATETABLEpgss_schema_2.tab_search_diff_2 (atext);
178+
CREATE TEMP TABLE tab_search_diff_2 (abigint);
179+
-- First permanent schema
180+
SET search_path='pgss_schema_1';
181+
SELECTcount(*)FROM tab_search_same;
182+
SELECT a, bFROM tab_search_same;
183+
SELECTcount(*)FROM tab_search_diff_1;
184+
SELECTcount(*)FROM tab_search_diff_2;
185+
SELECT aFROM tab_search_diff_2AS t1;
186+
SELECT aFROM tab_search_diff_2;
187+
SELECT aAS a1FROM tab_search_diff_2;
188+
-- Second permanent schema
189+
SET search_path='pgss_schema_2';
190+
SELECTcount(*)FROM tab_search_same;
191+
SELECT a, bFROM tab_search_same;
192+
SELECTcount(*)FROM tab_search_diff_1;
193+
SELECTcount(*)FROM tab_search_diff_2;
194+
SELECT aFROM tab_search_diff_2AS t1;
195+
SELECT aFROM tab_search_diff_2;
196+
SELECT aAS a1FROM tab_search_diff_2;
197+
-- Temporary schema
198+
SET search_path='pg_temp';
199+
SELECTcount(*)FROM tab_search_same;
200+
SELECT a, bFROM tab_search_same;
201+
SELECTcount(*)FROM tab_search_diff_1;
202+
SELECTcount(*)FROM tab_search_diff_2;
203+
SELECT aFROM tab_search_diff_2AS t1;
204+
SELECT aFROM tab_search_diff_2;
205+
SELECT aAS a1FROM tab_search_diff_2;
206+
RESET search_path;
207+
-- Schema qualifications
208+
SELECTcount(*)FROMpgss_schema_1.tab_search_same;
209+
SELECT a, bFROMpgss_schema_1.tab_search_same;
210+
SELECTcount(*)FROMpgss_schema_2.tab_search_diff_1;
211+
SELECTcount(*)FROMpg_temp.tab_search_diff_2;
212+
SELECT aFROMpgss_schema_2.tab_search_diff_2AS t1;
213+
SELECT aFROMpgss_schema_2.tab_search_diff_2;
214+
SELECT aAS a1FROMpgss_schema_2.tab_search_diff_2;
215+
SELECT calls, queryFROM pg_stat_statementsORDER BY query COLLATE"C";
216+
DROPSCHEMA pgss_schema_1 CASCADE;
217+
DROPSCHEMA pgss_schema_2 CASCADE;
218+
DROPTABLE tab_search_same, tab_search_diff_1, tab_search_diff_2;
219+
SELECT pg_stat_statements_reset()IS NOT NULLAS t;

0 commit comments

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