Re: Persistent Connections

Why is there now a second thread for this topic?

On Tue, Jun 23, 2020 at 3:21 PM Bee.Lists <bee(dot)lists(at)gmail(dot)com> wrote:

> >
> > On Jun 23, 2020, at 4:51 PM, Michael Lewis <mlewis(at)entrata(dot)com> wrote:
> >
> > Do you see anything in pg_stat_activity that stays idle for a while and
> then *does* disappear on its own? Perhaps some types of connections are
> doing client side/application stuff before telling the DB to close the
> connection.
>
> I’m finding those queries sticking around. These queries are very
> simple. Last login type of stuff.
>
> > Idle means the query finished and that was the last query run. It isn't
> active or waiting on another process, that connection is open by idle.
>
> OK. The page that I load up is a dashboard and has a handful of queries.
> From the looks of it, it looks like they’re still working, but idle. But
> you’re saying they’re just open connections? Why would they remain open?
>

"they (queries) are still working, but idle" - your terminology is
problematic and it is probably affecting your understanding. As I said on
the other thread you should probably post the actual output you are
commenting on if you want to actually move this discussion forward.

> It sounds like a good time to set one up.
>
> OK, some further questions:
>
> Who do the connections belong to? Not the client, not the server
> (apparently). Is there one that’s independent and behaves as the front end
> of connection management?
>

As I asked on the other thread: a connection is a link between two
parties. What does it mean to "belong to" in this context? You have
mis-interpreted Tom's answer from the other thread.

> I would increase the limit directly, or with a pooler and research which
> connections are behaving, and which are taking too long to close or not
> closing at all. You could set up a process to snapshot pg_stat_activity
> every minute or 5 and trace which pids are terminating properly, and/or
> make logging very verbose.
>
> How do I go about researching connection behaviour? I guess a pooler
> should be investigated first.

Until you get a better grasp of the basics you should not be introducing
any more moving parts. If anything you need to remove some in order to
figure out which one of the existing parts is causing your problem.

> Once I find culprits, what options do I have? Not sure why new
> connections are made when these idle past connections seem valid and
> usable.
>

Not sure how you expect an answer to "how do I fix the problem" without an
understanding of what the problem is.

There is agreement that ORMs shouldn’t be managing a connection pool, and
> this doesn’t achieve to do that. I’ll be looking into a pooler. This
> client (the gem is Sequel, btw) uses what it assumes are valid connections,
> but that’s where it fails as the database apparently disconnects
> prematurely. The gem has a procedure to check how long since the last pool
> was investigated for legit connections, but I think that’s irrelevant.
> It’s finding what it’s told are legit connections, which are not. It’s
> been lied to.
>

That the pooler in your application is being lied to is probably the most
likely answer, as Tom said in the other thread. But the rest of what you
are saying here just sounds like nonsense. "I'll be looking into a pooler.
... The gem has a procedure to check how long since the last pool was
investigated for legit connections ...". I'd like to point out that you
seem to be saying that you simultaneously have a connection pool and don't
have a connection pool involved here...

As I suggested on the other thread, and repeated above, you need to build
up a script that can reproduce the problem. Something that can open a
connection to the server and then wait for a period of time before
executing a query against it to see if that session gets dropped on the
client side while still remaining visible on the server.

David J.