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

[Tatsuo Ishii]

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