Reset pooled sessions between uses

[strk]

When using pooled connections the session configuration in a session will affect operations performed on what the client considers a new connection (but it is not, due to pooling).

For comparison, pg_bouncer default configuration is to issue a "DISCARD ALL" whenever a connection is released into the pool.

This ticket is for supporting a similar mechanism at the node pooled connection level. A "session reset" command could be configurable so that users using pg_bouncer could avoid doing it again.

See CartoDB/CartoDB-SQL-API#98

[brianc]

First of all, I totally agree if you check a client into the pool while it's still got queued queries or other "state" internally something should happen to clean up that state or at least alert the user somehow they're about to experience unexpected behavior. I'm just not sure what you mean by 'session.' We could make this configurable and off by default for v2.x. Perhaps we can switch it to on by default for v3.x since it probably isn't a breaking change in most cases & might be the more correct behavior? I just need to wrap my head around what you mean by a session.

[strk]

In node-postgres terms I guess by "session" I mean "client".
Each "client" should get a clean and reset environment to work in,
as if it was a new connection.

[brianc]

That makes total sense. Sorry to need more info still, but I guess the way I've always used postgres is pretty much "get a client, run some queries, return client" so I don't know how the client's environment can be modified in such a way it needs to be cleaned up or reset. Could you show an example of code where the client can alter it's state and upon it next being checked out it's dirty?

[strk]

Example:

First client query:
SET statement_timeout TO 1

Second client query:
WITH c AS ( select x from generate_series(1,9999999) x ) select count(*) from c

The second query will take more than 1 milliseconds and thus timeout,
but only if the session (connection, in this case) was "polluted" by the
previous client.

[brianc]

Perfect example. Now for another question....how do you reset the session? Send in a DISCARD ALL message?

[strk]

On Wed, Jul 03, 2013 at 06:19:51AM -0700, Brian C wrote:

Perfect example. Now for another question....how do you reset the session? Send in a DISCARD ALL message?

That's what pg_bouncer uses by default.
But I think it'd be nice to allow for overriding the sequence of commands.
For example there's a setseed() function that's used to control the
pseudo-random sequence returned by random(). That setting affects the
session/connection, so one might want to add a setseed(<another_random_source>)
to the "session initialization script".

[brianc]

Yeah maybe having a hook on 'before checkout' and 'before checkin' you could optionally implement on the pool.

Aside: I find myself yet again wishing the pooling was handled in it's own module.

[sarus]

Just saw this and would love to see some way to "reset" client's session state upon the client being returned to the pool. For my use case I'm setting session_replication_role to replica for bulk deletions and inserts and would like it to revert to origin (the default) after the client is returned to the pool so the setting does not affect the next query to be called using the same client.

[poshest]

I've got a situation where node is calling a postgres function in a loop (a basic job polling mechanism), and that function uses temp tables, which are dropped within the function, but resources for which don't seem to be released until the node server is stopped.

The node-postgres connections stay open indefinitely and because temp table resources aren't released, eventually the server runs out of memory and falls over.

DISCARD ALL seems to be exactly what we need: since it "Releases all temporary resources associated with the current session".

[poshest]

CORRECTION: DISCARD ALL did nothing when we called it manually after each transaction commit. So it's not needed for this use case.

[brianc]

If temp tables are kept alive for the length of a connection id recommend
opening & closing a skngle client in your loop instead of using a pooled
client - that way you have control over the lifecycle of everything.

[poshest]

Well, that will solve the immediate problem, but isn't a scalable long term solution.

Is there a way with node-postgres to configure a connection recycle time (TTL)? Ie some time within X-Y minutes connections will be closed and re-opened? See, my server is dying after several hours. If pooled connections had a TTL of between 10 and 20 minutes, they could be recycled in a staggered way that didn't disrupt the application, and problem solved.

[brianc]

I think your best solution is to use the client directly and implement your
own pooling behavior. I used the "generic-pool" module. There are likely
other generic pool modules that support TTL behavior.

[poshest]

I'm going down that route as we speak! :) Alternatively there's this
feature request in generic-pool
coopernurse/node-pool#133 which would make it
much easier to implement a TTL node-postgres pooling. I might even make a
pull request myself if I get the time.