pg_stat_statements: Fix nested tracking for implicitly closed cursors
Hi,
It was brought to my attention that there is pg_stat_statements
behavior where an implicitly closed cursor, via COMMIT or END,
is stored as toplevel for both the utility DECLARE CURSOR
statement and the underlying query.
```
BEGIN;
DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
FETCH FORWARD 1 FROM foocur;
x
---
(0 rows)
COMMIT;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
toplevel | calls | query
----------+-------+----------------------------------------------------------
t | 1 | BEGIN
t | 1 | COMMIT
t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
t | 1 | FETCH FORWARD $1 FROM foocur
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(6 rows)
```
Also, with track_planning enabled, the underlying query is
stored with toplevel = false for the plans counter and with
toplevel = true for the calls counter, resulting in an
additional entry.
```
SELECT toplevel, calls, plans, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
toplevel | calls | plans | query
----------+-------+-------+--------------------------------------------------------------
t | 1 | 0 | BEGIN
t | 1 | 0 | COMMIT
t | 1 | 0 | DECLARE FOOCUR CURSOR FOR SELECT * from
stats_track_tab
t | 1 | 0 | DECLARE FOOCUR CURSOR FOR SELECT * from
stats_track_tab;
f | 0 | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from
stats_track_tab;
t | 1 | 0 | FETCH FORWARD $1 FROM foocur
t | 1 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
t | 0 | 1 | SELECT toplevel, calls, plans, query FROM
pg_stat_statements+
| | | ORDER BY query COLLATE "C"
```
The reason this occurs is because PortalCleanup, which triggers
ExecutorEnd, runs after the ProcessUtility hook. At that point,
nesting_level has already been reset back to 0.
I am not sure how common this pattern is, but it is probably
worth fixing. At a minimum, we need tests to show this behavior,
but we can do better by checking whether we just processed a
COMMIT statement and setting a flag to let ExecutorEnd increment
nesting_level. There should be no other way to reach ExecutorEnd
after a COMMIT besides PortalCleanup, AFAICT. I could be proven
wrong.
The attached patch fixes this as described above.
Note that, due to f85f6ab051b7, there is a separate issue that
should be improved. Tracking the underlying SQL of a utility
statement using the utility statement itself is confusing
and should be fixed. That is a separate point, but I am
mentioning it here for clarity.
--
Sami Imseih
Amazon Web Services
Attachments:
v1-0001-pg_stat_statements-Fix-nested-tracking-for-implic.patchapplication/octet-stream; name=v1-0001-pg_stat_statements-Fix-nested-tracking-for-implic.patchDownload
From 72124999d7958766fdd3f8f4d527354260f03e54 Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Tue, 23 Dec 2025 22:33:28 +0000
Subject: [PATCH v1 1/1] pg_stat_statements: Fix nested tracking for implicitly
closed cursors
When cursors are implicitly closed during COMMIT, pg_stat_statements
incorrectly marks the cursor's underlying query as toplevel=true because
PortalCleanup, which triggers ExecutorEnd, runs after ProcessUtility and
at which point the nesting_level is 0.
Fix by adding an is_txn_end flag to detect COMMIT statements and
temporarily adjust nesting_level in ExecutorEnd.
Add regression tests for both explicit and implicit cursor closure,
with track = 'all' and with and without track_planning.
---
.../expected/level_tracking.out | 152 ++++++++++++++++++
.../pg_stat_statements/pg_stat_statements.c | 25 +++
.../pg_stat_statements/sql/level_tracking.sql | 53 ++++++
3 files changed, 230 insertions(+)
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index 8e8388dd5cb..3e0ad5cfba8 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -1122,6 +1122,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements
(2 rows)
-- DECLARE CURSOR, all-level tracking.
+-- Explicitly close cursor
SET pg_stat_statements.track = 'all';
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
@@ -1151,6 +1152,157 @@ SELECT toplevel, calls, query FROM pg_stat_statements
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(7 rows)
+-- Implicitly close cursor
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+ x
+---
+(0 rows)
+
+COMMIT;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------
+ t | 1 | BEGIN
+ 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
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(6 rows)
+
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+ x
+---
+(0 rows)
+
+END;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------
+ t | 1 | BEGIN
+ t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
+ f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+ t | 1 | END
+ t | 1 | FETCH FORWARD $1 FROM foocur
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(6 rows)
+
+-- DECLARE CURSOR, all-level tracking with track_planning
+-- Explicitly close cursor
+SET pg_stat_statements.track = 'all';
+SET pg_stat_statements.track_planning = 'on';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+ x
+---
+(0 rows)
+
+CLOSE foocur;
+COMMIT;
+SELECT toplevel, calls, plans, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+ toplevel | calls | plans | query
+----------+-------+-------+--------------------------------------------------------------
+ t | 1 | 0 | BEGIN
+ t | 1 | 0 | CLOSE foocur
+ t | 1 | 0 | COMMIT
+ t | 1 | 0 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
+ f | 1 | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+ t | 1 | 0 | FETCH FORWARD $1 FROM foocur
+ t | 1 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 0 | 1 | SELECT toplevel, calls, plans, query FROM pg_stat_statements+
+ | | | ORDER BY query COLLATE "C"
+(8 rows)
+
+-- Implicitly close cursor
+SET pg_stat_statements.track = 'all';
+SET pg_stat_statements.track_planning = 'on';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+ x
+---
+(0 rows)
+
+COMMIT;
+SELECT toplevel, calls, plans, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+ toplevel | calls | plans | query
+----------+-------+-------+--------------------------------------------------------------
+ t | 1 | 0 | BEGIN
+ t | 1 | 0 | COMMIT
+ t | 1 | 0 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
+ f | 1 | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+ t | 1 | 0 | FETCH FORWARD $1 FROM foocur
+ t | 1 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 0 | 1 | SELECT toplevel, calls, plans, query FROM pg_stat_statements+
+ | | | ORDER BY query COLLATE "C"
+(7 rows)
+
+SET pg_stat_statements.track = 'all';
+SET pg_stat_statements.track_planning = 'on';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+ x
+---
+(0 rows)
+
+END;
+SELECT toplevel, calls, plans, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+ toplevel | calls | plans | query
+----------+-------+-------+--------------------------------------------------------------
+ t | 1 | 0 | BEGIN
+ t | 1 | 0 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
+ f | 1 | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+ t | 1 | 0 | END
+ t | 1 | 0 | FETCH FORWARD $1 FROM foocur
+ t | 1 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | 0 | 1 | SELECT toplevel, calls, plans, query FROM pg_stat_statements+
+ | | | ORDER BY query COLLATE "C"
+(7 rows)
+
+RESET pg_stat_statements.track_planning;
-- DECLARE CURSOR, top-level tracking.
SET pg_stat_statements.track = 'top';
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 39208f80b5b..f4643c91afd 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -263,6 +263,9 @@ typedef struct pgssSharedState
/* Current nesting depth of planner/ExecutorRun/ProcessUtility calls */
static int nesting_level = 0;
+/* Flag to adjust nesting level during transaction end */
+static bool is_txn_end = false;
+
/* Saved hook values */
static shmem_request_hook_type prev_shmem_request_hook = NULL;
static shmem_startup_hook_type prev_shmem_startup_hook = NULL;
@@ -1079,6 +1082,9 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
{
int64 queryId = queryDesc->plannedstmt->queryId;
+ if (is_txn_end)
+ nesting_level++;
+
if (queryId != INT64CONST(0) && queryDesc->totaltime &&
pgss_enabled(nesting_level))
{
@@ -1108,6 +1114,12 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
prev_ExecutorEnd(queryDesc);
else
standard_ExecutorEnd(queryDesc);
+
+ if (is_txn_end)
+ {
+ nesting_level--;
+ is_txn_end = false;
+ }
}
/*
@@ -1236,6 +1248,19 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
0,
0,
pstmt->planOrigin);
+
+ /*
+ * Detect COMMIT/END statement to handle implicit cursor cleanup. When
+ * cursors are closed during transaction end, we need to adjust the
+ * nesting level in ExecutorEnd to ensure proper tracking for the
+ * cursor's underlying query.
+ */
+ if (IsA(parsetree, TransactionStmt))
+ {
+ TransactionStmt *stmt = (TransactionStmt *) parsetree;
+
+ is_txn_end = (stmt->kind == TRANS_STMT_COMMIT);
+ }
}
else
{
diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql
index 86f007e8552..f6d4f2d3024 100644
--- a/contrib/pg_stat_statements/sql/level_tracking.sql
+++ b/contrib/pg_stat_statements/sql/level_tracking.sql
@@ -252,6 +252,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
-- DECLARE CURSOR, all-level tracking.
+-- Explicitly close cursor
SET pg_stat_statements.track = 'all';
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
BEGIN;
@@ -261,6 +262,58 @@ CLOSE foocur;
COMMIT;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
+-- Implicitly close cursor
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+COMMIT;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+END;
+SELECT toplevel, calls, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+
+-- DECLARE CURSOR, all-level tracking with track_planning
+-- Explicitly close cursor
+SET pg_stat_statements.track = 'all';
+SET pg_stat_statements.track_planning = 'on';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+CLOSE foocur;
+COMMIT;
+SELECT toplevel, calls, plans, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+-- Implicitly close cursor
+SET pg_stat_statements.track = 'all';
+SET pg_stat_statements.track_planning = 'on';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+COMMIT;
+SELECT toplevel, calls, plans, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+
+SET pg_stat_statements.track = 'all';
+SET pg_stat_statements.track_planning = 'on';
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+BEGIN;
+DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
+FETCH FORWARD 1 FROM foocur;
+END;
+SELECT toplevel, calls, plans, query FROM pg_stat_statements
+ ORDER BY query COLLATE "C";
+RESET pg_stat_statements.track_planning;
-- DECLARE CURSOR, top-level tracking.
SET pg_stat_statements.track = 'top';
--
2.43.0
Hi Sami,
Please add a `PG_TRY` and `PG_FINALLY` to make sure we always reset the
nesting_level.
Also this will break the following scenario
```
BEGIN;
COMMIT;
SELECT 1; -- This will be stored as inner level because COMMIT sets
is_txn_end flag
```
Can we reset is_txn_end at executorStart to solve the problem?
--
Martin Huang
Amazon Web Services
On Fri, Jan 9, 2026 at 1:02 PM Sami Imseih <samimseih@gmail.com> wrote:
Show quoted text
Hi,
It was brought to my attention that there is pg_stat_statements
behavior where an implicitly closed cursor, via COMMIT or END,
is stored as toplevel for both the utility DECLARE CURSOR
statement and the underlying query.```
BEGIN;
DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
FETCH FORWARD 1 FROM foocur;
x
---
(0 rows)COMMIT;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
toplevel | calls | query----------+-------+----------------------------------------------------------
t | 1 | BEGIN
t | 1 | COMMIT
t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from
stats_track_tab;
t | 1 | FETCH FORWARD $1 FROM foocur
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(6 rows)
```Also, with track_planning enabled, the underlying query is
stored with toplevel = false for the plans counter and with
toplevel = true for the calls counter, resulting in an
additional entry.```
SELECT toplevel, calls, plans, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
toplevel | calls | plans | query----------+-------+-------+--------------------------------------------------------------
t | 1 | 0 | BEGIN
t | 1 | 0 | COMMIT
t | 1 | 0 | DECLARE FOOCUR CURSOR FOR SELECT * from
stats_track_tab
t | 1 | 0 | DECLARE FOOCUR CURSOR FOR SELECT * from
stats_track_tab;
f | 0 | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from
stats_track_tab;
t | 1 | 0 | FETCH FORWARD $1 FROM foocur
t | 1 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL
AS t
t | 0 | 1 | SELECT toplevel, calls, plans, query FROM
pg_stat_statements+
| | | ORDER BY query COLLATE "C"
```The reason this occurs is because PortalCleanup, which triggers
ExecutorEnd, runs after the ProcessUtility hook. At that point,
nesting_level has already been reset back to 0.I am not sure how common this pattern is, but it is probably
worth fixing. At a minimum, we need tests to show this behavior,
but we can do better by checking whether we just processed a
COMMIT statement and setting a flag to let ExecutorEnd increment
nesting_level. There should be no other way to reach ExecutorEnd
after a COMMIT besides PortalCleanup, AFAICT. I could be proven
wrong.The attached patch fixes this as described above.
Note that, due to f85f6ab051b7, there is a separate issue that
should be improved. Tracking the underlying SQL of a utility
statement using the utility statement itself is confusing
and should be fixed. That is a separate point, but I am
mentioning it here for clarity.--
Sami Imseih
Amazon Web Services