Hi,
It seems there's a bug in the logging of temporary file usage when the
extended protocol is used with unnamed portals.
For example, with the attached Java / pgJDBC programs, we get the
following logs:
[...] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp525566.0",
size 2416640
[..] STATEMENT: SELECT 1
but it should be:
[...] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp538230.0",
size 2416640
[...] STATEMENT: SELECT * FROM foo ORDER BY a OFFSET $1 LIMIT 2
It has been tested with HEAD and REL_17_STABLE.
My guess is that there's a race somewhere, probably when the global
variable "debug_query_string" is set.
The unnamed portal is closed when the BIND of the next query arrives
(here: SELECT 1), and I suspect that the variable is set before the
temporary file is deleted (and logged).
pgJDBC uses unnamed portals, but I don't think this is specific to JDBC.
I see the same problem with the attached Python / psycopg3 program.
I think it would be better if the drivers used named portals all the
time (and an explicit close message), but this seems to be a postgres bug.
What do you think?
Best regards,
Frédéric
PS : the dataset is created like this on the server:
CREATE UNLOGGED TABLE foo(a int);
INSERT INTO foo SELECT * FROM generate_series(1, 200000);
ALTER SYSTEM SET log_temp_files = 0;
ALTER SYSTEM SET log_min_duration_statement = -1
SELECT pg_reload_conf();
