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

[Eric Brawner]

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?

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.

Thanks in advance,

Eric

-- 
*Eric Brawner*
Data Engineer / Business Intelligence
*eXp Realty*
Livingston, Tx (Central Time)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20221025/c0bd42cd/attachment.htm>