[pgpool-general: 8935] Re: Server-side cursors using Declare/Describe do not work in pgpool-ii

[Tatsuo Ishii]

Hi,

> Hi,
> I am on pgpool-ii 4.4.4 hoping that it would fix a particular Describe
> issue I was seeing with psycopg3. (But alas,
> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=0d54c68d5cfba549e5b6fb7d3247d66ee50ab1d9
> did not fix my issue).
> 
> I was able to track down with the psycopg3 developers that the reason
> for the failure is that we do not track portals for DECLARE and
> subsequent calls to Describe could end up failing.
> 
> Here is the issue https://github.com/psycopg/psycopg/issues/648, but I
> will post the relevant pgpool-ii log:
> 2023-09-25 17:44:57.534: [unknown] pid 157: LOG:  Parse message from frontend.
> 2023-09-25 17:44:57.534: [unknown] pid 157: DETAIL:  statement: "",
> query: "BEGIN"
> 2023-09-25 17:44:57.535: [unknown] pid 157: LOG:  Bind message from frontend.
> 2023-09-25 17:44:57.535: [unknown] pid 157: DETAIL:  portal: "", statement: ""
> 2023-09-25 17:44:57.535: [unknown] pid 157: LOG:  Describe message
> from frontend.
> 2023-09-25 17:44:57.535: [unknown] pid 157: DETAIL:  portal: ""
> 2023-09-25 17:44:57.535: [unknown] pid 157: LOG:  Execute message from frontend.
> 2023-09-25 17:44:57.535: [unknown] pid 157: DETAIL:  portal: ""
> 2023-09-25 17:44:57.535: [unknown] pid 157: LOG:  Sync message from frontend.
> 2023-09-25 17:44:57.734: [unknown] pid 157: LOG:  Parse message from frontend.
> 2023-09-25 17:44:57.735: [unknown] pid 157: DETAIL:  statement: "",
> query: "DECLARE "serverside-cursor-broken" NO SCROLL CURSOR FOR SELECT
> 3"
> 2023-09-25 17:44:57.735: [unknown] pid 157: LOG:  Bind message from frontend.
> 2023-09-25 17:44:57.735: [unknown] pid 157: DETAIL:  portal: "", statement: ""
> 2023-09-25 17:44:57.735: [unknown] pid 157: LOG:  Describe message
> from frontend.
> 2023-09-25 17:44:57.735: [unknown] pid 157: DETAIL:  portal: ""
> 2023-09-25 17:44:57.735: [unknown] pid 157: LOG:  Execute message from frontend.
> 2023-09-25 17:44:57.735: [unknown] pid 157: DETAIL:  portal: ""
> 2023-09-25 17:44:57.735: [unknown] pid 157: LOG:  Sync message from frontend.
> 2023-09-25 17:44:58.013: [unknown] pid 157: LOG:  Describe message
> from frontend.
> 2023-09-25 17:44:58.013: [unknown] pid 157: DETAIL:  portal:
> "serverside-cursor-broken"
> 2023-09-25 17:44:58.013: [unknown] pid 157: FATAL:  unable to execute Describe
> 2023-09-25 17:44:58.013: [unknown] pid 157: DETAIL:  unable to get the
> bind message

Thank you for the report. I think your analysis is correct. Pgpool-II
needs to be fixed.

> I'd think that fixing this would either involve tracking DECLARE
> statements like other portal statements, or passing the message to the
> primary to let it handle the Describe on its own.

The latter looks simple but I need to investigate more.

> Let me know if there is anything I can do to help!

Thanks in advance,

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp