v1-0001-show-temp-file-creating-query.patch

From 6bd06e7cfaa4f4efb22be08ce72886324c69e566 Mon Sep 17 00:00:00 2001
From: Mircea Cadariu <cadariu.mircea@gmail.com>
Date: Wed, 12 Nov 2025 10:45:21 +0000
Subject: [PATCH v1] show temp file creating query

---
 src/backend/storage/file/fd.c                 | 40 ++++++++++++++++---
 src/backend/tcop/postgres.c                   | 10 -----
 .../modules/test_misc/t/009_log_temp_files.pl | 16 ++++----
 3 files changed, 42 insertions(+), 24 deletions(-)

diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index a4ec7959f3..a5f4e97f92 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -97,6 +97,7 @@
 #include "storage/aio.h"
 #include "storage/fd.h"
 #include "storage/ipc.h"
+#include "tcop/tcopprot.h"
 #include "utils/guc.h"
 #include "utils/guc_hooks.h"
 #include "utils/resowner.h"
@@ -206,6 +207,7 @@ typedef struct vfd
 	/* NB: fileName is malloc'd, and must be free'd when closing the VFD */
 	int			fileFlags;		/* open(2) flags for (re)opening the file */
 	mode_t		fileMode;		/* mode to pass to open(2) */
+	char	   *temp_file_creator_query;	/* creator query for this temp file, if any */
 } Vfd;
 
 /*
@@ -1482,6 +1484,11 @@ FreeVfd(File file)
 		free(vfdP->fileName);
 		vfdP->fileName = NULL;
 	}
+	if (vfdP->temp_file_creator_query != NULL)
+	{
+		free(vfdP->temp_file_creator_query);
+		vfdP->temp_file_creator_query = NULL;
+	}
 	vfdP->fdstate = 0x0;
 
 	vfdP->nextFree = VfdCache[0].nextFree;
@@ -1524,18 +1531,27 @@ FileAccess(File file)
 
 /*
  * Called whenever a temporary file is deleted to report its size.
+ * If temp_file_creator_query is non-NULL, it represents the query that created this
+ * temp file and will be logged.
  */
 static void
-ReportTemporaryFileUsage(const char *path, off_t size)
+ReportTemporaryFileUsage(const char *path, off_t size, const char *temp_file_creator_query)
 {
 	pgstat_report_tempfile(size);
 
 	if (log_temp_files >= 0)
 	{
 		if ((size / 1024) >= log_temp_files)
-			ereport(LOG,
-					(errmsg("temporary file: path \"%s\", size %lu",
-							path, (unsigned long) size)));
+		{
+			if (temp_file_creator_query != NULL)
+				ereport(LOG,
+						(errmsg("temporary file: path \"%s\", size %lu, created due to: %s",
+								path, (unsigned long) size, temp_file_creator_query)));
+			else
+				ereport(LOG,
+						(errmsg("temporary file: path \"%s\", size %lu",
+								path, (unsigned long) size)));
+		}
 	}
 }
 
@@ -1842,6 +1858,12 @@ OpenTemporaryFileInTablespace(Oid tblspcOid, bool rejectError)
 				 tempfilepath);
 	}
 
+	/*
+	 * Remember the creator query for this temp file.
+	 */
+	if (file > 0 && debug_query_string != NULL)
+		VfdCache[file].temp_file_creator_query = strdup(debug_query_string);
+
 	return file;
 }
 
@@ -1889,6 +1911,12 @@ PathNameCreateTemporaryFile(const char *path, bool error_on_failure)
 	/* Register it for automatic close. */
 	RegisterTemporaryFile(file);
 
+	/*
+	 * Remember the creator query for this temp file.
+	 */
+	if (debug_query_string != NULL)
+		VfdCache[file].temp_file_creator_query = strdup(debug_query_string);
+
 	return file;
 }
 
@@ -1960,7 +1988,7 @@ PathNameDeleteTemporaryFile(const char *path, bool error_on_failure)
 	}
 
 	if (stat_errno == 0)
-		ReportTemporaryFileUsage(path, filestats.st_size);
+		ReportTemporaryFileUsage(path, filestats.st_size, NULL);
 	else
 	{
 		errno = stat_errno;
@@ -2048,7 +2076,7 @@ FileClose(File file)
 
 		/* and last report the stat results */
 		if (stat_errno == 0)
-			ReportTemporaryFileUsage(vfdP->fileName, filestats.st_size);
+			ReportTemporaryFileUsage(vfdP->fileName, filestats.st_size, vfdP->temp_file_creator_query);
 		else
 		{
 			errno = stat_errno;
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 2bd8910268..7dd75a490a 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2327,16 +2327,6 @@ 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.  There is no need
-			 * for it beyond this point, so drop it now rather than wait for
-			 * the next Bind message to do this cleanup.  This ensures that
-			 * the correct statement is logged when cleaning up temporary file
-			 * usage.
-			 */
-			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 7ecd301ae2..4d88577e98 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 a FROM foo ORDER BY a OFFSET \$1/s,
+		qr/LOG:\s+temporary file: path.*created due to: SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"unnamed portal");
 
@@ -51,7 +51,7 @@ $node->safe_psql(
 SELECT a FROM foo ORDER BY a OFFSET \$1 \\bind 4991 \\g
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
+		qr/LOG:\s+temporary file: path.*created due to: SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"bind and implicit transaction");
 
@@ -65,7 +65,7 @@ SELECT 'named portal';
 END;
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
+		qr/LOG:\s+temporary file: path.*created due to: SELECT a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"named portal");
 
@@ -79,7 +79,7 @@ SELECT 'pipelined query';
 \\endpipeline
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET \$1/s,
+		qr/LOG:\s+temporary file: path.*created due to: 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
 \\bind_named p1 4993 \\g
 });
 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,
+		qr/LOG:\s+temporary file: path.*created due to: SELECT a, a, a FROM foo ORDER BY a OFFSET \$1/s,
 		$log_offset),
 	"parse and bind");
 
@@ -104,7 +104,7 @@ SELECT a FROM foo ORDER BY a OFFSET 4994;
 END;
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+SELECT a FROM foo ORDER BY a OFFSET 4994;/s,
+		qr/LOG:\s+temporary file: path.*created due to: SELECT a FROM foo ORDER BY a OFFSET 4994;/s,
 		$log_offset),
 	"simple query");
 
@@ -120,7 +120,7 @@ CLOSE mycur;
 END;
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+CLOSE mycur;/s,
+		qr/LOG:\s+temporary file: path.*created due to: FETCH 10 FROM mycur;/s,
 		$log_offset),
 	"cursor");
 
@@ -135,7 +135,7 @@ DEALLOCATE p1;
 END;
 });
 ok( $node->log_contains(
-		qr/LOG:\s+temporary file: path.*\n.*\ STATEMENT:\s+EXECUTE p1;/s,
+		qr/LOG:\s+temporary file: path.*created due to: EXECUTE p1;/s,
 		$log_offset),
 	"prepare/execute");
 
-- 
2.39.5 (Apple Git-154)