Re: Postgres hanging connections
List <[email protected]>
| Newsgroups | gmane.comp.lang.smalltalk.vwnc |
|---|---|
| Message-ID | <[email protected]> |
Hi Esteban, Explain more about this magic of read only connections … I have also questions about links between postgres session lifetime and postgres connection lifetime, because I can share the same accessor between 2 sessions. Annick > Le 26 nov. 2021 à 15:31, Esteban Maringolo <[email protected]> a écrit : > > Hi Annick, > > The bouncer is a proxy in between your client (Smalltalk) and the real Postgres server, but it's completely transparent to your client and to the server. > > I don't know how pg_bouncer manages it, but it gives you ~10x the connection capability to your PG server. > > There are different pooling strategies you can use (session/transaction/statement). > > I also have a 1 DB connection per Seaside Session, and because at peak times I can get hundreds of simultaneous users, but most are read only, I can manage them without raising the limits of the Postgres server maximum simultaneous connections. > > Regards, > > Esteban A. Maringolo > > > On Fri, Nov 26, 2021 at 10:39 AM List <[email protected] <mailto:[email protected]>> wrote: > Hi, > > Thank you all for your suggestions. Yes, I know I can switch off secure mode ! > > I made also another mistake, I tried to link the seaside session lifetime to the postgres session lifetime and created a WASession subclass cleaning up the connection when it was expired. > But I disconnect the whole pool instead of just one connection… > > To Esteban, is the lifetime of your bouncer the same as a usual postgres connection ? > > Annick > > >> Le 26 nov. 2021 à 13:48, Esteban Maringolo <[email protected] <mailto:[email protected]>> a écrit : >> >> Hi, >> >> If you have a lot of connections, using pg_bouncer as a connection pooler is great, because you can transparently pool connections to the server using different criteria. >> https://urldefense.com/v3/__http://www.pgbouncer.org/__;!!DZ3fjg!vZxJPBmmh-IhECgYJA2HYAX1KjUdQ4-OJE2T3z745aOaJbD5YpHP9nTxlFUdXFfc$ <https://urldefense.com/v3/__http://www.pgbouncer.org/__;!!DZ3fjg!u9WLRBbet6Qi8e664KO9OY6L_awUl3FsXVMh_D3d123MzgsJVwva8-ogqdN7YJ6g$> >> >> As for having stale connections, it is good to have a healthcheck to ensure the connection didn't drop or if that's the case you can recover it. >> >> I recommend using inUnitOfWorkDo: or transact: instead of manually doing being/end transactions/UOW. >> >> Regards, >> >> Esteban A. Maringolo >> >> >> On Fri, Nov 26, 2021 at 8:13 AM Joachim Tuchel <[email protected] <mailto:[email protected]>> wrote: >> Annick, >> >> I’d be interested in Niall‘s suggestion as well, because we’re in a similar boat as you. We use one connection per image and even share it between sessions. We’re far from heavy traffic, so this is not an issue yet. I know there are a few blog posts around that show how you coul use session pools in Seaside, but we haven’t investigated them further yet. >> >> One comment, however: I guess you know you can switch secure mode off? >> >> I am not sue it adds that much security anyways, it just Nils out the password units car after a successful logon. Given that it is very likely to be stored in some other place inside the image, that may or may not really add security… I doubt it, but I’d like to learn about this… >> >> Joachim >> >> > Am 26.11.2021 um 10:23 schrieb List <[email protected] <mailto:[email protected]>>: >> > >> > Dear Niall, >> > >> > Sorry not to answer earlier. >> > >> > Nice to hear of you. >> > >> > In fact I am unsure of how to design Postgres connections with a Seaside app on Visualworks. >> > >> > I started with pooled connections in a session, but it did not work, because the secure attribute of the connection dropped the password and prevented reconnection. >> > >> > I discovered this issue late in the process, so may be I dropped the idea too early. >> > >> > At this time, I have a « permanent » single connection, which is not a good idea I think. >> > >> > Could you explain what would be the best pattern ? >> > >> > Best regards >> > >> > Annick >> > >> >> Le 22 nov. 2021 à 23:21, Niall Ross (Cincom) <[email protected] <mailto:[email protected]>> a écrit : >> >> >> >> Dear Annick, >> >> >> >>> On 22/11/2021 10:01, List wrote: >> >>> I have Postgres hanging connections when using Glorp and Seaside, and the connections have opened transactions although they are old. >> >>> How is it possible to clean them ? >> >>> >> >>> Annick >> >> >> >> I'm not sure of the the problem state, so unsure what you are trying to achieve. By all means send a case to your Cincom Support contact, if appropriate. >> >> >> >> If you are inside a GlorpSession>>transact: then any kind of error should work its way outside the block, triggering rollbackUnitOfWork (rolls back the image) and then rollBackTransaction (rolls back the DB). A PostgresSocketConnection has a shortish timeout when connecting to a database and a longer one when sending queries and awaiting results (I've known very large queries need a lot of the 15 minutes provided, but it can be tedious to wait that long when you realise it is going to fail). In time,a timeout should be raised, which should work up the stack and cause these rollbacks.to <https://urldefense.com/v3/__http://rollbacks.to__;!!DZ3fjg!u9WLRBbet6Qi8e664KO9OY6L_awUl3FsXVMh_D3d123MzgsJVwva8-ogqZKrYJuT$> occur. >> >> >> >> If the PostgresSocketConnection instance(s) are hung such this is not happening, or no timeout is raised (odd), you could send disconnect to the connection. That should rollback and dismiss the database transaction after dismissing any and all sessions of the connection. It should then progress to the transact: rollback (or you should be able to call rollbackUnitOfWork and rollbackTransaction yourself). >> >> >> >> As noted, I am guessing wildly at the details of your issue, so feel at all sure the above is relevant. >> >> >> >> >> >> Yours faithfully >> >> Niall Ross >> >> >> >> -- >> >> This email has been checked for viruses by AVG. >> >> https://urldefense.com/v3/__https://www.avg.com__;!!DZ3fjg!sV6XSR9yKK6bDfJ4yUjNgVyiflf3JQzGPlraxptu6r_xhE6pkRrl4ms-P_1zS1KI$ <https://urldefense.com/v3/__https://www.avg.com__;!!DZ3fjg!sV6XSR9yKK6bDfJ4yUjNgVyiflf3JQzGPlraxptu6r_xhE6pkRrl4ms-P_1zS1KI$> >> > >> > >> >