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

[Avi Raboah]

Thanks for the explanation and fast response.

You mentioned the piggy internal queries, and I am trying to understand if
what I saw is the right behaving.

I open a transaction, and my second statement was an Insert statement which
was failed. In that case any other statements should be ignored until
rollback. But after the Insert statement I did a select statement which
triggers the piggy internal queries. In that case the first internal query
was failed and because of that the connection was rebooted.

In that case this is fine ? Or not? Why piggy behave different  in that
scenario where the internal query failed due to an earlier transaction
error?

Thanks 🙏

On Mon, 25 Apr 2022 at 3:26 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > Hi,
> >
> > I saw in the code the fixing for the hung query in pg_stat_activity. The
> > issue was fixed by killing the child process and restarting the session
> > instead of sending a sync message.
> > Do the fix is still relevant or has this issue already fixed by Postgres
> > team?
>
> No.
>
> I assume you are seeing the code in do_query(). Actually the code is
> not specific to pg_stat_activity at all.
>
> When client sends a query which includes reference to tables or
> functions, pgpool dynamically sends a system catalog lookup query to
> PostgreSQL. do_query() is the workhorse for the work.  do_query()
> piggybacks the query on the existing connection so that pgpool does
> not need to open a new connection to PostgreSQL (it would be slow and
> waste of resource). The query result from PostgreSQL is not returned
> to the client, rather is consumed by pgpool to analyze the result for
> further processing (for example, determining whether the table in
> question is a temporary table or not).
>
> Currently pgpool discards the session to client if the system catalog
> lookup query fails. The reason is, if pgpool does not do that, the
> recovery from the failure would be too complex and fragile. For
> example, if there are multiple PostgreSQL servers, pgpool creates
> connections to each PostgreSQL. do_query() uses one of the
> connections. Suppose by the time when do_query() gets called, a
> transaction was open and do_query() fails. If pgpool tries to recover
> the error, it needs to send back an error to the client but also needs
> to deal with other PostgreSQL servers. For example they need to close
> the transaction. Just sending sync message does not solve the problem.
>
> Also if the system catalog lookup query fails, then there's something
> going wrong in the PostgreSQL server, which should not happen
> frequently. So I think making do_query() more complex to deal with the
> rare case problems is not worth the trouble.
>
> > In case I don't want to use this fix what is the best way to ignore the
> > session booting?
>
> There's no way to avoid the session booting.
>
> 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/20220425/bcbed578/attachment.htm>