[pgpool-general: 8994] Re: autosave=always jdbc option & it only sends query SAVEPOINT PGJDBC_AUTOSAVE and hangs

[Bo Peng]

Hi,

Does the same issue occur if you set primary_routing_query_pattern_list =
''?

Could you set log_client_messages = on and share the logs?
Which version are you using?

Best regards,

2023年12月27日(水) 23:50 Karel Svec <karel.svec at finshape.com>:

> Hi,
>
> we are facing problem when we use pgpool as proxy our application with
> *autosave=always&cleanupSavepoints=true* options in url wont start
> properly, it hangs immediately issuing only *SAVEPOINT PGJDBC_AUTOSAVE , *there
> are no errors around, pgpool log is attached.
>
> When the application is connected directly to postgres db (bypassing
> pgpool) it works.
>
> My question is, can we use *autosave *connection parameter
> https://jdbc.postgresql.org/documentation/use/  with pgpool ?
>
> I suppose savepoint makes sense only on primary db so I tried to route
> such query to primary but nothing changed, it hangs as well.
>
> pgpool settings:
>
> backend_clustering_mode = 'streaming_replication'
> listen_addresses = '*'
> port = 9999
> unix_socket_directories = '/var/run/postgresql/'
> pcp_listen_addresses = 'localhost'
> pcp_port = 9898
> pcp_socket_dir = '/var/run/postgresql/'
> backend_hostname0 = '10.2.20.234'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/pgsql/14/data'
> backend_flag0 = 'ALWAYS_PRIMARY'
> backend_application_name0 = 'primarydb'
> backend_hostname1 = '10.2.20.233'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/pgsql/14/data'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> backend_application_name1 = 'standbydb'
> enable_pool_hba = off
> pool_passwd = ''
> authentication_timeout = 1min
> allow_clear_text_frontend_auth = on
> ssl = on
> ssl_key = '/var/lib/pgsql/14/data/server.key'
> ssl_cert = '/var/lib/pgsql/14/data/server.crt'
> num_init_children = 32
> min_spare_children = 5
> max_spare_children = 10
> child_life_time = 5min
> log_destination = 'stderr'
> log_connections = on
> log_disconnections = on
> log_hostname = on
> log_per_node_statement = on
> syslog_facility = 'LOCAL0'
> syslog_ident = 'pgpool'
> logging_collector = on
> log_directory = '/var/log/pgpool_logs'
> pid_file_name = '/run/pgpool-II/pgpool.pid'
> logdir = '/var/run/postgresql'
> load_balance_mode = on
> primary_routing_query_pattern_list = 'SAVEPOINT.*;select
> GLOBALSYSTEMPARAMETER.paramname.*;BEGIN.*'
> sr_check_period = 10
> sr_check_user = 'repmgr'
> sr_check_password = '******'
> sr_check_database = 'postgres'
> delay_threshold = 10000000
> health_check_user = 'nobody'
> health_check_password = '****'
> hostname0 = ''
>
>
> connection example: url=jdbc:postgresql://
> vbdb01-czdc-p.lx.finshape.com:9999/jtb_gen_test?currentSchema=udebs&autosave=always&cleanupSavepoints=true
>
>
> I will be grateful for any advice
>
> Karel.
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240111/cdb6f52e/attachment.htm>