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

[Avi Raboah]

All the configurations I didn’t provided are using the default.

One more thing, the issue is occurring only if query cache enabled

Thanks,

Avi

On Wed, 22 Jun 2022 at 4:10 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> 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
> >> >> >> >>
> >> >> >>
> >> >>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220622/762c4445/attachment.htm>