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

[Tatsuo Ishii]

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
>> >> >>
>> >>
>>