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

Commitf7a97b6

Browse files
committed
Update query_id computation
Properly fix:- the "ONLY" in FROM [ONLY] isn't hashed- the agglevelsup field in GROUPING isn't hashed- WITH TIES not being hashed (new in PG 13)- "DISTINCT" in "GROUP BY [DISTINCT]" isn't hashed (new in PG 14)Reported-by: Julien RouhaudDiscussion:https://postgr.es/m/20210425081119.ulyzxqz23ueh3wuj@nol
1 parent5df6aea commitf7a97b6

File tree

3 files changed

+207
-0
lines changed

3 files changed

+207
-0
lines changed

‎contrib/pg_stat_statements/expected/pg_stat_statements.out

Lines changed: 151 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -916,4 +916,155 @@ SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%
916916
$$ LANGUAGE plpgsql | | |
917917
(3 rows)
918918

919+
-- FROM [ONLY]
920+
CREATE TABLE tbl_inh(id integer);
921+
CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
922+
INSERT INTO tbl_inh_1 SELECT 1;
923+
SELECT * FROM tbl_inh;
924+
id
925+
----
926+
1
927+
(1 row)
928+
929+
SELECT * FROM ONLY tbl_inh;
930+
id
931+
----
932+
(0 rows)
933+
934+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%';
935+
count
936+
-------
937+
2
938+
(1 row)
939+
940+
-- WITH TIES
941+
CREATE TABLE limitoption AS SELECT 0 AS val FROM generate_series(1, 10);
942+
SELECT *
943+
FROM limitoption
944+
WHERE val < 2
945+
ORDER BY val
946+
FETCH FIRST 2 ROWS WITH TIES;
947+
val
948+
-----
949+
0
950+
0
951+
0
952+
0
953+
0
954+
0
955+
0
956+
0
957+
0
958+
0
959+
(10 rows)
960+
961+
SELECT *
962+
FROM limitoption
963+
WHERE val < 2
964+
ORDER BY val
965+
FETCH FIRST 2 ROW ONLY;
966+
val
967+
-----
968+
0
969+
0
970+
(2 rows)
971+
972+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
973+
count
974+
-------
975+
2
976+
(1 row)
977+
978+
-- GROUP BY [DISTINCT]
979+
SELECT a, b, c
980+
FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
981+
GROUP BY ROLLUP(a, b), rollup(a, c)
982+
ORDER BY a, b, c;
983+
a | b | c
984+
---+---+---
985+
1 | 2 | 3
986+
1 | 2 |
987+
1 | 2 |
988+
1 | | 3
989+
1 | | 3
990+
1 | |
991+
1 | |
992+
1 | |
993+
4 | | 6
994+
4 | | 6
995+
4 | | 6
996+
4 | |
997+
4 | |
998+
4 | |
999+
4 | |
1000+
4 | |
1001+
7 | 8 | 9
1002+
7 | 8 |
1003+
7 | 8 |
1004+
7 | | 9
1005+
7 | | 9
1006+
7 | |
1007+
7 | |
1008+
7 | |
1009+
| |
1010+
(25 rows)
1011+
1012+
SELECT a, b, c
1013+
FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
1014+
GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c)
1015+
ORDER BY a, b, c;
1016+
a | b | c
1017+
---+---+---
1018+
1 | 2 | 3
1019+
1 | 2 |
1020+
1 | | 3
1021+
1 | |
1022+
4 | | 6
1023+
4 | | 6
1024+
4 | |
1025+
4 | |
1026+
7 | 8 | 9
1027+
7 | 8 |
1028+
7 | | 9
1029+
7 | |
1030+
| |
1031+
(13 rows)
1032+
1033+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%GROUP BY%ROLLUP%';
1034+
count
1035+
-------
1036+
2
1037+
(1 row)
1038+
1039+
-- GROUPING SET agglevelsup
1040+
SELECT (
1041+
SELECT (
1042+
SELECT GROUPING(a,b) FROM (VALUES (1)) v2(c)
1043+
) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
1044+
) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
1045+
grouping
1046+
----------
1047+
0
1048+
0
1049+
0
1050+
(3 rows)
1051+
1052+
SELECT (
1053+
SELECT (
1054+
SELECT GROUPING(e,f) FROM (VALUES (1)) v2(c)
1055+
) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
1056+
) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
1057+
grouping
1058+
----------
1059+
3
1060+
0
1061+
1
1062+
(3 rows)
1063+
1064+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
1065+
count
1066+
-------
1067+
2
1068+
(1 row)
1069+
9191070
DROP EXTENSION pg_stat_statements;

‎contrib/pg_stat_statements/sql/pg_stat_statements.sql

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -385,4 +385,56 @@ END;
385385
$$ LANGUAGE plpgsql;
386386
SELECT query, toplevel, plans, callsFROM pg_stat_statementsWHERE queryLIKE'%DELETE%'ORDER BY query COLLATE"C", toplevel;
387387

388+
-- FROM [ONLY]
389+
CREATETABLEtbl_inh(idinteger);
390+
CREATETABLEtbl_inh_1() INHERITS (tbl_inh);
391+
INSERT INTO tbl_inh_1SELECT1;
392+
393+
SELECT*FROM tbl_inh;
394+
SELECT*FROM ONLY tbl_inh;
395+
396+
SELECTCOUNT(*)FROM pg_stat_statementsWHERE queryLIKE'%FROM%tbl_inh%';
397+
398+
-- WITH TIES
399+
CREATETABLElimitoptionASSELECT0AS valFROM generate_series(1,10);
400+
SELECT*
401+
FROM limitoption
402+
WHERE val<2
403+
ORDER BY val
404+
FETCH FIRST2 ROWS WITH TIES;
405+
406+
SELECT*
407+
FROM limitoption
408+
WHERE val<2
409+
ORDER BY val
410+
FETCH FIRST2 ROW ONLY;
411+
412+
SELECTCOUNT(*)FROM pg_stat_statementsWHERE queryLIKE'%FETCH FIRST%';
413+
414+
-- GROUP BY [DISTINCT]
415+
SELECT a, b, c
416+
FROM (VALUES (1,2,3), (4,NULL,6), (7,8,9))AS t (a, b, c)
417+
GROUP BY ROLLUP(a, b), rollup(a, c)
418+
ORDER BY a, b, c;
419+
SELECT a, b, c
420+
FROM (VALUES (1,2,3), (4,NULL,6), (7,8,9))AS t (a, b, c)
421+
GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c)
422+
ORDER BY a, b, c;
423+
424+
SELECTCOUNT(*)FROM pg_stat_statementsWHERE queryLIKE'%GROUP BY%ROLLUP%';
425+
426+
-- GROUPING SET agglevelsup
427+
SELECT (
428+
SELECT (
429+
SELECT GROUPING(a,b)FROM (VALUES (1)) v2(c)
430+
)FROM (VALUES (1,2)) v1(a,b)GROUP BY (a,b)
431+
)FROM (VALUES(6,7)) v3(e,f)GROUP BY ROLLUP(e,f);
432+
SELECT (
433+
SELECT (
434+
SELECT GROUPING(e,f)FROM (VALUES (1)) v2(c)
435+
)FROM (VALUES (1,2)) v1(a,b)GROUP BY (a,b)
436+
)FROM (VALUES(6,7)) v3(e,f)GROUP BY ROLLUP(e,f);
437+
438+
SELECTCOUNT(*)FROM pg_stat_statementsWHERE queryLIKE'%SELECT GROUPING%';
439+
388440
DROP EXTENSION pg_stat_statements;

‎src/backend/utils/misc/queryjumble.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -230,13 +230,15 @@ JumbleQueryInternal(JumbleState *jstate, Query *query)
230230
JumbleExpr(jstate, (Node*)query->onConflict);
231231
JumbleExpr(jstate, (Node*)query->returningList);
232232
JumbleExpr(jstate, (Node*)query->groupClause);
233+
APP_JUMB(query->groupDistinct);
233234
JumbleExpr(jstate, (Node*)query->groupingSets);
234235
JumbleExpr(jstate,query->havingQual);
235236
JumbleExpr(jstate, (Node*)query->windowClause);
236237
JumbleExpr(jstate, (Node*)query->distinctClause);
237238
JumbleExpr(jstate, (Node*)query->sortClause);
238239
JumbleExpr(jstate,query->limitOffset);
239240
JumbleExpr(jstate,query->limitCount);
241+
APP_JUMB(query->limitOption);
240242
JumbleRowMarks(jstate,query->rowMarks);
241243
JumbleExpr(jstate,query->setOperations);
242244
}
@@ -259,6 +261,7 @@ JumbleRangeTable(JumbleState *jstate, List *rtable)
259261
caseRTE_RELATION:
260262
APP_JUMB(rte->relid);
261263
JumbleExpr(jstate, (Node*)rte->tablesample);
264+
APP_JUMB(rte->inh);
262265
break;
263266
caseRTE_SUBQUERY:
264267
JumbleQueryInternal(jstate,rte->subquery);
@@ -399,6 +402,7 @@ JumbleExpr(JumbleState *jstate, Node *node)
399402
GroupingFunc*grpnode= (GroupingFunc*)node;
400403

401404
JumbleExpr(jstate, (Node*)grpnode->refs);
405+
APP_JUMB(grpnode->agglevelsup);
402406
}
403407
break;
404408
caseT_WindowFunc:

0 commit comments

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