pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
15+ messages / 5 participants
[nested] [flat]
* BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-06 06:39 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 15+ messages in thread
From: PG Bug reporting form @ 2025-06-06 06:39 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: a.kozhemyakin@postgrespro.ru
The following bug has been logged on the website:
Bug reference: 18947
Logged by: Alexander Kozhemyakin
Email address: a.kozhemyakin@postgrespro.ru
PostgreSQL version: 18beta1
Operating system: Ubuntu 24.04
Description:
Hi, I encountered an assertion failure when running the following query on
the master branch (commit 112e40b8) when building with --enable-cassert
reproduce
initdb -D data
echo "
shared_preload_libraries = 'pg_stat_statements'
" > data/postgresql.auto.conf
pg_ctl -D data -l logfile start
psql <<EOF
(values(1)) limit 1;
EOF
bt
(gdb)
#0 __pthread_kill_implementation (no_tid=0, signo=6, threadid=<optimized
out>) at ./nptl/pthread_kill.c:44
#1 __pthread_kill_internal (signo=6, threadid=<optimized out>) at
./nptl/pthread_kill.c:78
#2 __GI___pthread_kill (threadid=<optimized out>, signo=signo@entry=6) at
./nptl/pthread_kill.c:89
#3 0x00007b9ecba4527e in __GI_raise (sig=sig@entry=6) at
../sysdeps/posix/raise.c:26
#4 0x00007b9ecba288ff in __GI_abort () at ./stdlib/abort.c:79
#5 0x00005ee635e0da6f in ExceptionalCondition
(conditionName=conditionName@entry=0x7b9ecc55f97d "len_to_wrt >= 0",
fileName=fileName@entry=0x7b9ecc55f000
"/home/postgres/test/postgres/contrib/pg_stat_statements/pg_stat_statements.c",
lineNumber=lineNumber@entry=2929)
at /home/postgres/test/postgres/src/backend/utils/error/assert.c:66
#6 0x00007b9ecc55c5a6 in generate_normalized_query
(query_len_p=0x7fff53b9a348, query_loc=1, query=0x5ee6575d0df1 "values(1))
limit 1;", jstate=0x5ee657606e88) at
/home/postgres/test/postgres/contrib/pg_stat_statements/pg_stat_statements.c:2929
#7 pgss_store (query=0x5ee6575d0df1 "values(1)) limit 1;",
queryId=<optimized out>, query_location=<optimized out>,
query_len=<optimized out>, kind=kind@entry=PGSS_INVALID,
total_time=total_time@entry=0, rows=rows@entry=0, bufusage=0x0,
walusage=0x0, jitusage=0x0, jstate=0x5ee657606e88,
parallel_workers_to_launch=0, parallel_workers_launched=0) at
/home/postgres/test/postgres/contrib/pg_stat_statements/pg_stat_statements.c:1350
#8 0x00007b9ecc55c6ca in pgss_post_parse_analyze (pstate=0x5ee6575d1a98,
query=0x5ee6575d1ba8, jstate=0x5ee657606e88) at
/home/postgres/test/postgres/contrib/pg_stat_statements/pg_stat_statements.c:868
#9 0x00005ee6359f46cb in parse_analyze_fixedparams
(parseTree=parseTree@entry=0x5ee6575d1a18,
sourceText=sourceText@entry=0x5ee6575d0df0 "(values(1)) limit 1;",
paramTypes=paramTypes@entry=0x0, numParams=numParams@entry=0,
queryEnv=queryEnv@entry=0x0)
at /home/postgres/test/postgres/src/backend/parser/analyze.c:128
#10 0x00005ee635cb71a0 in pg_analyze_and_rewrite_fixedparams (queryEnv=0x0,
numParams=0, paramTypes=0x0, query_string=<optimized out>,
parsetree=<optimized out>) at
/home/postgres/test/postgres/src/backend/tcop/postgres.c:682
#11 exec_simple_query (query_string=0x5ee6575d0df0 "(values(1)) limit 1;")
at /home/postgres/test/postgres/src/backend/tcop/postgres.c:1189
#12 0x00005ee635cb8eb0 in PostgresMain (dbname=<optimized out>,
username=<optimized out>) at
/home/postgres/test/postgres/src/backend/tcop/postgres.c:4766
#13 0x00005ee635cb3293 in BackendMain (startup_data=<optimized out>,
startup_data_len=<optimized out>) at
/home/postgres/test/postgres/src/backend/tcop/backend_startup.c:124
#14 0x00005ee635c021e2 in postmaster_child_launch (child_type=<optimized
out>, child_slot=1, startup_data=startup_data@entry=0x7fff53b9a9d0,
startup_data_len=startup_data_len@entry=24,
client_sock=client_sock@entry=0x7fff53b9a9f0) at
/home/postgres/test/postgres/src/backend/postmaster/launch_backend.c:290
#15 0x00005ee635c06014 in BackendStartup (client_sock=0x7fff53b9a9f0) at
/home/postgres/test/postgres/src/backend/postmaster/postmaster.c:3580
#16 ServerLoop () at
/home/postgres/test/postgres/src/backend/postmaster/postmaster.c:1702
#17 0x00005ee635c07b0e in PostmasterMain (argc=argc@entry=3,
argv=argv@entry=0x5ee65758a0b0) at
/home/postgres/test/postgres/src/backend/postmaster/postmaster.c:1400
#18 0x00005ee6358d3f4e in main (argc=3, argv=0x5ee65758a0b0) at
/home/postgres/test/postgres/src/backend/main/main.c:227
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-07 16:49 Dilip Kumar <dilipbalaut@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 15+ messages in thread
From: Dilip Kumar @ 2025-06-07 16:49 UTC (permalink / raw)
To: a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Sat, Jun 7, 2025 at 9:10 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 18947
> Logged by: Alexander Kozhemyakin
> Email address: a.kozhemyakin@postgrespro.ru
> PostgreSQL version: 18beta1
> Operating system: Ubuntu 24.04
> Description:
>
> Hi, I encountered an assertion failure when running the following query on
> the master branch (commit 112e40b8) when building with --enable-cassert
> reproduce
> initdb -D data
> echo "
> shared_preload_libraries = 'pg_stat_statements'
> " > data/postgresql.auto.conf
> pg_ctl -D data -l logfile start
> psql <<EOF
> (values(1)) limit 1;
> EOF
> bt
> (gdb)
> #0 __pthread_kill_implementation (no_tid=0, signo=6, threadid=<optimized
> out>) at ./nptl/pthread_kill.c:44
> #1 __pthread_kill_internal (signo=6, threadid=<optimized out>) at
> ./nptl/pthread_kill.c:78
> #2 __GI___pthread_kill (threadid=<optimized out>, signo=signo@entry=6) at
> ./nptl/pthread_kill.c:89
> #3 0x00007b9ecba4527e in __GI_raise (sig=sig@entry=6) at
> ../sysdeps/posix/raise.c:26
> #4 0x00007b9ecba288ff in __GI_abort () at ./stdlib/abort.c:79
> #5 0x00005ee635e0da6f in ExceptionalCondition
> (conditionName=conditionName@entry=0x7b9ecc55f97d "len_to_wrt >= 0",
> fileName=fileName@entry=0x7b9ecc55f000
> "/home/postgres/test/postgres/contrib/pg_stat_statements/pg_stat_statements.c",
> lineNumber=lineNumber@entry=2929)
> at /home/postgres/test/postgres/src/backend/utils/error/assert.c:66
> #6 0x00007b9ecc55c5a6 in generate_normalized_query
> (query_len_p=0x7fff53b9a348, query_loc=1, query=0x5ee6575d0df1 "values(1))
> limit 1;", jstate=0x5ee657606e88) at
> /home/postgres/test/postgres/contrib/pg_stat_statements/pg_stat_statements.c:2929
> #7 pgss_store (query=0x5ee6575d0df1 "values(1)) limit 1;",
> queryId=<optimized out>, query_location=<optimized out>,
> query_len=<optimized out>, kind=kind@entry=PGSS_INVALID,
> total_time=total_time@entry=0, rows=rows@entry=0, bufusage=0x0,
> walusage=0x0, jitusage=0x0, jstate=0x5ee657606e88,
> parallel_workers_to_launch=0, parallel_workers_launched=0) at
> /home/postgres/test/postgres/contrib/pg_stat_statements/pg_stat_statements.c:1350
> #8 0x00007b9ecc55c6ca in pgss_post_parse_analyze (pstate=0x5ee6575d1a98,
> query=0x5ee6575d1ba8, jstate=0x5ee657606e88) at
> /home/postgres/test/postgres/contrib/pg_stat_statements/pg_stat_statements.c:868
> #9 0x00005ee6359f46cb in parse_analyze_fixedparams
> (parseTree=parseTree@entry=0x5ee6575d1a18,
> sourceText=sourceText@entry=0x5ee6575d0df0 "(values(1)) limit 1;",
> paramTypes=paramTypes@entry=0x0, numParams=numParams@entry=0,
> queryEnv=queryEnv@entry=0x0)
> at /home/postgres/test/postgres/src/backend/parser/analyze.c:128
> #10 0x00005ee635cb71a0 in pg_analyze_and_rewrite_fixedparams (queryEnv=0x0,
> numParams=0, paramTypes=0x0, query_string=<optimized out>,
> parsetree=<optimized out>) at
> /home/postgres/test/postgres/src/backend/tcop/postgres.c:682
> #11 exec_simple_query (query_string=0x5ee6575d0df0 "(values(1)) limit 1;")
> at /home/postgres/test/postgres/src/backend/tcop/postgres.c:1189
> #12 0x00005ee635cb8eb0 in PostgresMain (dbname=<optimized out>,
> username=<optimized out>) at
> /home/postgres/test/postgres/src/backend/tcop/postgres.c:4766
> #13 0x00005ee635cb3293 in BackendMain (startup_data=<optimized out>,
> startup_data_len=<optimized out>) at
> /home/postgres/test/postgres/src/backend/tcop/backend_startup.c:124
> #14 0x00005ee635c021e2 in postmaster_child_launch (child_type=<optimized
> out>, child_slot=1, startup_data=startup_data@entry=0x7fff53b9a9d0,
> startup_data_len=startup_data_len@entry=24,
> client_sock=client_sock@entry=0x7fff53b9a9f0) at
> /home/postgres/test/postgres/src/backend/postmaster/launch_backend.c:290
> #15 0x00005ee635c06014 in BackendStartup (client_sock=0x7fff53b9a9f0) at
> /home/postgres/test/postgres/src/backend/postmaster/postmaster.c:3580
> #16 ServerLoop () at
> /home/postgres/test/postgres/src/backend/postmaster/postmaster.c:1702
> #17 0x00005ee635c07b0e in PostmasterMain (argc=argc@entry=3,
> argv=argv@entry=0x5ee65758a0b0) at
> /home/postgres/test/postgres/src/backend/postmaster/postmaster.c:1400
> #18 0x00005ee6358d3f4e in main (argc=3, argv=0x5ee65758a0b0) at
> /home/postgres/test/postgres/src/backend/main/main.c:227
I didn't get time to debug, I might look into it tomorrow if someone
doesn't do it before that, but I am able to reproduce the issue and
seems like problem is with respect to computing the quer_loc in below
loop, and due to that 'quer_loc' is becoming bigger than query_len and
hitting the assert.
for (i = 0; i < jstate->clocations_count; i++)
{
...
quer_loc = off + tok_len;
}
len_to_wrt = query_len - quer_loc;
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-08 02:39 Michael Paquier <michael@paquier.xyz>
parent: Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: Michael Paquier @ 2025-06-08 02:39 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: a.kozhemyakin@postgrespro.ru, pgsql-bugs@lists.postgresql.org, Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
On Sat, Jun 07, 2025 at 10:19:53PM +0530, Dilip Kumar wrote:
> I didn't get time to debug, I might look into it tomorrow if someone
> doesn't do it before that, but I am able to reproduce the issue and
> seems like problem is with respect to computing the quer_loc in below
> loop, and due to that 'quer_loc' is becoming bigger than query_len and
> hitting the assert.
The loop in charge of assigning the constants in the queries needs to
be smarter in terms of the new locations assigned to sub-queries, here
the VALUES one.
499edb09741b is the origin of the regression, so open item assigned to
me (will look into it later), adding Anthonin in CC.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aET38DIyBht33bIP@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-08 10:25 Dilip Kumar <dilipbalaut@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
1 sibling, 0 replies; 15+ messages in thread
From: Dilip Kumar @ 2025-06-08 10:25 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: a.kozhemyakin@postgrespro.ru, pgsql-bugs@lists.postgresql.org, Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
On Sun, Jun 8, 2025 at 8:09 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Sat, Jun 07, 2025 at 10:19:53PM +0530, Dilip Kumar wrote:
> > I didn't get time to debug, I might look into it tomorrow if someone
> > doesn't do it before that, but I am able to reproduce the issue and
> > seems like problem is with respect to computing the quer_loc in below
> > loop, and due to that 'quer_loc' is becoming bigger than query_len and
> > hitting the assert.
>
> The loop in charge of assigning the constants in the queries needs to
> be smarter in terms of the new locations assigned to sub-queries, here
> the VALUES one.
>
> 499edb09741b is the origin of the regression, so open item assigned to
> me (will look into it later), adding Anthonin in CC.
Thanks, I was looking into the commit pointed by you, I found this
change problematic,
transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree)
{
Query *result;
+ /* Store RawStmt's length and location in pstate */
+ pstate->p_stmt_len = parseTree->stmt_len;
+ pstate->p_stmt_location = parseTree->stmt_location;
+
/* We're at top level, so allow SELECT INTO */
result = transformOptionalSelectInto(pstate, parseTree->stmt);
- result->stmt_location = parseTree->stmt_location;
- result->stmt_len = parseTree->stmt_len;
So here before this commit we were setting the 'query->stmt_len' as
parseTree->stmt_len from the Top level statement, but after this fix,
we are setting the length of the select statement, in query->stmt_len,
because now we are doing that in
transformOptionalSelectInto->transformStmt->setQueryLocationAndLength
so that is just transferring the select statement and has length of
that. Due to that in generate_normalized_query() we are getting
'quer_loc' bigger than the 'query_len'.
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-09 10:07 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Michael Paquier <michael@paquier.xyz>
1 sibling, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2025-06-09 10:07 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Dilip Kumar <dilipbalaut@gmail.com>; +Cc: a.kozhemyakin@postgrespro.ru, pgsql-bugs@lists.postgresql.org, Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
On 2025/06/08 11:39, Michael Paquier wrote:
> On Sat, Jun 07, 2025 at 10:19:53PM +0530, Dilip Kumar wrote:
>> I didn't get time to debug, I might look into it tomorrow if someone
>> doesn't do it before that, but I am able to reproduce the issue and
>> seems like problem is with respect to computing the quer_loc in below
>> loop, and due to that 'quer_loc' is becoming bigger than query_len and
>> hitting the assert.
>
> The loop in charge of assigning the constants in the queries needs to
> be smarter in terms of the new locations assigned to sub-queries, here
> the VALUES one.
>
> 499edb09741b is the origin of the regression, so open item assigned to
> me (will look into it later), adding Anthonin in CC.
The issue seems to be that the commit missed handling cases where
a clause follows a SELECT query wrapped in parentheses.
If the following clause includes a constant (e.g., LIMIT 1 or
FETCH FIRST 1 ROW ONLY), it can trigger the reported assertion failure.
If it doesn't include a constant (e.g., FETCH FIRST ROW ONLY),
the assertion failure doesn't occur, but only the inner SELECT query (inside
the parentheses) appears in pg_stat_statements, i.e.,
the "FETCH FIRST ROW ONLY" part is missing from pg_stat_statements.query.
+ SelectStmt *n = (SelectStmt *) $2;
+
+ /*
+ * As SelectStmt's location starts at the SELECT keyword,
+ * we need to track the length of the SelectStmt within
+ * parentheses to be able to extract the relevant part
+ * of the query. Without this, the RawStmt's length would
+ * be used and would include the closing parenthesis.
+ */
+ n->stmt_len = @3 - @2;
Shouldn't this part be skipped in cases where the SELECT with parens is
followed by a clause? At least in those cases, this logic doesn't seem
appropriate.
Regards,
--
Fujii Masao
NTT DATA Japan Corporation
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-10 05:45 Michael Paquier <michael@paquier.xyz>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2025-06-10 05:45 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru, pgsql-bugs@lists.postgresql.org, Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
On Mon, Jun 09, 2025 at 07:07:42PM +0900, Fujii Masao wrote:
> Shouldn't this part be skipped in cases where the SELECT with parens is
> followed by a clause? At least in those cases, this logic doesn't seem
> appropriate.
Touché. That means messing with any parent Node that includes or
touches select_with_parens, select_no_parens (JSON_ARRAY has one) or
SelectStmt (few of these, like in CTAS). That's not cool in the long
term because we would expect any new node that englobes a SelectStmt
to be able to set up their inner length and location as they should,
as far as I get it. I was wondering first if we could have done
something with the top-level statement depending on the nesting level
of PGSS, but that's not going to fly high, for example with cases like
this one (any case that has an inner SELECT):
copy ((select 1) union ((select 1)) fetch first 1 row only) to stdout
Even with that in mind, a second thing I was wondering is to compile
the length within select_with_parens, still there seems to be an extra
parenthesis pending at the end of some of the strings. Perhaps I'm
missing a simpler concept, of course.
Anyway, this is the second bug that we have in this area, and this one
is worse. Now that we are in the middle of beta, this is gathering
the signs that we should revert the change from the tree for the
moment and potentially revisit the whole in v19 with these edge cases
handled. So attached is a patch doing a revert of:
499edb09741b, nested statement tracking.
06450c7b8c70, follow-up fix for 499edb09741b.
There was also 6b652e6ce85a for the addition of query ID for CREATE
TABLE AS and DECLARE, and this is still OK as a standalone change.
Its tests are updated with an extra entry added in the non-top-level
case, with the query string matching the top-level string as a result
of the attached.
If you have comments, feel free.
--
Michael
Attachments:
[text/x-diff] 0001-Revert-support-for-improved-tracking-of-nested-queri.patch (47.4K, ../../aEfGl9bNPR5C76W2@paquier.xyz/2-0001-Revert-support-for-improved-tracking-of-nested-queri.patch)
download | inline diff:
From ada21d07b45307efedcc20f83a153daa5368641d Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Tue, 10 Jun 2025 14:32:16 +0900
Subject: [PATCH] Revert support for improved tracking of nested queries
This commit reverts the two following commits:
499edb09741b, track more precisely query locations for nested statements
06450c7b8c70, a follow-up fix of 499edb09741b with query locations.
These are proved to have issues with the tracking of SELECT statements,
when these use multiple parenthesis which is something supported by the
grammar. Incorrect location and lengths are causing pg_stat_statements
to become confused, failing its job in query normalization with
out-of-bound writes.
---
src/include/nodes/parsenodes.h | 10 -
src/include/parser/parse_node.h | 16 --
src/backend/parser/analyze.c | 90 +------
src/backend/parser/gram.y | 80 +-----
.../expected/pg_overexplain.out | 12 +-
.../expected/level_tracking.out | 233 +++++++-----------
.../pg_stat_statements/expected/planning.out | 10 +-
.../pg_stat_statements/expected/select.out | 2 +-
.../pg_stat_statements/expected/utility.out | 2 +-
.../pg_stat_statements/sql/level_tracking.sql | 26 --
contrib/pg_stat_statements/sql/planning.sql | 4 +-
11 files changed, 119 insertions(+), 366 deletions(-)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index dd00ab420b8a..0c1120beff3c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2100,8 +2100,6 @@ typedef struct InsertStmt
ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
OverridingKind override; /* OVERRIDING clause */
- ParseLoc stmt_location; /* start location, or -1 if unknown */
- ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */
} InsertStmt;
/* ----------------------
@@ -2116,8 +2114,6 @@ typedef struct DeleteStmt
Node *whereClause; /* qualifications */
ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
- ParseLoc stmt_location; /* start location, or -1 if unknown */
- ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */
} DeleteStmt;
/* ----------------------
@@ -2133,8 +2129,6 @@ typedef struct UpdateStmt
List *fromClause; /* optional from clause for more tables */
ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
- ParseLoc stmt_location; /* start location, or -1 if unknown */
- ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */
} UpdateStmt;
/* ----------------------
@@ -2150,8 +2144,6 @@ typedef struct MergeStmt
List *mergeWhenClauses; /* list of MergeWhenClause(es) */
ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
- ParseLoc stmt_location; /* start location, or -1 if unknown */
- ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */
} MergeStmt;
/* ----------------------
@@ -2221,8 +2213,6 @@ typedef struct SelectStmt
bool all; /* ALL specified? */
struct SelectStmt *larg; /* left child */
struct SelectStmt *rarg; /* right child */
- ParseLoc stmt_location; /* start location, or -1 if unknown */
- ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */
/* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 994284019fbb..f7d07c845425 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -108,20 +108,6 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param,
* byte-wise locations in parse structures to character-wise cursor
* positions.)
*
- * p_stmt_location: location of the top level RawStmt's start. During
- * transformation, the Query's location will be set to the statement's
- * location if available. Otherwise, the RawStmt's start location will
- * be used. Propagating the location through ParseState is needed for
- * the Query length calculation (see p_stmt_len below).
- *
- * p_stmt_len: length of the top level RawStmt. Most of the time, the
- * statement's length is not provided by the parser, with the exception
- * of SelectStmt within parentheses and PreparableStmt in COPY. If the
- * statement's location is provided by the parser, the top-level location
- * and length are needed to accurately compute the Query's length. If the
- * statement's location is not provided, the RawStmt's length can be used
- * directly.
- *
* p_rtable: list of RTEs that will become the rangetable of the query.
* Note that neither relname nor refname of these entries are necessarily
* unique; searching the rtable by name is a bad idea.
@@ -207,8 +193,6 @@ 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/backend/parser/analyze.c b/src/backend/parser/analyze.c
index a16fdd65601d..34f7c17f576e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -238,103 +238,24 @@ parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
return query;
}
-/*
- * setQueryLocationAndLength
- * Set query's location and length from statement and ParseState
- *
- * Some statements, like PreparableStmt, can be located within parentheses.
- * For example "(SELECT 1)" or "COPY (UPDATE ...) to x;". For those, we
- * cannot use the whole string from the statement's location or the SQL
- * string would yield incorrectly. The parser will set stmt_len, reflecting
- * the size of the statement within the parentheses. Thus, when stmt_len is
- * available, we need to use it for the Query's stmt_len.
- *
- * For other cases, the parser can't provide the length of individual
- * statements. However, we have the statement's location plus the length
- * (p_stmt_len) and location (p_stmt_location) of the top level RawStmt,
- * stored in pstate. Thus, the statement's length is the RawStmt's length
- * minus how much we've advanced in the RawStmt's string. If p_stmt_len
- * is 0, the SQL string is used up to its end.
- */
-static void
-setQueryLocationAndLength(ParseState *pstate, Query *qry, Node *parseTree)
-{
- ParseLoc stmt_len = 0;
-
- switch (nodeTag(parseTree))
- {
- case T_InsertStmt:
- qry->stmt_location = ((InsertStmt *) parseTree)->stmt_location;
- stmt_len = ((InsertStmt *) parseTree)->stmt_len;
- break;
-
- case T_DeleteStmt:
- qry->stmt_location = ((DeleteStmt *) parseTree)->stmt_location;
- stmt_len = ((DeleteStmt *) parseTree)->stmt_len;
- break;
-
- case T_UpdateStmt:
- qry->stmt_location = ((UpdateStmt *) parseTree)->stmt_location;
- stmt_len = ((UpdateStmt *) parseTree)->stmt_len;
- break;
-
- case T_MergeStmt:
- qry->stmt_location = ((MergeStmt *) parseTree)->stmt_location;
- stmt_len = ((MergeStmt *) parseTree)->stmt_len;
- break;
-
- case T_SelectStmt:
- qry->stmt_location = ((SelectStmt *) parseTree)->stmt_location;
- stmt_len = ((SelectStmt *) parseTree)->stmt_len;
- break;
-
- case T_PLAssignStmt:
- qry->stmt_location = ((PLAssignStmt *) parseTree)->location;
- break;
-
- default:
- qry->stmt_location = pstate->p_stmt_location;
- break;
- }
-
- if (stmt_len > 0)
- {
- /* Statement's length is known, use it */
- qry->stmt_len = stmt_len;
- }
- else if (pstate->p_stmt_len > 0)
- {
- /*
- * The top RawStmt's length is known, so calculate the statement's
- * length from the statement's location and the RawStmt's length and
- * location.
- */
- qry->stmt_len = pstate->p_stmt_len - (qry->stmt_location - pstate->p_stmt_location);
- }
-
- /* The calculated statement length should be calculated as positive. */
- Assert(qry->stmt_len >= 0);
-}
-
/*
* transformTopLevelStmt -
* transform a Parse tree into a Query tree.
*
- * This function is just responsible for storing location data
- * from the RawStmt into the ParseState.
+ * This function is just responsible for transferring statement location data
+ * from the RawStmt into the finished Query.
*/
Query *
transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree)
{
Query *result;
- /* Store RawStmt's length and location in pstate */
- pstate->p_stmt_len = parseTree->stmt_len;
- pstate->p_stmt_location = parseTree->stmt_location;
-
/* We're at top level, so allow SELECT INTO */
result = transformOptionalSelectInto(pstate, parseTree->stmt);
+ result->stmt_location = parseTree->stmt_location;
+ result->stmt_len = parseTree->stmt_len;
+
return result;
}
@@ -503,7 +424,6 @@ transformStmt(ParseState *pstate, Node *parseTree)
/* Mark as original query until we learn differently */
result->querySource = QSRC_ORIGINAL;
result->canSetTag = true;
- setQueryLocationAndLength(pstate, result, parseTree);
return result;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0b5652071d11..7a9b2ddddecb 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -154,7 +154,6 @@ static void base_yyerror(YYLTYPE *yylloc, core_yyscan_t yyscanner,
const char *msg);
static RawStmt *makeRawStmt(Node *stmt, int stmt_location);
static void updateRawStmtEnd(RawStmt *rs, int end_location);
-static void updatePreparableStmtEnd(Node *n, int end_location);
static Node *makeColumnRef(char *colname, List *indirection,
int location, core_yyscan_t yyscanner);
static Node *makeTypeCast(Node *arg, TypeName *typename, int location);
@@ -178,7 +177,7 @@ static void insertSelectOptions(SelectStmt *stmt,
SelectLimit *limitClause,
WithClause *withClause,
core_yyscan_t yyscanner);
-static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg, int location);
+static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
static Node *doNegate(Node *n, int location);
static void doNegateFloat(Float *v);
static Node *makeAndExpr(Node *lexpr, Node *rexpr, int location);
@@ -3417,7 +3416,6 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list
{
CopyStmt *n = makeNode(CopyStmt);
- updatePreparableStmtEnd($3, @4);
n->relation = NULL;
n->query = $3;
n->attlist = NIL;
@@ -12240,7 +12238,6 @@ InsertStmt:
$5->onConflictClause = $6;
$5->returningClause = $7;
$5->withClause = $1;
- $5->stmt_location = @$;
$$ = (Node *) $5;
}
;
@@ -12431,7 +12428,6 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
n->whereClause = $6;
n->returningClause = $7;
n->withClause = $1;
- n->stmt_location = @$;
$$ = (Node *) n;
}
;
@@ -12506,7 +12502,6 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
n->whereClause = $7;
n->returningClause = $8;
n->withClause = $1;
- n->stmt_location = @$;
$$ = (Node *) n;
}
;
@@ -12584,7 +12579,6 @@ MergeStmt:
m->joinCondition = $8;
m->mergeWhenClauses = $9;
m->returningClause = $10;
- m->stmt_location = @$;
$$ = (Node *) m;
}
@@ -12825,20 +12819,7 @@ SelectStmt: select_no_parens %prec UMINUS
;
select_with_parens:
- '(' select_no_parens ')'
- {
- SelectStmt *n = (SelectStmt *) $2;
-
- /*
- * As SelectStmt's location starts at the SELECT keyword,
- * we need to track the length of the SelectStmt within
- * parentheses to be able to extract the relevant part
- * of the query. Without this, the RawStmt's length would
- * be used and would include the closing parenthesis.
- */
- n->stmt_len = @3 - @2;
- $$ = $2;
- }
+ '(' select_no_parens ')' { $$ = $2; }
| '(' select_with_parens ')' { $$ = $2; }
;
@@ -12960,7 +12941,6 @@ simple_select:
n->groupDistinct = ($7)->distinct;
n->havingClause = $8;
n->windowClause = $9;
- n->stmt_location = @1;
$$ = (Node *) n;
}
| SELECT distinct_clause target_list
@@ -12978,7 +12958,6 @@ simple_select:
n->groupDistinct = ($7)->distinct;
n->havingClause = $8;
n->windowClause = $9;
- n->stmt_location = @1;
$$ = (Node *) n;
}
| values_clause { $$ = $1; }
@@ -12999,20 +12978,19 @@ simple_select:
n->targetList = list_make1(rt);
n->fromClause = list_make1($2);
- n->stmt_location = @1;
$$ = (Node *) n;
}
| select_clause UNION set_quantifier select_clause
{
- $$ = makeSetOp(SETOP_UNION, $3 == SET_QUANTIFIER_ALL, $1, $4, @1);
+ $$ = makeSetOp(SETOP_UNION, $3 == SET_QUANTIFIER_ALL, $1, $4);
}
| select_clause INTERSECT set_quantifier select_clause
{
- $$ = makeSetOp(SETOP_INTERSECT, $3 == SET_QUANTIFIER_ALL, $1, $4, @1);
+ $$ = makeSetOp(SETOP_INTERSECT, $3 == SET_QUANTIFIER_ALL, $1, $4);
}
| select_clause EXCEPT set_quantifier select_clause
{
- $$ = makeSetOp(SETOP_EXCEPT, $3 == SET_QUANTIFIER_ALL, $1, $4, @1);
+ $$ = makeSetOp(SETOP_EXCEPT, $3 == SET_QUANTIFIER_ALL, $1, $4);
}
;
@@ -13590,7 +13568,6 @@ values_clause:
{
SelectStmt *n = makeNode(SelectStmt);
- n->stmt_location = @1;
n->valuesLists = list_make1($3);
$$ = (Node *) n;
}
@@ -18748,47 +18725,6 @@ updateRawStmtEnd(RawStmt *rs, int end_location)
rs->stmt_len = end_location - rs->stmt_location;
}
-/*
- * Adjust a PreparableStmt to reflect that it doesn't run to the end of the
- * string.
- */
-static void
-updatePreparableStmtEnd(Node *n, int end_location)
-{
- if (IsA(n, SelectStmt))
- {
- SelectStmt *stmt = (SelectStmt *) n;
-
- stmt->stmt_len = end_location - stmt->stmt_location;
- }
- else if (IsA(n, InsertStmt))
- {
- InsertStmt *stmt = (InsertStmt *) n;
-
- stmt->stmt_len = end_location - stmt->stmt_location;
- }
- else if (IsA(n, UpdateStmt))
- {
- UpdateStmt *stmt = (UpdateStmt *) n;
-
- stmt->stmt_len = end_location - stmt->stmt_location;
- }
- else if (IsA(n, DeleteStmt))
- {
- DeleteStmt *stmt = (DeleteStmt *) n;
-
- stmt->stmt_len = end_location - stmt->stmt_location;
- }
- else if (IsA(n, MergeStmt))
- {
- MergeStmt *stmt = (MergeStmt *) n;
-
- stmt->stmt_len = end_location - stmt->stmt_location;
- }
- else
- elog(ERROR, "unexpected node type %d", (int) n->type);
-}
-
static Node *
makeColumnRef(char *colname, List *indirection,
int location, core_yyscan_t yyscanner)
@@ -19167,14 +19103,11 @@ insertSelectOptions(SelectStmt *stmt,
errmsg("multiple WITH clauses not allowed"),
parser_errposition(exprLocation((Node *) withClause))));
stmt->withClause = withClause;
-
- /* Update SelectStmt's location to the start of the WITH clause */
- stmt->stmt_location = withClause->location;
}
}
static Node *
-makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg, int location)
+makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
@@ -19182,7 +19115,6 @@ makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg, int location)
n->all = all;
n->larg = (SelectStmt *) larg;
n->rarg = (SelectStmt *) rarg;
- n->stmt_location = location;
return (Node *) n;
}
diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out
index cb5c396c5192..6de02323d7cf 100644
--- a/contrib/pg_overexplain/expected/pg_overexplain.out
+++ b/contrib/pg_overexplain/expected/pg_overexplain.out
@@ -37,7 +37,7 @@ EXPLAIN (DEBUG) SELECT 1;
Subplans Needing Rewind: none
Relation OIDs: none
Executor Parameter Types: none
- Parse Location: 16 for 8 bytes
+ Parse Location: 0 to end
(11 rows)
EXPLAIN (RANGE_TABLE) SELECT 1;
@@ -119,7 +119,7 @@ $$);
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: none
- Parse Location: 41 to end
+ Parse Location: 0 to end
RTI 1 (relation, inherited, in-from-clause):
Eref: vegetables (id, name, genus)
Relation: vegetables
@@ -240,7 +240,7 @@ $$);
<Subplans-Needing-Rewind>none</Subplans-Needing-Rewind> +
<Relation-OIDs>NNN...</Relation-OIDs> +
<Executor-Parameter-Types>none</Executor-Parameter-Types> +
- <Parse-Location>53 to end</Parse-Location> +
+ <Parse-Location>0 to end</Parse-Location> +
</PlannedStmt> +
<Range-Table> +
<Range-Table-Entry> +
@@ -344,7 +344,7 @@ $$);
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: none
- Parse Location: 28 to end
+ Parse Location: 0 to end
(37 rows)
SET debug_parallel_query = false;
@@ -372,7 +372,7 @@ $$);
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: 0
- Parse Location: 28 to end
+ Parse Location: 0 to end
(15 rows)
-- Create an index, and then attempt to force a nested loop with inner index
@@ -436,7 +436,7 @@ $$);
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: 23
- Parse Location: 75 for 62 bytes
+ Parse Location: 0 to end
(47 rows)
RESET enable_hashjoin;
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index 75e785e1719e..f2bd73dc66a1 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -206,37 +206,37 @@ EXPLAIN (COSTS OFF) SELECT 1 UNION SELECT 2;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+--------------------------------------------------------------------
- f | 1 | DELETE FROM stats_track_tab
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2)
+ f | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2);
t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table)
+ f | 1 | EXPLAIN (COSTS OFF) (TABLE test_table);
t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2))
+ f | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2));
t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab
+ f | 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 +
+ f | 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)
+ 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);
t | 1 | EXPLAIN (COSTS OFF) SELECT $1
t | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2
+ f | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2;
+ f | 1 | EXPLAIN (COSTS OFF) SELECT $1;
t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab
+ f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab;
t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2
+ f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2;
t | 1 | EXPLAIN (COSTS OFF) VALUES ($1)
- 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 UNION SELECT $2
- f | 1 | SELECT $1, $2
+ f | 1 | EXPLAIN (COSTS OFF) VALUES ($1);
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | TABLE stats_track_tab
- f | 1 | TABLE test_table
- f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2
- f | 1 | VALUES ($1)
- f | 1 | VALUES ($1, $2)
(23 rows)
-- EXPLAIN - top-level tracking.
@@ -405,20 +405,20 @@ EXPLAIN (COSTS OFF) SELECT 1, 2 UNION SELECT 3, 4\; EXPLAIN (COSTS OFF) (SELECT
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+-----------------------------------------------------------------
- f | 1 | (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3)
t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
+ f | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3); EXPLAIN (COSTS OFF) (SELECT 1, 2, 3, 4);
t | 1 | 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);
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
- f | 1 | SELECT $1
- f | 1 | SELECT $1, $2
- f | 1 | SELECT $1, $2 UNION SELECT $3, $4
- f | 1 | SELECT $1, $2, $3
- f | 1 | SELECT $1, $2, $3, $4
+ 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;
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(13 rows)
@@ -494,29 +494,29 @@ EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1))\; EXPLAIN (COSTS OF
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+--------------------------------------------------------------------
- f | 1 | DELETE FROM stats_track_tab
- f | 1 | DELETE FROM stats_track_tab WHERE x = $1
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------------------------------------------------------------------------------
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
+ 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;
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))
+ 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);
t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab
+ 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);
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
+ 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;
t | 1 | EXPLAIN (COSTS OFF) VALUES ($1)
- f | 1 | INSERT INTO stats_track_tab VALUES ($1), ($2)
- f | 1 | INSERT INTO stats_track_tab VALUES (($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));
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | TABLE stats_track_tab
- f | 1 | TABLE test_table
- 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)
- f | 1 | VALUES ($1, $2)
(21 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
@@ -547,18 +547,21 @@ EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+---------------------------------------------------------------
- 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 +
+ toplevel | calls | query
+----------+-------+------------------------------------------------------------------------------------------------
+ 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)
+ 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;
t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2, $3, $4, $5
- 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, $2, $3, $4, $5
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
@@ -786,29 +789,29 @@ EXPLAIN (COSTS OFF) WITH a AS (select 4) SELECT 1 UNION SELECT 2;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+------------------------------------------------------------------------------------------
+ toplevel | calls | query
+----------+-------+-------------------------------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) (WITH a AS (SELECT $1) (SELECT $2, $3))
+ f | 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
+ f | 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 +
+ f | 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)
+ 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);
t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2
+ f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2;
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) UPDATE stats_track_tab SET x = $2 WHERE x = $3;
t | 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 UNION SELECT $3;
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- 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 | WITH a AS (SELECT $1) SELECT $2
- f | 1 | WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3
- f | 1 | WITH a AS (select $1) SELECT $2 UNION SELECT $3
(15 rows)
-- EXPLAIN with CTEs - top-level tracking
@@ -918,13 +921,14 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+------------------------------------------------------------------------------
- t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) +
+ toplevel | calls | query
+----------+-------+-------------------------------------------------------------------------------
+ t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) +
| | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab
+ f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) +
+ | | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab;
t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1
- f | 1 | SELECT $1
- f | 1 | SELECT * FROM stats_track_tab
+ f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1;
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
@@ -1047,10 +1051,10 @@ SELECT toplevel, calls, query FROM pg_stat_statements
toplevel | calls | query
----------+-------+-----------------------------------------------------------------
t | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1
+ f | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1;
t | 1 | CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss
- f | 1 | SELECT $1
+ f | 1 | PREPARE test_prepare_pgss AS select generate_series($1, $2)
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | select generate_series($1, $2)
(5 rows)
-- CREATE TABLE AS, top-level tracking.
@@ -1088,10 +1092,10 @@ EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+---------------------------------------------------------------------------
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1
- f | 1 | SELECT $1
+ f | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1;
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(3 rows)
@@ -1136,14 +1140,14 @@ CLOSE foocur;
COMMIT;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+---------------------------------------------------------
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------
t | 1 | BEGIN
t | 1 | CLOSE foocur
t | 1 | COMMIT
t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
+ f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
t | 1 | FETCH FORWARD 1 FROM foocur
- f | 1 | SELECT * from stats_track_tab
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(7 rows)
@@ -1203,25 +1207,25 @@ COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout;
2
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+---------------------------------------------------------------------------
+ toplevel | calls | query
+----------+-------+-----------------------------------------------------------------------------
+ f | 1 | COPY (DELETE FROM stats_track_tab WHERE x = $1 RETURNING x) TO stdout
t | 1 | COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout
+ f | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES ($1) 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 +
+ 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
+ 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
+ f | 1 | COPY (SELECT $1 UNION SELECT $2) TO stdout
+ f | 1 | COPY (SELECT $1) TO stdout
t | 1 | COPY (SELECT 1 UNION SELECT 2) TO stdout
t | 1 | COPY (SELECT 1) TO stdout
+ f | 1 | COPY (UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x) TO stdout
t | 1 | COPY (UPDATE stats_track_tab SET x = 2 WHERE x = 1 RETURNING x) TO stdout
- f | 1 | DELETE FROM stats_track_tab WHERE x = $1 RETURNING x
- f | 1 | INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x
- f | 1 | 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
- f | 1 | SELECT $1
- f | 1 | SELECT $1 UNION SELECT $2
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x
(13 rows)
-- COPY - top-level tracking.
@@ -1319,57 +1323,6 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(4 rows)
--- DO block --- multiple inner queries with separators
-SET pg_stat_statements.track = 'all';
-SET pg_stat_statements.track_utility = TRUE;
-CREATE TABLE pgss_do_util_tab_1 (a int);
-CREATE TABLE pgss_do_util_tab_2 (a int);
-SELECT pg_stat_statements_reset() IS NOT NULL AS t;
- t
----
- t
-(1 row)
-
-DO $$
-DECLARE BEGIN
- EXECUTE 'CREATE TABLE pgss_do_table (id INT); DROP TABLE pgss_do_table';
- EXECUTE 'SELECT a FROM pgss_do_util_tab_1; SELECT a FROM pgss_do_util_tab_2';
-END $$;
-SELECT toplevel, calls, rows, query FROM pg_stat_statements
- WHERE toplevel IS FALSE
- ORDER BY query COLLATE "C";
- toplevel | calls | rows | query
-----------+-------+------+-------------------------------------
- f | 1 | 0 | CREATE TABLE pgss_do_table (id INT)
- f | 1 | 0 | DROP TABLE pgss_do_table
- f | 1 | 0 | SELECT a FROM pgss_do_util_tab_1
- f | 1 | 0 | SELECT a FROM pgss_do_util_tab_2
-(4 rows)
-
-SELECT pg_stat_statements_reset() IS NOT NULL AS t;
- t
----
- t
-(1 row)
-
--- Note the extra semicolon at the end of the query.
-DO $$
-DECLARE BEGIN
- EXECUTE 'CREATE TABLE pgss_do_table (id INT); DROP TABLE pgss_do_table;';
- EXECUTE 'SELECT a FROM pgss_do_util_tab_1; SELECT a FROM pgss_do_util_tab_2;';
-END $$;
-SELECT toplevel, calls, rows, query FROM pg_stat_statements
- WHERE toplevel IS FALSE
- ORDER BY query COLLATE "C";
- toplevel | calls | rows | query
-----------+-------+------+-------------------------------------
- f | 1 | 0 | CREATE TABLE pgss_do_table (id INT)
- f | 1 | 0 | DROP TABLE pgss_do_table
- f | 1 | 0 | SELECT a FROM pgss_do_util_tab_1
- f | 1 | 0 | SELECT a FROM pgss_do_util_tab_2
-(4 rows)
-
-DROP TABLE pgss_do_util_tab_1, pgss_do_util_tab_2;
-- PL/pgSQL function - top-level tracking.
SET pg_stat_statements.track = 'top';
SET pg_stat_statements.track_utility = FALSE;
diff --git a/contrib/pg_stat_statements/expected/planning.out b/contrib/pg_stat_statements/expected/planning.out
index 3ee1928cbe94..9effd11fdc85 100644
--- a/contrib/pg_stat_statements/expected/planning.out
+++ b/contrib/pg_stat_statements/expected/planning.out
@@ -58,7 +58,7 @@ SELECT 42;
(1 row)
SELECT plans, calls, rows, query FROM pg_stat_statements
- WHERE query NOT LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C";
+ WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
plans | calls | rows | query
-------+-------+------+----------------------------------------------------------
0 | 1 | 0 | ALTER TABLE stats_plan_test ADD COLUMN x int
@@ -72,10 +72,10 @@ SELECT plans, calls, rows, query FROM pg_stat_statements
-- for the prepared statement we expect at least one replan, but cache
-- invalidations could force more
SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_stat_statements
- WHERE query LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C";
- plans_ok | calls | rows | query
-----------+-------+------+--------------------------------------
- t | 4 | 4 | SELECT COUNT(*) FROM stats_plan_test
+ WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+ plans_ok | calls | rows | query
+----------+-------+------+-------------------------------------------------------
+ t | 4 | 4 | PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test
(1 row)
-- Cleanup
diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out
index 038ae1103645..e87835085f52 100644
--- a/contrib/pg_stat_statements/expected/select.out
+++ b/contrib/pg_stat_statements/expected/select.out
@@ -208,6 +208,7 @@ DEALLOCATE pgss_test;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
calls | rows | query
-------+------+------------------------------------------------------------------------------
+ 1 | 1 | PREPARE pgss_test (int) AS SELECT $1, $2 LIMIT $3
4 | 4 | SELECT $1 +
| | -- but this one will appear +
| | AS "text"
@@ -221,7 +222,6 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
2 | 2 | SELECT $1 AS "int" ORDER BY 1
1 | 2 | SELECT $1 AS i UNION SELECT $2 ORDER BY i
1 | 1 | SELECT $1 || $2
- 1 | 1 | SELECT $1, $2 LIMIT $3
2 | 2 | SELECT DISTINCT $1 AS "int"
0 | 0 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index aa4f0f7e6280..060d4416dd74 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -540,7 +540,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-------+------+----------------------------------------------------
2 | 0 | DEALLOCATE $1
2 | 0 | DEALLOCATE ALL
- 2 | 2 | SELECT $1 AS a
+ 2 | 2 | PREPARE stat_select AS SELECT $1 AS a
1 | 1 | SELECT $1 as a
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql
index 86f007e85524..6b81230f1869 100644
--- a/contrib/pg_stat_statements/sql/level_tracking.sql
+++ b/contrib/pg_stat_statements/sql/level_tracking.sql
@@ -334,32 +334,6 @@ END; $$;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C", toplevel;
--- DO block --- multiple inner queries with separators
-SET pg_stat_statements.track = 'all';
-SET pg_stat_statements.track_utility = TRUE;
-CREATE TABLE pgss_do_util_tab_1 (a int);
-CREATE TABLE pgss_do_util_tab_2 (a int);
-SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-DO $$
-DECLARE BEGIN
- EXECUTE 'CREATE TABLE pgss_do_table (id INT); DROP TABLE pgss_do_table';
- EXECUTE 'SELECT a FROM pgss_do_util_tab_1; SELECT a FROM pgss_do_util_tab_2';
-END $$;
-SELECT toplevel, calls, rows, query FROM pg_stat_statements
- WHERE toplevel IS FALSE
- ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset() IS NOT NULL AS t;
--- Note the extra semicolon at the end of the query.
-DO $$
-DECLARE BEGIN
- EXECUTE 'CREATE TABLE pgss_do_table (id INT); DROP TABLE pgss_do_table;';
- EXECUTE 'SELECT a FROM pgss_do_util_tab_1; SELECT a FROM pgss_do_util_tab_2;';
-END $$;
-SELECT toplevel, calls, rows, query FROM pg_stat_statements
- WHERE toplevel IS FALSE
- ORDER BY query COLLATE "C";
-DROP TABLE pgss_do_util_tab_1, pgss_do_util_tab_2;
-
-- PL/pgSQL function - top-level tracking.
SET pg_stat_statements.track = 'top';
SET pg_stat_statements.track_utility = FALSE;
diff --git a/contrib/pg_stat_statements/sql/planning.sql b/contrib/pg_stat_statements/sql/planning.sql
index 9cfe206b3b04..46f5d9b951c4 100644
--- a/contrib/pg_stat_statements/sql/planning.sql
+++ b/contrib/pg_stat_statements/sql/planning.sql
@@ -20,11 +20,11 @@ SELECT 42;
SELECT 42;
SELECT 42;
SELECT plans, calls, rows, query FROM pg_stat_statements
- WHERE query NOT LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C";
+ WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
-- for the prepared statement we expect at least one replan, but cache
-- invalidations could force more
SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_stat_statements
- WHERE query LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C";
+ WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
-- Cleanup
DROP TABLE stats_plan_test;
--
2.49.0
[application/pgp-signature] signature.asc (832B, ../../aEfGl9bNPR5C76W2@paquier.xyz/3-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-10 10:02 Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 15+ messages in thread
From: Anthonin Bonnefoy @ 2025-06-10 10:02 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Tue, Jun 10, 2025 at 7:46 AM Michael Paquier <michael@paquier.xyz> wrote:
> Touché. That means messing with any parent Node that includes or
> touches select_with_parens, select_no_parens (JSON_ARRAY has one) or
> SelectStmt (few of these, like in CTAS). That's not cool in the long
> term because we would expect any new node that englobes a SelectStmt
> to be able to set up their inner length and location as they should,
> as far as I get it. I was wondering first if we could have done
> something with the top-level statement depending on the nesting level
> of PGSS, but that's not going to fly high, for example with cases like
> this one (any case that has an inner SELECT):
> copy ((select 1) union ((select 1)) fetch first 1 row only) to stdout
One wrong assumption I made was that parenthesis aren't part of the
select statement, which is definitely wrong with queries like '(SELECT
1) limit 1;'. As we don't have end location tracking, we don't have
the possibility to get the length of the select statement.
I've joined a possible (and very rough, this would definitely require
more tests) fix:
- SelectStmt's location is now the outermost '(' position
- Don't track the length anymore in 'select_no_parens'
- Nodes that are embedding a SelectStatement would need to update its
length if necessary. For COPY, this is straightforward as we can use
the parenthesis location surrounding the statement. With CTAS,
SelectStmt's length should be updated if there's an existing
opt_with_data.
> Anyway, this is the second bug that we have in this area, and this one
> is worse. Now that we are in the middle of beta, this is gathering
> the signs that we should revert the change from the tree for the
> moment and potentially revisit the whole in v19 with these edge cases
> handled. So attached is a patch doing a revert of:
> 499edb09741b, nested statement tracking.
> 06450c7b8c70, follow-up fix for 499edb09741b.
That's fair. I can get a better patch with the approach mentioned, but
I don't know if that would be enough to cover all edge cases. Though
reverting this is also going to break pgAudit tests if I remember
correctly.
Regards,
Anthonin
Attachments:
[application/octet-stream] v01-0001-Fix-nested-select-tracking.patch (4.0K, ../../CAO6_Xqoxsm_Y5dxc_4YmrhRACTBe-Y8Q=PP6Xv-svo6eud1-Mg@mail.gmail.com/2-v01-0001-Fix-nested-select-tracking.patch)
download | inline diff:
From 198b720ddfa5de5c217e8b96293faa299dba439b Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Tue, 10 Jun 2025 11:04:58 +0200
Subject: Fix nested select tracking
Currently, we track the start of a select statement using SELECT's
position. However, this doesn't work for statements like '(SELECT 1)
limit 1;'.
This patch instead tracks the start of the select_stmt to the outermost
'(' and we don't track the select's statement length anymore.
The only place where length was necessary was for COPY's
PreparedStatement. This is replaced by directly setting the location and
length of PreparedStatement when CopyStmt is constructed.
This doesn't handle queries like 'CREATE TEMPORARY TABLE pgss_ctas_1
AS (SELECT 1) LIMIT 1 WITH DATA;' as the reported nested query will be
'(SELECT 1) LIMIT 1 WITH DATA'
---
src/backend/parser/gram.y | 39 +++++++++++++++++++++------------------
1 file changed, 21 insertions(+), 18 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0b5652071d1..72aabc09cfc 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -154,7 +154,7 @@ static void base_yyerror(YYLTYPE *yylloc, core_yyscan_t yyscanner,
const char *msg);
static RawStmt *makeRawStmt(Node *stmt, int stmt_location);
static void updateRawStmtEnd(RawStmt *rs, int end_location);
-static void updatePreparableStmtEnd(Node *n, int end_location);
+static void updatePreparableStmtEnd(Node *n, int location, int len);
static Node *makeColumnRef(char *colname, List *indirection,
int location, core_yyscan_t yyscanner);
static Node *makeTypeCast(Node *arg, TypeName *typename, int location);
@@ -3417,7 +3417,7 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list
{
CopyStmt *n = makeNode(CopyStmt);
- updatePreparableStmtEnd($3, @4);
+ updatePreparableStmtEnd($3, @2, @4 - @2);
n->relation = NULL;
n->query = $3;
n->attlist = NIL;
@@ -12828,18 +12828,16 @@ select_with_parens:
'(' select_no_parens ')'
{
SelectStmt *n = (SelectStmt *) $2;
-
- /*
- * As SelectStmt's location starts at the SELECT keyword,
- * we need to track the length of the SelectStmt within
- * parentheses to be able to extract the relevant part
- * of the query. Without this, the RawStmt's length would
- * be used and would include the closing parenthesis.
- */
- n->stmt_len = @3 - @2;
+ /* Track the outermost '(' as the select statement start */
+ n->stmt_location = @1;
+ $$ = $2;
+ }
+ | '(' select_with_parens ')'
+ {
+ SelectStmt *n = (SelectStmt *) $2;
+ n->stmt_location = @1;
$$ = $2;
}
- | '(' select_with_parens ')' { $$ = $2; }
;
/*
@@ -18753,37 +18751,42 @@ updateRawStmtEnd(RawStmt *rs, int end_location)
* string.
*/
static void
-updatePreparableStmtEnd(Node *n, int end_location)
+updatePreparableStmtEnd(Node *n, int location, int len)
{
if (IsA(n, SelectStmt))
{
SelectStmt *stmt = (SelectStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else if (IsA(n, InsertStmt))
{
InsertStmt *stmt = (InsertStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else if (IsA(n, UpdateStmt))
{
UpdateStmt *stmt = (UpdateStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else if (IsA(n, DeleteStmt))
{
DeleteStmt *stmt = (DeleteStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else if (IsA(n, MergeStmt))
{
MergeStmt *stmt = (MergeStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else
elog(ERROR, "unexpected node type %d", (int) n->type);
--
2.49.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-10 11:14 Michael Paquier <michael@paquier.xyz>
parent: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2025-06-10 11:14 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Tue, Jun 10, 2025 at 12:02:06PM +0200, Anthonin Bonnefoy wrote:
> One wrong assumption I made was that parenthesis aren't part of the
> select statement, which is definitely wrong with queries like '(SELECT
> 1) limit 1;'. As we don't have end location tracking, we don't have
> the possibility to get the length of the select statement.
Yeah.
> I've joined a possible (and very rough, this would definitely require
> more tests) fix:
> - SelectStmt's location is now the outermost '(' position
> - Don't track the length anymore in 'select_no_parens'
> - Nodes that are embedding a SelectStatement would need to update its
> length if necessary. For COPY, this is straightforward as we can use
> the parenthesis location surrounding the statement. With CTAS,
> SelectStmt's length should be updated if there's an existing
> opt_with_data.
Hmm. I am wondering whether this would be enough; these are not the
only places where we use select_no_parens and select_with_parens. I'd
be curious to see what you think a fully-bloomed fix should look like.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aEgToLnBX9W43ne-@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-10 22:39 Michael Paquier <michael@paquier.xyz>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2025-06-10 22:39 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Tue, Jun 10, 2025 at 08:14:40PM +0900, Michael Paquier wrote:
> Hmm. I am wondering whether this would be enough; these are not the
> only places where we use select_no_parens and select_with_parens. I'd
> be curious to see what you think a fully-bloomed fix should look like.
By the way, this report also means that none of the problematic query
patterns are run in the main regression test suite, or we would have
noticed something immediately when running the recovery test
027_stream_regress.pl that loads PGSS.
So we have a problem in test coverage with query normalization
patterns. But we also have a second, older, problem in terms of
coverage regarding the parentheses patterns that are assumed as
supported in the parser. Even if we finish by reverting the nested
query tracking support for now (let's settle things before beta2 not
after it), we should definitely improve the main regression test suite
in this area to make sure that reintroducing it safe.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aEi0N9AmJnrFC6Ug@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-10 22:57 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2025-06-10 22:57 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On 2025/06/11 7:39, Michael Paquier wrote:
> On Tue, Jun 10, 2025 at 08:14:40PM +0900, Michael Paquier wrote:
>> Hmm. I am wondering whether this would be enough; these are not the
>> only places where we use select_no_parens and select_with_parens. I'd
>> be curious to see what you think a fully-bloomed fix should look like.
>
> By the way, this report also means that none of the problematic query
> patterns are run in the main regression test suite, or we would have
> noticed something immediately when running the recovery test
> 027_stream_regress.pl that loads PGSS.
>
> So we have a problem in test coverage with query normalization
> patterns. But we also have a second, older, problem in terms of
> coverage regarding the parentheses patterns that are assumed as
> supported in the parser. Even if we finish by reverting the nested
> query tracking support for now (let's settle things before beta2 not
> after it), we should definitely improve the main regression test suite
> in this area to make sure that reintroducing it safe.
Sounds reasonable to me.
Also I agree with reverting the commit and revisiting the feature in v19.
Regards,
--
Fujii Masao
NTT DATA Japan Corporation
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-11 00:03 Michael Paquier <michael@paquier.xyz>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2025-06-11 00:03 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Wed, Jun 11, 2025 at 07:57:19AM +0900, Fujii Masao wrote:
> Also I agree with reverting the commit and revisiting the feature in v19.
Yeah, thanks for the input. If we rely on last year's schedule, v17
beta2 was tagged on the 24th of June, meaning that we should have
approximately a bit more than one week to settle entirely the matter
of this thread. That's just too short in my experience seeing the
query patterns to analyze, and we are dealing with what is going to be
a stable branch very soon.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aEjH0x7licySiK_L@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-11 09:04 Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 15+ messages in thread
From: Anthonin Bonnefoy @ 2025-06-11 09:04 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
Here's a v2 for the attempted fix. This is using the mentioned approach:
- SelectStmt's location is set to Select's position when the node if built
- If there are surrounding parentheses, we include them and move the
start of the statement to the outermost '('
The Statement length is only set for:
- COPY: We use the PreparableStatement's surrounding '()' to set both
location and length (setting location should be redundant here though)
- CTAS: we set the statement's length if there's a tailing 'WITH (NO) DATA'.
- Other than those, the statement length will fallback to use the top
RawStmt's remaining length.
I haven't done it for Views and JSON_ARRAY. Their nested queries
aren't currently tracked and reported by pgss so there's no way to
test those. This could be done but there's no available way to test
this is correct AFAIK?
Attachments:
[application/octet-stream] v02-0001-Fix-location-and-length-tracking-of-select-state.patch (19.9K, ../../CAO6_XqpxskZZdhgNJ0xQ1cyX3Br=11iSAb_1GLQHO1=OziKNoQ@mail.gmail.com/2-v02-0001-Fix-location-and-length-tracking-of-select-state.patch)
download | inline diff:
From 40c3ea62fc7ffc31439f42d524b2768dd0120cc0 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Tue, 10 Jun 2025 11:04:58 +0200
Subject: Fix location and length tracking of select statement
Previously, we tracked the start of a select statement using SELECT's
position. However, this doesn't work for statements like '(SELECT 1)
limit 1;' as the 'limit 1' needs to be part of the statement and thus,
the parenthesis needs to be included.
This patch instead tracks the start of the select_stmt to the outermost
'(' if present. The statement's length is only filled where we can
reliably get the information:
- COPY: We use the position of the '()' surrounding the PreparableStmt
- CTAS: Use the location of the opt_with_data to update the length.
Outside of those cases, the statement length is unset by default and it
will fallback to the RawStmt's length to compute the remaining length.
---
.../expected/level_tracking.out | 84 +++++++++++--------
.../pg_stat_statements/expected/select.out | 10 ++-
.../pg_stat_statements/sql/level_tracking.sql | 12 ++-
contrib/pg_stat_statements/sql/select.sql | 3 +
src/backend/parser/gram.y | 54 ++++++++----
5 files changed, 109 insertions(+), 54 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index 75e785e1719..a078822a89b 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -208,6 +208,9 @@ SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
toplevel | calls | query
----------+-------+--------------------------------------------------------------------
+ f | 1 | (SELECT $1, $2)
+ f | 1 | (TABLE test_table)
+ f | 1 | (VALUES ($1, $2))
f | 1 | DELETE FROM stats_track_tab
t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2)
t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table)
@@ -230,13 +233,10 @@ SELECT toplevel, calls, query FROM pg_stat_statements
| | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
f | 1 | SELECT $1
f | 1 | SELECT $1 UNION SELECT $2
- f | 1 | SELECT $1, $2
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
f | 1 | TABLE stats_track_tab
- f | 1 | TABLE test_table
f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2
f | 1 | VALUES ($1)
- f | 1 | VALUES ($1, $2)
(23 rows)
-- EXPLAIN - top-level tracking.
@@ -407,7 +407,9 @@ SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
toplevel | calls | query
----------+-------+-----------------------------------------------------------------
+ f | 1 | (SELECT $1, $2, $3)
f | 1 | (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
+ f | 1 | (SELECT $1, $2, $3, $4)
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)
@@ -417,8 +419,6 @@ SELECT toplevel, calls, query FROM pg_stat_statements
f | 1 | SELECT $1
f | 1 | SELECT $1, $2
f | 1 | SELECT $1, $2 UNION SELECT $3, $4
- f | 1 | SELECT $1, $2, $3
- f | 1 | SELECT $1, $2, $3, $4
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(13 rows)
@@ -496,6 +496,8 @@ SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
toplevel | calls | query
----------+-------+--------------------------------------------------------------------
+ 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
t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table)
@@ -512,11 +514,9 @@ SELECT toplevel, calls, query FROM pg_stat_statements
f | 1 | INSERT INTO stats_track_tab VALUES (($1))
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
f | 1 | TABLE stats_track_tab
- f | 1 | TABLE test_table
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)
- f | 1 | VALUES ($1, $2)
(21 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
@@ -788,6 +788,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
toplevel | calls | query
----------+-------+------------------------------------------------------------------------------------------
+ f | 1 | (WITH a AS (SELECT $1) (SELECT $2, $3))
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))
@@ -799,7 +800,6 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3
t | 1 | EXPLAIN (COSTS OFF) WITH a AS (select $1) SELECT $2 UNION SELECT $3
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- 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 +
@@ -1041,17 +1041,33 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
(1 row)
CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT 1;
-CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss;
+CREATE TEMPORARY TABLE pgss_ctas_2 AS SELECT 1 as a, 2 as b WITH DATA;
+CREATE TEMPORARY TABLE pgss_ctas_3 AS EXECUTE test_prepare_pgss;
+CREATE TEMPORARY TABLE pgss_ctas_5 AS (SELECT (SELECT 2)) WITH DATA;
+CREATE TEMPORARY TABLE pgss_ctas_6 AS (SELECT (SELECT ((SELECT 3)) limit 1) order by 1) WITH DATA;
+CREATE TEMPORARY TABLE pgss_ctas_7 AS (SELECT (SELECT (SELECT 3))) WITH NO DATA;
+CREATE TEMPORARY TABLE IF NOT EXISTS pgss_ctas_8 AS SELECT 1 as a;
+CREATE TEMPORARY TABLE IF NOT EXISTS pgss_ctas_9 AS ((SELECT 1 as b) limit 1) WITH DATA;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+-----------------------------------------------------------------
+ toplevel | calls | query
+----------+-------+-----------------------------------------------------------------------------------------------------
+ f | 1 | ((SELECT $1 as b) limit $2)
+ f | 1 | (SELECT (SELECT $1))
+ f | 1 | (SELECT (SELECT ((SELECT $1)) limit $2) order by 1)
+ t | 1 | CREATE TEMPORARY TABLE IF NOT EXISTS pgss_ctas_8 AS SELECT $1 as a
+ t | 1 | CREATE TEMPORARY TABLE IF NOT EXISTS pgss_ctas_9 AS ((SELECT $1 as b) limit $2) WITH DATA
t | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1
- t | 1 | CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss
- f | 1 | SELECT $1
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_2 AS SELECT $1 as a, $2 as b WITH DATA
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_3 AS EXECUTE test_prepare_pgss
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_5 AS (SELECT (SELECT $1)) WITH DATA
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_6 AS (SELECT (SELECT ((SELECT $1)) limit $2) order by 1) WITH DATA
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_7 AS (SELECT (SELECT (SELECT $1))) WITH NO DATA
+ f | 2 | SELECT $1
+ f | 1 | SELECT $1 as a, $2 as b
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
f | 1 | select generate_series($1, $2)
-(5 rows)
+(15 rows)
-- CREATE TABLE AS, top-level tracking.
SET pg_stat_statements.track = 'top';
@@ -1061,14 +1077,14 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
(1 row)
-CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT 1;
-CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss;
+CREATE TEMPORARY TABLE pgss_ctas_top_1 AS SELECT 1;
+CREATE TEMPORARY TABLE pgss_ctas_top_2 AS EXECUTE test_prepare_pgss;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY 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
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_top_1 AS SELECT $1
+ t | 1 | CREATE TEMPORARY TABLE pgss_ctas_top_2 AS EXECUTE test_prepare_pgss
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(3 rows)
@@ -1205,23 +1221,23 @@ SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY 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
- f | 1 | DELETE FROM stats_track_tab WHERE x = $1 RETURNING x
- f | 1 | INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x
- f | 1 | MERGE INTO stats_track_tab USING (SELECT $1 id) ON x = id +
+ f | 1 | (DELETE FROM stats_track_tab WHERE x = $1 RETURNING x
+ f | 1 | (INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x
+ f | 1 | (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
- f | 1 | SELECT $1
- f | 1 | SELECT $1 UNION SELECT $2
+ f | 1 | (SELECT $1
+ f | 1 | (SELECT $1 UNION SELECT $2
+ f | 1 | (UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x
+ 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 | UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x
(13 rows)
-- COPY - top-level tracking.
diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out
index 038ae110364..beb21ff9002 100644
--- a/contrib/pg_stat_statements/expected/select.out
+++ b/contrib/pg_stat_statements/expected/select.out
@@ -185,6 +185,13 @@ select '{"a":1, "b":2}'::jsonb ? 'b';
t
(1 row)
+-- values with limit
+(values(1)) limit 1;
+ column1
+---------
+ 1
+(1 row)
+
-- cte
WITH t(f) AS (
VALUES (1.0), (2.0)
@@ -208,6 +215,7 @@ DEALLOCATE pgss_test;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
calls | rows | query
-------+------+------------------------------------------------------------------------------
+ 1 | 1 | (values($1)) limit $2
4 | 4 | SELECT $1 +
| | -- but this one will appear +
| | AS "text"
@@ -230,7 +238,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
| | ) +
| | SELECT f FROM t ORDER BY f
1 | 1 | select $1::jsonb ? $2
-(17 rows)
+(18 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql
index 86f007e8552..7a961252b05 100644
--- a/contrib/pg_stat_statements/sql/level_tracking.sql
+++ b/contrib/pg_stat_statements/sql/level_tracking.sql
@@ -225,15 +225,21 @@ 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;
+CREATE TEMPORARY TABLE pgss_ctas_2 AS SELECT 1 as a, 2 as b WITH DATA;
+CREATE TEMPORARY TABLE pgss_ctas_3 AS EXECUTE test_prepare_pgss;
+CREATE TEMPORARY TABLE pgss_ctas_5 AS (SELECT (SELECT 2)) WITH DATA;
+CREATE TEMPORARY TABLE pgss_ctas_6 AS (SELECT (SELECT ((SELECT 3)) limit 1) order by 1) WITH DATA;
+CREATE TEMPORARY TABLE pgss_ctas_7 AS (SELECT (SELECT (SELECT 3))) WITH NO DATA;
+CREATE TEMPORARY TABLE IF NOT EXISTS pgss_ctas_8 AS SELECT 1 as a;
+CREATE TEMPORARY TABLE IF NOT EXISTS pgss_ctas_9 AS ((SELECT 1 as b) limit 1) WITH DATA;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY 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;
+CREATE TEMPORARY TABLE pgss_ctas_top_1 AS SELECT 1;
+CREATE TEMPORARY TABLE pgss_ctas_top_2 AS EXECUTE test_prepare_pgss;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql
index 189d405512f..ff9328fbe02 100644
--- a/contrib/pg_stat_statements/sql/select.sql
+++ b/contrib/pg_stat_statements/sql/select.sql
@@ -65,6 +65,9 @@ SELECT 1 AS i UNION SELECT 2 ORDER BY i;
-- ? operator
select '{"a":1, "b":2}'::jsonb ? 'b';
+-- values with limit
+(values(1)) limit 1;
+
-- cte
WITH t(f) AS (
VALUES (1.0), (2.0)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0b5652071d1..eb69162866e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -154,7 +154,8 @@ static void base_yyerror(YYLTYPE *yylloc, core_yyscan_t yyscanner,
const char *msg);
static RawStmt *makeRawStmt(Node *stmt, int stmt_location);
static void updateRawStmtEnd(RawStmt *rs, int end_location);
-static void updatePreparableStmtEnd(Node *n, int end_location);
+static void updatePreparableStmtEnd(Node *n, int location, int len);
+static void updateSelectStmtEnd(Node *n, int end_location);
static Node *makeColumnRef(char *colname, List *indirection,
int location, core_yyscan_t yyscanner);
static Node *makeTypeCast(Node *arg, TypeName *typename, int location);
@@ -3417,9 +3418,9 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list
{
CopyStmt *n = makeNode(CopyStmt);
- updatePreparableStmtEnd($3, @4);
n->relation = NULL;
n->query = $3;
+ updatePreparableStmtEnd($3, @2, @4 - @2);
n->attlist = NIL;
n->is_from = false;
n->is_program = $6;
@@ -4805,6 +4806,7 @@ CreateAsStmt:
CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
ctas->query = $6;
+ updateSelectStmtEnd($6, @7);
ctas->into = $4;
ctas->objtype = OBJECT_TABLE;
ctas->is_select_into = false;
@@ -4819,6 +4821,7 @@ CreateAsStmt:
CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
ctas->query = $9;
+ updateSelectStmtEnd($9, @10);
ctas->into = $7;
ctas->objtype = OBJECT_TABLE;
ctas->is_select_into = false;
@@ -12828,18 +12831,20 @@ select_with_parens:
'(' select_no_parens ')'
{
SelectStmt *n = (SelectStmt *) $2;
-
/*
- * As SelectStmt's location starts at the SELECT keyword,
- * we need to track the length of the SelectStmt within
- * parentheses to be able to extract the relevant part
- * of the query. Without this, the RawStmt's length would
- * be used and would include the closing parenthesis.
+ * With queries like '(SELECT 1) limit 1', parentheses are part of the
+ * query so we need to move the location of the select stmt to the '('
*/
- n->stmt_len = @3 - @2;
+ n->stmt_location = @1;
+ $$ = $2;
+ }
+ | '(' select_with_parens ')'
+ {
+ SelectStmt *n = (SelectStmt *) $2;
+ /* Track the outermost '(' as the select statement start */
+ n->stmt_location = @1;
$$ = $2;
}
- | '(' select_with_parens ')' { $$ = $2; }
;
/*
@@ -18753,42 +18758,59 @@ updateRawStmtEnd(RawStmt *rs, int end_location)
* string.
*/
static void
-updatePreparableStmtEnd(Node *n, int end_location)
+updatePreparableStmtEnd(Node *n, int location, int len)
{
if (IsA(n, SelectStmt))
{
SelectStmt *stmt = (SelectStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else if (IsA(n, InsertStmt))
{
InsertStmt *stmt = (InsertStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else if (IsA(n, UpdateStmt))
{
UpdateStmt *stmt = (UpdateStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else if (IsA(n, DeleteStmt))
{
DeleteStmt *stmt = (DeleteStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else if (IsA(n, MergeStmt))
{
MergeStmt *stmt = (MergeStmt *) n;
- stmt->stmt_len = end_location - stmt->stmt_location;
+ stmt->stmt_location = location;
+ stmt->stmt_len = len;
}
else
elog(ERROR, "unexpected node type %d", (int) n->type);
}
+/*
+ * Adjust SelectStmt's length if we have a known end location
+ */
+static void
+updateSelectStmtEnd(Node *n, int end_location)
+{
+ SelectStmt *stmt = (SelectStmt *) n;
+ if (end_location > -1)
+ stmt->stmt_len = end_location - stmt->stmt_location;
+}
+
+
static Node *
makeColumnRef(char *colname, List *indirection,
int location, core_yyscan_t yyscanner)
--
2.49.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-11 23:46 Michael Paquier <michael@paquier.xyz>
parent: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2025-06-11 23:46 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Wed, Jun 11, 2025 at 11:04:36AM +0200, Anthonin Bonnefoy wrote:
> Here's a v2 for the attempted fix. This is using the mentioned approach:
> - SelectStmt's location is set to Select's position when the node if built
> - If there are surrounding parentheses, we include them and move the
> start of the statement to the outermost '('
>
> The Statement length is only set for:
> - COPY: We use the PreparableStatement's surrounding '()' to set both
> location and length (setting location should be redundant here though)
> - CTAS: we set the statement's length if there's a tailing 'WITH (NO) DATA'.
> - Other than those, the statement length will fallback to use the top
> RawStmt's remaining length.
@@ -3417,9 +3418,9 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list
[...]
- updatePreparableStmtEnd($3, @2, @4 - @2);
+ updatePreparableStmtEnd($3, @3, @4 - @3);
For the case of COPY, it is possible to get a much better report by
setting the location and length to not include the first parenthesis,
like that.
> I haven't done it for Views and JSON_ARRAY. Their nested queries
> aren't currently tracked and reported by pgss so there's no way to
> test those.
I have to admit that it is inconsistent to set a location in their
respective inner nodes while we don't use updateSelectStmtEnd() to
adjust the length based on the location assigned by the parser.
There's a risk of having a code path in the future looking at the
location later on based on the parsed state and taking a wrong
decision because the location and the length do not match. Such cases
could be easier to reach outside of core, for example imagine an
extension looking at a ViewStmt where we set the location but not a
matching length. We would consider as inner query a string that
begins at the beginning of the location until the end of the string
because the length is not set to match with what the parser finds.
> This could be done but there's no available way to test
> this is correct AFAIK?
Yes, perhaps we should think harder about this part. There are two
patterns among a few others like UNION, which can also be used in
views.
Anyway, I am going to pull the plug on this one today, because we need
to take a decision with beta2 approaching fast but it's mostly me
being not fully confident that this is right as-is. If we're wrong in
one path, that's an out-of-bound memory write waiting for us in PGSS
for the query normalization, as this report proves, and that would be
bad.
The first step would be to improve the main regression test suite for
all these grammar "parenthesis" patterns we have never had coverage
for in core. I am wondering if it is actually possible to reach an
inconsistent state even in older stable branches because we've never
tested all these grammar patterns. Perhaps I'm wrong, but we would be
more confident with all these extra patterns stressed first by
027_stream_regress.pl which forces queries to have some normalization
applied.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aEoVUjPKJWWWM4QE@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-12 23:50 Michael Paquier <michael@paquier.xyz>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2025-06-12 23:50 UTC (permalink / raw)
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Thu, Jun 12, 2025 at 08:46:26AM +0900, Michael Paquier wrote:
> Anyway, I am going to pull the plug on this one today, because we need
> to take a decision with beta2 approaching fast but it's mostly me
> being not fully confident that this is right as-is. If we're wrong in
> one path, that's an out-of-bound memory write waiting for us in PGSS
> for the query normalization, as this report proves, and that would be
> bad.
Two things that I have in mind regarding all that, worth dumping now:
- 06450c7b8c70 is reverted, but I have kept the test introduced in
this commit to check the location of multiple queries separated by
semicolons. This was something that we failed to track in the tree,
with David Steele's pgaudit only providing coverage.
- About the tests we could do to validate more in depth the locations
and lengths assigned to the parsed node without relying on PGSS and
EXPLAIN, which will support never support the full range of things
like views. I was wondering about the addition of a test module that
plugs into one or more hooks (only the post-parse one should be
enough), where we simply print the Nodes generated to a string using
the facility in src/backend/nodes/print.c. It would be then possible
to filter the output generated with some regex magic to print the
fields we want to check. That would work for the CREATE VIEW case,
for example, and it could be used for other things than just the
statement length and/or locations set in a gIven Node.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aEtn1pJRWzJpnd1K@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements
@ 2025-06-18 06:33 Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 15+ messages in thread
From: Anthonin Bonnefoy @ 2025-06-18 06:33 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Dilip Kumar <dilipbalaut@gmail.com>; a.kozhemyakin@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Fri, Jun 13, 2025 at 1:51 AM Michael Paquier <michael@paquier.xyz> wrote:
> - About the tests we could do to validate more in depth the locations
> and lengths assigned to the parsed node without relying on PGSS and
> EXPLAIN, which will support never support the full range of things
> like views. I was wondering about the addition of a test module that
> plugs into one or more hooks (only the post-parse one should be
> enough), where we simply print the Nodes generated to a string using
> the facility in src/backend/nodes/print.c. It would be then possible
> to filter the output generated with some regex magic to print the
> fields we want to check. That would work for the CREATE VIEW case,
> for example, and it could be used for other things than just the
> statement length and/or locations set in a gIven Node.
Agree on the tests. Having a test module that dumps the parse tree in
an usable way for the tests was definitely something I had in mind.
Relying on PGSS to test parse behaviour definitely has its limit.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2025-06-18 06:33 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-06 06:39 BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements PG Bug reporting form <noreply@postgresql.org>
2025-06-07 16:49 ` Dilip Kumar <dilipbalaut@gmail.com>
2025-06-08 02:39 ` Michael Paquier <michael@paquier.xyz>
2025-06-08 10:25 ` Dilip Kumar <dilipbalaut@gmail.com>
2025-06-09 10:07 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2025-06-10 05:45 ` Michael Paquier <michael@paquier.xyz>
2025-06-10 10:02 ` Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
2025-06-10 11:14 ` Michael Paquier <michael@paquier.xyz>
2025-06-10 22:39 ` Michael Paquier <michael@paquier.xyz>
2025-06-10 22:57 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2025-06-11 00:03 ` Michael Paquier <michael@paquier.xyz>
2025-06-11 09:04 ` Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
2025-06-11 23:46 ` Michael Paquier <michael@paquier.xyz>
2025-06-12 23:50 ` Michael Paquier <michael@paquier.xyz>
2025-06-18 06:33 ` Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox