Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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: executor_hook.patch
Description: application/octet-stream (3.6 KB)
Attachment: pg_stat_statements.tar.gz
Description: application/octet-stream (3.6 KB)

Responses

pgsql-hackers by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group