Hi,
I understand the point that’s been raised. Would it be an option to
indeed revert the portal drop in postgres.c, but then append the right
query in the "temporary file: ..." log line instead? This would work at
least for me.
Attached is a POC patch (contains a layering violation, hoping it could
be avoided somehow).
Kind regards,
Mircea Cadariu
From 6bd06e7cfaa4f4efb22be08ce72886324c69e566 Mon Sep 17 00:00:00 2001
From: Mircea Cadariu <[email protected]>
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)
