[pgpool-general: 8921] Re: WARNING: there is no transaction in progress

[Bo Peng]

Hi,

Thank you for sharing the configurations.
It seems it is difficult to reproduce this issue.

If possible, could you enable "log_client_messages = on" and reproduce this issue again?
Please share the pgpool logs.

Thanks!

On Sat, 19 Aug 2023 08:44:28 -0400
Franklin Anderson de Oliveira Souza <franklinbr at gmail.com> wrote:

> Hi,
> 
> This is the config I'm using:
> 
> backend_clustering_mode = 'streaming_replication'
> port = port_value
> unix_socket_directories = '/run/pgpool'
> reserved_connections = 2
> pcp_listen_addresses = '*'
> backend_hostname0 = 'ip_address'
> backend_port0 = port_value
> backend_weight0 = 1
> backend_data_directory0 = '/path/data'
> backend_flag0 = 'ALWAYS_PRIMARY'
> backend_application_name0 = 'MASTER'
> backend_hostname1 = 'ip_address'
> backend_port1 = port_value
> backend_weight1 = 9
> backend_data_directory1 = '/path/data'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> backend_application_name1 = 'Rep1'
> pool_passwd = 'pool_passwd'
> authentication_timeout = 1min
> allow_clear_text_frontend_auth = off
> process_management_mode = dynamic
> process_management_strategy = gentle
> num_init_children = 60
> min_spare_children = 5
> max_spare_children = 10
> max_pool = 4
> child_life_time = 5min
> child_max_connections = 0
> connection_life_time = 0
> client_idle_limit = 0
> log_destination = 'syslog,stderr'
> log_line_prefix = '%t: appname[%a] user[%u] db[%d] pid[%p]: '
> log_connections = on
> log_hostname = on
> log_standby_delay = 'if_over_threshold'
> pid_file_name = '/run/pgpool/pgpool.pid'
> connection_cache = on
> reset_query_list = 'ABORT; DISCARD ALL'
> replicate_select = off
> insert_lock = off
> lobj_lock_table = ''
> load_balance_mode = on
> ignore_leading_white_space = on
> write_function_list = 'currval,lastval,nextval,setval'
> allow_sql_comments = on
> disable_load_balance_on_write = 'transaction'
> sr_check_period = 5
> sr_check_user = 'postgres'
> sr_check_database = 'postgres'
> delay_threshold = 10485760
> prefer_lower_delay_standby = on
> health_check_period = 20
> health_check_timeout = 20
> health_check_user = 'user'
> health_check_database = 'database'
> health_check_max_retries = 5
> hostname0 = ''
> relcache_expire = 3600
> relcache_size = 4096
> relcache_query_target = load_balance_node
> socket_dir = '/run/pgpool'
> pcp_socket_dir = '/run/pgpool'
> backend_socket_dir = '/run/pgpool/'
> 
> 
> Thanks !
> 
> 
> Em qui., 10 de ago. de 2023 às 01:00, Bo Peng <pengbo at sraoss.co.jp> escreveu:
> >
> > Hi,
> >
> > > after setting the parameter prefer_lower_delay_standby = off the errors
> > > disappeared. I will keep it like this.
> >
> > I think it might be a bug of prefer_lower_delay_standby.
> > If you would like to help us to reproduce the issue,
> > could you share your pgpool.conf and the steps to reproduce this issue?
> >
> > Thanks!
> >
> > On Wed, 9 Aug 2023 16:34:15 -0400
> > Franklin Anderson de Oliveira Souza <franklinbr at gmail.com> wrote:
> >
> > > Hello,
> > >
> > > after setting the parameter prefer_lower_delay_standby = off the errors
> > > disappeared. I will keep it like this.
> > >
> > > Em qua., 9 de ago. de 2023 às 00:34, Bo Peng <pengbo at sraoss.co.jp> escreveu:
> > >
> > > > Hi
> > > >
> > > > > We have a java application that uses jdbc to connect to pgpool 4.4.3 that
> > > > > is connected to a master and a replica. It's working but in the logs
> > > > there
> > > > > are thousands of the following warning:
> > > > >
> > > > > WARNING: there is no transaction in progress
> > > > >
> > > > > Any tips to resolve this warning ?! pgpool is committing on the replica
> > > > > with no open transactions.
> > > >
> > > > I am not sure if pgpool sent "commit/abort" or not.
> > > > Could you share the pgpool log and PostgreSQL logs of both master and
> > > > replica?
> > > >
> > > > If it is possible, could you share the query that caused this issue?
> > > > Alternatively, if possible, could you set log_statement = 'all' and share
> > > > the logs?
> > > > Please note that after reproducing the issue,
> > > > disable log_statement = 'all' to save the disk space.
> > > >
> > > > On Tue, 8 Aug 2023 09:28:55 -0400
> > > > Franklin Anderson de Oliveira Souza <franklinbr at gmail.com> wrote:
> > > >
> > > > > Hello everyone,
> > > > >
> > > > > We have a java application that uses jdbc to connect to pgpool 4.4.3 that
> > > > > is connected to a master and a replica. It's working but in the logs
> > > > there
> > > > > are thousands of the following warning:
> > > > >
> > > > > WARNING: there is no transaction in progress
> > > > >
> > > > > Any tips to resolve this warning ?! pgpool is committing on the replica
> > > > > with no open transactions.
> > > > >
> > > > > Tanks
> > > > >
> > > > > --
> > > > > foobar
> > > >
> > > >
> > > > --
> > > > Bo Peng <pengbo at sraoss.co.jp>
> > > > SRA OSS LLC
> > > > TEL: 03-5979-2701 FAX: 03-5979-2702
> > > > URL: https://www.sraoss.co.jp/
> > > >
> > >
> > >
> > > --
> > > foobar
> >
> >
> > --
> > Bo Peng <pengbo at sraoss.co.jp>
> > SRA OSS LLC
> > TEL: 03-5979-2701 FAX: 03-5979-2702
> > URL: https://www.sraoss.co.jp/
> 
> 
> 
> -- 
> foobar


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/