WIP: executor_hook for pg_stat_statements - Mailing list pgsql-patches

I'm working on light-weight SQL logging for PostgreSQL.
http://archives.postgresql.org/pgsql-hackers/2008-06/msg00601.php

I divide the SQL logging feature into a core patch and an extension module.
I hope only the patch is to be applied in the core. The extension module
would be better to be developed separately from the core.


The attached patch (executor_hook.patch) modifies HEAD as follows.

- Add "tag" field (uint32) into PlannedStmt.
- Add executor_hook to replace ExecutePlan().
- Move ExecutePlan() to a global function.


The archive file (pg_stat_statements.tar.gz) is a sample extension module.
It uses the existing planner_hook and the new executor_hook to record
statements on planned and executed. You can see all of executed statements
through the following VIEW:

View "public.pg_stat_statements"
   Column   |  Type  | Description
------------+--------+------------------------------------
 userid     | oid    | user id who execute the statement
 datid      | oid    | target database
 query      | text   | query's SQL text
 planned    | bigint | number of planned
 calls      | bigint | number of executed
 total_time | bigint | total executing time in msec

Here is a sample output of the view.

postgres=# SELECT pg_stat_statements_reset();
$ pgbench -c10 -t1000 -M prepared
postgres=# SELECT * FROM pg_stat_statements ORDER BY query;
 userid | datid |                                             query                                             |
planned| calls | total_time 

--------+-------+-----------------------------------------------------------------------------------------------+---------+-------+------------
     10 | 11505 | INSERT INTO history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); |
10| 10000 |        196 
     10 | 11505 | SELECT * FROM pg_stat_statements ORDER BY query;                                              |
1|     0 |          0 
     10 | 11505 | SELECT abalance FROM accounts WHERE aid = $1;                                                 |
10| 10000 |        288 
     10 | 11505 | UPDATE accounts SET abalance = abalance + $1 WHERE aid = $2;                                  |
10| 10000 |       1269 
     10 | 11505 | UPDATE branches SET bbalance = bbalance + $1 WHERE bid = $2;                                  |
10| 10000 |      21737 
     10 | 11505 | UPDATE tellers SET tbalance = tbalance + $1 WHERE tid = $2;                                   |
10| 10000 |       6950 
     10 | 11505 | delete from history                                                                           |
1|     1 |          0 
     10 | 11505 | select count(*) from branches                                                                 |
1|     1 |          0 
(8 rows)

You need to add the below options in postgresql.conf.
    shared_preload_libraries = 'pg_stat_statements'
    custom_variable_classes = 'statspack'
    statspack.max_statements = 1000    # max number of distinct statements
    statspack.statement_buffer = 1024  # buffer to record SQL text

This module is WIP and far from complete. It allocates fixed shared
memory and record SQLs there, but doesn't handle out-of-memory situaton
for now. Also, It can handle statements using extended prorocol or
prepared statements, but not simple protocol queries. And every user
can view other user's queries.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center