[pgpool-general: 8471] Re: Is there a way to have Set session_timeout load balanced to Reader Nodes?

[Tatsuo Ishii]

> 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