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

[Karel Svec]

Hi,

version: pgpool-II-4.4.2-1.rhel8.x86_64

log file attached, there is pid 3996197 that is affected by the autosave 
issue, in database are connections reported as idle in transaction and 
waiting on client. This testing environment has just primary database 
configured.

Our development team has decided to get rid of autosave for future 
releases, but I still have the current version, so I can test your 
recommendations if you can provide any.

Thanks,

Karel.


requested setting:

#  egrep 'primary_routing_query_pattern_list|log_client_messages' 
pgpool.conf

log_client_messages = on
#primary_routing_query_pattern_list = ''


# grep ^[a-z] pgpool.conf
backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 9999
unix_socket_directories = '/run/pgpool-II,/var/run/postgresql/'
pc_listen_addresses = 'localhost'
backend_hostname0 = '10.2.20.105'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/14/data'
backend_flag0 = 'ALWAYS_PRIMARY'
backend_application_name0 = 'primarydb'
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 = 800
log_destination = 'stderr'
log_connections = on
log_disconnections = off
log_statement = on
log_client_messages = on
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
client_min_messages = log           # values in order of decreasing detail:
log_min_messages = info             # values in order of decreasing detail:
logging_collector = on
log_directory = '/var/log/pgpool_logs'
pid_file_name = '/run/pgpool-II/pgpool.pid'
sr_check_user = 'nobody'
sr_check_password = 'b'
health_check_user = 'nobody'
health_check_password = 'b'
hostname0 = ''
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = 'b'

On 1/11/24 10:01, Bo Peng wrote:
> 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:
>
>     ...
>
>
>     connection example:
>     url=jdbc:postgresql://vbdb01-czdc-p.lx.finshape.com:9999/jtb_gen_test?currentSchema=udebs&autosave=always&cleanupSavepoints=true
>     <http://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/20240122/9b50c714/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool-2024-01-19_143548.log.gz
Type: application/gzip
Size: 245222 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240122/9b50c714/attachment.gz>