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

[Tatsuo Ishii]

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