[pgpool-general: 8467] Re: Is there a way to have Set session_timeout load balanced to Reader Nodes?
Tatsuo Ishii
ishii at sraoss.co.jp
Wed Oct 26 14:13:09 JST 2022
> Hi,
>
> We are currently using PgPool on AWS Aurora Postgres with 2 reader nodes
> solely for our API read-only calls. We have whitelisted our functions with
> fn_ and calls go to Readers fine. However, our lambda/application gateway
> has a hard timeout of 30 seconds. Meaning any queries running longer than
> 25 seconds are aborted by the lamba. This has the effect of leaving those
> queries still running on the database. As you probably surmise, once a
> query is aborted it is then attempted again, compounding the load on the
> server and potentially leaving 2 orphaned queries taking up server
> resources.
>
> We implemented: set statement_timeout = 30000; select * from
> fn_myfunc(param);
> . However, PGPool is sending these statements to the Writer/Master
> instance only.
>
> Can we either configure PGPool to load balance this set statement? or set
> some sort of global session configuration that is used for all sessions?
Unfortunately Pgpool-II does not support multi statements (multiple
queries conjunct with ";"). If such a statement is sent, Pgpool-II
sends it to writer/master uncondionaly. So you need to split the
statement in 2 statements like:
set statement_timeout = 30000;
select * from fn_myfunc(param);
> I've found from the Postgres docs that we can set the session_timeout at
> the role level. However, that would be enough of a development/refactor
> effort to warrant exhausting other options first.
I don't think session_timeout will work in this case since it is fired
after a query ends (I assume your intention is canceling queries
running longer than 30 seconds on backend).
Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
More information about the pgpool-general
mailing list