I configure PGBouncer as a database connection pool for my application. I also use it in session pool mode.
Due to some application requirements, I need to save a temporary table while using each connection. I am currently using the connect_query parameter to create my temporary table.
According to the documentation, this request is "executed after the connection is established." As far as I was able to verify this means that connect_query is executed every time the connection is borrowed from the pool.
I would like to avoid the following scenario:
- I take the connection from the pool, if the physical connection does not exist, it will be created. connect_query will be executed.
- I am returning a connection to the pool.
- I again request a connection to the pool, suppose the pool repeats the same connection as in step 1 and returns it. connect_query is executed again.
UPDATE
I can see that connect_query is executed once per connection request when connecting to PGBouncer via JDBC and uses the connection to execute the request. The following is an example java class:
public class TestPgbouncerConnectQuery {
public static void main(String[] args) {
for (int i = 0; i < 1000; i++) {
try {
System.out.println("Iteration: " + i);
Connection conn = getConnection();
executeQuery(conn);
conn.close();
} catch (SQLException e) {
}
}
}
private static Connection getConnection() {
Connection conn = null;
try {
Properties properties = new Properties();
properties.setProperty("user", "myuser");
properties.setProperty("password", "mypass");
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection("jdbc:postgresql://localhost:6432/mydatabase", properties);
conn.setAutoCommit(false);
} catch (Exception e) {
}
return conn;
}
private static void executeQuery(Connection conn) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("select 1");
ps.executeQuery();
} catch (Exception e) {
} finally {
if (ps != null) {
try {
ps.close();
} catch (Exception e) {
}
}
}
}
}
After executing this code, if I ask pg_stat_statements:
select * from pg_stat_statements
I see that connect_query was executed once per connection received. But if I comment on this line so as not to execute the request at each iteration:
executeQuery(conn);
, .. pg_stat_statements connect_query .