Connection_query in PGBouncer + JDBC

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 .

+4
1

, , :

-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)

pgbouncer ( , ) - . . pgbouncer temp, ...

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

, , " ", . :

-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;
                    ^

DISCARD ALL - , , . , - . , . , - ...

+1

Source: https://habr.com/ru/post/1687778/


All Articles