[BUG] temporary file usage report with extended protocol and unnamed portals

From afb228f07f847c467ba05dbe204861e7be2ffc32 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yhuel@dalibo.com>
Date: Fri, 18 Apr 2025 13:20:52 +0200
Subject: [PATCH] fix reporting of temp files usage

when extended protocol is used with unnamed portals
---
 src/backend/utils/mmgr/portalmem.c | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index e3526e78064..246e711db81 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -27,6 +27,7 @@
 #include "utils/memutils.h"
 #include "utils/snapmgr.h"
 #include "utils/timestamp.h"
+#include "tcop/tcopprot.h"
 
 /*
  * Estimate of the maximum number of open portals a user would have,
@@ -488,6 +489,9 @@ PortalDrop(Portal portal, bool isTopCommit)
 				(errcode(ERRCODE_INVALID_CURSOR_STATE),
 				 errmsg("cannot drop active portal \"%s\"", portal->name)));
 
+	if (portal->queryDesc)
+		debug_query_string = portal->queryDesc->sourceText;
+
 	/*
 	 * Allow portalcmds.c to clean up the state it knows about, in particular
 	 * shutting down the executor if still active.  This step potentially runs
-- 
2.47.2

From b86c752889aabd40e4e2f6ab1b36f090fe0abc6b Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Sat, 19 Apr 2025 08:00:27 -0500
Subject: [PATCH v2 1/1] Fix race condition between debug_query_string and
 PortalDrop
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

It was discovered that temporary file logging could be associated
with incorrect query text. This is because temporary file logging
occurs when the portal is dropped, but this can occur when a new
query starts execution, such as in the case of unnamed portals.
To correct this behavior, PortalDrop should temporarily switch to
using a debug_query_string set to that of the query associated with
the portal being dropped.

Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972%40dalibo.com
---
 src/backend/utils/mmgr/portalmem.c | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index e3526e78064..04224551cdf 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -27,6 +27,7 @@
 #include "utils/memutils.h"
 #include "utils/snapmgr.h"
 #include "utils/timestamp.h"
+#include "tcop/tcopprot.h"
 
 /*
  * Estimate of the maximum number of open portals a user would have,
@@ -469,6 +470,8 @@ MarkPortalFailed(Portal portal)
 void
 PortalDrop(Portal portal, bool isTopCommit)
 {
+	char *save_debug_query_string;
+
 	Assert(PortalIsValid(portal));
 
 	/*
@@ -488,6 +491,17 @@ PortalDrop(Portal portal, bool isTopCommit)
 				(errcode(ERRCODE_INVALID_CURSOR_STATE),
 				 errmsg("cannot drop active portal \"%s\"", portal->name)));
 
+	/*
+	 * By the time this point is reached, a query string different from the one
+	 * referenced by this to-be-dropped portal could be used. Therefore, let's
+	 * temporarily switch the debug_query_string to report on this portal.
+	 */
+	if (portal->queryDesc && debug_query_string)
+	{
+		save_debug_query_string = pstrdup(debug_query_string);
+		debug_query_string = portal->queryDesc->sourceText;
+	}
+
 	/*
 	 * Allow portalcmds.c to clean up the state it knows about, in particular
 	 * shutting down the executor if still active.  This step potentially runs
@@ -596,6 +610,8 @@ PortalDrop(Portal portal, bool isTopCommit)
 	/* release subsidiary storage */
 	MemoryContextDelete(portal->portalContext);
 
+	debug_query_string = save_debug_query_string;
+
 	/* release portal struct (it's in TopPortalContext) */
 	pfree(portal);
 }
-- 
2.39.5 (Apple Git-154)

From 07d331ba0f91b999fcefd12696bfc1eda7e8f20f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yhuel@dalibo.com>
Date: Fri, 18 Apr 2025 13:20:52 +0200
Subject: [PATCH v3] fix reporting of temp files usage

when extended protocol is used with unnamed portals
---
 src/backend/tcop/postgres.c        | 19 +++++++++++++------
 src/backend/utils/mmgr/portalmem.c |  4 ++++
 2 files changed, 17 insertions(+), 6 deletions(-)

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index dc4c600922d..1b0c98828c8 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1018,7 +1018,11 @@ exec_simple_query(const char *query_string)
 	bool		was_logged = false;
 	bool		use_implicit_block;
 	char		msec_str[32];
+	Portal		portal;
 
+	portal = GetPortalByName("");
+	if (PortalIsValid(portal))
+		PortalDrop(portal, false);
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1672,6 +1676,12 @@ exec_bind_message(StringInfo input_message)
 					 errmsg("unnamed prepared statement does not exist")));
 	}
 
+	if (portal_name[0] == '\0')
+	{
+		portal = GetPortalByName(portal_name);
+		if (PortalIsValid(portal))
+			PortalDrop(portal, false);
+	}
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1749,13 +1759,10 @@ exec_bind_message(StringInfo input_message)
 				 errdetail_abort()));
 
 	/*
-	 * Create the portal.  Allow silent replacement of an existing portal only
-	 * if the unnamed portal is specified.
+	 * Create the portal.  Don't allow silent replacement of an existing named portal.
+	 * An unnamed portal should have been removed already.
 	 */
-	if (portal_name[0] == '\0')
-		portal = CreatePortal(portal_name, true, true);
-	else
-		portal = CreatePortal(portal_name, false, false);
+	portal = CreatePortal(portal_name, false, false);
 
 	/*
 	 * Prepare to copy stuff into the portal's memory context.  We do all this
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index e3526e78064..246e711db81 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -27,6 +27,7 @@
 #include "utils/memutils.h"
 #include "utils/snapmgr.h"
 #include "utils/timestamp.h"
+#include "tcop/tcopprot.h"
 
 /*
  * Estimate of the maximum number of open portals a user would have,
@@ -488,6 +489,9 @@ PortalDrop(Portal portal, bool isTopCommit)
 				(errcode(ERRCODE_INVALID_CURSOR_STATE),
 				 errmsg("cannot drop active portal \"%s\"", portal->name)));
 
+	if (portal->queryDesc)
+		debug_query_string = portal->queryDesc->sourceText;
+
 	/*
 	 * Allow portalcmds.c to clean up the state it knows about, in particular
 	 * shutting down the executor if still active.  This step potentially runs
-- 
2.47.2

From c8f88cc7be57821ea9eb28bf6280ac980fed3519 Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Fri, 25 Apr 2025 16:59:50 -0500
Subject: [PATCH 1/1] Correct timing of portal drop in an execute message

In the extended query protocol, unnamed portals within a transaction
are dropped whenever the next query execution starts, and creating a
new unnamed portal requires dropping the previous one. This behavior
is problematic for several reasons, not least because it delays the
execution of the ExecutorEnd hook. This patch addresses the issue by
ensuring that the portal is dropped only after it has been fully
fetched to completion.
---
 src/backend/tcop/postgres.c | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index dc4c600922d..cb4c4854078 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2327,6 +2327,10 @@ exec_execute_message(const char *portal_name, long max_rows)
 
 		/* Send appropriate CommandComplete to client */
 		EndCommand(&qc, dest, false);
+
+		/* close the portal, if not pinned */
+		if (!portal->portalPinned)
+			PortalDrop(portal, false);
 	}
 	else
 	{
-- 
2.39.5 (Apple Git-154)

From 3558aba4af7b77ab98464f4d90155c46a2307534 Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)"
 <simseih@dev-dsk-simseih-1d-3940b79e.us-east-1.amazon.com>
Date: Sat, 26 Apr 2025 17:34:09 +0000
Subject: [PATCH v5 1/1] Correct timing of portal drop in an execute message

In the extended query protocol, unnamed portals within a transaction
are dropped whenever the next query execution starts, which at that
point creating a new unnamed portal requires dropping the previous one.
This is problematic for problematic for several reasons, not least because
it delays the execution of the ExecutorEnd hook or delays the logging of
temp file usage ( log_temp_files ). This patch addresses the issue by
ensuring that the portal is dropped only after it has been fully
fetched to completion, in the cases that the portal is not a transaction
control statement or requires to be run outside of a transaction block.
---
 src/backend/tcop/postgres.c | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index dc4c600922d..8c537dfca88 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2323,6 +2323,10 @@ exec_execute_message(const char *portal_name, long max_rows)
 			 * message.  The next protocol message will start a fresh timeout.
 			 */
 			disable_statement_timeout();
+
+			/* unnamed portal executed to completion, so close it */
+			if (portal_name[0] == '\0')
+				PortalDrop(portal, false);
 		}
 
 		/* Send appropriate CommandComplete to client */
-- 
2.47.1

From 99c6e96c09f8807bc33712001d7f89628231c241 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yhuel@dalibo.com>
Date: Wed, 18 Jun 2025 09:32:13 +0200
Subject: [PATCH v6] Fix reporting of temp files usage

Drop unnamed portals early enough so that we avoid blaming the wrong
query. There will be cases where no query is logged at all, like when
using the extended protocol with unnamed portal. Moreover, when using
SQL cursors, either the CLOSE request or the COMMIT will be logged,
which isn't great, but like before this patch.

Reviewed-by: Sami Imseih <samimseih@gmail.com>
---
 src/backend/tcop/postgres.c                   |  21 +++-
 .../modules/test_misc/t/008_log_temp_files.pl | 113 ++++++++++++++++++
 2 files changed, 128 insertions(+), 6 deletions(-)
 create mode 100644 src/test/modules/test_misc/t/008_log_temp_files.pl

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 2f8c3d5f918..f307ed67827 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1748,13 +1748,10 @@ exec_bind_message(StringInfo input_message)
 				 errdetail_abort()));
 
 	/*
-	 * Create the portal.  Allow silent replacement of an existing portal only
-	 * if the unnamed portal is specified.
+	 * Create the portal.  Don't allow silent replacement of an existing named portal.
+	 * An unnamed portal should have been removed already.
 	 */
-	if (portal_name[0] == '\0')
-		portal = CreatePortal(portal_name, true, true);
-	else
-		portal = CreatePortal(portal_name, false, false);
+	portal = CreatePortal(portal_name, false, false);
 
 	/*
 	 * Prepare to copy stuff into the portal's memory context.  We do all this
@@ -4745,6 +4742,18 @@ PostgresMain(const char *dbname, const char *username)
 		if (ignore_till_sync && firstchar != EOF)
 			continue;
 
+		/* Drop unnamed portals early enough in order to avoid weird things with
+		 * debug_query_string, like blaming the wrong query in the logs, when
+		 * we log temporary files. */
+		if (firstchar == PqMsg_Query || firstchar == PqMsg_Bind)
+		{
+			Portal		portal;
+
+			portal = GetPortalByName("");
+			if (PortalIsValid(portal))
+				PortalDrop(portal, false);
+		}
+
 		switch (firstchar)
 		{
 			case PqMsg_Query:
diff --git a/src/test/modules/test_misc/t/008_log_temp_files.pl b/src/test/modules/test_misc/t/008_log_temp_files.pl
new file mode 100644
index 00000000000..ebdd36010ff
--- /dev/null
+++ b/src/test/modules/test_misc/t/008_log_temp_files.pl
@@ -0,0 +1,113 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Verify that temp files are logged with the right statement.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+sub check_log
+{
+	my $node = shift;
+	my $offset = shift;
+
+	if ($node->log_contains('STATEMENT:  SELECT 1', $offset))
+	{
+		ok(0, "The wrong query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  SELECT a FROM foo ORDER BY a', $offset))
+	{
+		ok(1, "The right query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  CLOSE', $offset))
+	{
+		ok(1, "Cursor CLOSE has been logged, OK");
+	}
+	else
+	{
+		ok(1, "No query has been logged");
+	}
+}
+
+my $offset = 0;
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+log_statement = none
+));
+$node->start;
+
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+VACUUM ANALYZE foo;
+});
+
+$offset = -s $node->logfile;
+# The following sequence used to logged the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following pipelined sequence used to logged the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the right query
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all (named statement, unnamed portal)
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the CLOSE query
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+COMMIT;
+});
+check_log($node, $offset);
+
+
+$node->stop('fast');
+done_testing();
-- 
2.47.2

From 554e8e62675574cbde0fd35b0a10f6ae56e5f0d6 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yhuel@dalibo.com>
Date: Wed, 18 Jun 2025 09:32:13 +0200
Subject: [PATCH v7] Fix reporting of temp files usage

Drop unnamed portals early enough so that we avoid blaming the wrong
query. There will be cases where no query is logged at all, like when
using the extended protocol with unnamed portal. Moreover, when using
SQL cursors, either the CLOSE request or the COMMIT will be logged,
which isn't great, but like before this patch.

Reviewed-by: Sami Imseih <samimseih@gmail.com>
---
 src/backend/tcop/postgres.c                   |  21 +++-
 src/test/modules/test_misc/meson.build        |   1 +
 .../modules/test_misc/t/008_log_temp_files.pl | 113 ++++++++++++++++++
 3 files changed, 129 insertions(+), 6 deletions(-)
 create mode 100644 src/test/modules/test_misc/t/008_log_temp_files.pl

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 2f8c3d5f918..f307ed67827 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1748,13 +1748,10 @@ exec_bind_message(StringInfo input_message)
 				 errdetail_abort()));
 
 	/*
-	 * Create the portal.  Allow silent replacement of an existing portal only
-	 * if the unnamed portal is specified.
+	 * Create the portal.  Don't allow silent replacement of an existing named portal.
+	 * An unnamed portal should have been removed already.
 	 */
-	if (portal_name[0] == '\0')
-		portal = CreatePortal(portal_name, true, true);
-	else
-		portal = CreatePortal(portal_name, false, false);
+	portal = CreatePortal(portal_name, false, false);
 
 	/*
 	 * Prepare to copy stuff into the portal's memory context.  We do all this
@@ -4745,6 +4742,18 @@ PostgresMain(const char *dbname, const char *username)
 		if (ignore_till_sync && firstchar != EOF)
 			continue;
 
+		/* Drop unnamed portals early enough in order to avoid weird things with
+		 * debug_query_string, like blaming the wrong query in the logs, when
+		 * we log temporary files. */
+		if (firstchar == PqMsg_Query || firstchar == PqMsg_Bind)
+		{
+			Portal		portal;
+
+			portal = GetPortalByName("");
+			if (PortalIsValid(portal))
+				PortalDrop(portal, false);
+		}
+
 		switch (firstchar)
 		{
 			case PqMsg_Query:
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 9c50de7efb0..44d44d5344c 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -16,6 +16,7 @@ tests += {
       't/005_timeouts.pl',
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
+      't/008_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/008_log_temp_files.pl b/src/test/modules/test_misc/t/008_log_temp_files.pl
new file mode 100644
index 00000000000..ebdd36010ff
--- /dev/null
+++ b/src/test/modules/test_misc/t/008_log_temp_files.pl
@@ -0,0 +1,113 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Verify that temp files are logged with the right statement.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+sub check_log
+{
+	my $node = shift;
+	my $offset = shift;
+
+	if ($node->log_contains('STATEMENT:  SELECT 1', $offset))
+	{
+		ok(0, "The wrong query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  SELECT a FROM foo ORDER BY a', $offset))
+	{
+		ok(1, "The right query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  CLOSE', $offset))
+	{
+		ok(1, "Cursor CLOSE has been logged, OK");
+	}
+	else
+	{
+		ok(1, "No query has been logged");
+	}
+}
+
+my $offset = 0;
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+log_statement = none
+));
+$node->start;
+
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+VACUUM ANALYZE foo;
+});
+
+$offset = -s $node->logfile;
+# The following sequence used to logged the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following pipelined sequence used to logged the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the right query
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all (named statement, unnamed portal)
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the CLOSE query
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+COMMIT;
+});
+check_log($node, $offset);
+
+
+$node->stop('fast');
+done_testing();
-- 
2.47.2

From cc8b4c3b019126b4359290dfcc473ac131643558 Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)"
 <simseih@dev-dsk-simseih-1e-c76a1a95.us-east-1.amazon.com>
Date: Fri, 29 Aug 2025 17:19:34 +0000
Subject: [PATCH v8 1/1] Drop unnamed portal before setting debug_query_string

Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/backend/tcop/postgres.c                   |  35 ++++++
 src/test/modules/test_misc/meson.build        |   1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 115 ++++++++++++++++++
 3 files changed, 151 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 0cecd464902..ae86d93b900 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -148,6 +148,7 @@ static bool ignore_till_sync = false;
  * in order to reduce overhead for short-lived queries.
  */
 static CachedPlanSource *unnamed_stmt_psrc = NULL;
+static bool unnamed_portal = false;
 
 /* assorted command-line switches */
 static const char *userDoption = NULL;	/* -D switch */
@@ -182,6 +183,7 @@ static bool IsTransactionExitStmt(Node *parsetree);
 static bool IsTransactionExitStmtList(List *pstmts);
 static bool IsTransactionStmtList(List *pstmts);
 static void drop_unnamed_stmt(void);
+static void drop_unnamed_portal(void);
 static void log_disconnections(int code, Datum arg);
 static void enable_statement_timeout(void);
 static void disable_statement_timeout(void);
@@ -1020,6 +1022,12 @@ exec_simple_query(const char *query_string)
 	bool		use_implicit_block;
 	char		msec_str[32];
 
+	/*
+	 * Drop the unnamed portal before setting debug_query_string, to avoid
+	 * attributing messages from the drop (e.g., temp usage) to the new query.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1672,6 +1680,12 @@ exec_bind_message(StringInfo input_message)
 					 errmsg("unnamed prepared statement does not exist")));
 	}
 
+	/*
+	 * Same as exec_simple_query, drop the unnamed portal before setting
+	 * debug_query_string.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1753,10 +1767,14 @@ exec_bind_message(StringInfo input_message)
 	 * if the unnamed portal is specified.
 	 */
 	if (portal_name[0] == '\0')
+	{
 		portal = CreatePortal(portal_name, true, true);
+		unnamed_portal = true;
+	}
 	else
 		portal = CreatePortal(portal_name, false, false);
 
+
 	/*
 	 * Prepare to copy stuff into the portal's memory context.  We do all this
 	 * copying first, because it could possibly fail (out-of-memory) and we
@@ -5229,3 +5247,20 @@ disable_statement_timeout(void)
 	if (get_timeout_active(STATEMENT_TIMEOUT))
 		disable_timeout(STATEMENT_TIMEOUT, false);
 }
+
+/* Drop the unnamed portal if one exists */
+static void
+drop_unnamed_portal(void)
+{
+	Portal		portal;
+
+	if (!unnamed_portal)
+		return;
+
+	/* Get the portal and drop it */
+	portal = GetPortalByName("");
+	if (PortalIsValid(portal))
+		PortalDrop(portal, false);
+
+	unnamed_portal = false;
+}
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf46..f258bf1ccd9 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 00000000000..9699f1767f1
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,115 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Verify that temp files are logged with the right statement.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+sub check_log
+{
+	my $node = shift;
+	my $offset = shift;
+
+	if ($node->log_contains('STATEMENT:  SELECT 1', $offset))
+	{
+		ok(0, "The wrong query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  SELECT a FROM foo ORDER BY a', $offset))
+	{
+		ok(1, "The right query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  CLOSE', $offset))
+	{
+		ok(1, "Cursor CLOSE has been logged, OK");
+	}
+	else
+	{
+		ok(1, "No query has been logged");
+	}
+}
+
+my $offset = 0;
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+log_statement = all
+log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,host=%h,app=%a,cilent=%h'
+log_min_duration_statement = 0
+));
+$node->start;
+
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+VACUUM ANALYZE foo;
+});
+
+$offset = -s $node->logfile;
+# The following sequence used to logged the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following pipelined sequence used to logged the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the right query
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all (named statement, unnamed portal)
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the CLOSE query
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+COMMIT;
+});
+check_log($node, $offset);
+
+
+$node->stop('fast');
+done_testing();
-- 
2.47.3

From b2bc429dd0fb4f01920a3bf70a9756cf912f0c56 Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)"
 <simseih@dev-dsk-simseih-1e-c76a1a95.us-east-1.amazon.com>
Date: Fri, 29 Aug 2025 17:19:34 +0000
Subject: [PATCH v9] Drop unnamed portal before setting debug_query_string

Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/backend/tcop/postgres.c                   |  48 +++++++-
 src/backend/utils/mmgr/portalmem.c            |  62 ++++++----
 src/include/utils/portal.h                    |   1 +
 src/test/modules/test_misc/meson.build        |   1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 115 ++++++++++++++++++
 5 files changed, 199 insertions(+), 28 deletions(-)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 0cecd46490..bbfee7c0ce 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -148,6 +148,7 @@ static bool ignore_till_sync = false;
  * in order to reduce overhead for short-lived queries.
  */
 static CachedPlanSource *unnamed_stmt_psrc = NULL;
+static bool unnamed_portal = false;
 
 /* assorted command-line switches */
 static const char *userDoption = NULL;	/* -D switch */
@@ -182,6 +183,7 @@ static bool IsTransactionExitStmt(Node *parsetree);
 static bool IsTransactionExitStmtList(List *pstmts);
 static bool IsTransactionStmtList(List *pstmts);
 static void drop_unnamed_stmt(void);
+static void drop_unnamed_portal(void);
 static void log_disconnections(int code, Datum arg);
 static void enable_statement_timeout(void);
 static void disable_statement_timeout(void);
@@ -1020,6 +1022,12 @@ exec_simple_query(const char *query_string)
 	bool		use_implicit_block;
 	char		msec_str[32];
 
+	/*
+	 * Drop the unnamed portal before setting debug_query_string, to avoid
+	 * attributing messages from the drop (e.g., temp usage) to the new query.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1213,7 +1221,13 @@ exec_simple_query(const char *query_string)
 		 * Create unnamed portal to run the query or queries in. If there
 		 * already is one, silently drop it.
 		 */
-		portal = CreatePortal("", true, true);
+		if (!unnamed_portal)
+			portal = CreatePortalOnly("");
+		else
+			portal = CreatePortal("", true, true);
+
+		unnamed_portal = true;
+
 		/* Don't display the portal in pg_cursors */
 		portal->visible = false;
 
@@ -1672,6 +1686,12 @@ exec_bind_message(StringInfo input_message)
 					 errmsg("unnamed prepared statement does not exist")));
 	}
 
+	/*
+	 * Same as exec_simple_query, drop the unnamed portal before setting
+	 * debug_query_string.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1753,7 +1773,14 @@ exec_bind_message(StringInfo input_message)
 	 * if the unnamed portal is specified.
 	 */
 	if (portal_name[0] == '\0')
-		portal = CreatePortal(portal_name, true, true);
+	{
+		if (!unnamed_portal)
+			portal = CreatePortalOnly(portal_name);
+		else
+			portal = CreatePortal(portal_name, true, true);
+
+		unnamed_portal = true;
+	}
 	else
 		portal = CreatePortal(portal_name, false, false);
 
@@ -5229,3 +5256,20 @@ disable_statement_timeout(void)
 	if (get_timeout_active(STATEMENT_TIMEOUT))
 		disable_timeout(STATEMENT_TIMEOUT, false);
 }
+
+/* Drop the unnamed portal if one exists */
+static void
+drop_unnamed_portal(void)
+{
+	Portal		portal;
+
+	if (!unnamed_portal)
+		return;
+
+	/* Get the portal and drop it */
+	portal = GetPortalByName("");
+	if (PortalIsValid(portal))
+		PortalDrop(portal, false);
+
+	unnamed_portal = false;
+}
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index 0be1c2b0ff..facb475fd9 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -162,37 +162,13 @@ PortalGetPrimaryStmt(Portal portal)
 	return NULL;
 }
 
-/*
- * CreatePortal
- *		Returns a new portal given a name.
- *
- * allowDup: if true, automatically drop any pre-existing portal of the
- * same name (if false, an error is raised).
- *
- * dupSilent: if true, don't even emit a WARNING.
- */
 Portal
-CreatePortal(const char *name, bool allowDup, bool dupSilent)
+CreatePortalOnly(const char *name)
 {
-	Portal		portal;
+	Portal portal;
 
 	Assert(PointerIsValid(name));
 
-	portal = GetPortalByName(name);
-	if (PortalIsValid(portal))
-	{
-		if (!allowDup)
-			ereport(ERROR,
-					(errcode(ERRCODE_DUPLICATE_CURSOR),
-					 errmsg("cursor \"%s\" already exists", name)));
-		if (!dupSilent)
-			ereport(WARNING,
-					(errcode(ERRCODE_DUPLICATE_CURSOR),
-					 errmsg("closing existing cursor \"%s\"",
-							name)));
-		PortalDrop(portal, false);
-	}
-
 	/* make new portal structure */
 	portal = (Portal) MemoryContextAllocZero(TopPortalContext, sizeof *portal);
 
@@ -227,6 +203,40 @@ CreatePortal(const char *name, bool allowDup, bool dupSilent)
 	return portal;
 }
 
+/*
+ * CreatePortal
+ *		Returns a new portal given a name.
+ *
+ * allowDup: if true, automatically drop any pre-existing portal of the
+ * same name (if false, an error is raised).
+ *
+ * dupSilent: if true, don't even emit a WARNING.
+ */
+Portal
+CreatePortal(const char *name, bool allowDup, bool dupSilent)
+{
+	Portal		portal;
+
+	Assert(PointerIsValid(name));
+
+	portal = GetPortalByName(name);
+	if (PortalIsValid(portal))
+	{
+		if (!allowDup)
+			ereport(ERROR,
+					(errcode(ERRCODE_DUPLICATE_CURSOR),
+					 errmsg("cursor \"%s\" already exists", name)));
+		if (!dupSilent)
+			ereport(WARNING,
+					(errcode(ERRCODE_DUPLICATE_CURSOR),
+					 errmsg("closing existing cursor \"%s\"",
+							name)));
+		PortalDrop(portal, false);
+	}
+
+	return CreatePortalOnly(name);
+}
+
 /*
  * CreateNewPortal
  *		Create a new portal, assigning it a random nonconflicting name.
diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h
index 0b62143af8..fb80bf3945 100644
--- a/src/include/utils/portal.h
+++ b/src/include/utils/portal.h
@@ -226,6 +226,7 @@ extern void AtSubAbort_Portals(SubTransactionId mySubid,
 							   ResourceOwner myXactOwner,
 							   ResourceOwner parentXactOwner);
 extern void AtSubCleanup_Portals(SubTransactionId mySubid);
+extern Portal CreatePortalOnly(const char *name);
 extern Portal CreatePortal(const char *name, bool allowDup, bool dupSilent);
 extern Portal CreateNewPortal(void);
 extern void PinPortal(Portal portal);
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf4..f258bf1ccd 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 0000000000..d16eb82a54
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,115 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Verify that temp files are logged with the right statement.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+sub check_log
+{
+	my $node = shift;
+	my $offset = shift;
+
+	if ($node->log_contains('STATEMENT:  SELECT 1', $offset))
+	{
+		ok(0, "The wrong query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  SELECT a FROM foo ORDER BY a', $offset))
+	{
+		ok(1, "The right query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  CLOSE', $offset))
+	{
+		ok(1, "Cursor CLOSE has been logged, OK");
+	}
+	else
+	{
+		ok(1, "No query has been logged");
+	}
+}
+
+my $offset = 0;
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+log_statement = all
+log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,host=%h,app=%a,cilent=%h'
+log_min_duration_statement = 0
+));
+$node->start;
+
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+VACUUM ANALYZE foo;
+});
+
+$offset = -s $node->logfile;
+# The following sequence used to log the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following pipelined sequence used to log the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the right query
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all (named statement, unnamed portal)
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the CLOSE query
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+COMMIT;
+});
+check_log($node, $offset);
+
+
+$node->stop('fast');
+done_testing();

base-commit: 09119238a18191dea3deed635a2b2a6ffe904932
-- 
2.39.5 (Apple Git-154)

From 268e2a5bac002ed32db4b2b8ab5dc4bb4f64c290 Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)"
 <simseih@dev-dsk-simseih-1e-c76a1a95.us-east-1.amazon.com>
Date: Fri, 29 Aug 2025 17:19:34 +0000
Subject: [PATCH v10] =?UTF-8?q?Fix=20incorrect=20query=20attribution=20in?=
 =?UTF-8?q?=20temporary=E2=80=90file=20logging?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Previously, in the extended query protocol with unnamed
portals, the log line for temporary files would show the
wrong STATEMENT: line (e.g. from a subsequent simple query)
rather than the query that actually caused the temp file.
This happened because the unnamed portal from the previous
Bind was dropped after setting the new debug_query_string,
so the logging triggered by PortalDrop used the new query
text instead of the one that had generated the temporary file.

This patch ensures that when an unnamed portal is dropped,
it is associated with the correct query text by dropping
the previous unnamed portal before assigning the debug_query_string.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/backend/tcop/postgres.c                   |  35 ++++++
 src/test/modules/test_misc/meson.build        |   1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 115 ++++++++++++++++++
 3 files changed, 151 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index d356830f75..d74e9e7bd1 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -148,6 +148,7 @@ static bool ignore_till_sync = false;
  * in order to reduce overhead for short-lived queries.
  */
 static CachedPlanSource *unnamed_stmt_psrc = NULL;
+static bool unnamed_portal = false;
 
 /* assorted command-line switches */
 static const char *userDoption = NULL;	/* -D switch */
@@ -182,6 +183,7 @@ static bool IsTransactionExitStmt(Node *parsetree);
 static bool IsTransactionExitStmtList(List *pstmts);
 static bool IsTransactionStmtList(List *pstmts);
 static void drop_unnamed_stmt(void);
+static void drop_unnamed_portal(void);
 static void log_disconnections(int code, Datum arg);
 static void enable_statement_timeout(void);
 static void disable_statement_timeout(void);
@@ -1024,6 +1026,12 @@ exec_simple_query(const char *query_string)
 	bool		use_implicit_block;
 	char		msec_str[32];
 
+	/*
+	 * Drop the unnamed portal before setting debug_query_string, to avoid
+	 * attributing messages from the drop (e.g., temp usage) to the new query.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1676,6 +1684,12 @@ exec_bind_message(StringInfo input_message)
 					 errmsg("unnamed prepared statement does not exist")));
 	}
 
+	/*
+	 * Same as exec_simple_query, drop the unnamed portal before setting
+	 * debug_query_string.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1757,10 +1771,14 @@ exec_bind_message(StringInfo input_message)
 	 * if the unnamed portal is specified.
 	 */
 	if (portal_name[0] == '\0')
+	{
 		portal = CreatePortal(portal_name, true, true);
+		unnamed_portal = true;
+	}
 	else
 		portal = CreatePortal(portal_name, false, false);
 
+
 	/*
 	 * Prepare to copy stuff into the portal's memory context.  We do all this
 	 * copying first, because it could possibly fail (out-of-memory) and we
@@ -5236,3 +5254,20 @@ disable_statement_timeout(void)
 	if (get_timeout_active(STATEMENT_TIMEOUT))
 		disable_timeout(STATEMENT_TIMEOUT, false);
 }
+
+/* Drop the unnamed portal if one exists */
+static void
+drop_unnamed_portal(void)
+{
+	Portal		portal;
+
+	if (!unnamed_portal)
+		return;
+
+	/* Get the portal and drop it */
+	portal = GetPortalByName("");
+	if (PortalIsValid(portal))
+		PortalDrop(portal, false);
+
+	unnamed_portal = false;
+}
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf4..f258bf1ccd 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 0000000000..d16eb82a54
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,115 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Verify that temp files are logged with the right statement.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+sub check_log
+{
+	my $node = shift;
+	my $offset = shift;
+
+	if ($node->log_contains('STATEMENT:  SELECT 1', $offset))
+	{
+		ok(0, "The wrong query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  SELECT a FROM foo ORDER BY a', $offset))
+	{
+		ok(1, "The right query has been logged");
+	}
+	elsif ($node->log_contains('STATEMENT:  CLOSE', $offset))
+	{
+		ok(1, "Cursor CLOSE has been logged, OK");
+	}
+	else
+	{
+		ok(1, "No query has been logged");
+	}
+}
+
+my $offset = 0;
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+log_statement = all
+log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,host=%h,app=%a,cilent=%h'
+log_min_duration_statement = 0
+));
+$node->start;
+
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+VACUUM ANALYZE foo;
+});
+
+$offset = -s $node->logfile;
+# The following sequence used to log the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following pipelined sequence used to log the second query instead of the first one
+# Now it should log no query at all
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the right query
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log no query at all (named statement, unnamed portal)
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+COMMIT;
+});
+check_log($node, $offset);
+
+$offset = -s $node->logfile;
+# The following sequence should log the CLOSE query
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+COMMIT;
+});
+check_log($node, $offset);
+
+
+$node->stop('fast');
+done_testing();

base-commit: ae0e1be9f2a20f6b64072dcee5b8dd7b9027a8fa
-- 
2.39.5 (Apple Git-154)

From 3a506025e84349d212d57949971eb7f70767d328 Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Tue, 16 Sep 2025 19:00:06 +0000
Subject: [PATCH v11 1/2] Fix temp file log blame for unnamed portals.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

With the extended query protocol, temporary files created by unnamed
portals could be blamed on the wrong statement. Because temp file logging
happens when the portal is dropped, this patch ensures that unnamed portals
are dropped before resetting debug_query_string, so that temp files are
blamed on the correct statement.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/backend/tcop/postgres.c | 35 +++++++++++++++++++++++++++++++++++
 1 file changed, 35 insertions(+)

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index d356830f756..d74e9e7bd12 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -148,6 +148,7 @@ static bool ignore_till_sync = false;
  * in order to reduce overhead for short-lived queries.
  */
 static CachedPlanSource *unnamed_stmt_psrc = NULL;
+static bool unnamed_portal = false;
 
 /* assorted command-line switches */
 static const char *userDoption = NULL;	/* -D switch */
@@ -182,6 +183,7 @@ static bool IsTransactionExitStmt(Node *parsetree);
 static bool IsTransactionExitStmtList(List *pstmts);
 static bool IsTransactionStmtList(List *pstmts);
 static void drop_unnamed_stmt(void);
+static void drop_unnamed_portal(void);
 static void log_disconnections(int code, Datum arg);
 static void enable_statement_timeout(void);
 static void disable_statement_timeout(void);
@@ -1024,6 +1026,12 @@ exec_simple_query(const char *query_string)
 	bool		use_implicit_block;
 	char		msec_str[32];
 
+	/*
+	 * Drop the unnamed portal before setting debug_query_string, to avoid
+	 * attributing messages from the drop (e.g., temp usage) to the new query.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1676,6 +1684,12 @@ exec_bind_message(StringInfo input_message)
 					 errmsg("unnamed prepared statement does not exist")));
 	}
 
+	/*
+	 * Same as exec_simple_query, drop the unnamed portal before setting
+	 * debug_query_string.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1757,10 +1771,14 @@ exec_bind_message(StringInfo input_message)
 	 * if the unnamed portal is specified.
 	 */
 	if (portal_name[0] == '\0')
+	{
 		portal = CreatePortal(portal_name, true, true);
+		unnamed_portal = true;
+	}
 	else
 		portal = CreatePortal(portal_name, false, false);
 
+
 	/*
 	 * Prepare to copy stuff into the portal's memory context.  We do all this
 	 * copying first, because it could possibly fail (out-of-memory) and we
@@ -5236,3 +5254,20 @@ disable_statement_timeout(void)
 	if (get_timeout_active(STATEMENT_TIMEOUT))
 		disable_timeout(STATEMENT_TIMEOUT, false);
 }
+
+/* Drop the unnamed portal if one exists */
+static void
+drop_unnamed_portal(void)
+{
+	Portal		portal;
+
+	if (!unnamed_portal)
+		return;
+
+	/* Get the portal and drop it */
+	portal = GetPortalByName("");
+	if (PortalIsValid(portal))
+		PortalDrop(portal, false);
+
+	unnamed_portal = false;
+}
-- 
2.43.0

From d6ec2d08f5059cb17c43908d768b5a89127aaa11 Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Tue, 16 Sep 2025 19:03:54 +0000
Subject: [PATCH v11 2/2] Add tests for temp table logging

---
 src/test/modules/test_misc/meson.build        |  1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 91 +++++++++++++++++++
 2 files changed, 92 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf46..f258bf1ccd9 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 00000000000..dc5d6aee70a
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,91 @@
+#
+# Verify that temp files are logged with the right statement.
+#
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+#
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a new PostgreSQL test cluster
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+    'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+log_statement = all
+log_min_duration_statement = 0
+));
+$node->start;
+
+# Setup table and populate with data
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+VACUUM ANALYZE foo;
+});
+
+# unnamed portal test
+my $log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+END;
+});
+$node->wait_for_log(qr/LOG:\s+execute <unnamed>:\s+SELECT a FROM foo ORDER BY a OFFSET \$1.*LOG:\s+temporary file: path/s, $log_offset);
+ok("log temp with unnamed portal");
+
+# named portal test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+END;
+});
+$node->wait_for_log(qr/LOG:\s+execute stmt:\s+SELECT a FROM foo ORDER BY a OFFSET \$1.*LOG:\s+temporary file: path/s, $log_offset);
+ok("log temp with named portal");
+
+# pipelined query test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+$node->wait_for_log(qr/LOG:\s+execute <unnamed>:\s+SELECT a FROM foo ORDER BY a OFFSET \$1.*LOG:\s+temporary file: path/s, $log_offset);
+ok("log temp with pipelined query");
+
+# cursor test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+END;
+});
+$node->wait_for_log(qr/LOG:  statement: CLOSE mycur;.*LOG:  temporary file: path.*/s, $log_offset);
+ok("log temp with cursor");
+
+# simple query test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+END;
+});
+$node->wait_for_log(qr/LOG:  statement: SELECT a FROM foo ORDER BY a OFFSET 4999;.*statement: END;/s, $log_offset);
+ok("log temp with simple query");
+
+# Stop the node
+$node->stop('fast');
+done_testing();
-- 
2.43.0

From 41d252c82eebf7dc9c06637996df7dcdb90a5061 Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Thu, 18 Sep 2025 15:34:23 +0000
Subject: [PATCH v12 1/2] Fix temp file logging blame in extended query
 protocol
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

With the extended query protocol, temporary files created by unnamed
portals could be blamed on the wrong statement. Because temp file logging
happens when the portal is dropped, this patch ensures that unnamed portals
are dropped before resetting debug_query_string, so that temp files are
blamed on the correct statement.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/backend/tcop/postgres.c | 44 +++++++++++++++++++++++++++++++++++++
 1 file changed, 44 insertions(+)

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index d356830f756..80fffd7aada 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -148,6 +148,7 @@ static bool ignore_till_sync = false;
  * in order to reduce overhead for short-lived queries.
  */
 static CachedPlanSource *unnamed_stmt_psrc = NULL;
+static bool unnamed_portal = false;
 
 /* assorted command-line switches */
 static const char *userDoption = NULL;	/* -D switch */
@@ -182,6 +183,7 @@ static bool IsTransactionExitStmt(Node *parsetree);
 static bool IsTransactionExitStmtList(List *pstmts);
 static bool IsTransactionStmtList(List *pstmts);
 static void drop_unnamed_stmt(void);
+static void drop_unnamed_portal(void);
 static void log_disconnections(int code, Datum arg);
 static void enable_statement_timeout(void);
 static void disable_statement_timeout(void);
@@ -1024,6 +1026,12 @@ exec_simple_query(const char *query_string)
 	bool		use_implicit_block;
 	char		msec_str[32];
 
+	/*
+	 * Drop the unnamed portal before setting debug_query_string, to avoid
+	 * attributing messages from the drop (e.g., temp usage) to the new query.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1676,6 +1684,12 @@ exec_bind_message(StringInfo input_message)
 					 errmsg("unnamed prepared statement does not exist")));
 	}
 
+	/*
+	 * Same as exec_simple_query, drop the unnamed portal before setting
+	 * debug_query_string.
+	 */
+	drop_unnamed_portal();
+
 	/*
 	 * Report query to various monitoring facilities.
 	 */
@@ -1757,10 +1771,14 @@ exec_bind_message(StringInfo input_message)
 	 * if the unnamed portal is specified.
 	 */
 	if (portal_name[0] == '\0')
+	{
 		portal = CreatePortal(portal_name, true, true);
+		unnamed_portal = true;
+	}
 	else
 		portal = CreatePortal(portal_name, false, false);
 
+
 	/*
 	 * Prepare to copy stuff into the portal's memory context.  We do all this
 	 * copying first, because it could possibly fail (out-of-memory) and we
@@ -5236,3 +5254,29 @@ disable_statement_timeout(void)
 	if (get_timeout_active(STATEMENT_TIMEOUT))
 		disable_timeout(STATEMENT_TIMEOUT, false);
 }
+
+/* Drop the unnamed portal if one exists */
+static void
+drop_unnamed_portal(void)
+{
+	Portal		portal;
+
+	if (!unnamed_portal)
+		return;
+
+	/* Get the portal and drop it */
+	portal = GetPortalByName("");
+	if (PortalIsValid(portal))
+	{
+		/*
+		 * Set debug_query_string from the QueryDesc so that if logging occurs
+		 * during PortalDrop, messages are attributed to the query being run.
+		 */
+		if (portal->queryDesc)
+			debug_query_string = portal->queryDesc->sourceText;
+
+		PortalDrop(portal, false);
+	}
+
+	unnamed_portal = false;
+}
-- 
2.43.0

From 6209155e4bad2202b9202c44108d02e0a0bc17df Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Thu, 18 Sep 2025 15:36:33 +0000
Subject: [PATCH v12 2/2] Add tests for temp file logging
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Commit 41d252c82eebf7d revealed a lack of test coverage for temp file
logging. This commit adds the missing tests to improve coverage and
reliability.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/test/modules/test_misc/meson.build        |  1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 89 +++++++++++++++++++
 2 files changed, 90 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf46..f258bf1ccd9 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 00000000000..053f839410a
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,89 @@
+#
+# Verify that temp files are logged with the right statement.
+#
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+#
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a new PostgreSQL test cluster
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+    'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+));
+$node->start;
+
+# Setup table and populate with data
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+VACUUM ANALYZE foo;
+});
+
+# unnamed portal test
+my $log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+END;
+});
+$node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset);
+ok("log temp with unnamed portal");
+
+# named portal test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+END;
+});
+$node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset);
+ok("log temp with named portal");
+
+# pipelined query test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+$node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset);
+ok("log temp with pipelined query");
+
+# cursor test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+END;
+});
+$node->wait_for_log(qr/CLOSE mycur;\n.*\ LOG:\s+temporary file:/s, $log_offset);
+ok("log temp with cursor");
+
+# simple query test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+END;
+});
+$node->wait_for_log(qr/SELECT a FROM foo ORDER BY a OFFSET 4999;\n.*\ LOG:\s+temporary file: path/s, $log_offset);
+ok("log temp with simple query");
+
+# Stop the node
+$node->stop('fast');
+done_testing();
-- 
2.43.0

From 46d276b56f1ccbb317d5a08a8b01e048b33c0aca Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Thu, 18 Sep 2025 15:36:33 +0000
Subject: [PATCH v13 2/2] Add tests for temp file logging
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Commit 41d252c82eebf7d revealed a lack of test coverage for temp file
logging. This commit adds the missing tests to improve coverage and
reliability.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/test/modules/test_misc/meson.build        |   1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 115 ++++++++++++++++++
 2 files changed, 116 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf46..f258bf1ccd9 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 00000000000..3d1a25bcfa8
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,115 @@
+#
+# Verify that temporary file usage is logged with the expected statement text
+# for various query execution paths (portals, pipelines, cursors, prepared
+# statements, etc.).
+#
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+#
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a new PostgreSQL test cluster
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+    'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+));
+$node->start;
+
+my $log_offset = -s $node->logfile;
+
+# Setup table and populate with data
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+});
+
+# unnamed portal test
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+END;
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset);
+ok("log temp with unnamed portal");
+
+# bind with implicit transaction test
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset);
+ok("log temp with bind and implicit transaction");
+
+# named portal test
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+END;
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset);
+ok("log temp with named portal");
+
+# pipelined query test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset);
+ok("log temp with pipelined query");
+
+# parse and bind tests
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+SELECT a, a, a FROM foo ORDER BY a OFFSET \$1 \\parse p1
+\\bind_named p1 4999 \\g
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a, a, a FROM foo ORDER BY a OFFSET \$1/s, $log_offset);
+ok("log temp with parse and bind");
+
+# simple query test
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+END;
+});
+$log_offset = $node->wait_for_log(qr/SELECT a FROM foo ORDER BY a OFFSET 4999;\n.*\ LOG:\s+temporary file: path/s, $log_offset);
+ok("log temp with simple query");
+
+# cursor test
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+END;
+});
+$log_offset = $node->wait_for_log(qr/CLOSE mycur;\n.*\ LOG:\s+temporary file:/s, $log_offset);
+ok("log temp with cursor");
+
+# prepare/execute test
+$node->safe_psql("postgres", qq{
+BEGIN;
+PREPARE p1 AS SELECT a FROM foo ORDER BY a OFFSET 4999;
+EXECUTE p1;
+DEALLOCATE p1;
+END;
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file:/s, $log_offset);
+ok("prepare and execute cursor");
+
+# Stop the node
+$node->stop('fast');
+done_testing();
-- 
2.43.0

From 19fc3ce6185f9c74d0393ee1bfbd6fe8020131c7 Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Fri, 19 Sep 2025 20:06:37 +0000
Subject: [PATCH v13 1/2] Fix temp file logging blame in extended query
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

With the extended query protocol, temporary files created by unnamed
portals could be blamed on the wrong statement. Because temp file logging
happens when the portal is dropped, this patch ensures that unnamed portals
are dropped before resetting debug_query_string, so that temp files are
blamed on the correct statement.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/backend/utils/mmgr/portalmem.c | 15 +++++++++++++++
 1 file changed, 15 insertions(+)

diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index 0be1c2b0fff..c18d44c3e6a 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -23,6 +23,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "storage/ipc.h"
+#include "tcop/tcopprot.h"
 #include "utils/builtins.h"
 #include "utils/memutils.h"
 #include "utils/snapmgr.h"
@@ -467,8 +468,19 @@ MarkPortalFailed(Portal portal)
 void
 PortalDrop(Portal portal, bool isTopCommit)
 {
+	const char *saved_debug_query_string = NULL;
+
 	Assert(PortalIsValid(portal));
 
+	/*
+	 * Save the current debug_query_string and temporarily set it to the
+	 * portal's query while dropping, so log messages are blamed on the
+	 * portal.
+	 */
+	saved_debug_query_string = debug_query_string;
+	if (portal->queryDesc)
+		debug_query_string = portal->queryDesc->sourceText;
+
 	/*
 	 * Don't allow dropping a pinned portal, it's still needed by whoever
 	 * pinned it.
@@ -594,6 +606,9 @@ PortalDrop(Portal portal, bool isTopCommit)
 	/* release subsidiary storage */
 	MemoryContextDelete(portal->portalContext);
 
+	/* reset the debug_query_string */
+	debug_query_string = saved_debug_query_string;
+
 	/* release portal struct (it's in TopPortalContext) */
 	pfree(portal);
 }
-- 
2.43.0

From 4b55965fc145d978287c8bd8b79681cf58d4ff24 Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Thu, 18 Sep 2025 15:36:33 +0000
Subject: [PATCH v14] Add tests for temp file logging
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Temporary file usage is sometimes attributed to the wrong query in the logs
output. One identified reason is that unnamed portal cleanup (and consequently
temp file logging) happens during the next BIND message, after
debug_query_string has already been updated to the new query.

This commit is a first step towards fixing this, adding tests to track
which query is displayed next to the temp file when the portal is dropped.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/test/modules/test_misc/meson.build        |   1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 115 ++++++++++++++++++
 2 files changed, 116 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf4..f258bf1ccd 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 0000000000..f5071ebf89
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,115 @@
+#
+# Verify that temporary file usage is logged with the expected statement text
+# for various query execution paths (portals, pipelines, cursors, prepared
+# statements, etc.).
+#
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+#
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a new PostgreSQL test cluster
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+    'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+));
+$node->start;
+
+my $log_offset = -s $node->logfile;
+
+# Setup table and populate with data
+$node->safe_psql("postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+});
+
+# unnamed portal test
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+SELECT 1;
+END;
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 1/s, $log_offset);
+ok("log temp with unnamed portal");
+
+# bind with implicit transaction test
+$node->safe_psql("postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\g
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file:/s, $log_offset);
+ok("log temp with bind and implicit transaction");
+
+# named portal test
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 1;
+END;
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 1/s, $log_offset);
+ok("log temp with named portal");
+
+# pipelined query test
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4999 \\sendpipeline
+SELECT 1;
+\\endpipeline
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 1/s, $log_offset);
+ok("log temp with pipelined query");
+
+# parse and bind tests
+$log_offset = -s $node->logfile;
+$node->safe_psql("postgres", qq{
+SELECT a, a, a FROM foo ORDER BY a OFFSET \$1 \\parse p1
+\\bind_named p1 4999 \\g
+});
+$log_offset = $node->wait_for_log(qr/LOG:\s+temporary file:/s, $log_offset);
+ok("log temp with parse and bind");
+
+# simple query test
+$node->safe_psql("postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET 4999;
+END;
+});
+$log_offset = $node->wait_for_log(qr/SELECT a FROM foo ORDER BY a OFFSET 4999;\n.*\ LOG:\s+temporary file: path/s, $log_offset);
+ok("log temp with simple query");
+
+# cursor test
+$node->safe_psql("postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4999;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+END;
+});
+$log_offset = $node->wait_for_log(qr/CLOSE mycur;\n.*\ LOG:\s+temporary file:/s, $log_offset);
+ok("log temp with cursor");
+
+# prepare/execute test
+$node->safe_psql("postgres", qq{
+BEGIN;
+PREPARE p1 AS SELECT a FROM foo ORDER BY a OFFSET 4999;
+EXECUTE p1;
+DEALLOCATE p1;
+END;
+});
+$log_offset = $node->wait_for_log(qr/EXECUTE p1;\n.*\ LOG:\s+temporary file:/s, $log_offset);
+ok("prepare and execute cursor");
+
+# Stop the node
+$node->stop('fast');
+done_testing();
-- 
2.39.5 (Apple Git-154)

From fdf8e391b8a23d70cf0748bd63682c0662c6c03e Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Thu, 18 Sep 2025 15:36:33 +0000
Subject: [PATCH v14] Add tests for temp file logging
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Temporary file usage is sometimes attributed to the wrong query in the logs
output. One identified reason is that unnamed portal cleanup (and consequently
temp file logging) happens during the next BIND message, after
debug_query_string has already been updated to the new query.

This commit is a first step towards fixing this, adding tests to track
which query is displayed next to the temp file when the portal is dropped.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/test/modules/test_misc/meson.build        |   1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 135 ++++++++++++++++++
 2 files changed, 136 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf46d..f258bf1ccd94 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 000000000000..928d7dc36c20
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,135 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Check how temporary file drops and statement queries are associated
+# in the server logs for various query sequences with the simple and
+# extended query protocols.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a new PostgreSQL test cluster
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+debug_parallel_query = off
+));
+$node->start;
+
+# Setup table and populate with data
+$node->safe_psql(
+	"postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+});
+
+note "unnamed portal: temporary file dropped under second SELECT query";
+my $log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4990 \\g
+SELECT 'unnamed portal';
+END;
+});
+ok( $node->log_contains(
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'unnamed portal'/s,
+		$log_offset),
+	"unnamed portal");
+
+note "bind and implicit transaction: temporary file dropped";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4991 \\g
+});
+ok($node->log_contains(qr/LOG:\s+temporary file:/s, $log_offset),
+	"bind and implicit transaction");
+
+note "named portal: temporary file dropped under second SELECT query";
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 'named portal';
+END;
+});
+ok( $node->log_contains(
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'named portal'/s,
+		$log_offset),
+	"named portal");
+
+note "pipelined query: temporary file dropped under second SELECT query";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4992 \\sendpipeline
+SELECT 'pipelined query';
+\\endpipeline
+});
+ok( $node->log_contains(
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'pipelined query'/s,
+		$log_offset),
+	"pipelined query");
+
+note "parse and bind: temporary file dropped";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+SELECT a, a, a FROM foo ORDER BY a OFFSET \$1 \\parse p1
+\\bind_named p1 4993 \\g
+});
+ok($node->log_contains(qr/LOG:\s+temporary file:/s, $log_offset),
+	"parse and bind");
+
+note "simple query: temporary file dropped under SELECT query";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET 4994;
+END;
+});
+ok( $node->log_contains(
+		qr/SELECT a FROM foo ORDER BY a OFFSET 4994;\n.*\ LOG:\s+temporary file: path/s,
+		$log_offset),
+	"simple query");
+
+note "cursor: temporary file dropped under CLOSE";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4995;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+END;
+});
+ok( $node->log_contains(
+		qr/CLOSE mycur;\n.*\ LOG:\s+temporary file:/s, $log_offset),
+	"cursor");
+
+note "prepare/execute: temporary file dropped under EXECUTE";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+PREPARE p1 AS SELECT a FROM foo ORDER BY a OFFSET 4996;
+EXECUTE p1;
+DEALLOCATE p1;
+END;
+});
+ok( $node->log_contains(
+		qr/EXECUTE p1;\n.*\ LOG:\s+temporary file:/s, $log_offset),
+	"prepare/execute");
+
+$node->stop('fast');
+done_testing();
-- 
2.51.0

From 5099366cf05c881ee43f5e6f1bc80eba5938f5f5 Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Thu, 18 Sep 2025 15:36:33 +0000
Subject: [PATCH v15] Add tests for temp file logging
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Temporary file usage is sometimes attributed to the wrong query in the logs
output. One identified reason is that unnamed portal cleanup (and consequently
temp file logging) happens during the next BIND message, after
debug_query_string has already been updated to the new query.

This commit is a first step towards fixing this, adding tests to track
which query is displayed next to the temp file when the portal is dropped.

Author: Sami Imseih <samimseih@gmail.com>
Author: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Reviewed-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/3d07ee43-8855-42db-97e0-bad5db82d972@dalibo.com
---
 src/test/modules/test_misc/meson.build        |   1 +
 .../modules/test_misc/t/009_log_temp_files.pl | 135 ++++++++++++++++++
 2 files changed, 136 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/009_log_temp_files.pl

diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf4..f258bf1ccd 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
       't/008_replslot_single_user.pl',
+      't/009_log_temp_files.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
new file mode 100644
index 0000000000..fe5a125852
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -0,0 +1,135 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Check how temporary file drops and statement queries are associated
+# in the server logs for various query sequences with the simple and
+# extended query protocols.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a new PostgreSQL test cluster
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', qq(
+work_mem = 64kB
+log_temp_files = 0
+debug_parallel_query = off
+));
+$node->start;
+
+# Setup table and populate with data
+$node->safe_psql(
+	"postgres", qq{
+CREATE UNLOGGED TABLE foo(a int);
+INSERT INTO foo(a) SELECT * FROM generate_series(1, 5000);
+});
+
+note "unnamed portal: temporary file dropped under second SELECT query";
+my $log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4990 \\g
+SELECT 'unnamed portal';
+END;
+});
+ok( $node->log_contains(
+		qr/LOG:.*?temporary file:.*?(?:statement|STATEMENT):\s+SELECT 'unnamed portal'/s,
+		$log_offset),
+	"unnamed portal");
+
+note "bind and implicit transaction: temporary file dropped";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4991 \\g
+});
+ok($node->log_contains(qr/LOG:.*?temporary file:/s, $log_offset),
+	"bind and implicit transaction");
+
+note "named portal: temporary file dropped under second SELECT query";
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\parse stmt
+\\bind_named stmt 4999 \\g
+SELECT 'named portal';
+END;
+});
+ok( $node->log_contains(
+		qr/LOG:.*?temporary file:.*?(?:statement|STATEMENT):\s+SELECT 'named portal'/s,
+		$log_offset),
+	"named portal");
+
+note "pipelined query: temporary file dropped under second SELECT query";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+\\startpipeline
+SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4992 \\sendpipeline
+SELECT 'pipelined query';
+\\endpipeline
+});
+ok( $node->log_contains(
+		qr/LOG:.*?temporary file:.*?(?:STATEMENT:\s+SELECT 'pipelined query'|execute <unnamed>: SELECT 'pipelined query')/s,
+		$log_offset),
+	"pipelined query");
+
+note "parse and bind: temporary file dropped";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+SELECT a, a, a FROM foo ORDER BY a OFFSET \$1 \\parse p1
+\\bind_named p1 4993 \\g
+});
+ok($node->log_contains(qr/LOG:.*?temporary file:/s, $log_offset),
+	"parse and bind");
+
+note "simple query: temporary file dropped under SELECT query";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+SELECT a FROM foo ORDER BY a OFFSET 4994;
+END;
+});
+ok( $node->log_contains(
+		qr/SELECT a FROM foo ORDER BY a OFFSET 4994;.*?LOG:.*?temporary file: path/s,
+		$log_offset),
+	"simple query");
+
+note "cursor: temporary file dropped under CLOSE";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+DECLARE mycur CURSOR FOR SELECT a FROM foo ORDER BY a OFFSET 4995;
+FETCH 10 FROM mycur;
+SELECT 1;
+CLOSE mycur;
+END;
+});
+ok( $node->log_contains(
+		qr/CLOSE mycur;.*?LOG:.*?temporary file:/s, $log_offset),
+	"cursor");
+
+note "prepare/execute: temporary file dropped under EXECUTE";
+$log_offset = -s $node->logfile;
+$node->safe_psql(
+	"postgres", qq{
+BEGIN;
+PREPARE p1 AS SELECT a FROM foo ORDER BY a OFFSET 4996;
+EXECUTE p1;
+DEALLOCATE p1;
+END;
+});
+ok( $node->log_contains(
+		qr/EXECUTE p1;.*?LOG:.*?temporary file:/s, $log_offset),
+	"prepare/execute");
+
+$node->stop('fast');
+done_testing();
-- 
2.39.5 (Apple Git-154)

From b2e851c81e75b37ed4e59a76e5f10e53401c96a0 Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Tue, 28 Oct 2025 17:13:41 +0000
Subject: [PATCH v16 1/1] Drop unnamed portal immediately after execution to
 completion

Previously, unnamed portals were kept until the next Bind or the end of
the transaction. This could cause temporary files to persist longer than
expected and make logging not reflect the actual SQL responsible for
the temporary file.

This patch changes exec_execute_message() to drop unnamed portals
immediately after execution to completion, ensuring temporary files are
removed promptly and statement logging correctly reflects the SQL that
created the temporary file.

The documentation is updated to accurately describe the lifetime of
unnamed portals, and test cases are updated to verify temporary file
removal and proper statement logging after unnamed portal execution.

Discussion: https://www.postgresql.org/message-id/CAA5RZ0tTrTUoEr3kDXCuKsvqYGq8OOHiBwoD-dyJocq95uEOTQ%40mail.gmail.com
---
 doc/src/sgml/protocol.sgml                         |  4 ++--
 src/backend/tcop/postgres.c                        |  7 +++++++
 src/test/modules/test_misc/t/009_log_temp_files.pl | 10 +++++-----
 3 files changed, 14 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 9d755232873..d1b9af11b07 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -1006,8 +1006,8 @@ SELCT 1/0;<!-- this typo is intentional -->
    <para>
     If successfully created, a named portal object lasts till the end of the
     current transaction, unless explicitly destroyed.  An unnamed portal is
-    destroyed at the end of the transaction, or as soon as the next Bind
-    statement specifying the unnamed portal as destination is issued.  (Note
+    destroyed at the end of the transaction, or as soon as the statement
+    specifying the unnamed portal as destination is processed to completion.  (Note
     that a simple Query message also destroys the unnamed portal.)  Named
     portals must be explicitly closed before they can be redefined by another
     Bind message, but this is not required for the unnamed portal.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 7dd75a490aa..9a5534f8d13 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2327,6 +2327,13 @@ exec_execute_message(const char *portal_name, long max_rows)
 			 * message.  The next protocol message will start a fresh timeout.
 			 */
 			disable_statement_timeout();
+
+			/*
+			 * We completed fetching from an unnamed portal, we don't need it
+			 * beyond this point.
+			 */
+			if (portal->name[0] == '\0')
+				PortalDrop(portal, false);
 		}
 
 		/* Send appropriate CommandComplete to client */
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
index 462a949e411..383d9971fe2 100644
--- a/src/test/modules/test_misc/t/009_log_temp_files.pl
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -39,7 +39,7 @@ SELECT 'unnamed portal';
 END;
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'unnamed portal'/s,
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"unnamed portal");
 
@@ -51,7 +51,7 @@ SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4991 \\g
 });
 ok( $node->log_contains(qr/LOG:\s+temporary file:/s, $log_offset),
 	"bind and implicit transaction, temporary file removed");
-ok( !$node->log_contains(qr/STATEMENT:/s, $log_offset),
+ok( $node->log_contains(qr/STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset),
 	"bind and implicit transaction, no statement logged");
 
 note "named portal: temporary file dropped under second SELECT query";
@@ -64,7 +64,7 @@ SELECT 'named portal';
 END;
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'named portal'/s,
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"named portal");
 
@@ -78,7 +78,7 @@ SELECT 'pipelined query';
 \\endpipeline
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'pipelined query'/s,
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"pipelined query");
 
@@ -91,7 +91,7 @@ SELECT a, a, a FROM foo ORDER BY a OFFSET \$1 \\parse p1
 });
 ok($node->log_contains(qr/LOG:\s+temporary file:/s, $log_offset),
 	"parse and bind, temporary file removed");
-ok(!$node->log_contains(qr/STATEMENT:/s, $log_offset),
+ok($node->log_contains(qr/STATEMENT:\s+SELECT a, a, a FROM foo ORDER BY a OFFSET \$1/s, $log_offset),
 	"bind and bind, no statement logged");
 
 note "simple query: temporary file dropped under SELECT query";
-- 
2.43.0

From c94588fa1de8a75b2cdea78f548b82e3c55f569f Mon Sep 17 00:00:00 2001
From: Ubuntu <ubuntu@ip-172-31-46-230.ec2.internal>
Date: Tue, 28 Oct 2025 17:13:41 +0000
Subject: [PATCH v17 1/1] Drop unnamed portal immediately after execution to
 completion

Previously, unnamed portals were kept until the next Bind or the end of
the transaction. This could cause temporary files to persist longer than
expected and make logging not reflect the actual SQL responsible for
the temporary file.

This patch changes exec_execute_message() to drop unnamed portals
immediately after execution to completion, ensuring temporary files are
removed promptly and statement logging correctly reflects the SQL that
created the temporary file.

The documentation is updated to accurately describe the lifetime of
unnamed portals, and test cases are updated to verify temporary file
removal and proper statement logging after unnamed portal execution.

Discussion: https://www.postgresql.org/message-id/CAA5RZ0tTrTUoEr3kDXCuKsvqYGq8OOHiBwoD-dyJocq95uEOTQ%40mail.gmail.com
---
 doc/src/sgml/protocol.sgml                     |  4 ++--
 src/backend/tcop/postgres.c                    |  7 +++++++
 .../modules/test_misc/t/009_log_temp_files.pl  | 18 +++++++-----------
 3 files changed, 16 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 9d755232873..d1b9af11b07 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -1006,8 +1006,8 @@ SELCT 1/0;<!-- this typo is intentional -->
    <para>
     If successfully created, a named portal object lasts till the end of the
     current transaction, unless explicitly destroyed.  An unnamed portal is
-    destroyed at the end of the transaction, or as soon as the next Bind
-    statement specifying the unnamed portal as destination is issued.  (Note
+    destroyed at the end of the transaction, or as soon as the statement
+    specifying the unnamed portal as destination is processed to completion.  (Note
     that a simple Query message also destroys the unnamed portal.)  Named
     portals must be explicitly closed before they can be redefined by another
     Bind message, but this is not required for the unnamed portal.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 7dd75a490aa..9a5534f8d13 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2327,6 +2327,13 @@ exec_execute_message(const char *portal_name, long max_rows)
 			 * message.  The next protocol message will start a fresh timeout.
 			 */
 			disable_statement_timeout();
+
+			/*
+			 * We completed fetching from an unnamed portal, we don't need it
+			 * beyond this point.
+			 */
+			if (portal->name[0] == '\0')
+				PortalDrop(portal, false);
 		}
 
 		/* Send appropriate CommandComplete to client */
diff --git a/src/test/modules/test_misc/t/009_log_temp_files.pl b/src/test/modules/test_misc/t/009_log_temp_files.pl
index 462a949e411..46d0c7789ca 100644
--- a/src/test/modules/test_misc/t/009_log_temp_files.pl
+++ b/src/test/modules/test_misc/t/009_log_temp_files.pl
@@ -39,7 +39,7 @@ SELECT 'unnamed portal';
 END;
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'unnamed portal'/s,
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"unnamed portal");
 
@@ -49,10 +49,8 @@ $node->safe_psql(
 	"postgres", qq{
 SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4991 \\g
 });
-ok( $node->log_contains(qr/LOG:\s+temporary file:/s, $log_offset),
-	"bind and implicit transaction, temporary file removed");
-ok( !$node->log_contains(qr/STATEMENT:/s, $log_offset),
-	"bind and implicit transaction, no statement logged");
+ok( $node->log_contains(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s, $log_offset),
+	"bind and implicit transaction");
 
 note "named portal: temporary file dropped under second SELECT query";
 $node->safe_psql(
@@ -64,7 +62,7 @@ SELECT 'named portal';
 END;
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'named portal'/s,
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"named portal");
 
@@ -78,7 +76,7 @@ SELECT 'pipelined query';
 \\endpipeline
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT 'pipelined query'/s,
+		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"pipelined query");
 
@@ -89,10 +87,8 @@ $node->safe_psql(
 SELECT a, a, a FROM foo ORDER BY a OFFSET \$1 \\parse p1
 \\bind_named p1 4993 \\g
 });
-ok($node->log_contains(qr/LOG:\s+temporary file:/s, $log_offset),
-	"parse and bind, temporary file removed");
-ok(!$node->log_contains(qr/STATEMENT:/s, $log_offset),
-	"bind and bind, no statement logged");
+ok($node->log_contains(qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a, a, a FROM foo ORDER BY a OFFSET \$1/s, $log_offset),
+	"parse and bind");
 
 note "simple query: temporary file dropped under SELECT query";
 $log_offset = -s $node->logfile;
-- 
2.43.0