Hello,
At Fri, 30 Dec 2016 15:10:42 +0100 (CET), Fabien COELHO <coe...@cri.ensmp.fr>
wrote in <alpine.DEB.2.20.1612301453280.32017@lancre>
>
> Hello,
>
> >> Yeah, that's what I was thinking of. There aren't very many places
> >> that
> >> would need to know about that, I believe; [...]
> >
> > For fixing the information in pg_stat_statement, the location data
> > must be transported from the parsed node to the query to the planned
> > node, because the later two nodes types are passed to different hooks.
> >
> > Now the detail is that utility statements, which seems to be nearly
> > all of them but select/update/delete/insert, do not have plans: The
> > statement itself is its own plan... so there is no place to store the
> > location & length.
>
> Here is an updated version:
>
> Changes wrt v2:
>
> - I have added the missing stuff under /nodes/, this is stupid code that
> should be automatically generated:-(
>
> - I have added comments in "gram.y" about how the length is computed.
> I have also slightly simplified the rule code there.
>
> - I have rename "location" in create table space to "location_dir"
> to avoid confusion.
>
> - I have renamed the fields "location" and "length" instead of q*.
>
> - I have moved the location & lenth copies in standard_planner.
>
> - I have fixed the function declaration typo.
>
> - I have simplified pgss_store code to avoid a copy, and move the
> length truncation in qtext_store.
>
> - I have improved again the pg_stat_statement regression tests with
> combined utility statement tests, which implied some fixes to
> extract the right substring for utility queries.
>
> However, not changed:
>
> - I cannot use the intermediate node trick suggested by Tom because
> it does not work for utility statements which do not have plans, so
> the code still adds location & length, sorry.
Wrapping the output of pg_parse_query still works for non-utility
commands. But pg_stat_statement retrieves the location
information via ProcessUtility, which has plantree. Wrapping
plantree with the same struct also works but it imacts too widely
and extremely error-prone.
One available solution is, as Fabien did, bring location and
length data along with transformation. And anther is give a
chopped query to query tree.
The attached patch does the second way (but quite ugly and
incomplete, it's PoC). This seems working as expected to a
certain degree but somewhat bogus.. A significant drawback of
this is that this makes a copy of each query in multistatement.
> - I still use the 'last_semicolon' lexer variable. The alternative is to
> change rules so as not to skip empty statements, then write a loop to
> compute the length based on successor location, and remove the empty
> statements. It can be done, I do not think it is better, it is only
> different and more verbose. I'll do it if required by a committer.
I think this is doable in the way shown in this patch. But this
seems somewhat bogus, too..
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
>From 20b34be19e51b878c33306d13a453bdd05da23bc Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp>
Date: Thu, 12 Jan 2017 21:51:34 +0900
Subject: [PATCH 1/2] Fix current query string of query tree.
---
src/backend/catalog/pg_proc.c | 2 +-
src/backend/commands/extension.c | 2 +-
src/backend/commands/foreigncmds.c | 3 ++-
src/backend/commands/prepare.c | 2 +-
src/backend/commands/tablecmds.c | 2 +-
src/backend/executor/functions.c | 2 +-
src/backend/executor/spi.c | 4 ++--
src/backend/nodes/copyfuncs.c | 15 +++++++++++++++
src/backend/nodes/equalfuncs.c | 13 +++++++++++++
src/backend/parser/analyze.c | 4 ++++
src/backend/parser/gram.y | 20 ++++++++++++++++++--
src/backend/parser/parse_type.c | 2 +-
src/backend/tcop/postgres.c | 22 ++++++++++++++++------
src/backend/tcop/pquery.c | 1 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 20 ++++++++++++++++++++
src/include/nodes/plannodes.h | 2 ++
17 files changed, 100 insertions(+), 17 deletions(-)
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 6d8f17d..6d9e639 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -934,7 +934,7 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
querytree_list = NIL;
foreach(lc, raw_parsetree_list)
{
- Node *parsetree = (Node *) lfirst(lc);
+ Node *parsetree = (Node *) stripParseNode(lfirst(lc));
List *querytree_sublist;
querytree_sublist = pg_analyze_and_rewrite_params(parsetree,
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index be52148..36ebf93 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -712,7 +712,7 @@ execute_sql_string(const char *sql, const char *filename)
*/
foreach(lc1, raw_parsetree_list)
{
- Node *parsetree = (Node *) lfirst(lc1);
+ Node *parsetree = (Node *) stripParseNode(lfirst(lc1));
List *stmt_list;
ListCell *lc2;
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index 06b4bc3..12fa20f 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -1572,7 +1572,8 @@ ImportForeignSchema(ImportForeignSchemaStmt *stmt)
*/
foreach(lc2, raw_parsetree_list)
{
- CreateForeignTableStmt *cstmt = lfirst(lc2);
+ CreateForeignTableStmt *cstmt =
+ (CreateForeignTableStmt *) stripParseNode(lfirst(lc2));
/*
* Because we only allow CreateForeignTableStmt, we can skip parse
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index d768cf8..5f6e8e4 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -255,7 +255,7 @@ ExecuteQuery(ExecuteStmt *stmt, IntoClause *intoClause,
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("prepared statement is not a SELECT")));
- pstmt = (PlannedStmt *) linitial(plan_list);
+ pstmt = (PlannedStmt *) stripParseNode(linitial(plan_list));
if (!IsA(pstmt, PlannedStmt) ||
pstmt->commandType != CMD_SELECT ||
pstmt->utilityStmt != NULL)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 42558ec..5b01cf7 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9285,7 +9285,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
querytree_list = NIL;
foreach(list_item, raw_parsetree_list)
{
- Node *stmt = (Node *) lfirst(list_item);
+ Node *stmt = (Node *) stripParseNode(lfirst(list_item));
if (IsA(stmt, IndexStmt))
querytree_list = lappend(querytree_list,
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 0a1bb9d..46c2388 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -695,7 +695,7 @@ init_sql_fcache(FmgrInfo *finfo, Oid collation, bool lazyEvalOK)
flat_query_list = NIL;
foreach(lc, raw_parsetree_list)
{
- Node *parsetree = (Node *) lfirst(lc);
+ Node *parsetree = (Node *) stripParseNode(lfirst(lc));
List *queryTree_sublist;
queryTree_sublist = pg_analyze_and_rewrite_params(parsetree,
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index ee7a7e2..2f35f6b 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -1757,7 +1757,7 @@ _SPI_prepare_plan(const char *src, SPIPlanPtr plan)
foreach(list_item, raw_parsetree_list)
{
- Node *parsetree = (Node *) lfirst(list_item);
+ Node *parsetree = (Node *) stripParseNode(lfirst(list_item));
List *stmt_list;
CachedPlanSource *plansource;
@@ -1859,7 +1859,7 @@ _SPI_prepare_oneshot_plan(const char *src, SPIPlanPtr plan)
foreach(list_item, raw_parsetree_list)
{
- Node *parsetree = (Node *) lfirst(list_item);
+ Node *parsetree = (Node *) stripParseNode(lfirst(list_item));
CachedPlanSource *plansource;
plansource = CreateOneShotCachedPlan(parsetree,
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 930f2f1..47d6934 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2771,6 +2771,18 @@ _copyQuery(const Query *from)
return newnode;
}
+static ParseNode *
+_copyParseNode(const ParseNode *from)
+{
+ ParseNode *newnode = makeNode(ParseNode);
+
+ COPY_SCALAR_FIELD(location);
+ COPY_SCALAR_FIELD(length);
+ COPY_NODE_FIELD(stmt);
+
+ return newnode;
+}
+
static InsertStmt *
_copyInsertStmt(const InsertStmt *from)
{
@@ -4728,6 +4740,9 @@ copyObject(const void *from)
case T_Query:
retval = _copyQuery(from);
break;
+ case T_ParseNode:
+ retval = _copyParseNode(from);
+ break;
case T_InsertStmt:
retval = _copyInsertStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index a27e5ed..a81bf87 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -951,6 +951,16 @@ _equalQuery(const Query *a, const Query *b)
}
static bool
+_equalParseNode(const ParseNode *a, const ParseNode *b)
+{
+ COMPARE_SCALAR_FIELD(location);
+ COMPARE_SCALAR_FIELD(length);
+ COMPARE_NODE_FIELD(stmt);
+
+ return true;
+}
+
+static bool
_equalInsertStmt(const InsertStmt *a, const InsertStmt *b)
{
COMPARE_NODE_FIELD(relation);
@@ -3015,6 +3025,9 @@ equal(const void *a, const void *b)
case T_Query:
retval = _equalQuery(a, b);
break;
+ case T_ParseNode:
+ retval = _equalParseNode(a, b);
+ break;
case T_InsertStmt:
retval = _equalInsertStmt(a, b);
break;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 5116cbb..909734d 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -183,6 +183,8 @@ parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
Query *
transformTopLevelStmt(ParseState *pstate, Node *parseTree)
{
+ parseTree = stripParseNode(parseTree);
+
if (IsA(parseTree, SelectStmt))
{
SelectStmt *stmt = (SelectStmt *) parseTree;
@@ -224,6 +226,8 @@ transformStmt(ParseState *pstate, Node *parseTree)
{
Query *result;
+ Assert(!IsA(parseTree, ParseNode));
+
/*
* We apply RAW_EXPRESSION_COVERAGE_TEST testing to basic DML statements;
* we can't just run it on everything because raw_expression_tree_walker()
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9eef550..45ac3ae 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -761,15 +761,31 @@ stmtblock: stmtmulti
/* the thrashing around here is to discard "empty" statements... */
stmtmulti: stmtmulti ';' stmt
{
+ if ($1 != NULL)
+ {
+ ParseNode *last = (ParseNode *) llast($1);
+ last->length = @2 - last->location + 1;
+ }
if ($3 != NULL)
- $$ = lappend($1, $3);
+ {
+ ParseNode *pn = makeNode(ParseNode);
+ pn->stmt = $3;
+ pn->location = @3;
+ $$ = lappend($1, pn);
+ }
else
$$ = $1;
}
| stmt
{
if ($1 != NULL)
- $$ = list_make1($1);
+ {
+ ParseNode *pn = makeNode(ParseNode);
+ pn->location = @1;
+ pn->length = 0;
+ pn->stmt = $1;
+ $$ = list_make1(pn);
+ }
else
$$ = NIL;
}
diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c
index e2c884c..f9001a0 100644
--- a/src/backend/parser/parse_type.c
+++ b/src/backend/parser/parse_type.c
@@ -720,7 +720,7 @@ typeStringToTypeName(const char *str)
*/
if (list_length(raw_parsetree_list) != 1)
goto fail;
- stmt = (SelectStmt *) linitial(raw_parsetree_list);
+ stmt = (SelectStmt *) stripParseNode(linitial(raw_parsetree_list));
if (stmt == NULL ||
!IsA(stmt, SelectStmt) ||
stmt->distinctClause != NIL ||
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 05b2e57..1e5b50f 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -881,7 +881,6 @@ exec_simple_query(const char *query_string)
bool isTopLevel;
char msec_str[32];
-
/*
* Report query to various monitoring facilities.
*/
@@ -955,7 +954,8 @@ exec_simple_query(const char *query_string)
*/
foreach(parsetree_item, parsetree_list)
{
- Node *parsetree = (Node *) lfirst(parsetree_item);
+ ParseNode *raw_parsetree = (ParseNode *) lfirst(parsetree_item);
+ Node *parsetree = stripParseNode(raw_parsetree);
bool snapshot_set = false;
const char *commandTag;
char completionTag[COMPLETION_TAG_BUFSIZE];
@@ -964,7 +964,16 @@ exec_simple_query(const char *query_string)
Portal portal;
DestReceiver *receiver;
int16 format;
+ char *current_query_string = (char *)query_string;
+ if (list_length(parsetree_list) > 1)
+ {
+ current_query_string = palloc(raw_parsetree->length + 1);
+ memcpy(current_query_string,
+ query_string + raw_parsetree->location,
+ raw_parsetree->length + 1);
+ current_query_string[raw_parsetree->length] = 0;
+ }
/*
* Get the command name for use in status display (it also becomes the
* default completion tag, down inside PortalRun). Set ps_status and
@@ -1016,7 +1025,8 @@ exec_simple_query(const char *query_string)
*/
oldcontext = MemoryContextSwitchTo(MessageContext);
- querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
+ querytree_list = pg_analyze_and_rewrite(parsetree,
+ current_query_string,
NULL, 0);
plantree_list = pg_plan_queries(querytree_list,
@@ -1044,7 +1054,7 @@ exec_simple_query(const char *query_string)
*/
PortalDefineQuery(portal,
NULL,
- query_string,
+ current_query_string,
commandTag,
plantree_list,
NULL);
@@ -1279,7 +1289,7 @@ exec_parse_message(const char *query_string, /* string to execute */
bool snapshot_set = false;
int i;
- raw_parse_tree = (Node *) linitial(parsetree_list);
+ raw_parse_tree = (Node *) stripParseNode(linitial(parsetree_list));
/*
* Get the command name for possible use in status display.
@@ -2064,7 +2074,7 @@ check_log_statement(List *stmt_list)
/* Else we have to inspect the statement(s) to see whether to log */
foreach(stmt_item, stmt_list)
{
- Node *stmt = (Node *) lfirst(stmt_item);
+ Node *stmt = stripParseNode(lfirst(stmt_item));
if (GetCommandLogLevel(stmt) <= log_statement)
return true;
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 3b5da73..9ddfc55 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -31,6 +31,7 @@
* if there are several).
*/
Portal ActivePortal = NULL;
+char *portal_query_string = NULL;
static void ProcessQuery(PlannedStmt *plan,
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index a1bb0ac..928da44 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -302,6 +302,7 @@ typedef enum NodeTag
* TAGS FOR STATEMENT NODES (mostly in parsenodes.h)
*/
T_Query,
+ T_ParseNode,
T_PlannedStmt,
T_InsertStmt,
T_DeleteStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 7ceaa22..23bbfc8 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -78,6 +78,26 @@ typedef uint32 AclMode; /* a bitmask of privilege bits */
/* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */
#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
+/*
+ * A ParseNode is a Node with additional location information.
+ * Zero qlengh means not set.
+ * If non-zero, then location is within to the initial query string.
+ */
+typedef struct ParseNode
+{
+ NodeTag type;
+ int location;
+ int length;
+ Node *stmt;
+} ParseNode;
+
+/*
+ * All high-level statements coming out of the parser are ParseNode,
+ * plus Query & PlannedStmt.
+ */
+#define isParseNodeTag(tag) ((T_Query <= (tag)) && ((tag) < T_A_Expr))
+#define isParseNode(nodeptr) IsA((nodeptr), ParseNode)
+#define stripParseNode(nodeptr) (isParseNode(nodeptr)? ((ParseNode *)(nodeptr))->stmt : (Node *) (nodeptr))
/*****************************************************************************
* Query Tree
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 692a626..921d04c 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -36,6 +36,8 @@
typedef struct PlannedStmt
{
NodeTag type;
+ int location; /* query location */
+ int length; /* query length, 0 if unset */
CmdType commandType; /* select|insert|update|delete */
--
2.9.2
>From cddfc4d9d7e3d51d19e8ed80c60c34c159869d20 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp>
Date: Thu, 12 Jan 2017 21:52:14 +0900
Subject: [PATCH 2/2] Change regtest of pg_stat_statements.
This is just the same as Fabien's patch, but fails in somewhat bogus
way..
---
.../expected/pg_stat_statements.out | 362 ++++++++++++++++++++-
.../pg_stat_statements/sql/pg_stat_statements.sql | 183 ++++++++++-
2 files changed, 530 insertions(+), 15 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 3573c19..06f6ffe 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -1,21 +1,363 @@
CREATE EXTENSION pg_stat_statements;
-CREATE TABLE test (a int, b char(20));
--- test the basic functionality of pg_stat_statements
+--
+--
+-- simple and compound statements
+--
+SET pg_stat_statements.track_utility = FALSE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT 1 AS "int";
+ int
+-----
+ 1
+(1 row)
+
+SELECT 'hello'
+ -- multiline
+ AS "text";
+ text
+-------
+ hello
+(1 row)
+
+SELECT 'world' AS "text";
+ text
+-------
+ world
+(1 row)
+
+-- transaction
+BEGIN;
+SELECT 1 AS "int";
+ int
+-----
+ 1
+(1 row)
+
+SELECT 'hello' AS "text";
+ text
+-------
+ hello
+(1 row)
+
+COMMIT;
+-- compound transaction
+BEGIN \;
+SELECT 2.0 AS "float" \;
+SELECT 'world' AS "text" \;
+COMMIT;
+-- compound with empty statements and spurious leading spacing
+\;\; SELECT 3 + 3 \;\;\; SELECT ' ' || ' !' \;\; SELECT 1 + 4 \;;
+ ?column?
+----------
+ 5
+(1 row)
+
+-- non ;-terminated statements
+SELECT 1 + 1 + 1 AS "add" \gset
+SELECT :add + 1 + 1 AS "add" \;
+SELECT :add + 1 + 1 AS "add" \gset
+-- set operator
+SELECT 1 AS i UNION SELECT 2 ORDER BY i;
+ i
+---
+ 1
+ 2
+(2 rows)
+
+-- cte
+WITH t(f) AS (
+ VALUES (1.0), (2.0)
+)
+ SELECT f FROM t ORDER BY f;
+ f
+-----
+ 1.0
+ 2.0
+(2 rows)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+ query | calls | rows
+-----------------------------------------+-------+------
+ SELECT ? || ? | 1 | 1
+ SELECT ? AS "float" | 1 | 1
+ SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT ? + ? | 2 | 2
+ SELECT ? AS "int" | 2 | 2
+ SELECT ? AS i UNION SELECT ? ORDER BY i | 1 | 2
+ WITH t(f) AS ( +| 1 | 2
+ VALUES (?), (?) +| |
+ ) +| |
+ SELECT f FROM t ORDER BY f | |
+ SELECT ? + ? + ? AS "add" | 3 | 3
+ SELECT ? +| 4 | 4
+ +| |
+ AS "text" | |
+(9 rows)
+
+--
+--
+-- CRUD: INSERT SELECT UPDATE DELETE on test table
+--
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
(1 row)
+-- utility "create table" must not show
+CREATE TABLE test (a int, b char(20));
INSERT INTO test VALUES(generate_series(1, 10), 'aaa');
-UPDATE test SET b = 'bbb' WHERE a > 5;
-SELECT query, calls, rows from pg_stat_statements ORDER BY rows;
- query | calls | rows
-----------------------------------------------------+-------+------
- SELECT pg_stat_statements_reset(); | 1 | 1
- UPDATE test SET b = ? WHERE a > ?; | 1 | 5
- INSERT INTO test VALUES(generate_series(?, ?), ?); | 1 | 10
+UPDATE test SET b = 'bbb' WHERE a > 7;
+DELETE FROM test WHERE a > 9;
+-- explicit transaction
+BEGIN;
+UPDATE test SET b = '111' WHERE a = 1 ;
+COMMIT;
+BEGIN \;
+UPDATE test SET b = '222' WHERE a = 2 \;
+COMMIT ;
+UPDATE test SET b = '333' WHERE a = 3 \;
+UPDATE test SET b = '444' WHERE a = 4 ;
+BEGIN \;
+UPDATE test SET b = '555' WHERE a = 5 \;
+UPDATE test SET b = '666' WHERE a = 6 \;
+COMMIT ;
+-- SELECT with constants
+SELECT * FROM test WHERE a > 5 ORDER BY a ;
+ a | b
+---+----------------------
+ 6 | 666
+ 7 | aaa
+ 8 | bbb
+ 9 | bbb
+(4 rows)
+
+SELECT *
+ FROM test
+ WHERE a > 9
+ ORDER BY a ;
+ a | b
+---+---
+(0 rows)
+
+-- SELECT without constants
+SELECT * FROM test ORDER BY a;
+ a | b
+---+----------------------
+ 1 | 111
+ 2 | 222
+ 3 | 333
+ 4 | 444
+ 5 | 555
+ 6 | 666
+ 7 | aaa
+ 8 | bbb
+ 9 | bbb
+(9 rows)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+ query | calls | rows
+---------------------------------------------------+-------+------
+ DELETE FROM test WHERE a > ? | 1 | 1
+ SELECT pg_stat_statements_reset() | 1 | 1
+ UPDATE test SET b = ? WHERE a > ? | 1 | 3
+ SELECT * FROM test WHERE a > ? ORDER BY a | 2 | 4
+ UPDATE test SET b = ? WHERE a = ? | 6 | 6
+ SELECT * FROM test ORDER BY a | 1 | 9
+ INSERT INTO test VALUES(generate_series(?, ?), ?) | 1 | 10
+(7 rows)
+
+--
+--
+-- pg_stat_statements.track = none
+--
+SET pg_stat_statements.track = 'none';
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT 1 AS "one";
+ one
+-----
+ 1
+(1 row)
+
+SELECT 1 + 1 AS "two";
+ two
+-----
+ 2
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+ query | calls | rows
+-------+-------+------
+(0 rows)
+
+--
+--
+-- pg_stat_statements.track = top
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SET pg_stat_statements.track = 'top';
+DO LANGUAGE plpgsql $$
+BEGIN
+ -- this is a SELECT
+ PERFORM 'hello world'::TEXT;
+END;
+$$;
+-- PL/pgSQL function
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+ r INTEGER;
+BEGIN
+ SELECT (i + 1 + 1.0)::INTEGER INTO r;
+ RETURN r;
+END; $$ LANGUAGE plpgsql;
+SELECT PLUS_TWO(3);
+ plus_two
+----------
+ 5
+(1 row)
+
+SELECT PLUS_TWO(7);
+ plus_two
+----------
+ 9
+(1 row)
+
+-- SQL function
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER $$ LANGUAGE SQL;
+SELECT PLUS_ONE(8);
+ plus_one
+----------
+ 9
+(1 row)
+
+SELECT PLUS_ONE(10);
+ plus_one
+----------
+ 11
+(1 row)
+
+DROP FUNCTION PLUS_ONE(INTEGER);
+DROP FUNCTION PLUS_TWO(INTEGER);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+ query | calls | rows
+--------------------+-------+------
+ SELECT ?::TEXT | 1 | 1
+ SELECT PLUS_ONE(?) | 2 | 2
+ SELECT PLUS_TWO(?) | 2 | 2
(3 rows)
-DROP TABLE test;
+--
+--
+-- pg_stat_statements.track = all
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SET pg_stat_statements.track = 'all';
+-- recreate PL/pgSQL function
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+ r INTEGER;
+BEGIN
+ SELECT (i + 1 + 1.0)::INTEGER INTO r;
+ RETURN r;
+END; $$ LANGUAGE plpgsql;
+SELECT PLUS_TWO(-1);
+ plus_two
+----------
+ 1
+(1 row)
+
+SELECT PLUS_TWO(2);
+ plus_two
+----------
+ 4
+(1 row)
+
+-- SQL function nesting
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER $$ LANGUAGE SQL;
+SELECT PLUS_ONE(3);
+ plus_one
+----------
+ 4
+(1 row)
+
+SELECT PLUS_ONE(1);
+ plus_one
+----------
+ 2
+(1 row)
+
+-- bug? PLUS_ONE expansion is missing
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+ query | calls | rows
+-----------------------------------+-------+------
+ SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT (i + ? + ?)::INTEGER | 2 | 2
+ SELECT PLUS_ONE(?) | 2 | 2
+ SELECT PLUS_TWO(?) | 2 | 2
+(4 rows)
+
+--
+--
+-- utility commands
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SET pg_stat_statements.track_utility = TRUE;
+SELECT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+CREATE INDEX test_b ON test(b);
+DROP TABLE test \;
+DROP TABLE IF EXISTS test \;
+DROP FUNCTION PLUS_ONE(INTEGER);
+NOTICE: table "test" does not exist, skipping
+DROP TABLE IF EXISTS test \;
+DROP TABLE IF EXISTS test \;
+DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
+NOTICE: table "test" does not exist, skipping
+NOTICE: table "test" does not exist, skipping
+NOTICE: function plus_one(pg_catalog.int4) does not exist, skipping
+DROP FUNCTION PLUS_TWO(INTEGER);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+ query | calls | rows
+-------------------------------------------+-------+------
+ CREATE INDEX test_b ON test(b) | 1 | 0
+ DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER) | 1 | 0
+ DROP FUNCTION PLUS_ONE(INTEGER) | 1 | 0
+ DROP FUNCTION PLUS_TWO(INTEGER) | 1 | 0
+ DROP TABLE IF EXISTS test | 3 | 0
+ DROP TABLE test | 1 | 0
+ SELECT ? | 1 | 1
+ SELECT pg_stat_statements_reset() | 1 | 1
+(8 rows)
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 7e2b263..2a32f23 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -1,15 +1,188 @@
CREATE EXTENSION pg_stat_statements;
-CREATE TABLE test (a int, b char(20));
+--
+--
+-- simple and compound statements
+--
+SET pg_stat_statements.track_utility = FALSE;
+SELECT pg_stat_statements_reset();
+
+SELECT 1 AS "int";
+
+
+SELECT 'hello'
+ -- multiline
+ AS "text";
+SELECT 'world' AS "text";
+
+-- transaction
+BEGIN;
+SELECT 1 AS "int";
+SELECT 'hello' AS "text";
+COMMIT;
+
+-- compound transaction
+BEGIN \;
+SELECT 2.0 AS "float" \;
+SELECT 'world' AS "text" \;
+COMMIT;
+
+-- compound with empty statements and spurious leading spacing
+\;\; SELECT 3 + 3 \;\;\; SELECT ' ' || ' !' \;\; SELECT 1 + 4 \;;
+
+-- non ;-terminated statements
+SELECT 1 + 1 + 1 AS "add" \gset
+SELECT :add + 1 + 1 AS "add" \;
+SELECT :add + 1 + 1 AS "add" \gset
+
+-- set operator
+SELECT 1 AS i UNION SELECT 2 ORDER BY i;
+
+-- cte
+WITH t(f) AS (
+ VALUES (1.0), (2.0)
+)
+ SELECT f FROM t ORDER BY f;
--- test the basic functionality of pg_stat_statements
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+
+--
+--
+-- CRUD: INSERT SELECT UPDATE DELETE on test table
+--
SELECT pg_stat_statements_reset();
+-- utility "create table" must not show
+CREATE TABLE test (a int, b char(20));
+
INSERT INTO test VALUES(generate_series(1, 10), 'aaa');
-UPDATE test SET b = 'bbb' WHERE a > 5;
+UPDATE test SET b = 'bbb' WHERE a > 7;
+DELETE FROM test WHERE a > 9;
+
+-- explicit transaction
+BEGIN;
+UPDATE test SET b = '111' WHERE a = 1 ;
+COMMIT;
+
+BEGIN \;
+UPDATE test SET b = '222' WHERE a = 2 \;
+COMMIT ;
+
+UPDATE test SET b = '333' WHERE a = 3 \;
+UPDATE test SET b = '444' WHERE a = 4 ;
+
+BEGIN \;
+UPDATE test SET b = '555' WHERE a = 5 \;
+UPDATE test SET b = '666' WHERE a = 6 \;
+COMMIT ;
+
+-- SELECT with constants
+SELECT * FROM test WHERE a > 5 ORDER BY a ;
+SELECT *
+ FROM test
+ WHERE a > 9
+ ORDER BY a ;
+
+-- SELECT without constants
+SELECT * FROM test ORDER BY a;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+
+--
+--
+-- pg_stat_statements.track = none
+--
+SET pg_stat_statements.track = 'none';
+SELECT pg_stat_statements_reset();
+SELECT 1 AS "one";
+SELECT 1 + 1 AS "two";
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+
+--
+--
+-- pg_stat_statements.track = top
+--
+SELECT pg_stat_statements_reset();
+SET pg_stat_statements.track = 'top';
+
+DO LANGUAGE plpgsql $$
+BEGIN
+ -- this is a SELECT
+ PERFORM 'hello world'::TEXT;
+END;
+$$;
+
+-- PL/pgSQL function
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+ r INTEGER;
+BEGIN
+ SELECT (i + 1 + 1.0)::INTEGER INTO r;
+ RETURN r;
+END; $$ LANGUAGE plpgsql;
+
+SELECT PLUS_TWO(3);
+SELECT PLUS_TWO(7);
+
+-- SQL function
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER $$ LANGUAGE SQL;
+
+SELECT PLUS_ONE(8);
+SELECT PLUS_ONE(10);
+
+DROP FUNCTION PLUS_ONE(INTEGER);
+DROP FUNCTION PLUS_TWO(INTEGER);
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+
+--
+--
+-- pg_stat_statements.track = all
+--
+SELECT pg_stat_statements_reset();
+SET pg_stat_statements.track = 'all';
+
+-- recreate PL/pgSQL function
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+ r INTEGER;
+BEGIN
+ SELECT (i + 1 + 1.0)::INTEGER INTO r;
+ RETURN r;
+END; $$ LANGUAGE plpgsql;
+
+SELECT PLUS_TWO(-1);
+SELECT PLUS_TWO(2);
+
+-- SQL function nesting
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER $$ LANGUAGE SQL;
+
+SELECT PLUS_ONE(3);
+SELECT PLUS_ONE(1);
+
+-- bug? PLUS_ONE expansion is missing
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
+
+--
+--
+-- utility commands
+--
+SELECT pg_stat_statements_reset();
+SET pg_stat_statements.track_utility = TRUE;
+
+SELECT 1;
+CREATE INDEX test_b ON test(b);
+DROP TABLE test \;
+DROP TABLE IF EXISTS test \;
+DROP FUNCTION PLUS_ONE(INTEGER);
+DROP TABLE IF EXISTS test \;
+DROP TABLE IF EXISTS test \;
+DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
+DROP FUNCTION PLUS_TWO(INTEGER);
-SELECT query, calls, rows from pg_stat_statements ORDER BY rows;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY rows, query;
-DROP TABLE test;
DROP EXTENSION pg_stat_statements;
--
2.9.2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
