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

[Avi Raboah]

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/20220621/e7eed6f8/attachment.htm>