[pgpool-general: 7676] Re: Load-balancing issue with app_name_redirect_preference_list

[Bo Peng]

hello,


I have tested this issue on the following environment:

  PostgreSQL 12 
  postgresql-42.2.11.jar

It seems that the application_name is not included in the startup packet and
the application_name is SET after electing the load balance node.
Currently, Pgpool-II can only detect the application_name from the startup packet.

=======
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  selecting load balance node
2021-08-26 13:57:44: [[unknown]] pid 22417: DETAIL:  selected backend id is 1
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 0 backend pid: 22432 statement: SELECT version()
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  pool_reuse_block: blockid: 0
2021-08-26 13:57:44: [[unknown]] pid 22417: CONTEXT:  while searching system catalog, When relcache is missed
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 0 backend pid: 22432 statement: Parse: SET extra_float_digits = 3
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Parse: SET extra_float_digits = 3
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 0 backend pid: 22432 statement: Bind: SET extra_float_digits = 3
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Bind: SET extra_float_digits = 3
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 0 backend pid: 22432 statement: Execute: SET extra_float_digits = 3
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Execute: SET extra_float_digits = 3
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 0 backend pid: 22432 statement: Parse: SET application_name = 'TEST'
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Parse: SET application_name = 'TEST'
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 0 backend pid: 22432 statement: Bind: SET application_name = 'TEST'
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Bind: SET application_name = 'TEST'
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 0 backend pid: 22432 statement: Execute: SET application_name = 'TEST'
2021-08-26 13:57:44: [[unknown]] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Execute: SET application_name = 'TEST'
2021-08-26 13:57:44: [TEST] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Parse: SELECT 1
2021-08-26 13:57:44: [TEST] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Bind: SELECT 1
2021-08-26 13:57:44: [TEST] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: D message
2021-08-26 13:57:44: [TEST] pid 22417: LOG:  DB node id: 1 backend pid: 22433 statement: Execute: SELECT 1
=======

> We are running a pgpool/postgres cluster with 2 pgpool nodes and 2 postgres nodes in streaming replication setup with load-balancing enabled. Recently we have discovered a problem with certain queries from our Java application, which is caused by the replication being asynchronous (we cannot really use the synchronous replication due to occasional problems with the underlaying infrastructure) - jobs of the application then fail due to finding different data on the STANDBY node than what is expected by querying them on the PRIMARY node. We are now trying to find the way how to limit SELECT queries from this specific application to the PRIMARY node only without turning off the load-balancing completely, so it can still be used by other applications, which don't have a problem with the asynchronous replication.
> 
> We have set the "ApplicationName" parameter in the URL of the data source for our application:
> 
> jdbc:postgresql://aisgpgpvip.cca.cz:9999/aisgdvyv?ApplicationName=AisgDozorovaDS
> 
> ... and then used it in the pgpool.conf like so:
> 
> app_name_redirect_preference_list = 'AisgDozorovaDS:primary'
> 
> After reloading the pgpool service and restarting the application, it does not seem to work - read queries are still load-balanced between both nodes. When I tried to perform some queries from PSQL that is using a PGAPPNAME parameter set to "AisgDozorovaDS" it seems to correctly prevent the session to load-balance itself to the STANDBY node. I am using the pgaudit extension to track the queries for a specific user (application is being ran under that user) and I noticed the following:
> 
> When I issue some query from PSQL it logs on postgres server (in SYSLOG) like this:
> 
> Aug 25 08:53:29 AISGDBD01 postgres[14782]: [8] 2021-08-25 08:53:29.020 CEST ::: APP - AisgDozorovaDS :A: DB - aisgdvyv :D: USER - aisg :U: HOST - 172.20.15.64 :H: CMD - SELECT :C: |LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,SELECT count(*) FROM databasechangelog;,<not logged>
> 
> You can notice the application name here being used ("AisgDozorovaDS") and also the type of the command being "SELECT".
> 
> When the application issues its own query it looks like this:
> 
> Aug 25 09:28:58 AISGDBD02 postgres[14369]: [459] 2021-08-25 09:28:58.583 CEST ::: APP - AisgDozorovaDS :A: DB - aisgdvyv :D: USER - aisg :U: HOST - 172.20.15.64 :H: CMD - BIND :C: |LOG:  AUDIT: SESSION,452,1,READ,SELECT,,,SELECT * FROM quartz.qrtz_SCHEDULER_STATE WHERE SCHED_NAME = 'AISG-QUARTZ',<not logged>
> 
> The application name is the same, but the type of the command is "BIND".
> 
> Do you think this might be reason for the app_name_redirect_preference_list parameter working for the PSQL (and also for pgAdmin for example), but not the Java application? What can we test to verify that?
> 
> I have also considered it might be because the JDBC driver doesn't set the application name from the get go (in the first packet of the session), but rather after a login, so the pgpool cannot utilize it, but according to your documentation it was problem for JDBC version 9.3 or older, while we are using the version 42.2.5 for our application.
> 
> I attached our current pgpool.conf to this message, in it you can also see that I have been trying the black_query_pattern_list parameter to catch some specific queries that our application uses, but I would prefer to use it as a second option, since it does not have to cover 100% of the application activity and can also affect other applications that might use some similar query. Thank you for any help.
> 
> With best regards,
> Vladimír Láznička


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
http://www.sraoss.co.jp/