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();
import psycopg
with psycopg.connect("dbname=postgres user=postgres port=5434 host=localhost") as conn:
conn.execute("SELECT * FROM foo ORDER BY a OFFSET %s LIMIT %s", [100000,2])
conn.execute("SELECT 1")
conn.close()
// CREATE UNLOGGED TABLE foo(a int);
// INSERT INTO foo SELECT * FROM generate_series(1, 200000);
public class Babar {
public static void main(String[] args)
throws ClassNotFoundException, java.sql.SQLException {
Class.forName("org.postgresql.Driver");
java.sql.Connection conn =
java.sql.DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5434/postgres?user=postgres"
);
conn.setAutoCommit(false);
java.sql.ResultSet rs;
java.sql.PreparedStatement stmt =
conn.prepareStatement(
"SELECT * FROM foo ORDER BY a OFFSET ? LIMIT 2"
);
java.sql.PreparedStatement stmt2 =
conn.prepareStatement(
"SELECT 1"
);
stmt.setInt(1, 100000);
rs = stmt.executeQuery();
rs.close();
stmt.close();
rs = stmt2.executeQuery();
rs.close();
stmt2.close();
conn.commit();
conn.close();
}
}
