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

[Tatsuo Ishii]

> 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 --------------
A non-text attachment was scrubbed...
Name: fix_aborted_transaction.patch
Type: text/x-patch
Size: 2630 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220507/b2dd7da7/attachment.bin>