<div dir="auto">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. </div><div dir="auto">And I can see that the issue is reproduced in case transaction aborted and the catalog queries are launched.</div><div dir="auto"><br></div><div dir="auto">I checked in the code and found that check_transaction_state_and_abort happens only in simpleQuery process. </div><div dir="auto"><br></div><div dir="auto">Thanks, </div><div dir="auto"><br></div><div dir="auto">Avi</div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 21 Jun 2022 at 6:43 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)">Can you elaborate more?<br>
<br>
I tried following case and pgpool ignores all queries from client<br>
after a parse error occurs as expected.<br>
<br>
pgproto -d test -p 11000 -f ../pgproto.data<br>
FE=> Parse(stmt="", query="BEGIN")<br>
FE=> Bind(stmt="", portal="")<br>
FE=> Execute(portal="")<br>
FE=> Parse(stmt="", query="INSERT INTO non_existing_table values(1)")<br>
FE=> Bind(stmt="", portal="")<br>
FE=> Execute(portal="")<br>
FE=> Parse(stmt="", query="END")<br>
FE=> Bind(stmt="", portal="")<br>
FE=> Execute(portal="")<br>
FE=> Sync<br>
<= BE ParseComplete<br>
<= BE BindComplete<br>
<= BE CommandComplete(BEGIN)<br>
<= 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 )<br>
<= BE ReadyForQuery(E)<br>
FE=> Terminate<br>
<br>
> Hi,<br>
> <br>
> After investigating the code I found that the patch you provided is fixing<br>
> the problem only for simple query. In extended query the issue still exist.<br>
> <br>
> Do you know where is the right place to add the transaction failure<br>
> verification in extended query process?<br>
> On Mon, 9 May 2022 at 16:17 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> The patch (plus more fix) has been committed to master and 4.3 stable<br>
>> branches.<br>
>><br>
>> <a href="https://www.pgpool.net/pipermail/pgpool-committers/2022-May/008592.html" rel="noreferrer" target="_blank">https://www.pgpool.net/pipermail/pgpool-committers/2022-May/008592.html</a><br>
>> <a href="https://www.pgpool.net/pipermail/pgpool-committers/2022-May/008591.html" rel="noreferrer" target="_blank">https://www.pgpool.net/pipermail/pgpool-committers/2022-May/008591.html</a><br>
>><br>
>> The patches will appear in the next release (May 19, 2022).<br>
>><br>
>> I have not pushed to other stable branches because the fix is not<br>
>> small and I wanted to minimize risks.<br>
>><br>
>> > Thanks a lot for the patch have a great weekend<br>
>> ><br>
>> > On Sat, 7 May 2022 at 13:35 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
>> ><br>
>> >> > Hi,<br>
>> >> ><br>
>> >> > Still thinking.<br>
>> >> ><br>
>> >> > Best reagards,<br>
>> >> > --<br>
>> >> > Tatsuo Ishii<br>
>> >> > SRA OSS, Inc. Japan<br>
>> >> > English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> > Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >> ><br>
>> >> ><br>
>> >> >> Hi,<br>
>> >> >><br>
>> >> >> Any news about this thread?<br>
>> >> >><br>
>> >> >> Thanks,<br>
>> >> >> Avi.<br>
>> >> >><br>
>> >> >> On Mon, 25 Apr 2022 at 11:13 Avi Raboah <<a href="mailto:avi.raboah@gmail.com" target="_blank">avi.raboah@gmail.com</a>><br>
>> wrote:<br>
>> >> >><br>
>> >> >>> It will be perfect thanks a lot!<br>
>> >> >>><br>
>> >> >>> On Mon, 25 Apr 2022 at 10:47 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> wrote:<br>
>> >> >>><br>
>> >> >>>> > No, it doesn't.<br>
>> >> >>>> > In that case when you make the select statement, piggy internal<br>
>> >> queries<br>
>> >> >>>> > failed and the session rebooted<br>
>> >> >>>><br>
>> >> >>>> I see it now.<br>
>> >> >>>><br>
>> >> >>>> test=# begin;<br>
>> >> >>>> BEGIN<br>
>> >> >>>> test=*# insert into ttt values(1);<br>
>> >> >>>> ERROR: relation "ttt" does not exist<br>
>> >> >>>> LINE 1: insert into ttt values(1);<br>
>> >> >>>> ^<br>
>> >> >>>> test=!# select * from t1;<br>
>> >> >>>> FATAL: Backend throw an error message<br>
>> >> >>>> DETAIL: Exiting current session because of an error from backend<br>
>> >> >>>> HINT: BACKEND Error: "current transaction is aborted, commands<br>
>> >> ignored<br>
>> >> >>>> until end of transaction block"<br>
>> >> >>>> server closed the connection unexpectedly<br>
>> >> >>>> This probably means the server terminated abnormally<br>
>> >> >>>> before or while processing the request.<br>
>> >> >>>> The connection to the server was lost. Attempting reset: Succeeded.<br>
>> >> >>>><br>
>> >> >>>> I think pgpool could remember that current transaction is in abort<br>
>> >> >>>> status and does not issue the piggyback query when the selec is<br>
>> >> >>>> issued. Let me see what we can do for this.<br>
>> >><br>
>> >> I have come up with a patch. In this patch pgpool checks if current<br>
>> >> transaction is in abort status. If so, it does not accept new query at<br>
>> >> all and just reply back frontend with the message:<br>
>> >><br>
>> >> "current transaction is aborted, commands ignored until end of<br>
>> transaction<br>
>> >> block"<br>
>> >><br>
>> >> until commit or abort command is sent from frontend. After applying<br>
>> >> the patch the example session above looks like this:<br>
>> >><br>
>> >> test=# begin;<br>
>> >> BEGIN<br>
>> >> test=*# insert into ttt values(1);<br>
>> >> ERROR: relation "ttt" does not exist<br>
>> >> LINE 1: insert into ttt values(1);<br>
>> >> ^<br>
>> >> test=!# select * from t1;<br>
>> >> ERROR: current transaction is aborted, commands ignored until end of<br>
>> >> transaction block<br>
>> >><br>
>> >> The patch was tested in master branch, but I believe it can be<br>
>> >> applied to 4.3 stable branch as well.<br>
>> >><br>
>> >> Best reagards,<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS, Inc. Japan<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >><br>
>><br>
</blockquote></div></div>