WIP: executor_hook for pg_stat_statements

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-patches(at)postgresql(dot)org
Subject: WIP: executor_hook for pg_stat_statements
Date: 2008-06-23 06:22:57
Message-ID: 20080623150535.946E.52131E4D@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers 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

Attachment Content-Type Size
pg_stat_statements.tar.gz application/octet-stream 3.6 KB
executor_hook.patch application/octet-stream 3.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message KaiGai Kohei 2008-06-23 08:23:24 Proposal of SE-PostgreSQL patches [try#2]
Previous Message ITAGAKI Takahiro 2008-06-23 06:22:52 Re: pg_stat_statements

Browse pgsql-patches by date

  From Date Subject
Next Message Pavel Stehule 2008-06-23 13:13:22 variadic function support
Previous Message Bruce Momjian 2008-06-23 02:21:19 Re: Simplify formatting.c