connect_query setting in PGBouncer + JDBC

Question

I'm configuring PGBouncer as the database connection pool for my application. I'm also using it with the session pool mode.

Due to some application requirements I need to keep a temporary table during the usage of each connection. What I'm currently doing is using the connect_query setting to create my temporary table.

According to the documentation this query "is executed after a connection is established". As far as I have been able to check this means that the connect_query is executed each time a connection is borrowed from the pool.

What I would like to avoid is the following scenario:

1. I borrow a connection from the pool, if the physical connection does not exists, it will be created. The connect_query will be executed.
2. I return the connection to the pool.
3. I request a connection to the pool again, let's suppose that the pool reuses the same connection used in step 1) and returns it. The connect_query is executed again.

UPDATE I'm able to see that the connect_query is being executed once per connection request when connecting to PGBouncer through JDBC and use the connection to execute a query. See the below java class as an example:

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 query to pg_stat_statements:

select * from pg_stat_statements

I can see that the connect_query has been executed once per connection retrieved. But if I comment this line, to not execute the query on each iteration:

executeQuery(conn);

I can not reproduce this issue,i.e., in the pg_stat_statements the connect_query does not appear once per connection retrieved.

Answer 1

I think it works just as you need it:

-bash-4.2$ psql -p 6432 -d t -U v -h 1.1.1.1
Password for user v:
psql (9.3.18)
Type "help" for help.

t=> select * from tt;
 i
---
(0 rows)

t=> insert into tt select 1;
INSERT 0 1
t=> select * from dblink('port=6432 dbname=t hostaddr=1.1.1.1 user=v password=v','select i from tt') as t(i int);
 i
---
(0 rows)

t=> select i from tt;
 i
---
 1
(1 row)

t=> select * from dblink('port=5432 dbname=mon hostaddr=1.1.1.1 user=v password=v','select i from tt') as t(i int);
ERROR:  relation "tt" does not exist
CONTEXT:  Error occurred on dblink connection named "unnamed": could not execute query.
t=> select current_database();
 current_database
------------------
 mon
(1 row)

a new session over pgbouncer silently creates temp table ( no exception it existed) and is available for new session - no recreation or check needed. in session pool mode contents are different for different sessions. If I connect not over pgbouncer temp table does not exist...

here is config:

t=> \! head -2 /etc/pgbouncer/pgbouncer.ini
[databases]
t = host=/var/run/postgresql dbname=mon connect_query = 'create temp table tt(i int)'

UPDATE

And now I think it wont work for you exactly because it creates table on "phisical connect" and not on taking connection form the pool. here is evidence:

-bash-4.2$ psql -p 6432 -d t -U v -h localhost
Password for user v:
psql (9.3.18)
Type "help" for help.

t=> insert into tt select 5;
INSERT 0 1
t=> \q
-bash-4.2$ psql -c "select query from pg_stat_activity"
               query
------------------------------------
 DISCARD ALL
 select query from pg_stat_activity
(2 rows)

-bash-4.2$ psql -p 6432 -d t -U v -h localhost
Password for user v:
psql (9.3.18)
Type "help" for help.

t=> insert into tt select 5;
ERROR:  relation "tt" does not exist
LINE 1: insert into tt select 5;
                    ^

before giving a connection from the pool it DISCARD ALL - and thus drops all temp tables. this is why my code above both sessions have its own table, while the latest example - not. because on connection closed table got dropped and session went back to pool. so on next connect a new connection was not initiated, but old reused - and the table is not there already...