[pgpool-general: 8233] Re: Pg_stat_activity hung queries issue

[Tatsuo Ishii]

What is backend_clustering_mode?

> Pgpool.conf-
> 
> listen_addresses = '*'
> 
> port = '9999'
> 
> socket_dir = '/var/run/pgpool'
> 
> pcp_socket_dir = '/var/run/pgpool'
> 
> wd_ipc_socket_dir = '/var/run/pgpool'
> 
> pid_file_name = '/var/run/pgpool/pgpool.pid'
> 
> backend_hostname0 = db_instance
> 
> backend_port0 = '5432'
> 
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> 
> backend_weight0 = '1'
> 
> connection_cache = on
> 
> num_init_children = 200
> 
> child_life_time = 0
> 
> max_pool = 4
> 
> log_destination = 'stderr'
> 
> log_line_prefix = '%m: %a pid %p: '
> 
> log_connections = off
> 
> log_disconnections = off
> 
> log_statement = off
> 
> log_client_messages = on
> 
> client_min_messages = info
> 
> log_min_messages = debug
> 
> logging_collector = on
> 
> log_directory = '/tmp/pgpool_logs'
> 
> log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
> 
> log_file_mode = 0600
> 
> log_truncate_on_rotation = off
> 
> log_rotation_age = 1d
> 
> log_rotation_size = 200MB
> 
> sr_check_user = 'user'
> 
> sr_check_password = 'password'
> 
> sr_check_period = '10'
> 
> memory_cache_enabled = 'on'
> 
> memqcache_total_size = 64MB
> 
> memqcache_max_num_cache = 1000000
> 
> memqcache_maxcache = 400kB
> 
> memqcache_cache_block_size = 1MB
> 
> memqcache_oiddir = '/var/log/pgpool/oiddir'
> 
> cache_safe_memqcache_table_list = ''
> 
> cache_unsafe_memqcache_table_list = ''
> 
> check_unlogged_table = on
> 
> health_check_period = '10'
> 
> health_check_user = 'user'
> 
> failover_on_backend_error = 'off'
> 
> 
> 
> 
> On Tue, 21 Jun 2022 at 10:51 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> Please share the log with log_client_messages = on so that I can see
>> what your client actually is sending.  Also I need pgpool.conf.
>>
>> > We are using asyncPg module in python in order to send our queries the
>> > difference between that module to psycopg is that asyncpg using extended
>> > queries.
>> > And I can see that the issue is reproduced in case transaction aborted
>> and
>> > the catalog queries are launched.
>> >
>> > I checked in the code and found that check_transaction_state_and_abort
>> > happens only in simpleQuery process.
>> >
>> > Thanks,
>> >
>> > Avi
>> >
>> > On Tue, 21 Jun 2022 at 6:43 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >
>> >> Can you elaborate more?
>> >>
>> >> I tried following case and pgpool ignores all queries from client
>> >> after a parse error occurs as expected.
>> >>
>> >> pgproto -d test -p 11000 -f ../pgproto.data
>> >> FE=> Parse(stmt="", query="BEGIN")
>> >> FE=> Bind(stmt="", portal="")
>> >> FE=> Execute(portal="")
>> >> FE=> Parse(stmt="", query="INSERT INTO non_existing_table values(1)")
>> >> FE=> Bind(stmt="", portal="")
>> >> FE=> Execute(portal="")
>> >> FE=> Parse(stmt="", query="END")
>> >> FE=> Bind(stmt="", portal="")
>> >> FE=> Execute(portal="")
>> >> FE=> Sync
>> >> <= BE ParseComplete
>> >> <= BE BindComplete
>> >> <= BE CommandComplete(BEGIN)
>> >> <= BE ErrorResponse(S ERROR V ERROR C 42P01 M relation
>> >> "non_existing_table" does not exist P 13 F parse_relation.c L 1381 R
>> >> parserOpenTable )
>> >> <= BE ReadyForQuery(E)
>> >> FE=> Terminate
>> >>
>> >> > Hi,
>> >> >
>> >> > After investigating the code I found that the patch you provided is
>> >> fixing
>> >> > the problem only for simple query. In extended query the issue still
>> >> exist.
>> >> >
>> >> > Do you know where is the right place to add the transaction failure
>> >> > verification in extended query process?
>> >> > On Mon, 9 May 2022 at 16:17 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >> >
>> >> >> The patch (plus more fix) has been committed to master and 4.3 stable
>> >> >> branches.
>> >> >>
>> >> >>
>> https://www.pgpool.net/pipermail/pgpool-committers/2022-May/008592.html
>> >> >>
>> https://www.pgpool.net/pipermail/pgpool-committers/2022-May/008591.html
>> >> >>
>> >> >> The patches will appear in the next release (May 19, 2022).
>> >> >>
>> >> >> I have not pushed to other stable branches because the fix is not
>> >> >> small and I wanted to minimize risks.
>> >> >>
>> >> >> > Thanks a lot for the patch have a great weekend
>> >> >> >
>> >> >> > On Sat, 7 May 2022 at 13:35 Tatsuo Ishii <ishii at sraoss.co.jp>
>> wrote:
>> >> >> >
>> >> >> >> > Hi,
>> >> >> >> >
>> >> >> >> > Still thinking.
>> >> >> >> >
>> >> >> >> > Best reagards,
>> >> >> >> > --
>> >> >> >> > Tatsuo Ishii
>> >> >> >> > SRA OSS, Inc. Japan
>> >> >> >> > English: http://www.sraoss.co.jp/index_en.php
>> >> >> >> > Japanese:http://www.sraoss.co.jp
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >> Hi,
>> >> >> >> >>
>> >> >> >> >> Any news about this thread?
>> >> >> >> >>
>> >> >> >> >> Thanks,
>> >> >> >> >> Avi.
>> >> >> >> >>
>> >> >> >> >> On Mon, 25 Apr 2022 at 11:13 Avi Raboah <avi.raboah at gmail.com>
>> >> >> wrote:
>> >> >> >> >>
>> >> >> >> >>> It will be perfect thanks a lot!
>> >> >> >> >>>
>> >> >> >> >>> On Mon, 25 Apr 2022 at 10:47 Tatsuo Ishii <ishii at sraoss.co.jp
>> >
>> >> >> wrote:
>> >> >> >> >>>
>> >> >> >> >>>> > No, it doesn't.
>> >> >> >> >>>> > In that case when you make the select statement, piggy
>> >> internal
>> >> >> >> queries
>> >> >> >> >>>> > failed and the session rebooted
>> >> >> >> >>>>
>> >> >> >> >>>> I see it now.
>> >> >> >> >>>>
>> >> >> >> >>>> test=# begin;
>> >> >> >> >>>> BEGIN
>> >> >> >> >>>> test=*# insert into ttt values(1);
>> >> >> >> >>>> ERROR:  relation "ttt" does not exist
>> >> >> >> >>>> LINE 1: insert into ttt values(1);
>> >> >> >> >>>>                     ^
>> >> >> >> >>>> test=!# select * from t1;
>> >> >> >> >>>> FATAL:  Backend throw an error message
>> >> >> >> >>>> DETAIL:  Exiting current session because of an error from
>> >> backend
>> >> >> >> >>>> HINT:  BACKEND Error: "current transaction is aborted,
>> commands
>> >> >> >> ignored
>> >> >> >> >>>> until end of transaction block"
>> >> >> >> >>>> server closed the connection unexpectedly
>> >> >> >> >>>>         This probably means the server terminated abnormally
>> >> >> >> >>>>         before or while processing the request.
>> >> >> >> >>>> The connection to the server was lost. Attempting reset:
>> >> Succeeded.
>> >> >> >> >>>>
>> >> >> >> >>>> I think pgpool could remember that current transaction is in
>> >> abort
>> >> >> >> >>>> status and does not issue the piggyback query when the selec
>> is
>> >> >> >> >>>> issued. Let me see what we can do for this.
>> >> >> >>
>> >> >> >> I have come up with a patch. In this patch pgpool checks if
>> current
>> >> >> >> transaction is in abort status. If so, it does not accept new
>> query
>> >> at
>> >> >> >> all and just reply back frontend with the message:
>> >> >> >>
>> >> >> >> "current transaction is aborted, commands ignored until end of
>> >> >> transaction
>> >> >> >> block"
>> >> >> >>
>> >> >> >> until commit or abort command is sent from frontend. After
>> applying
>> >> >> >> the patch the example session above looks like this:
>> >> >> >>
>> >> >> >> test=# begin;
>> >> >> >> BEGIN
>> >> >> >> test=*# insert into ttt values(1);
>> >> >> >> ERROR:  relation "ttt" does not exist
>> >> >> >> LINE 1: insert into ttt values(1);
>> >> >> >>                     ^
>> >> >> >> test=!# select * from t1;
>> >> >> >> ERROR:  current transaction is aborted, commands ignored until
>> end of
>> >> >> >> transaction block
>> >> >> >>
>> >> >> >> The patch was tested in master branch, but I believe it can be
>> >> >> >> applied to 4.3 stable branch as well.
>> >> >> >>
>> >> >> >> Best reagards,
>> >> >> >> --
>> >> >> >> Tatsuo Ishii
>> >> >> >> SRA OSS, Inc. Japan
>> >> >> >> English: http://www.sraoss.co.jp/index_en.php
>> >> >> >> Japanese:http://www.sraoss.co.jp
>> >> >> >>
>> >> >>
>> >>
>>