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

Commit43cbeda

Browse files
author
Amit Kapila
committed
Extend pg_stat_statements_reset to reset statistics specific to a
particular user/db/query.The function pg_stat_statements_reset() is extended to accept userid, dbid,and queryid as input parameters. Now, it can discard the statisticsgathered so far by pg_stat_statements corresponding to the specifieduserid, dbid, and queryid. If no parameter is specified or all thespecified parameters have default value aka 0, it will discard allstatistics as per the old behavior.The new behavior is useful to get the fresh statistics for a specificuser/database/query without resetting all the existing statistics.Author: Haribabu Kommi, with few additional changes by meReviewed-by: Michael Paquier, Amit Kapila and Fujii MasaoDiscussion:https://postgr.es/m/CAJrrPGcyh-gkFswyc6C661K6cknL0XkNqVT0sQt2mFNMR4HRKA@mail.gmail.com
1 parent3b174b1 commit43cbeda

File tree

7 files changed

+455
-41
lines changed

7 files changed

+455
-41
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4,10 +4,10 @@ MODULE_big = pg_stat_statements
44
OBJS = pg_stat_statements.o$(WIN32RES)
55

66
EXTENSION = pg_stat_statements
7-
DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.5--1.6.sql\
8-
pg_stat_statements--1.4--1.5.sql pg_stat_statements--1.3--1.4.sql\
9-
pg_stat_statements--1.2--1.3.sql pg_stat_statements--1.1--1.2.sql\
10-
pg_stat_statements--1.0--1.1.sql\
7+
DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.6--1.7.sql\
8+
pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql\
9+
pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql\
10+
pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql\
1111
pg_stat_statements--unpackaged--1.0.sql
1212
PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
1313

‎contrib/pg_stat_statements/expected/pg_stat_statements.out

Lines changed: 198 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -395,4 +395,202 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
395395
SELECT pg_stat_statements_reset() | 1 | 1
396396
(8 rows)
397397

398+
--
399+
-- Track user activity and reset them
400+
--
401+
SELECT pg_stat_statements_reset();
402+
pg_stat_statements_reset
403+
--------------------------
404+
405+
(1 row)
406+
407+
CREATE ROLE regress_stats_user1;
408+
CREATE ROLE regress_stats_user2;
409+
SET ROLE regress_stats_user1;
410+
SELECT 1 AS "ONE";
411+
ONE
412+
-----
413+
1
414+
(1 row)
415+
416+
SELECT 1+1 AS "TWO";
417+
TWO
418+
-----
419+
2
420+
(1 row)
421+
422+
RESET ROLE;
423+
SET ROLE regress_stats_user2;
424+
SELECT 1 AS "ONE";
425+
ONE
426+
-----
427+
1
428+
(1 row)
429+
430+
SELECT 1+1 AS "TWO";
431+
TWO
432+
-----
433+
2
434+
(1 row)
435+
436+
RESET ROLE;
437+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
438+
query | calls | rows
439+
-----------------------------------+-------+------
440+
CREATE ROLE regress_stats_user1 | 1 | 0
441+
CREATE ROLE regress_stats_user2 | 1 | 0
442+
RESET ROLE | 2 | 0
443+
SELECT $1 AS "ONE" | 1 | 1
444+
SELECT $1 AS "ONE" | 1 | 1
445+
SELECT $1+$2 AS "TWO" | 1 | 1
446+
SELECT $1+$2 AS "TWO" | 1 | 1
447+
SELECT pg_stat_statements_reset() | 1 | 1
448+
SET ROLE regress_stats_user1 | 1 | 0
449+
SET ROLE regress_stats_user2 | 1 | 0
450+
(10 rows)
451+
452+
--
453+
-- Don't reset anything if any of the parameter is NULL
454+
--
455+
SELECT pg_stat_statements_reset(NULL);
456+
pg_stat_statements_reset
457+
--------------------------
458+
459+
(1 row)
460+
461+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
462+
query | calls | rows
463+
------------------------------------------------------------------------------+-------+------
464+
CREATE ROLE regress_stats_user1 | 1 | 0
465+
CREATE ROLE regress_stats_user2 | 1 | 0
466+
RESET ROLE | 2 | 0
467+
SELECT $1 AS "ONE" | 1 | 1
468+
SELECT $1 AS "ONE" | 1 | 1
469+
SELECT $1+$2 AS "TWO" | 1 | 1
470+
SELECT $1+$2 AS "TWO" | 1 | 1
471+
SELECT pg_stat_statements_reset($1) | 1 | 1
472+
SELECT pg_stat_statements_reset() | 1 | 1
473+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 10
474+
SET ROLE regress_stats_user1 | 1 | 0
475+
SET ROLE regress_stats_user2 | 1 | 0
476+
(12 rows)
477+
478+
--
479+
-- remove query ('SELECT $1+$2 AS "TWO"') executed by regress_stats_user2
480+
-- in the current_database
481+
--
482+
SELECT pg_stat_statements_reset(
483+
(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user2'),
484+
(SELECT d.oid FROM pg_database As d where datname = current_database()),
485+
(SELECT s.queryid FROM pg_stat_statements AS s
486+
WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1));
487+
pg_stat_statements_reset
488+
--------------------------
489+
490+
(1 row)
491+
492+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
493+
query | calls | rows
494+
----------------------------------------------------------------------------------+-------+------
495+
CREATE ROLE regress_stats_user1 | 1 | 0
496+
CREATE ROLE regress_stats_user2 | 1 | 0
497+
RESET ROLE | 2 | 0
498+
SELECT $1 AS "ONE" | 1 | 1
499+
SELECT $1 AS "ONE" | 1 | 1
500+
SELECT $1+$2 AS "TWO" | 1 | 1
501+
SELECT pg_stat_statements_reset( +| 1 | 1
502+
(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| |
503+
(SELECT d.oid FROM pg_database As d where datname = current_database()),+| |
504+
(SELECT s.queryid FROM pg_stat_statements AS s +| |
505+
WHERE s.query = $2 LIMIT $3)) | |
506+
SELECT pg_stat_statements_reset($1) | 1 | 1
507+
SELECT pg_stat_statements_reset() | 1 | 1
508+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 2 | 22
509+
SET ROLE regress_stats_user1 | 1 | 0
510+
SET ROLE regress_stats_user2 | 1 | 0
511+
(12 rows)
512+
513+
--
514+
-- remove query ('SELECT $1 AS "ONE"') executed by two users
515+
--
516+
SELECT pg_stat_statements_reset(0,0,s.queryid)
517+
FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"';
518+
pg_stat_statements_reset
519+
--------------------------
520+
521+
522+
(2 rows)
523+
524+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
525+
query | calls | rows
526+
----------------------------------------------------------------------------------+-------+------
527+
CREATE ROLE regress_stats_user1 | 1 | 0
528+
CREATE ROLE regress_stats_user2 | 1 | 0
529+
RESET ROLE | 2 | 0
530+
SELECT $1+$2 AS "TWO" | 1 | 1
531+
SELECT pg_stat_statements_reset( +| 1 | 1
532+
(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| |
533+
(SELECT d.oid FROM pg_database As d where datname = current_database()),+| |
534+
(SELECT s.queryid FROM pg_stat_statements AS s +| |
535+
WHERE s.query = $2 LIMIT $3)) | |
536+
SELECT pg_stat_statements_reset($1) | 1 | 1
537+
SELECT pg_stat_statements_reset($1,$2,s.queryid) +| 1 | 2
538+
FROM pg_stat_statements AS s WHERE s.query = $3 | |
539+
SELECT pg_stat_statements_reset() | 1 | 1
540+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 3 | 34
541+
SET ROLE regress_stats_user1 | 1 | 0
542+
SET ROLE regress_stats_user2 | 1 | 0
543+
(11 rows)
544+
545+
--
546+
-- remove query of a user (regress_stats_user1)
547+
--
548+
SELECT pg_stat_statements_reset(r.oid)
549+
FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1';
550+
pg_stat_statements_reset
551+
--------------------------
552+
553+
(1 row)
554+
555+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
556+
query | calls | rows
557+
----------------------------------------------------------------------------------+-------+------
558+
CREATE ROLE regress_stats_user1 | 1 | 0
559+
CREATE ROLE regress_stats_user2 | 1 | 0
560+
RESET ROLE | 2 | 0
561+
SELECT pg_stat_statements_reset( +| 1 | 1
562+
(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| |
563+
(SELECT d.oid FROM pg_database As d where datname = current_database()),+| |
564+
(SELECT s.queryid FROM pg_stat_statements AS s +| |
565+
WHERE s.query = $2 LIMIT $3)) | |
566+
SELECT pg_stat_statements_reset($1) | 1 | 1
567+
SELECT pg_stat_statements_reset($1,$2,s.queryid) +| 1 | 2
568+
FROM pg_stat_statements AS s WHERE s.query = $3 | |
569+
SELECT pg_stat_statements_reset() | 1 | 1
570+
SELECT pg_stat_statements_reset(r.oid) +| 1 | 1
571+
FROM pg_roles AS r WHERE r.rolname = $1 | |
572+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 4 | 45
573+
SET ROLE regress_stats_user2 | 1 | 0
574+
(10 rows)
575+
576+
--
577+
-- reset all
578+
--
579+
SELECT pg_stat_statements_reset(0,0,0);
580+
pg_stat_statements_reset
581+
--------------------------
582+
583+
(1 row)
584+
585+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
586+
query | calls | rows
587+
----------------------------------------+-------+------
588+
SELECT pg_stat_statements_reset(0,0,0) | 1 | 1
589+
(1 row)
590+
591+
--
592+
-- cleanup
593+
--
594+
DROP ROLE regress_stats_user1;
595+
DROP ROLE regress_stats_user2;
398596
DROP EXTENSION pg_stat_statements;
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.6--1.7.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_stat_statements UPDATE TO '1.7'" to load this file. \quit
5+
6+
/* First we have to remove them from the extension*/
7+
ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements_reset();
8+
9+
/* Then we can drop them*/
10+
DROPFUNCTION pg_stat_statements_reset();
11+
12+
/* Now redefine*/
13+
CREATEFUNCTIONpg_stat_statements_reset(IN useridOid DEFAULT0,
14+
IN dbidOid DEFAULT0,
15+
IN queryidbigint DEFAULT0
16+
)
17+
RETURNS void
18+
AS'MODULE_PATHNAME','pg_stat_statements_reset_1_7'
19+
LANGUAGE C STRICT PARALLEL SAFE;
20+
21+
-- Don't want this to be available to non-superusers.
22+
REVOKE ALLON FUNCTION pg_stat_statements_reset(Oid,Oid,bigint)FROM PUBLIC;

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 84 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -290,6 +290,7 @@ void_PG_init(void);
290290
void_PG_fini(void);
291291

292292
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
293+
PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_7);
293294
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
294295
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
295296
PG_FUNCTION_INFO_V1(pg_stat_statements);
@@ -327,7 +328,7 @@ static char *qtext_fetch(Size query_offset, int query_len,
327328
char*buffer,Sizebuffer_size);
328329
staticboolneed_gc_qtexts(void);
329330
staticvoidgc_qtexts(void);
330-
staticvoidentry_reset(void);
331+
staticvoidentry_reset(Oiduserid,Oiddbid,uint64queryid);
331332
staticvoidAppendJumble(pgssJumbleState*jstate,
332333
constunsignedchar*item,Sizesize);
333334
staticvoidJumbleQuery(pgssJumbleState*jstate,Query*query);
@@ -1148,8 +1149,17 @@ pgss_store(const char *query, uint64 queryId,
11481149
* For utility statements, we just hash the query string to get an ID.
11491150
*/
11501151
if (queryId==UINT64CONST(0))
1152+
{
11511153
queryId=pgss_hash_string(query,query_len);
11521154

1155+
/*
1156+
* If we are unlucky enough to get a hash of zero(invalid), use queryID
1157+
* as 2 instead, queryID 1 is already in use for normal statements.
1158+
*/
1159+
if (queryId==UINT64CONST(0))
1160+
queryId=UINT64CONST(2);
1161+
}
1162+
11531163
/* Set up key for hashtable search */
11541164
key.userid=GetUserId();
11551165
key.dbid=MyDatabaseId;
@@ -1293,16 +1303,32 @@ pgss_store(const char *query, uint64 queryId,
12931303
}
12941304

12951305
/*
1296-
* Reset all statement statistics.
1306+
* Reset statement statistics corresponding to userid, dbid, and queryid.
1307+
*/
1308+
Datum
1309+
pg_stat_statements_reset_1_7(PG_FUNCTION_ARGS)
1310+
{
1311+
Oiduserid;
1312+
Oiddbid;
1313+
uint64queryid;
1314+
1315+
userid=PG_GETARG_OID(0);
1316+
dbid=PG_GETARG_OID(1);
1317+
queryid= (uint64)PG_GETARG_INT64(2);
1318+
1319+
entry_reset(userid,dbid,queryid);
1320+
1321+
PG_RETURN_VOID();
1322+
}
1323+
1324+
/*
1325+
* Reset statement statistics.
12971326
*/
12981327
Datum
12991328
pg_stat_statements_reset(PG_FUNCTION_ARGS)
13001329
{
1301-
if (!pgss|| !pgss_hash)
1302-
ereport(ERROR,
1303-
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1304-
errmsg("pg_stat_statements must be loaded via shared_preload_libraries")));
1305-
entry_reset();
1330+
entry_reset(0,0,0);
1331+
13061332
PG_RETURN_VOID();
13071333
}
13081334

@@ -2229,23 +2255,68 @@ gc_qtexts(void)
22292255
}
22302256

22312257
/*
2232-
* Releaseallentries.
2258+
* Release entries corresponding to parameters passed.
22332259
*/
22342260
staticvoid
2235-
entry_reset(void)
2261+
entry_reset(Oiduserid,Oiddbid,uint64queryid)
22362262
{
22372263
HASH_SEQ_STATUShash_seq;
22382264
pgssEntry*entry;
22392265
FILE*qfile;
2266+
longnum_entries;
2267+
longnum_remove=0;
2268+
pgssHashKeykey;
2269+
2270+
if (!pgss|| !pgss_hash)
2271+
ereport(ERROR,
2272+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
2273+
errmsg("pg_stat_statements must be loaded via shared_preload_libraries")));
22402274

22412275
LWLockAcquire(pgss->lock,LW_EXCLUSIVE);
2276+
num_entries=hash_get_num_entries(pgss_hash);
22422277

2243-
hash_seq_init(&hash_seq,pgss_hash);
2244-
while ((entry=hash_seq_search(&hash_seq))!=NULL)
2278+
if (userid!=0&&dbid!=0&&queryid!=UINT64CONST(0))
2279+
{
2280+
/* If all the parameters are available, use the fast path. */
2281+
key.userid=userid;
2282+
key.dbid=dbid;
2283+
key.queryid=queryid;
2284+
2285+
/* Remove the key if exists */
2286+
entry= (pgssEntry*)hash_search(pgss_hash,&key,HASH_REMOVE,NULL);
2287+
if (entry)/* found */
2288+
num_remove++;
2289+
}
2290+
elseif (userid!=0||dbid!=0||queryid!=UINT64CONST(0))
22452291
{
2246-
hash_search(pgss_hash,&entry->key,HASH_REMOVE,NULL);
2292+
/* Remove entries corresponding to valid parameters. */
2293+
hash_seq_init(&hash_seq,pgss_hash);
2294+
while ((entry=hash_seq_search(&hash_seq))!=NULL)
2295+
{
2296+
if ((!userid||entry->key.userid==userid)&&
2297+
(!dbid||entry->key.dbid==dbid)&&
2298+
(!queryid||entry->key.queryid==queryid))
2299+
{
2300+
hash_search(pgss_hash,&entry->key,HASH_REMOVE,NULL);
2301+
num_remove++;
2302+
}
2303+
}
2304+
}
2305+
else
2306+
{
2307+
/* Remove all entries. */
2308+
hash_seq_init(&hash_seq,pgss_hash);
2309+
while ((entry=hash_seq_search(&hash_seq))!=NULL)
2310+
{
2311+
hash_search(pgss_hash,&entry->key,HASH_REMOVE,NULL);
2312+
num_remove++;
2313+
}
22472314
}
22482315

2316+
/* All entries are removed? */
2317+
if (num_entries!=num_remove)
2318+
gotorelease_lock;
2319+
22492320
/*
22502321
* Write new empty query file, perhaps even creating a new one to recover
22512322
* if the file was missing.
@@ -2274,6 +2345,7 @@ entry_reset(void)
22742345
/* This counts as a query text garbage collection for our purposes */
22752346
record_gc_qtexts();
22762347

2348+
release_lock:
22772349
LWLockRelease(pgss->lock);
22782350
}
22792351

0 commit comments

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