Hi -hackers,
This is my first patch here in the mailing list, so I tried to explain the "why" and the "how" of this enhancement.
Needs :
Formerly, Oracle DBA, I used to query v$sql to know the latest queries issued by each session with their timestamp. I found postgresql pg_stat_statements very useful for this need, but the aggregation did not always permitted me to analyze correctly the queries issued (at least for a buffer stats per query overview). So, I enhanced the existing pg_stat_statements.
Changes overview :
- new configuration pg_stat_statements.track_every = (TRUE|FALSE)
-> generating per query data in a new view : pg_stat_sql
-> can be resetted by using : pg_stat_sql_reset(userid,dbid,queryid)
- added the timestamp per query to the view (replacing the number of calls of pg_stat_statements)
-> the view column itself :
userid oid,
dbid oid,
queryid bigint,
query text,
start timestamp, *
total_time float8,
rows int8,
shared_blks_hit int8,
shared_blks_read int8,
shared_blks_dirtied int8,
shared_blks_written int8,
local_blks_hit int8,
local_blks_read int8,
local_blks_dirtied int8,
local_blks_written int8,
temp_blks_read int8,
temp_blks_written int8,
blk_read_time float8,
blk_write_time float8,
wal_records int8,
wal_fpi int8,
wal_bytes numeric
- data are stored in a hash in a new shared memory area.
- query texts are still stored in the same file.
The goal was to avoid generating too much data with track_every option enabled.
- added some tests to the sql/pg_stat_statements.sql
- added views to pg_stat_statements--1.7--1.8.sql
Bug fix :
- with UTF8 encoding, the "\0" to delimit the end of the query text was buggy; modified to query[query_len]=0;
Note :
I didn't want to change version number by myself, the attached files are still pointing to 1.8
This is my first code for pgsql.
I wanted to share with you this enhancement, hope you'll find it useful.
--
Regards,
Yoan SULTAN
