hi. Anthonin
please check attached v9-0001, v9-0002, v9-003.
v9-0001-Better-error-reporting-from-extension-scripts-Was.patch
same as v4-0001-Improve-parser-s-reporting-of-statement-start-loc.patch in [1]
v9-0002-Add-tests-covering-pgss-nested-queries.patch same as
v8-0001-Add-tests-covering-pgss-nested-queries.patch in [2]
which is your work.
v9-0003-Track-location-in-nested-explain-statement.patch
is the main change I made based on your patch.
in [3] I mentioned adding "ParseLoc location" to ExplainStmt, then you
found some problems at [4] with multi statements,
now I found a way to resolve it.
I also add "ParseLoc location;" to typedef struct CopyStmt.
copy (select 1) to stdout;
I tested my change can tracking
beginning location and length of the nested query ("select 1")
I didn't touch other nested queries cases yet, so far only ExplainStmt
and CopyStmt1
IMHO, it's more neat than your patches.
Can you give it a try?
[1] https://www.postgresql.org/message-id/2245576.1728418678%40sss.pgh.pa.us
[2]
https://www.postgresql.org/message-id/CAO6_XqqMYOxJmHJWCKjP44T9AsW0MmKV87XUYCP3R9JZvYcVaw%40mail.gmail.com
[3]
https://www.postgresql.org/message-id/CACJufxEXSfk4o2jHDhf50fOY6WC%2BdFQke2gmpcz%2BEHVUsmEptg%40mail.gmail.com
[4]
https://www.postgresql.org/message-id/CAO6_Xqrjr_1Ss0bRe5VFm6OsUwX2nuN_VhbhYj0LFP3acoaaWw%40mail.gmail.com
--------------------------------------------
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
explain(verbose) SELECT 1, 2, 3;
explain(verbose) (SELECT 1, 2, 3);
SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query
COLLATE "C", toplevel;
will have 2 calls for "SELECT $1, $2, $3"
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
explain(verbose) (SELECT 1, 2, 3);
explain(verbose) SELECT 1, 2, 3;
SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query
COLLATE "C", toplevel;
will have 2 calls for " (SELECT $1, $2, $3)"
I think that's fine.
From 8718894eecfaf03dcce44f6dd3c90e0bd7294291 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 16 Oct 2024 20:56:28 +0800
Subject: [PATCH v9 1/3] Better error reporting from extension scripts (Was:
Extend ALTER OPERATOR)
---
.../pg_stat_statements/expected/select.out | 5 +-
contrib/pg_stat_statements/sql/select.sql | 3 +-
src/backend/nodes/queryjumblefuncs.c | 6 ++
src/backend/parser/gram.y | 66 +++++++------------
4 files changed, 34 insertions(+), 46 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out
index dd6c756f67..e0e2fa265c 100644
--- a/contrib/pg_stat_statements/expected/select.out
+++ b/contrib/pg_stat_statements/expected/select.out
@@ -19,8 +19,9 @@ SELECT 1 AS "int";
1
(1 row)
+/* this comment should not appear in the output */
SELECT 'hello'
- -- multiline
+ -- but this one will appear
AS "text";
text
-------
@@ -129,7 +130,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-------+------+------------------------------------------------------------------------------
1 | 1 | PREPARE pgss_test (int) AS SELECT $1, $2 LIMIT $3
4 | 4 | SELECT $1 +
- | | -- multiline +
+ | | -- but this one will appear +
| | AS "text"
2 | 2 | SELECT $1 + $2
3 | 3 | SELECT $1 + $2 + $3 AS "add"
diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql
index eb45cb81ad..e0be58d5e2 100644
--- a/contrib/pg_stat_statements/sql/select.sql
+++ b/contrib/pg_stat_statements/sql/select.sql
@@ -12,8 +12,9 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
--
SELECT 1 AS "int";
+/* this comment should not appear in the output */
SELECT 'hello'
- -- multiline
+ -- but this one will appear
AS "text";
SELECT 'world' AS "text";
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 5e43fd9229..e8bf95690b 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -90,6 +90,12 @@ CleanQuerytext(const char *query, int *location, int *len)
/*
* Discard leading and trailing whitespace, too. Use scanner_isspace()
* not libc's isspace(), because we want to match the lexer's behavior.
+ *
+ * Note: the parser now strips leading comments and whitespace from the
+ * reported stmt_location, so this first loop will only iterate in the
+ * unusual case that the location didn't propagate to here. But the
+ * statement length will extend to the end-of-string or terminating
+ * semicolon, so the second loop often does something useful.
*/
while (query_len > 0 && scanner_isspace(query[0]))
query++, query_location++, query_len--;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4aa8646af7..4bab2117d9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -67,39 +67,25 @@
/*
- * Location tracking support --- simpler than bison's default, since we only
- * want to track the start position not the end position of each nonterminal.
+ * Location tracking support. Unlike bison's default, we only want
+ * to track the start position not the end position of each nonterminal.
+ * Nonterminals that reduce to empty receive position "-1". Since a
+ * production's leading RHS nonterminal(s) may have reduced to empty,
+ * we have to scan to find the first one that's not -1.
*/
#define YYLLOC_DEFAULT(Current, Rhs, N) \
do { \
- if ((N) > 0) \
- (Current) = (Rhs)[1]; \
- else \
- (Current) = (-1); \
+ (Current) = (-1); \
+ for (int _i = 1; _i <= (N); _i++) \
+ { \
+ if ((Rhs)[_i] >= 0) \
+ { \
+ (Current) = (Rhs)[_i]; \
+ break; \
+ } \
+ } \
} while (0)
-/*
- * The above macro assigns -1 (unknown) as the parse location of any
- * nonterminal that was reduced from an empty rule, or whose leftmost
- * component was reduced from an empty rule. This is problematic
- * for nonterminals defined like
- * OptFooList: / * EMPTY * / { ... } | OptFooList Foo { ... } ;
- * because we'll set -1 as the location during the first reduction and then
- * copy it during each subsequent reduction, leaving us with -1 for the
- * location even when the list is not empty. To fix that, do this in the
- * action for the nonempty rule(s):
- * if (@$ < 0) @$ = @2;
- * (Although we have many nonterminals that follow this pattern, we only
- * bother with fixing @$ like this when the nonterminal's parse location
- * is actually referenced in some rule.)
- *
- * A cleaner answer would be to make YYLLOC_DEFAULT scan all the Rhs
- * locations until it's found one that's not -1. Then we'd get a correct
- * location for any nonterminal that isn't entirely empty. But this way
- * would add overhead to every rule reduction, and so far there's not been
- * a compelling reason to pay that overhead.
- */
-
/*
* Bison doesn't allocate anything that needs to live across parser calls,
* so we can easily have it use palloc instead of malloc. This prevents
@@ -930,7 +916,7 @@ parse_toplevel:
| MODE_PLPGSQL_EXPR PLpgSQL_Expr
{
pg_yyget_extra(yyscanner)->parsetree =
- list_make1(makeRawStmt($2, 0));
+ list_make1(makeRawStmt($2, @2));
}
| MODE_PLPGSQL_ASSIGN1 PLAssignStmt
{
@@ -938,7 +924,7 @@ parse_toplevel:
n->nnames = 1;
pg_yyget_extra(yyscanner)->parsetree =
- list_make1(makeRawStmt((Node *) n, 0));
+ list_make1(makeRawStmt((Node *) n, @2));
}
| MODE_PLPGSQL_ASSIGN2 PLAssignStmt
{
@@ -946,7 +932,7 @@ parse_toplevel:
n->nnames = 2;
pg_yyget_extra(yyscanner)->parsetree =
- list_make1(makeRawStmt((Node *) n, 0));
+ list_make1(makeRawStmt((Node *) n, @2));
}
| MODE_PLPGSQL_ASSIGN3 PLAssignStmt
{
@@ -954,19 +940,15 @@ parse_toplevel:
n->nnames = 3;
pg_yyget_extra(yyscanner)->parsetree =
- list_make1(makeRawStmt((Node *) n, 0));
+ list_make1(makeRawStmt((Node *) n, @2));
}
;
/*
* At top level, we wrap each stmt with a RawStmt node carrying start location
- * and length of the stmt's text. Notice that the start loc/len are driven
- * entirely from semicolon locations (@2). It would seem natural to use
- * @1 or @3 to get the true start location of a stmt, but that doesn't work
- * for statements that can start with empty nonterminals (opt_with_clause is
- * the main offender here); as noted in the comments for YYLLOC_DEFAULT,
- * we'd get -1 for the location in such cases.
- * We also take care to discard empty statements entirely.
+ * and length of the stmt's text.
+ * We also take care to discard empty statements entirely (which among other
+ * things dodges the problem of assigning them a location).
*/
stmtmulti: stmtmulti ';' toplevel_stmt
{
@@ -976,14 +958,14 @@ stmtmulti: stmtmulti ';' toplevel_stmt
updateRawStmtEnd(llast_node(RawStmt, $1), @2);
}
if ($3 != NULL)
- $$ = lappend($1, makeRawStmt($3, @2 + 1));
+ $$ = lappend($1, makeRawStmt($3, @3));
else
$$ = $1;
}
| toplevel_stmt
{
if ($1 != NULL)
- $$ = list_make1(makeRawStmt($1, 0));
+ $$ = list_make1(makeRawStmt($1, @1));
else
$$ = NIL;
}
@@ -1584,8 +1566,6 @@ CreateSchemaStmt:
OptSchemaEltList:
OptSchemaEltList schema_stmt
{
- if (@$ < 0) /* see comments for YYLLOC_DEFAULT */
- @$ = @2;
$$ = lappend($1, $2);
}
| /* EMPTY */
--
2.34.1
From dbe7d9c8e72a92d1199d5e645ba69501f4253c55 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 17 Oct 2024 11:28:23 +0800
Subject: [PATCH v9 3/3] Track location in nested explain statement
---
.../expected/level_tracking.out | 92 +++++++++----------
src/backend/commands/copy.c | 2 +-
src/backend/commands/copyto.c | 7 +-
src/backend/parser/analyze.c | 16 ++++
src/backend/parser/gram.y | 6 ++
src/include/commands/copy.h | 3 +-
src/include/nodes/parsenodes.h | 3 +
src/include/parser/parse_node.h | 2 +
.../test_copy_callbacks/test_copy_callbacks.c | 2 +-
9 files changed, 82 insertions(+), 51 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index 8c5c1b5b55..36584ddefa 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -221,19 +221,19 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | explain (costs off) TABLE stats_track_tab
t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2
t | 1 | explain (costs off) VALUES($1)
- f | 1 | explain (costs off) (SELECT $1, $2);
- f | 1 | explain (costs off) (TABLE test_table);
- f | 1 | explain (costs off) (VALUES($1, $2));
- f | 1 | explain (costs off) DELETE FROM stats_track_tab;
- f | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1));
- f | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id+
+ f | 1 | (SELECT $1, $2)
+ f | 1 | (TABLE test_table)
+ f | 1 | (VALUES($1, $2))
+ f | 1 | DELETE FROM stats_track_tab
+ f | 1 | INSERT INTO stats_track_tab VALUES (($1))
+ f | 1 | MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
| | WHEN MATCHED THEN UPDATE SET x = id +
- | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
- f | 1 | explain (costs off) SELECT $1 UNION SELECT $2;
- f | 1 | explain (costs off) SELECT $1;
- f | 1 | explain (costs off) TABLE stats_track_tab;
- f | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2;
- f | 1 | explain (costs off) VALUES($1);
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ f | 1 | SELECT $1
+ f | 1 | SELECT $1 UNION SELECT $2
+ f | 1 | TABLE stats_track_tab
+ f | 1 | UPDATE stats_track_tab SET x=$1 WHERE x=$2
+ f | 1 | VALUES($1)
(23 rows)
-- Explain - top-level tracking.
@@ -480,8 +480,8 @@ explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY toplevel desc, query COLLATE "C";
- toplevel | calls | query
-----------+-------+----------------------------------------------------------------------------------------------------------------------------------
+ toplevel | calls | query
+----------+-------+------------------------------------------------------------------------------------------------------------
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
t | 1 | explain (costs off) (SELECT $1, $2, $3)
t | 1 | explain (costs off) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
@@ -492,8 +492,8 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | explain (costs off) DELETE FROM stats_track_tab WHERE x=$1
t | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES ($1), ($2)
t | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1))
- t | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
+ t | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id+
+ | | WHEN MATCHED THEN UPDATE SET x = id +
| | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
t | 1 | explain (costs off) SELECT $1
t | 1 | explain (costs off) SELECT $1, $2
@@ -503,28 +503,26 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1
t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2
t | 1 | explain (costs off) VALUES($1)
- f | 1 | explain (costs off) (SELECT $1, $2, $3); explain (costs off) (SELECT 1, 2, 3, 4);
- f | 1 | explain (costs off) (SELECT 1, 2, 3); explain (costs off) (SELECT $1, $2, $3, $4);
- f | 1 | explain (costs off) DELETE FROM stats_track_tab; explain (costs off) DELETE FROM stats_track_tab WHERE x=$1;
- f | 1 | explain (costs off) DELETE FROM stats_track_tab; explain (costs off) DELETE FROM stats_track_tab WHERE x=1;
- f | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1)); explain (costs off) INSERT INTO stats_track_tab VALUES (1), (2);
- f | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES ((1)); explain (costs off) INSERT INTO stats_track_tab VALUES ($1), ($2);
- f | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
- | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); explain (costs off) SELECT 1, 2, 3, 4, 5;
- f | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
- | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); explain (costs off) SELECT $1, $2, $3, $4, $5;
- f | 1 | explain (costs off) SELECT $1, $2 UNION SELECT $3, $4; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5;
- f | 1 | explain (costs off) SELECT $1; explain (costs off) SELECT 1, 2;
- f | 1 | explain (costs off) SELECT 1, 2 UNION SELECT 3, 4; explain (costs off) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6;
- f | 1 | explain (costs off) SELECT 1; explain (costs off) SELECT $1, $2;
- f | 1 | explain (costs off) TABLE stats_track_tab; explain (costs off) (TABLE test_table);
- f | 1 | explain (costs off) TABLE stats_track_tab; explain (costs off) (TABLE test_table);
- f | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2; explain (costs off) UPDATE stats_track_tab SET x=1;
- f | 1 | explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1; explain (costs off) UPDATE stats_track_tab SET x=$1;
- f | 1 | explain (costs off) VALUES($1); explain (costs off) (VALUES(1, 2));
- f | 1 | explain (costs off) VALUES(1); explain (costs off) (VALUES($1, $2));
+ f | 1 | (SELECT $1, $2, $3)
+ f | 1 | (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
+ f | 1 | (SELECT $1, $2, $3, $4)
+ f | 1 | (TABLE test_table)
+ f | 1 | (VALUES($1, $2))
+ f | 1 | DELETE FROM stats_track_tab
+ f | 1 | DELETE FROM stats_track_tab WHERE x=$1
+ f | 1 | INSERT INTO stats_track_tab VALUES ($1), ($2)
+ f | 1 | INSERT INTO stats_track_tab VALUES (($1))
+ f | 1 | MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ f | 1 | SELECT $1
+ f | 1 | SELECT $1, $2
+ f | 1 | SELECT $1, $2 UNION SELECT $3, $4
+ f | 1 | SELECT $1, $2, $3, $4, $5
+ f | 1 | TABLE stats_track_tab
+ f | 1 | UPDATE stats_track_tab SET x=$1
+ f | 1 | UPDATE stats_track_tab SET x=$1 WHERE x=$2
+ f | 1 | VALUES($1)
(37 rows)
-- Explain - top-level tracking with multi statement.
@@ -762,15 +760,15 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2
t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 UNION SELECT $3
t | 1 | explain (costs off) WITH a AS (select $1) UPDATE stats_track_tab SET x=$2 WHERE x=$3
- f | 1 | explain (costs off) (WITH a AS (select $1) (SELECT $2, $3));
- f | 1 | explain (costs off) WITH a AS (select $1) DELETE FROM stats_track_tab;
- f | 1 | explain (costs off) WITH a AS (select $1) INSERT INTO stats_track_tab VALUES (($2));
- f | 1 | explain (costs off) WITH a AS (select $1) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($2, $3) id) ON x = id+
+ f | 1 | (WITH a AS (select $1) (SELECT $2, $3))
+ f | 1 | WITH a AS (select $1) DELETE FROM stats_track_tab
+ f | 1 | WITH a AS (select $1) INSERT INTO stats_track_tab VALUES (($2))
+ f | 1 | WITH a AS (select $1) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($2, $3) id) ON x = id +
| | WHEN MATCHED THEN UPDATE SET x = id +
- | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
- f | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 UNION SELECT $3;
- f | 1 | explain (costs off) WITH a AS (select $1) SELECT $2;
- f | 1 | explain (costs off) WITH a AS (select $1) UPDATE stats_track_tab SET x=$2 WHERE x=$3;
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ f | 1 | WITH a AS (select $1) SELECT $2
+ f | 1 | WITH a AS (select $1) SELECT $2 UNION SELECT $3
+ f | 1 | WITH a AS (select $1) UPDATE stats_track_tab SET x=$2 WHERE x=$3
(15 rows)
-- Explain with CTE - top-level tracking
@@ -882,7 +880,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab
t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT $1
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT $1;
+ f | 1 | SELECT $1
(4 rows)
-- Explain analyze, top tracking.
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 0b093dbb2a..6794a2f16d 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -312,7 +312,7 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
cstate = BeginCopyTo(pstate, rel, query, relid,
stmt->filename, stmt->is_program,
- NULL, stmt->attlist, stmt->options);
+ NULL, stmt->attlist, stmt->options, stmt->location, stmt->stmt_len);
*processed = DoCopyTo(cstate); /* copy from database to file */
EndCopyTo(cstate);
}
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 463083e645..a38f688ffa 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -355,7 +355,9 @@ BeginCopyTo(ParseState *pstate,
bool is_program,
copy_data_dest_cb data_dest_cb,
List *attnamelist,
- List *options)
+ List *options,
+ int location,
+ int stmt_len)
{
CopyToState cstate;
bool pipe = (filename == NULL && data_dest_cb == NULL);
@@ -485,6 +487,9 @@ BeginCopyTo(ParseState *pstate,
query = linitial_node(Query, rewritten);
+ query->stmt_location = location;
+ query->stmt_len = stmt_len;
+
/* The grammar allows SELECT INTO, but we don't support that */
if (query->utilityStmt != NULL &&
IsA(query->utilityStmt, CreateTableAsStmt))
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 2d3d8fcf76..23d61c9837 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -113,6 +113,8 @@ parse_analyze_fixedparams(RawStmt *parseTree, const char *sourceText,
Assert(sourceText != NULL); /* required as of 8.4 */
pstate->p_sourcetext = sourceText;
+ pstate->p_stmt_len = parseTree->stmt_len;
+ pstate->p_stmt_location = parseTree->stmt_location;
if (numParams > 0)
setup_parse_fixed_parameters(pstate, paramTypes, numParams);
@@ -153,6 +155,8 @@ parse_analyze_varparams(RawStmt *parseTree, const char *sourceText,
Assert(sourceText != NULL); /* required as of 8.4 */
pstate->p_sourcetext = sourceText;
+ pstate->p_stmt_len = parseTree->stmt_len;
+ pstate->p_stmt_location = parseTree->stmt_location;
setup_parse_variable_parameters(pstate, paramTypes, numParams);
@@ -195,6 +199,8 @@ parse_analyze_withcb(RawStmt *parseTree, const char *sourceText,
Assert(sourceText != NULL); /* required as of 8.4 */
pstate->p_sourcetext = sourceText;
+ pstate->p_stmt_len = parseTree->stmt_len;
+ pstate->p_stmt_location = parseTree->stmt_location;
pstate->p_queryEnv = queryEnv;
(*parserSetup) (pstate, parserSetupArg);
@@ -2968,6 +2974,7 @@ static Query *
transformExplainStmt(ParseState *pstate, ExplainStmt *stmt)
{
Query *result;
+ Query *explained_query;
bool generic_plan = false;
Oid *paramTypes = NULL;
int numParams = 0;
@@ -2996,6 +3003,15 @@ transformExplainStmt(ParseState *pstate, ExplainStmt *stmt)
/* transform contained query, allowing SELECT INTO */
stmt->query = (Node *) transformOptionalSelectInto(pstate, stmt->query);
+ explained_query = (Query *) stmt->query;
+ explained_query->stmt_location = stmt->location;
+
+ /*
+ * the being explained query stmt_len is top level query stmt_len minus the
+ * being EXPLAIN nested query's beginning position.
+ */
+ explained_query->stmt_len = pstate->p_stmt_location + pstate->p_stmt_len - stmt->location;
+
/* make sure all is well with parameter types */
if (generic_plan)
check_variable_parameters(pstate, (Query *) stmt->query);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4bab2117d9..97bc52de3b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3390,6 +3390,8 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list
n->is_program = $6;
n->filename = $7;
n->options = $9;
+ n->location = @3;
+ n->stmt_len = @4 - @3;
if (n->is_program && n->filename == NULL)
ereport(ERROR,
@@ -11958,6 +11960,7 @@ ExplainStmt:
ExplainStmt *n = makeNode(ExplainStmt);
n->query = $2;
+ n->location = @2;
n->options = NIL;
$$ = (Node *) n;
}
@@ -11966,6 +11969,7 @@ ExplainStmt:
ExplainStmt *n = makeNode(ExplainStmt);
n->query = $4;
+ n->location = @4;
n->options = list_make1(makeDefElem("analyze", NULL, @2));
if ($3)
n->options = lappend(n->options,
@@ -11977,6 +11981,7 @@ ExplainStmt:
ExplainStmt *n = makeNode(ExplainStmt);
n->query = $3;
+ n->location = @3;
n->options = list_make1(makeDefElem("verbose", NULL, @2));
$$ = (Node *) n;
}
@@ -11985,6 +11990,7 @@ ExplainStmt:
ExplainStmt *n = makeNode(ExplainStmt);
n->query = $5;
+ n->location = @5;
n->options = $3;
$$ = (Node *) n;
}
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 4002a7f538..0440ff2345 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -121,7 +121,8 @@ extern DestReceiver *CreateCopyDestReceiver(void);
*/
extern CopyToState BeginCopyTo(ParseState *pstate, Relation rel, RawStmt *raw_query,
Oid queryRelId, const char *filename, bool is_program,
- copy_data_dest_cb data_dest_cb, List *attnamelist, List *options);
+ copy_data_dest_cb data_dest_cb, List *attnamelist, List *options,
+ int location, int stmt_len);
extern void EndCopyTo(CopyToState cstate);
extern uint64 DoCopyTo(CopyToState cstate);
extern List *CopyGetAttnums(TupleDesc tupDesc, Relation rel,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index c92cef3d16..bb22ecc6b3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2596,6 +2596,8 @@ typedef struct CopyStmt
char *filename; /* filename, or NULL for STDIN/STDOUT */
List *options; /* List of DefElem nodes */
Node *whereClause; /* WHERE condition (or NULL) */
+ ParseLoc location; /* the nest query location. COPY TO only */
+ int stmt_len; /* the nest query length. COPY TO only */
} CopyStmt;
/* ----------------------
@@ -3887,6 +3889,7 @@ typedef struct ExplainStmt
NodeTag type;
Node *query; /* the query (see comments above) */
List *options; /* list of DefElem nodes */
+ ParseLoc location; /* location of the statement being explained */
} ExplainStmt;
/* ----------------------
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 543df56814..ba572b3aea 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -193,6 +193,8 @@ struct ParseState
{
ParseState *parentParseState; /* stack link */
const char *p_sourcetext; /* source text, or NULL if not available */
+ ParseLoc p_stmt_location; /* start location, or -1 if unknown */
+ ParseLoc p_stmt_len; /* length in bytes; 0 means "rest of string" */
List *p_rtable; /* range table so far */
List *p_rteperminfos; /* list of RTEPermissionInfo nodes for each
* RTE_RELATION entry in rtable */
diff --git a/src/test/modules/test_copy_callbacks/test_copy_callbacks.c b/src/test/modules/test_copy_callbacks/test_copy_callbacks.c
index 0bbd2aa6bb..453e53e466 100644
--- a/src/test/modules/test_copy_callbacks/test_copy_callbacks.c
+++ b/src/test/modules/test_copy_callbacks/test_copy_callbacks.c
@@ -38,7 +38,7 @@ test_copy_to_callback(PG_FUNCTION_ARGS)
int64 processed;
cstate = BeginCopyTo(NULL, rel, NULL, RelationGetRelid(rel), NULL, false,
- to_cb, NIL, NIL);
+ to_cb, NIL, NIL, 0, 0);
processed = DoCopyTo(cstate);
EndCopyTo(cstate);
--
2.34.1
From a5ed8307b25c932f9ef14ffc16dd893e00a2b58b Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Mon, 7 Oct 2024 10:45:49 +0200
Subject: [PATCH v9 2/3] Add tests covering pgss nested queries
What pgss reports for nested statements can be confusing. Some
statements like CreateTableAs, DeclareCursor and CreateMaterializedView
don't jumble the nested query and thus won't report it in pgss. Explain
explicitely Jumble the nested query and will appear in pgss. However,
the reported query string will be the same as the top level statement.
Multi statements queries are also not handled correctly. The statement
length used in the top RawStmt is not propagated in the nested
statement. Thus, it fallbacks to the "use the whole query string"
default which is reported to pgss.
This patch creates tests to cover the current behaviour.
---
.../expected/level_tracking.out | 1100 +++++++++++++++++
.../pg_stat_statements/sql/level_tracking.sql | 229 +++-
2 files changed, 1328 insertions(+), 1 deletion(-)
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index bb65e98ce0..8c5c1b5b55 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -112,6 +112,1106 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(2 rows)
+-- Explain - all-level tracking.
+CREATE TABLE test_table (x int);
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+explain (costs off) SELECT 1;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) (SELECT 1, 2);
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) TABLE stats_track_tab;
+ QUERY PLAN
+-----------------------------
+ Seq Scan on stats_track_tab
+(1 row)
+
+explain (costs off) (TABLE test_table);
+ QUERY PLAN
+------------------------
+ Seq Scan on test_table
+(1 row)
+
+explain (costs off) VALUES(1);
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) (VALUES(1, 2));
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1;
+ QUERY PLAN
+-----------------------------------
+ Update on stats_track_tab
+ -> Seq Scan on stats_track_tab
+ Filter: (x = 1)
+(3 rows)
+
+explain (costs off) DELETE FROM stats_track_tab;
+ QUERY PLAN
+-----------------------------------
+ Delete on stats_track_tab
+ -> Seq Scan on stats_track_tab
+(2 rows)
+
+explain (costs off) INSERT INTO stats_track_tab VALUES ((1));
+ QUERY PLAN
+---------------------------
+ Insert on stats_track_tab
+ -> Result
+(2 rows)
+
+explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+ QUERY PLAN
+-------------------------------------------------------
+ Merge on stats_track_tab
+ -> Hash Right Join
+ Hash Cond: (stats_track_tab.x = id.id)
+ -> Seq Scan on stats_track_tab
+ -> Hash
+ -> Function Scan on generate_series id
+(6 rows)
+
+explain (costs off) SELECT 1 UNION SELECT 2;
+ QUERY PLAN
+--------------------------
+ Unique
+ -> Sort
+ Sort Key: (1)
+ -> Append
+ -> Result
+ -> Result
+(6 rows)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+------------------------------------------------------------------------------------------------------------
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 1 | explain (costs off) (SELECT $1, $2)
+ t | 1 | explain (costs off) (TABLE test_table)
+ t | 1 | explain (costs off) (VALUES($1, $2))
+ t | 1 | explain (costs off) DELETE FROM stats_track_tab
+ t | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1))
+ t | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id+
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ t | 1 | explain (costs off) SELECT $1
+ t | 1 | explain (costs off) SELECT $1 UNION SELECT $2
+ t | 1 | explain (costs off) TABLE stats_track_tab
+ t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2
+ t | 1 | explain (costs off) VALUES($1)
+ f | 1 | explain (costs off) (SELECT $1, $2);
+ f | 1 | explain (costs off) (TABLE test_table);
+ f | 1 | explain (costs off) (VALUES($1, $2));
+ f | 1 | explain (costs off) DELETE FROM stats_track_tab;
+ f | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1));
+ f | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id+
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+ f | 1 | explain (costs off) SELECT $1 UNION SELECT $2;
+ f | 1 | explain (costs off) SELECT $1;
+ f | 1 | explain (costs off) TABLE stats_track_tab;
+ f | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2;
+ f | 1 | explain (costs off) VALUES($1);
+(23 rows)
+
+-- Explain - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+explain (costs off) SELECT 1;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) (SELECT 1, 2);
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) TABLE stats_track_tab;
+ QUERY PLAN
+-----------------------------
+ Seq Scan on stats_track_tab
+(1 row)
+
+explain (costs off) (TABLE test_table);
+ QUERY PLAN
+------------------------
+ Seq Scan on test_table
+(1 row)
+
+explain (costs off) VALUES(1);
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) (VALUES(1, 2));
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1;
+ QUERY PLAN
+-----------------------------------
+ Update on stats_track_tab
+ -> Seq Scan on stats_track_tab
+ Filter: (x = 1)
+(3 rows)
+
+explain (costs off) DELETE FROM stats_track_tab;
+ QUERY PLAN
+-----------------------------------
+ Delete on stats_track_tab
+ -> Seq Scan on stats_track_tab
+(2 rows)
+
+explain (costs off) INSERT INTO stats_track_tab VALUES ((1));
+ QUERY PLAN
+---------------------------
+ Insert on stats_track_tab
+ -> Result
+(2 rows)
+
+explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+ QUERY PLAN
+-------------------------------------------------------
+ Merge on stats_track_tab
+ -> Hash Right Join
+ Hash Cond: (stats_track_tab.x = id.id)
+ -> Seq Scan on stats_track_tab
+ -> Hash
+ -> Function Scan on generate_series id
+(6 rows)
+
+explain (costs off) SELECT 1 UNION SELECT 2;
+ QUERY PLAN
+--------------------------
+ Unique
+ -> Sort
+ Sort Key: (1)
+ -> Append
+ -> Result
+ -> Result
+(6 rows)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+------------------------------------------------------------------------------------------------------------
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 1 | explain (costs off) (SELECT $1, $2)
+ t | 1 | explain (costs off) (TABLE test_table)
+ t | 1 | explain (costs off) (VALUES($1, $2))
+ t | 1 | explain (costs off) DELETE FROM stats_track_tab
+ t | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1))
+ t | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id+
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ t | 1 | explain (costs off) SELECT $1
+ t | 1 | explain (costs off) SELECT $1 UNION SELECT $2
+ t | 1 | explain (costs off) TABLE stats_track_tab
+ t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2
+ t | 1 | explain (costs off) VALUES($1)
+(12 rows)
+
+-- Explain - all-level tracking with multi statement.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+explain (costs off) SELECT 1\; explain (costs off) SELECT 1, 2;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) (SELECT 1, 2, 3)\; explain (costs off) (SELECT 1, 2, 3, 4);
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) TABLE stats_track_tab\; explain (costs off) (TABLE test_table);
+ QUERY PLAN
+-----------------------------
+ Seq Scan on stats_track_tab
+(1 row)
+
+ QUERY PLAN
+------------------------
+ Seq Scan on test_table
+(1 row)
+
+explain (costs off) VALUES(1)\; explain (costs off) (VALUES(1, 2));
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1\; explain (costs off) UPDATE stats_track_tab SET x=1;
+ QUERY PLAN
+-----------------------------------
+ Update on stats_track_tab
+ -> Seq Scan on stats_track_tab
+ Filter: (x = 1)
+(3 rows)
+
+ QUERY PLAN
+-----------------------------------
+ Update on stats_track_tab
+ -> Seq Scan on stats_track_tab
+(2 rows)
+
+explain (costs off) DELETE FROM stats_track_tab\; explain (costs off) DELETE FROM stats_track_tab WHERE x=1;
+ QUERY PLAN
+-----------------------------------
+ Delete on stats_track_tab
+ -> Seq Scan on stats_track_tab
+(2 rows)
+
+ QUERY PLAN
+-----------------------------------
+ Delete on stats_track_tab
+ -> Seq Scan on stats_track_tab
+ Filter: (x = 1)
+(3 rows)
+
+explain (costs off) INSERT INTO stats_track_tab VALUES ((1))\; explain (costs off) INSERT INTO stats_track_tab VALUES (1), (2);
+ QUERY PLAN
+---------------------------
+ Insert on stats_track_tab
+ -> Result
+(2 rows)
+
+ QUERY PLAN
+---------------------------------
+ Insert on stats_track_tab
+ -> Values Scan on "*VALUES*"
+(2 rows)
+
+explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id)\; explain (costs off) SELECT 1, 2, 3, 4, 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Merge on stats_track_tab
+ -> Hash Right Join
+ Hash Cond: (stats_track_tab.x = id.id)
+ -> Seq Scan on stats_track_tab
+ -> Hash
+ -> Function Scan on generate_series id
+(6 rows)
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5;
+ QUERY PLAN
+----------------------------
+ Unique
+ -> Sort
+ Sort Key: (1), (2)
+ -> Append
+ -> Result
+ -> Result
+(6 rows)
+
+ QUERY PLAN
+---------------------------------
+ Unique
+ -> Sort
+ Sort Key: (1), (2), (3)
+ -> Append
+ -> Result
+ -> Result
+(6 rows)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------------------------------------------------------------------------------
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 1 | explain (costs off) (SELECT $1, $2, $3)
+ t | 1 | explain (costs off) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
+ t | 1 | explain (costs off) (SELECT $1, $2, $3, $4)
+ t | 1 | explain (costs off) (TABLE test_table)
+ t | 1 | explain (costs off) (VALUES($1, $2))
+ t | 1 | explain (costs off) DELETE FROM stats_track_tab
+ t | 1 | explain (costs off) DELETE FROM stats_track_tab WHERE x=$1
+ t | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES ($1), ($2)
+ t | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1))
+ t | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ t | 1 | explain (costs off) SELECT $1
+ t | 1 | explain (costs off) SELECT $1, $2
+ t | 1 | explain (costs off) SELECT $1, $2 UNION SELECT $3, $4
+ t | 1 | explain (costs off) SELECT $1, $2, $3, $4, $5
+ t | 1 | explain (costs off) TABLE stats_track_tab
+ t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1
+ t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2
+ t | 1 | explain (costs off) VALUES($1)
+ f | 1 | explain (costs off) (SELECT $1, $2, $3); explain (costs off) (SELECT 1, 2, 3, 4);
+ f | 1 | explain (costs off) (SELECT 1, 2, 3); explain (costs off) (SELECT $1, $2, $3, $4);
+ f | 1 | explain (costs off) DELETE FROM stats_track_tab; explain (costs off) DELETE FROM stats_track_tab WHERE x=$1;
+ f | 1 | explain (costs off) DELETE FROM stats_track_tab; explain (costs off) DELETE FROM stats_track_tab WHERE x=1;
+ f | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1)); explain (costs off) INSERT INTO stats_track_tab VALUES (1), (2);
+ f | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES ((1)); explain (costs off) INSERT INTO stats_track_tab VALUES ($1), ($2);
+ f | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); explain (costs off) SELECT 1, 2, 3, 4, 5;
+ f | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); explain (costs off) SELECT $1, $2, $3, $4, $5;
+ f | 1 | explain (costs off) SELECT $1, $2 UNION SELECT $3, $4; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5;
+ f | 1 | explain (costs off) SELECT $1; explain (costs off) SELECT 1, 2;
+ f | 1 | explain (costs off) SELECT 1, 2 UNION SELECT 3, 4; explain (costs off) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6;
+ f | 1 | explain (costs off) SELECT 1; explain (costs off) SELECT $1, $2;
+ f | 1 | explain (costs off) TABLE stats_track_tab; explain (costs off) (TABLE test_table);
+ f | 1 | explain (costs off) TABLE stats_track_tab; explain (costs off) (TABLE test_table);
+ f | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2; explain (costs off) UPDATE stats_track_tab SET x=1;
+ f | 1 | explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1; explain (costs off) UPDATE stats_track_tab SET x=$1;
+ f | 1 | explain (costs off) VALUES($1); explain (costs off) (VALUES(1, 2));
+ f | 1 | explain (costs off) VALUES(1); explain (costs off) (VALUES($1, $2));
+(37 rows)
+
+-- Explain - top-level tracking with multi statement.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+explain (costs off) SELECT 1\; explain (costs off) SELECT 1, 2;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) (SELECT 1, 2, 3)\; explain (costs off) (SELECT 1, 2, 3, 4);
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) TABLE stats_track_tab\; explain (costs off) (TABLE test_table);
+ QUERY PLAN
+-----------------------------
+ Seq Scan on stats_track_tab
+(1 row)
+
+ QUERY PLAN
+------------------------
+ Seq Scan on test_table
+(1 row)
+
+explain (costs off) VALUES(1)\; explain (costs off) (VALUES(1, 2));
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1\; explain (costs off) UPDATE stats_track_tab SET x=1;
+ QUERY PLAN
+-----------------------------------
+ Update on stats_track_tab
+ -> Seq Scan on stats_track_tab
+ Filter: (x = 1)
+(3 rows)
+
+ QUERY PLAN
+-----------------------------------
+ Update on stats_track_tab
+ -> Seq Scan on stats_track_tab
+(2 rows)
+
+explain (costs off) DELETE FROM stats_track_tab\; explain (costs off) DELETE FROM stats_track_tab WHERE x=1;
+ QUERY PLAN
+-----------------------------------
+ Delete on stats_track_tab
+ -> Seq Scan on stats_track_tab
+(2 rows)
+
+ QUERY PLAN
+-----------------------------------
+ Delete on stats_track_tab
+ -> Seq Scan on stats_track_tab
+ Filter: (x = 1)
+(3 rows)
+
+explain (costs off) INSERT INTO stats_track_tab VALUES ((1))\; explain (costs off) INSERT INTO stats_track_tab VALUES ((1), (2));
+ QUERY PLAN
+---------------------------
+ Insert on stats_track_tab
+ -> Result
+(2 rows)
+
+ERROR: INSERT has more expressions than target columns
+LINE 1: ...n (costs off) INSERT INTO stats_track_tab VALUES ((1), (2));
+ ^
+explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id)\; explain (costs off) SELECT 1, 2, 3, 4, 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Merge on stats_track_tab
+ -> Hash Right Join
+ Hash Cond: (stats_track_tab.x = id.id)
+ -> Seq Scan on stats_track_tab
+ -> Hash
+ -> Function Scan on generate_series id
+(6 rows)
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5;
+ QUERY PLAN
+----------------------------
+ Unique
+ -> Sort
+ Sort Key: (1), (2)
+ -> Append
+ -> Result
+ -> Result
+(6 rows)
+
+ QUERY PLAN
+---------------------------------
+ Unique
+ -> Sort
+ Sort Key: (1), (2), (3)
+ -> Append
+ -> Result
+ -> Result
+(6 rows)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+------------------------------------------------------------------------------------------------------------
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 1 | explain (costs off) (SELECT $1, $2, $3)
+ t | 1 | explain (costs off) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
+ t | 1 | explain (costs off) (SELECT $1, $2, $3, $4)
+ t | 1 | explain (costs off) (TABLE test_table)
+ t | 1 | explain (costs off) (VALUES($1, $2))
+ t | 1 | explain (costs off) DELETE FROM stats_track_tab
+ t | 1 | explain (costs off) DELETE FROM stats_track_tab WHERE x=$1
+ t | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1))
+ t | 1 | explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id+
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ t | 1 | explain (costs off) SELECT $1
+ t | 1 | explain (costs off) SELECT $1, $2
+ t | 1 | explain (costs off) SELECT $1, $2 UNION SELECT $3, $4
+ t | 1 | explain (costs off) SELECT $1, $2, $3, $4, $5
+ t | 1 | explain (costs off) TABLE stats_track_tab
+ t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1
+ t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2
+ t | 1 | explain (costs off) VALUES($1)
+(18 rows)
+
+-- Explain with CTE - all-level tracking
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+explain (costs off) WITH a AS (select 4) SELECT 1;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) (WITH a AS (select 4) (SELECT 1, 2));
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) WITH a AS (select 4) UPDATE stats_track_tab SET x=1 WHERE x=1;
+ QUERY PLAN
+-----------------------------------
+ Update on stats_track_tab
+ -> Seq Scan on stats_track_tab
+ Filter: (x = 1)
+(3 rows)
+
+explain (costs off) WITH a AS (select 4) DELETE FROM stats_track_tab;
+ QUERY PLAN
+-----------------------------------
+ Delete on stats_track_tab
+ -> Seq Scan on stats_track_tab
+(2 rows)
+
+explain (costs off) WITH a AS (select 4) INSERT INTO stats_track_tab VALUES ((1));
+ QUERY PLAN
+---------------------------
+ Insert on stats_track_tab
+ -> Result
+(2 rows)
+
+explain (costs off) WITH a AS (select 4) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+ QUERY PLAN
+-------------------------------------------------------
+ Merge on stats_track_tab
+ -> Hash Right Join
+ Hash Cond: (stats_track_tab.x = id.id)
+ -> Seq Scan on stats_track_tab
+ -> Hash
+ -> Function Scan on generate_series id
+(6 rows)
+
+explain (costs off) WITH a AS (select 4) SELECT 1 UNION SELECT 2;
+ QUERY PLAN
+--------------------------
+ Unique
+ -> Sort
+ Sort Key: (1)
+ -> Append
+ -> Result
+ -> Result
+(6 rows)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------------------------------------------------------------------------------
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 1 | explain (costs off) (WITH a AS (select $1) (SELECT $2, $3))
+ t | 1 | explain (costs off) WITH a AS (select $1) DELETE FROM stats_track_tab
+ t | 1 | explain (costs off) WITH a AS (select $1) INSERT INTO stats_track_tab VALUES (($2))
+ t | 1 | explain (costs off) WITH a AS (select $1) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($2, $3) id) ON x = id+
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2
+ t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 UNION SELECT $3
+ t | 1 | explain (costs off) WITH a AS (select $1) UPDATE stats_track_tab SET x=$2 WHERE x=$3
+ f | 1 | explain (costs off) (WITH a AS (select $1) (SELECT $2, $3));
+ f | 1 | explain (costs off) WITH a AS (select $1) DELETE FROM stats_track_tab;
+ f | 1 | explain (costs off) WITH a AS (select $1) INSERT INTO stats_track_tab VALUES (($2));
+ f | 1 | explain (costs off) WITH a AS (select $1) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($2, $3) id) ON x = id+
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+ f | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 UNION SELECT $3;
+ f | 1 | explain (costs off) WITH a AS (select $1) SELECT $2;
+ f | 1 | explain (costs off) WITH a AS (select $1) UPDATE stats_track_tab SET x=$2 WHERE x=$3;
+(15 rows)
+
+-- Explain with CTE - top-level tracking
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+explain (costs off) WITH a AS (select 4) SELECT 1;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) (WITH a AS (select 4) (SELECT 1, 2));
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+explain (costs off) WITH a AS (select 4) UPDATE stats_track_tab SET x=1 WHERE x=1;
+ QUERY PLAN
+-----------------------------------
+ Update on stats_track_tab
+ -> Seq Scan on stats_track_tab
+ Filter: (x = 1)
+(3 rows)
+
+explain (costs off) WITH a AS (select 4) DELETE FROM stats_track_tab;
+ QUERY PLAN
+-----------------------------------
+ Delete on stats_track_tab
+ -> Seq Scan on stats_track_tab
+(2 rows)
+
+explain (costs off) WITH a AS (select 4) INSERT INTO stats_track_tab VALUES ((1));
+ QUERY PLAN
+---------------------------
+ Insert on stats_track_tab
+ -> Result
+(2 rows)
+
+explain (costs off) WITH a AS (select 4) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+ QUERY PLAN
+-------------------------------------------------------
+ Merge on stats_track_tab
+ -> Hash Right Join
+ Hash Cond: (stats_track_tab.x = id.id)
+ -> Seq Scan on stats_track_tab
+ -> Hash
+ -> Function Scan on generate_series id
+(6 rows)
+
+explain (costs off) WITH a AS (select 4) SELECT 1 UNION SELECT 2;
+ QUERY PLAN
+--------------------------
+ Unique
+ -> Sort
+ Sort Key: (1)
+ -> Append
+ -> Result
+ -> Result
+(6 rows)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------------------------------------------------------------------------------
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 1 | explain (costs off) (WITH a AS (select $1) (SELECT $2, $3))
+ t | 1 | explain (costs off) WITH a AS (select $1) DELETE FROM stats_track_tab
+ t | 1 | explain (costs off) WITH a AS (select $1) INSERT INTO stats_track_tab VALUES (($2))
+ t | 1 | explain (costs off) WITH a AS (select $1) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($2, $3) id) ON x = id+
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2
+ t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 UNION SELECT $3
+ t | 1 | explain (costs off) WITH a AS (select $1) UPDATE stats_track_tab SET x=$2 WHERE x=$3
+(8 rows)
+
+-- Explain analyze, all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT 100;
+ QUERY PLAN
+--------------------------------
+ Result (actual rows=1 loops=1)
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab;
+ QUERY PLAN
+-----------------------------------------------------
+ Seq Scan on stats_track_tab (actual rows=0 loops=1)
+(1 row)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------------------------------------------------
+ t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab
+ t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT $1
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT $1;
+(4 rows)
+
+-- Explain analyze, top tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT 100;
+ QUERY PLAN
+--------------------------------
+ Result (actual rows=1 loops=1)
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab;
+ QUERY PLAN
+-----------------------------------------------------
+ Seq Scan on stats_track_tab (actual rows=0 loops=1)
+(1 row)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------------------------------------------------
+ t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab
+ t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT $1
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(3 rows)
+
+-- Create Materialized View, all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+CREATE MATERIALIZED VIEW pgss_materialized_view AS SELECT * FROM generate_series(1, 5) as id;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+------------------------------------------------------------------------------------------------
+ t | 1 | CREATE MATERIALIZED VIEW pgss_materialized_view AS SELECT * FROM generate_series($1, $2) as id
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(2 rows)
+
+-- Create Materialized View, top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+CREATE MATERIALIZED VIEW pgss_materialized_view_2 AS SELECT * FROM generate_series(1, 5) as id;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+--------------------------------------------------------------------------------------------------
+ t | 1 | CREATE MATERIALIZED VIEW pgss_materialized_view_2 AS SELECT * FROM generate_series($1, $2) as id
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(2 rows)
+
+-- Refresh Materialized View, all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+REFRESH MATERIALIZED VIEW pgss_materialized_view;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+----------------------------------------------------
+ t | 1 | REFRESH MATERIALIZED VIEW pgss_materialized_view
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(2 rows)
+
+-- Refresh Materialized View, top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+REFRESH MATERIALIZED VIEW pgss_materialized_view;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+----------------------------------------------------
+ t | 1 | REFRESH MATERIALIZED VIEW pgss_materialized_view
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(2 rows)
+
+-- Create Table As, all-level tracking.
+SET pg_stat_statements.track = 'all';
+PREPARE test_prepare_pgss AS select generate_series(1, 10);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT 1;
+CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+-----------------------------------------------------------------
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ f | 1 | PREPARE test_prepare_pgss AS select generate_series($1, $2)
+(4 rows)
+
+-- Create Table As, top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT 1;
+CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+-----------------------------------------------------------------
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT $1
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(3 rows)
+
+-- Explain with Create Table As - all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------------
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 1 | explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1
+(2 rows)
+
+-- Explain with Create Table As - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------------
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 1 | explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1
+(2 rows)
+
+-- Declare cursor, all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+ x
+---
+(0 rows)
+
+CLOSE foocur;
+COMMIT;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------
+ t | 1 | BEGIN
+ t | 1 | CLOSE foocur
+ t | 1 | COMMIT
+ t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
+ t | 1 | FETCH FORWARD 1 FROM foocur
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(6 rows)
+
+-- Declare cursor, top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+ x
+---
+(0 rows)
+
+CLOSE foocur;
+COMMIT;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------
+ t | 1 | BEGIN
+ t | 1 | CLOSE foocur
+ t | 1 | COMMIT
+ t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
+ t | 1 | FETCH FORWARD 1 FROM foocur
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(6 rows)
+
+-- COPY - all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+COPY (SELECT 1) to stdout;
+1
+COPY (SELECT 1 UNION SELECT 2) to stdout;
+1
+2
+COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout;
+1
+COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout;
+1
+COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout;
+2
+2
+COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout;
+2
+2
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+--------------------------------------------------------------------------
+ t | 1 | COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout
+ t | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout
+ t | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout
+ t | 1 | COPY (SELECT 1 UNION SELECT 2) to stdout
+ t | 1 | COPY (SELECT 1) to stdout
+ t | 1 | COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ f | 1 | COPY (DELETE FROM stats_track_tab WHERE x=$1 RETURNING x) to stdout
+ f | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x) to stdout
+ f | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT $1 id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout
+ f | 1 | COPY (SELECT $1 UNION SELECT $2) to stdout
+ f | 1 | COPY (SELECT $1) to stdout
+ f | 1 | COPY (UPDATE stats_track_tab SET x=$1 WHERE x=$2 RETURNING x) to stdout
+(13 rows)
+
+-- COPY - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+COPY (SELECT 1) to stdout;
+1
+COPY (SELECT 1 UNION SELECT 2) to stdout;
+1
+2
+COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout;
+1
+COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout;
+1
+COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout;
+2
+2
+COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout;
+2
+2
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+ toplevel | calls | query
+----------+-------+-------------------------------------------------------------------------
+ t | 1 | COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout
+ t | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout
+ t | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout
+ t | 1 | COPY (SELECT 1 UNION SELECT 2) to stdout
+ t | 1 | COPY (SELECT 1) to stdout
+ t | 1 | COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(7 rows)
+
-- DO block - top-level tracking without utility.
SET pg_stat_statements.track = 'top';
SET pg_stat_statements.track_utility = FALSE;
diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql
index 65a17147a5..87aee416d1 100644
--- a/contrib/pg_stat_statements/sql/level_tracking.sql
+++ b/contrib/pg_stat_statements/sql/level_tracking.sql
@@ -32,7 +32,6 @@ BEGIN
END; $$;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C", toplevel;
-
-- Procedure with multiple utility statements.
CREATE OR REPLACE PROCEDURE proc_with_utility_stmt()
LANGUAGE SQL
@@ -55,6 +54,234 @@ CALL proc_with_utility_stmt();
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C", toplevel;
+-- Explain - all-level tracking.
+CREATE TABLE test_table (x int);
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+explain (costs off) SELECT 1;
+explain (costs off) (SELECT 1, 2);
+explain (costs off) TABLE stats_track_tab;
+explain (costs off) (TABLE test_table);
+explain (costs off) VALUES(1);
+explain (costs off) (VALUES(1, 2));
+explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1;
+explain (costs off) DELETE FROM stats_track_tab;
+explain (costs off) INSERT INTO stats_track_tab VALUES ((1));
+explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+explain (costs off) SELECT 1 UNION SELECT 2;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+explain (costs off) SELECT 1;
+explain (costs off) (SELECT 1, 2);
+explain (costs off) TABLE stats_track_tab;
+explain (costs off) (TABLE test_table);
+explain (costs off) VALUES(1);
+explain (costs off) (VALUES(1, 2));
+explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1;
+explain (costs off) DELETE FROM stats_track_tab;
+explain (costs off) INSERT INTO stats_track_tab VALUES ((1));
+explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+explain (costs off) SELECT 1 UNION SELECT 2;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain - all-level tracking with multi statement.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+explain (costs off) SELECT 1\; explain (costs off) SELECT 1, 2;
+explain (costs off) (SELECT 1, 2, 3)\; explain (costs off) (SELECT 1, 2, 3, 4);
+explain (costs off) TABLE stats_track_tab\; explain (costs off) (TABLE test_table);
+explain (costs off) VALUES(1)\; explain (costs off) (VALUES(1, 2));
+explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1\; explain (costs off) UPDATE stats_track_tab SET x=1;
+explain (costs off) DELETE FROM stats_track_tab\; explain (costs off) DELETE FROM stats_track_tab WHERE x=1;
+explain (costs off) INSERT INTO stats_track_tab VALUES ((1))\; explain (costs off) INSERT INTO stats_track_tab VALUES (1), (2);
+explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id)\; explain (costs off) SELECT 1, 2, 3, 4, 5;
+explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain - top-level tracking with multi statement.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+explain (costs off) SELECT 1\; explain (costs off) SELECT 1, 2;
+explain (costs off) (SELECT 1, 2, 3)\; explain (costs off) (SELECT 1, 2, 3, 4);
+explain (costs off) TABLE stats_track_tab\; explain (costs off) (TABLE test_table);
+explain (costs off) VALUES(1)\; explain (costs off) (VALUES(1, 2));
+explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1\; explain (costs off) UPDATE stats_track_tab SET x=1;
+explain (costs off) DELETE FROM stats_track_tab\; explain (costs off) DELETE FROM stats_track_tab WHERE x=1;
+explain (costs off) INSERT INTO stats_track_tab VALUES ((1))\; explain (costs off) INSERT INTO stats_track_tab VALUES ((1), (2));
+explain (costs off) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id)\; explain (costs off) SELECT 1, 2, 3, 4, 5;
+explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain with CTE - all-level tracking
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+explain (costs off) WITH a AS (select 4) SELECT 1;
+explain (costs off) (WITH a AS (select 4) (SELECT 1, 2));
+explain (costs off) WITH a AS (select 4) UPDATE stats_track_tab SET x=1 WHERE x=1;
+explain (costs off) WITH a AS (select 4) DELETE FROM stats_track_tab;
+explain (costs off) WITH a AS (select 4) INSERT INTO stats_track_tab VALUES ((1));
+explain (costs off) WITH a AS (select 4) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+explain (costs off) WITH a AS (select 4) SELECT 1 UNION SELECT 2;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain with CTE - top-level tracking
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+explain (costs off) WITH a AS (select 4) SELECT 1;
+explain (costs off) (WITH a AS (select 4) (SELECT 1, 2));
+explain (costs off) WITH a AS (select 4) UPDATE stats_track_tab SET x=1 WHERE x=1;
+explain (costs off) WITH a AS (select 4) DELETE FROM stats_track_tab;
+explain (costs off) WITH a AS (select 4) INSERT INTO stats_track_tab VALUES ((1));
+explain (costs off) WITH a AS (select 4) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
+explain (costs off) WITH a AS (select 4) SELECT 1 UNION SELECT 2;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain analyze, all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT 100;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain analyze, top tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT 100;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Create Materialized View, all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+CREATE MATERIALIZED VIEW pgss_materialized_view AS SELECT * FROM generate_series(1, 5) as id;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Create Materialized View, top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+CREATE MATERIALIZED VIEW pgss_materialized_view_2 AS SELECT * FROM generate_series(1, 5) as id;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Refresh Materialized View, all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+REFRESH MATERIALIZED VIEW pgss_materialized_view;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Refresh Materialized View, top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+REFRESH MATERIALIZED VIEW pgss_materialized_view;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Create Table As, all-level tracking.
+SET pg_stat_statements.track = 'all';
+PREPARE test_prepare_pgss AS select generate_series(1, 10);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT 1;
+CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Create Table As, top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT 1;
+CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain with Create Table As - all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Explain with Create Table As - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Declare cursor, all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+CLOSE foocur;
+COMMIT;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- Declare cursor, top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+CLOSE foocur;
+COMMIT;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- COPY - all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+COPY (SELECT 1) to stdout;
+COPY (SELECT 1 UNION SELECT 2) to stdout;
+COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout;
+COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout;
+COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout;
+COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
+-- COPY - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+COPY (SELECT 1) to stdout;
+COPY (SELECT 1 UNION SELECT 2) to stdout;
+COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id
+ WHEN MATCHED THEN UPDATE SET x = id
+ WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout;
+COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout;
+COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout;
+COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY toplevel desc, query COLLATE "C";
+
-- DO block - top-level tracking without utility.
SET pg_stat_statements.track = 'top';
SET pg_stat_statements.track_utility = FALSE;
--
2.34.1
