I created a minimal reproducible case, and in doing so I've figured out what is happening, in a classical rubber duck debugging fashion.
https://github.com/hyperknot/pyramid_connections_bug Nevertheless, it's still a very interesting problem, as I don't think Pyramid/pyramid_tm should ever silently open a 2nd database connection. DB connections are quite a scare resource on the server side, postgresql guys always recommend me to have no more than CPU_cores * 2 + 1, using pgBouncer if I need more, etc. What was happening here is that 1. I needed a dbsession to set up config variables before a request is available. 2. I was using it to setup the app. After the values have been read from the db, I had to explicitly rollback() it, but close() also worked. Which one is recommended in this case? 3. I used this dbsession (thinking it's the same as request.dbsession) in config.add_request_method: https://github.com/hyperknot/pyramid_connections_bug/blob/master/pyramid_connections_bug/__init__.py#L27 was using a different dbsession. 4. This one ended up silently creating a new connection, which was stuck in "idle in transaction" forever. pyramid_tm.explicit_manager didn't help, but actually I don't know what is it doing. What is the recommended way to use a dbsession to read config values and then just get rid of it? I mean close the connection and delete the variable, to make sure that I cannot use it like I did? Also, what is the point of explicit_manager and how can I benefit from including it? Zsolt On 1 April 2018 at 09:53, Zsolt Ero <[email protected]> wrote: > Hi, > > I'm running Pyramid + SQLAlchemy + PostgreSQL in a "classic" sync stack > (like the cookiecutter one, pyramid_tm, etc.), simple gunicorn default > (process) workers. > > I have a few problems (via pg_top or ps aux): > > 1. I'm getting about 2x the number of PostgreSQL connections as the number > of workers. Is this normal? > 2. About half of the connections are in "idle in transaction" state. They > shouldn't be in transaction, right? > > So for --workers=1 I get one idle and one idle in transaction connection. > > 3. PGSQL 9.6+ supports idle_in_transaction_session_timeout > > https://www.postgresql.org/docs/9.6/static/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT > > Is this a good idea to use this to clean those idle in transaction > connections? > > Should I use something else to close the normal, idle connections as well? > Is this a good idea? Would Pyramid reconnect if needed? > > Zsolt > > -- > You received this message because you are subscribed to a topic in the > Google Groups "pylons-discuss" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/pylons-discuss/_MJflNUcjdg/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/pylons-discuss/8cc52db3-3f5a-43b9-b4e1-4b1b1a6149e8%40googlegroups.com. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAKw-smD0mVBzU_UOXJ2F%2BpfoRAkBWVczxHHAe9fF448rdLQL1w%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
