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

[Tatsuo Ishii]

> 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