v2-0001-show-temp-file-creating-query.patch
text/plain
Filename: v2-0001-show-temp-file-creating-query.patch
Type: text/plain
Part: 0
From 08e5e884a2d86e0510d29c94fdf17405a25d822c 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 | 39 ++++++++++++++++---
src/backend/tcop/postgres.c | 10 -----
src/include/miscadmin.h | 2 +
src/include/tcop/tcopprot.h | 1 -
.../modules/test_misc/t/009_log_temp_files.pl | 16 ++++----
5 files changed, 43 insertions(+), 25 deletions(-)
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index a4ec7959f3..f03d31fbd1 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -206,6 +206,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 +1483,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 +1530,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 +1857,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 +1910,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 +1987,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 +2075,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/include/miscadmin.h b/src/include/miscadmin.h
index 9a7d733dde..030ad593a8 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -208,6 +208,8 @@ extern PGDLLIMPORT Oid MyDatabaseId;
extern PGDLLIMPORT Oid MyDatabaseTableSpace;
+extern PGDLLIMPORT const char *debug_query_string;
+
extern PGDLLIMPORT bool MyDatabaseHasLoginEventTriggers;
/*
diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h
index c1bcfdec67..962eec7f9b 100644
--- a/src/include/tcop/tcopprot.h
+++ b/src/include/tcop/tcopprot.h
@@ -23,7 +23,6 @@
typedef struct ExplainState ExplainState; /* defined in explain_state.h */
extern PGDLLIMPORT CommandDest whereToSendOutput;
-extern PGDLLIMPORT const char *debug_query_string;
extern PGDLLIMPORT int PostAuthDelay;
extern PGDLLIMPORT int client_connection_check_interval;
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)