WIP: executor_hook for pg_stat_statements
ITAGAKI Takahiro <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.devel.patches |
|---|---|
| Message-ID | <[email protected]> |
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
--
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
pg_stat_statements.tar.gz
(application/octet-stream, 3.6 KB) - not displayed
executor_hook.patch
(application/octet-stream, 3.6 KB)
Index: src/backend/executor/execMain.c =================================================================== --- src/backend/executor/execMain.c (HEAD) +++ src/backend/executor/execMain.c (working copy) @@ -67,14 +67,12 @@ struct evalPlanQual *free; /* list of free PlanQual plans */ } evalPlanQual; +/* Hook for plugins to get control in ExecutorRun() */ +executor_hook_type executor_hook = NULL; + /* decls for local routines only used within this module */ static void InitPlan(QueryDesc *queryDesc, int eflags); static void ExecEndPlan(PlanState *planstate, EState *estate); -static TupleTableSlot *ExecutePlan(EState *estate, PlanState *planstate, - CmdType operation, - long numberTuples, - ScanDirection direction, - DestReceiver *dest); static void ExecSelect(TupleTableSlot *slot, DestReceiver *dest, EState *estate); static void ExecInsert(TupleTableSlot *slot, ItemPointer tupleid, @@ -262,6 +260,8 @@ */ if (ScanDirectionIsNoMovement(direction)) result = NULL; + else if (executor_hook) + result = executor_hook(queryDesc, direction, count); else result = ExecutePlan(estate, queryDesc->planstate, @@ -1182,7 +1182,7 @@ * user can see it * ---------------------------------------------------------------- */ -static TupleTableSlot * +TupleTableSlot * ExecutePlan(EState *estate, PlanState *planstate, CmdType operation, Index: src/backend/nodes/copyfuncs.c =================================================================== --- src/backend/nodes/copyfuncs.c (HEAD) +++ src/backend/nodes/copyfuncs.c (working copy) @@ -85,6 +85,7 @@ COPY_NODE_FIELD(rowMarks); COPY_NODE_FIELD(relationOids); COPY_SCALAR_FIELD(nParamExec); + COPY_SCALAR_FIELD(tag); return newnode; } Index: src/backend/nodes/outfuncs.c =================================================================== --- src/backend/nodes/outfuncs.c (HEAD) +++ src/backend/nodes/outfuncs.c (working copy) @@ -252,6 +252,7 @@ WRITE_NODE_FIELD(rowMarks); WRITE_NODE_FIELD(relationOids); WRITE_INT_FIELD(nParamExec); + WRITE_INT_FIELD(tag); } /* Index: src/include/executor/executor.h =================================================================== --- src/include/executor/executor.h (HEAD) +++ src/include/executor/executor.h (working copy) @@ -138,6 +138,11 @@ ScanDirection direction, long count); extern void ExecutorEnd(QueryDesc *queryDesc); extern void ExecutorRewind(QueryDesc *queryDesc); +extern TupleTableSlot *ExecutePlan(EState *estate, PlanState *planstate, + CmdType operation, + long numberTuples, + ScanDirection direction, + DestReceiver *dest); extern void InitResultRelInfo(ResultRelInfo *resultRelInfo, Relation resultRelationDesc, Index resultRelationIndex, @@ -152,6 +157,11 @@ extern PlanState *ExecGetActivePlanTree(QueryDesc *queryDesc); extern DestReceiver *CreateIntoRelDestReceiver(void); +/* Hook for plugins to get control in ExecutorRun() */ +typedef TupleTableSlot *(*executor_hook_type) (QueryDesc *queryDesc, + ScanDirection direction, long count); +extern PGDLLIMPORT executor_hook_type executor_hook; + /* * prototypes from functions in execProcnode.c */ Index: src/include/nodes/plannodes.h =================================================================== --- src/include/nodes/plannodes.h (HEAD) +++ src/include/nodes/plannodes.h (working copy) @@ -73,6 +73,8 @@ List *relationOids; /* OIDs of relations the plan depends on */ int nParamExec; /* number of PARAM_EXEC Params used */ + + uint32 tag; /* plan tag */ } PlannedStmt; /* macro for fetching the Plan associated with a SubPlan node */