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

Commit9148440

Browse files
committed
Expose qurey ID in pg_stat_statements view.
The query ID is the internal hash identifier of the statement,and was not available in pg_stat_statements view so far.Daniel Farina, Sameer Thakur and Peter Geoghegan, reviewed by me.
1 parentef32675 commit9148440

File tree

7 files changed

+202
-71
lines changed

7 files changed

+202
-71
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,8 @@ MODULE_big = pg_stat_statements
44
OBJS = pg_stat_statements.o
55

66
EXTENSION = pg_stat_statements
7-
DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql\
8-
pg_stat_statements--unpackaged--1.0.sql
7+
DATA = pg_stat_statements--1.2.sql pg_stat_statements--1.1--1.2.sql\
8+
pg_stat_statements--1.0--1.1.sql pg_stat_statements--unpackaged--1.0.sql
99

1010
ifdefUSE_PGXS
1111
PG_CONFIG = pg_config
Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.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.2'" to load this file. \quit
5+
6+
/* First we have to remove them from the extension*/
7+
ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
8+
ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements();
9+
10+
/* Then we can drop them*/
11+
DROPVIEW pg_stat_statements;
12+
DROPFUNCTION pg_stat_statements();
13+
14+
/* Now redefine*/
15+
CREATEFUNCTIONpg_stat_statements(
16+
OUT useridoid,
17+
OUT dbidoid,
18+
OUT queryidbigint,
19+
OUT querytext,
20+
OUT calls int8,
21+
OUT total_time float8,
22+
OUT rows int8,
23+
OUT shared_blks_hit int8,
24+
OUT shared_blks_read int8,
25+
OUT shared_blks_dirtied int8,
26+
OUT shared_blks_written int8,
27+
OUT local_blks_hit int8,
28+
OUT local_blks_read int8,
29+
OUT local_blks_dirtied int8,
30+
OUT local_blks_written int8,
31+
OUT temp_blks_read int8,
32+
OUT temp_blks_written int8,
33+
OUT blk_read_time float8,
34+
OUT blk_write_time float8
35+
)
36+
RETURNS SETOF record
37+
AS'MODULE_PATHNAME'
38+
LANGUAGE C;
39+
40+
CREATEVIEWpg_stat_statementsAS
41+
SELECT*FROM pg_stat_statements();
42+
43+
GRANTSELECTON pg_stat_statements TO PUBLIC;
Lines changed: 0 additions & 43 deletions
Original file line numberDiff line numberDiff line change
@@ -1,43 +0,0 @@
1-
/* contrib/pg_stat_statements/pg_stat_statements--1.1.sql*/
2-
3-
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4-
\echo Use"CREATE EXTENSION pg_stat_statements" to load this file. \quit
5-
6-
-- Register functions.
7-
CREATEFUNCTIONpg_stat_statements_reset()
8-
RETURNS void
9-
AS'MODULE_PATHNAME'
10-
LANGUAGE C;
11-
12-
CREATEFUNCTIONpg_stat_statements(
13-
OUT useridoid,
14-
OUT dbidoid,
15-
OUT querytext,
16-
OUT calls int8,
17-
OUT total_time float8,
18-
OUT rows int8,
19-
OUT shared_blks_hit int8,
20-
OUT shared_blks_read int8,
21-
OUT shared_blks_dirtied int8,
22-
OUT shared_blks_written int8,
23-
OUT local_blks_hit int8,
24-
OUT local_blks_read int8,
25-
OUT local_blks_dirtied int8,
26-
OUT local_blks_written int8,
27-
OUT temp_blks_read int8,
28-
OUT temp_blks_written int8,
29-
OUT blk_read_time float8,
30-
OUT blk_write_time float8
31-
)
32-
RETURNS SETOF record
33-
AS'MODULE_PATHNAME'
34-
LANGUAGE C;
35-
36-
-- Register a view on the function for ease of use.
37-
CREATEVIEWpg_stat_statementsAS
38-
SELECT*FROM pg_stat_statements();
39-
40-
GRANTSELECTON pg_stat_statements TO PUBLIC;
41-
42-
-- Don't want this to be available to non-superusers.
43-
REVOKE ALLON FUNCTION pg_stat_statements_reset()FROM PUBLIC;
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.2.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"CREATE EXTENSION pg_stat_statements" to load this file. \quit
5+
6+
-- Register functions.
7+
CREATEFUNCTIONpg_stat_statements_reset()
8+
RETURNS void
9+
AS'MODULE_PATHNAME'
10+
LANGUAGE C;
11+
12+
CREATEFUNCTIONpg_stat_statements(
13+
OUT useridoid,
14+
OUT dbidoid,
15+
OUT queryidbigint,
16+
OUT querytext,
17+
OUT calls int8,
18+
OUT total_time float8,
19+
OUT rows int8,
20+
OUT shared_blks_hit int8,
21+
OUT shared_blks_read int8,
22+
OUT shared_blks_dirtied int8,
23+
OUT shared_blks_written int8,
24+
OUT local_blks_hit int8,
25+
OUT local_blks_read int8,
26+
OUT local_blks_dirtied int8,
27+
OUT local_blks_written int8,
28+
OUT temp_blks_read int8,
29+
OUT temp_blks_written int8,
30+
OUT blk_read_time float8,
31+
OUT blk_write_time float8
32+
)
33+
RETURNS SETOF record
34+
AS'MODULE_PATHNAME'
35+
LANGUAGE C;
36+
37+
-- Register a view on the function for ease of use.
38+
CREATEVIEWpg_stat_statementsAS
39+
SELECT*FROM pg_stat_statements();
40+
41+
GRANTSELECTON pg_stat_statements TO PUBLIC;
42+
43+
-- Don't want this to be available to non-superusers.
44+
REVOKE ALLON FUNCTION pg_stat_statements_reset()FROM PUBLIC;

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 52 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -67,7 +67,9 @@ PG_MODULE_MAGIC;
6767
#definePGSS_DUMP_FILE"global/pg_stat_statements.stat"
6868

6969
/* This constant defines the magic number in the stats file header */
70-
staticconstuint32PGSS_FILE_HEADER=0x20120328;
70+
staticconstuint32PGSS_FILE_HEADER=0x20131115;
71+
/* PostgreSQL major version number, changes in which invalidate all entries */
72+
staticconstuint32PGSS_PG_MAJOR_VERSION=PG_VERSION_NUM /100;
7173

7274
/* XXX: Should USAGE_EXEC reflect execution time and/or buffer usage? */
7375
#defineUSAGE_EXEC(duration)(1.0)
@@ -79,6 +81,16 @@ static const uint32 PGSS_FILE_HEADER = 0x20120328;
7981

8082
#defineJUMBLE_SIZE1024/* query serialization buffer size */
8183

84+
/*
85+
* Extension version number, for supporting older extension versions' objects
86+
*/
87+
typedefenumpgssVersion
88+
{
89+
PGSS_V1_0=0,
90+
PGSS_V1_1,
91+
PGSS_V1_2
92+
}pgssVersion;
93+
8294
/*
8395
* Hashtable key that defines the identity of a hashtable entry. We separate
8496
* queries by user and by database even if they are otherwise identical.
@@ -390,6 +402,7 @@ pgss_shmem_startup(void)
390402
FILE*file;
391403
uint32header;
392404
int32num;
405+
int32pgver;
393406
int32i;
394407
intquery_size;
395408
intbuffer_size;
@@ -465,6 +478,8 @@ pgss_shmem_startup(void)
465478

466479
if (fread(&header,sizeof(uint32),1,file)!=1||
467480
header!=PGSS_FILE_HEADER||
481+
fread(&pgver,sizeof(uint32),1,file)!=1||
482+
pgver!=PGSS_PG_MAJOR_VERSION||
468483
fread(&num,sizeof(int32),1,file)!=1)
469484
gotoerror;
470485

@@ -565,6 +580,8 @@ pgss_shmem_shutdown(int code, Datum arg)
565580

566581
if (fwrite(&PGSS_FILE_HEADER,sizeof(uint32),1,file)!=1)
567582
gotoerror;
583+
if (fwrite(&PGSS_PG_MAJOR_VERSION,sizeof(uint32),1,file)!=1)
584+
gotoerror;
568585
num_entries=hash_get_num_entries(pgss_hash);
569586
if (fwrite(&num_entries,sizeof(int32),1,file)!=1)
570587
gotoerror;
@@ -1069,7 +1086,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
10691086
}
10701087

10711088
#definePG_STAT_STATEMENTS_COLS_V1_014
1072-
#definePG_STAT_STATEMENTS_COLS18
1089+
#definePG_STAT_STATEMENTS_COLS_V1_118
1090+
#definePG_STAT_STATEMENTS_COLS19
10731091

10741092
/*
10751093
* Retrieve statement statistics.
@@ -1086,7 +1104,7 @@ pg_stat_statements(PG_FUNCTION_ARGS)
10861104
boolis_superuser=superuser();
10871105
HASH_SEQ_STATUShash_seq;
10881106
pgssEntry*entry;
1089-
boolsql_supports_v1_1_counters= true;
1107+
pgssVersiondetected_version;
10901108

10911109
if (!pgss|| !pgss_hash)
10921110
ereport(ERROR,
@@ -1107,8 +1125,21 @@ pg_stat_statements(PG_FUNCTION_ARGS)
11071125
/* Build a tuple descriptor for our result type */
11081126
if (get_call_result_type(fcinfo,NULL,&tupdesc)!=TYPEFUNC_COMPOSITE)
11091127
elog(ERROR,"return type must be a row type");
1110-
if (tupdesc->natts==PG_STAT_STATEMENTS_COLS_V1_0)
1111-
sql_supports_v1_1_counters= false;
1128+
1129+
switch (tupdesc->natts)
1130+
{
1131+
casePG_STAT_STATEMENTS_COLS_V1_0:
1132+
detected_version=PGSS_V1_0;
1133+
break;
1134+
casePG_STAT_STATEMENTS_COLS_V1_1:
1135+
detected_version=PGSS_V1_1;
1136+
break;
1137+
casePG_STAT_STATEMENTS_COLS:
1138+
detected_version=PGSS_V1_2;
1139+
break;
1140+
default:
1141+
elog(ERROR,"pgss version unrecognized from tuple descriptor");
1142+
}
11121143

11131144
per_query_ctx=rsinfo->econtext->ecxt_per_query_memory;
11141145
oldcontext=MemoryContextSwitchTo(per_query_ctx);
@@ -1140,6 +1171,9 @@ pg_stat_statements(PG_FUNCTION_ARGS)
11401171
{
11411172
char*qstr;
11421173

1174+
if (detected_version >=PGSS_V1_2)
1175+
values[i++]=Int64GetDatumFast((int64)entry->key.queryid);
1176+
11431177
qstr= (char*)
11441178
pg_do_encoding_conversion((unsignedchar*)entry->query,
11451179
entry->query_len,
@@ -1150,7 +1184,12 @@ pg_stat_statements(PG_FUNCTION_ARGS)
11501184
pfree(qstr);
11511185
}
11521186
else
1187+
{
1188+
if (detected_version >=PGSS_V1_2)
1189+
nulls[i++]= true;
1190+
11531191
values[i++]=CStringGetTextDatum("<insufficient privilege>");
1192+
}
11541193

11551194
/* copy counters to a local variable to keep locking time short */
11561195
{
@@ -1170,24 +1209,27 @@ pg_stat_statements(PG_FUNCTION_ARGS)
11701209
values[i++]=Int64GetDatumFast(tmp.rows);
11711210
values[i++]=Int64GetDatumFast(tmp.shared_blks_hit);
11721211
values[i++]=Int64GetDatumFast(tmp.shared_blks_read);
1173-
if (sql_supports_v1_1_counters)
1212+
if (detected_version >=PGSS_V1_1)
11741213
values[i++]=Int64GetDatumFast(tmp.shared_blks_dirtied);
11751214
values[i++]=Int64GetDatumFast(tmp.shared_blks_written);
11761215
values[i++]=Int64GetDatumFast(tmp.local_blks_hit);
11771216
values[i++]=Int64GetDatumFast(tmp.local_blks_read);
1178-
if (sql_supports_v1_1_counters)
1217+
if (detected_version >=PGSS_V1_1)
11791218
values[i++]=Int64GetDatumFast(tmp.local_blks_dirtied);
11801219
values[i++]=Int64GetDatumFast(tmp.local_blks_written);
11811220
values[i++]=Int64GetDatumFast(tmp.temp_blks_read);
11821221
values[i++]=Int64GetDatumFast(tmp.temp_blks_written);
1183-
if (sql_supports_v1_1_counters)
1222+
if (detected_version >=PGSS_V1_1)
11841223
{
11851224
values[i++]=Float8GetDatumFast(tmp.blk_read_time);
11861225
values[i++]=Float8GetDatumFast(tmp.blk_write_time);
11871226
}
11881227

1189-
Assert(i== (sql_supports_v1_1_counters ?
1190-
PG_STAT_STATEMENTS_COLS :PG_STAT_STATEMENTS_COLS_V1_0));
1228+
Assert(i== (detected_version==PGSS_V1_0?
1229+
PG_STAT_STATEMENTS_COLS_V1_0:
1230+
detected_version==PGSS_V1_1?
1231+
PG_STAT_STATEMENTS_COLS_V1_1:
1232+
PG_STAT_STATEMENTS_COLS));
11911233

11921234
tuplestore_putvalues(tupstore,tupdesc,values,nulls);
11931235
}
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_stat_statements extension
22
comment = 'track execution statistics of all SQL statements executed'
3-
default_version = '1.1'
3+
default_version = '1.2'
44
module_pathname = '$libdir/pg_stat_statements'
55
relocatable = true

0 commit comments

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