Pyramid + SQLAlchemy + PostgreSQL: idle connections

[Zsolt Ero]

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

[Zsolt Ero]

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

> --
> 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
> pylons-discus...@googlegroups.com.
> To post to this group, send email to pylons-...@googlegroups.com.
> 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.

[Jonathan Vanasco]

Pyramid isn't opening the database connection, the SqlAlchemy pool is.

On first glance, you're connecting to the database in your main(). This is guaranteed to create issues with SqlAlchemy's connection pool when you involve multiple workers/threads/processes.  In some situations the connection gets reused by multiple processes, in others it gets lost.

The proper way to fix this is to call `engine.dispose()` [http://docs.sqlalchemy.org/en/latest/core/connections.html#engine-disposal] either immediately after you use it, or in a post-fork hook. 

I also don't see an add_finished_callback registered to call 'session.close() on every request (or at least the ones where you grab a dbsession), or the close() being called in any other way (like a tween).  

Between both of those traits, you're going to have a connection pool that constantly needs new connections (it has to wait for python's GC to clear out the connection), and might re-use existing connections unreliably.

There could be other factors causing this too, but you should integrate a `dispose` after the app starts and add a utility to register a session.close() via add_finished_callback().  If that doesn't solve it, there are a lot of docs in SqlAlchemy on the connection pool specifics.

[Zsolt Ero]

Hi Jonathan,

I'm not 100% sure I understand when are you talking about the
"standard" Pyramid way of the cookiecutter template's get_tm_session's
implementation and when are you talking about my special addition of
using the db to set up the app.

I don't know how and when is a gunicorn process fork happening in the
standard Pyramid app, but I presume it's tested and proven by people
who understand the internals of SQLAlchemy connections. By this I mean
that I think about request.dbsession as something which is managed for
me. This results in 1 idle connection per gunicorn worker.

So if I only want to solve my addition and not touch the standard
design of request.dbsession, what would I need to do?

So far, my workaround which seems to work is the following:

dbsession = config.registry['dbsession_factory']()
siteconfig = get_siteconfig(dbsession)
dbsession.close()
del dbsession

(I'm deleting it to make sure that I cannot accidentally call it again).

I tried calling dbsession.connection().engine.dispose() but it didn't
do anything.

dbsession.connection().close() seems to behave the same as if I call
dbsession.close().

Does this sound as a reasonable solution? It seems to fix my problem,
I just want to know if I'm doing anything wrong here.

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
> pylons-discus...@googlegroups.com.
> To post to this group, send email to pylons-...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/d21f0d7e-f20c-4760-9e03-9e057dde376c%40googlegroups.com.

[Jonathan Vanasco]

On Sunday, April 1, 2018 at 8:18:39 PM UTC-4, Zsolt Ero wrote:

Hi Jonathan,

I'm not 100% sure I understand when are you talking about the
"standard" Pyramid way of the cookiecutter template's get_tm_session's
implementation and when are you talking about my special addition of
using the db to set up the app.

I don't know why it was removed from the cookiecutter.

It's been present in the cookbook and docs for years, see the cookbook:

https://docs.pylonsproject.org/projects/pyramid-cookbook/en/latest/database/sqlalchemy.html

I've opened a ticket on the cookiecutter to correct this.

I don't know how and when is a gunicorn process fork happening in the
standard Pyramid app, but I presume it's tested and proven by people
who understand the internals of SQLAlchemy connections. By this I mean
that I think about request.dbsession as something which is managed for
me. This results in 1 idle connection per gunicorn worker.

It's not.  SqlAlchemy, Pyramid and Zope.SqlAlchemy (the transaction extension) do nothing to check or manage this.  Anything that is happening is pure coincidence and luck.  

If you connect to the database before the fork, you need to call `engine.dispose()`  If you don't connect to the database before the fork, you don't need to call `dispose`. 

So far, my workaround which seems to work is the following:

    dbsession = config.registry['dbsession_factory']()
    siteconfig = get_siteconfig(dbsession)
    dbsession.close()
    del dbsession

(I'm deleting it to make sure that I cannot accidentally call it again).

I tried calling dbsession.connection().engine.dispose() but it didn't
do anything.

dbsession.connection().close() seems to behave the same as if I call
dbsession.close().

I can try to spin up your test later.  `dispose` is really what you need to call in your situation.  `close` just 'ends' the session and returns the connection to the pool; `dispose` will close the actual db connection. (perhaps this is being done by the delete, i don't know).   it is important to close the connection and reset the pool before the fork, because you run the risk of different workers using the same connection.

 

[Bert JW Regeer]

On Apr 2, 2018, at 09:54, Jonathan Vanasco <jvan...@gmail.com> wrote:

I don't know why it was removed from the cookiecutter.

It's been present in the cookbook and docs for years, see the cookbook:

https://docs.pylonsproject.org/projects/pyramid-cookbook/en/latest/database/sqlalchemy.html

I've opened a ticket on the cookiecutter to correct this.

This is only required if you are not using pyramid_tm. If you are using pyramid_tm which is what the sqlalchemy cookie cutter does, you do NOT need to add this.

Notice that the cookie cutter users pyramid_tm: https://github.com/Pylons/pyramid-cookiecutter-alchemy/blob/latest/%7B%7Bcookiecutter.repo_name%7D%7D/%7B%7Bcookiecutter.repo_name%7D%7D/models/__init__.py#L63

[Zsolt Ero]

I've been looking into it a bit more.

I _think_ when a dbsession is deleted / garbage collected, it is
closed. And transaction.manager / pyramid_tm is making each session
garbage collected after each request, isn't it? Thus
add_finished_callback is implemented in SQLAlchemy side, at least this
is my idea.

Where is the forking happening in a normal Pyramid app? After the main
function finishes / "return config.make_wsgi_app()"?

So if I understand right, the engine in a gunicorn / process worker
Pyramid app is global / shared between all processes, right? And at
the time of forking, there should be no dbsessions being active and no
connections in the connection pool, right?

So the super safe code for init time db lookup is this?

# dbsession, siteconfig

dbsession = config.registry['dbsession_factory']()
siteconfig = get_siteconfig(dbsession)
dbsession.close()

dbsession.connection().engine.dispose()
del dbsession

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
> pylons-discus...@googlegroups.com.
> To post to this group, send email to pylons-...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/e5c2df72-f9b8-4de9-92e1-639e13e52391%40googlegroups.com.

[Jonathan Vanasco]

On Monday, April 2, 2018 at 12:07:14 PM UTC-4, Bert JW Regeer wrote:

This is only required if you are not using pyramid_tm. If you are using pyramid_tm which is what the sqlalchemy cookie cutter does, you do NOT need to add this.

Thanks, Bert.  I stand corrected.  pyarmid_tm has a finish() that eventually calls a session.close() in Zope.SqlAlchemy.

[Zsolt Ero]

Wouldn't the second / "foolproof" way described here be a good choice
for the default Pyramid implementation? Just as a safety measure.

http://docs.sqlalchemy.org/en/latest/core/pooling.html#using-connection-pools-with-multiprocessing

> --
> 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
> pylons-discus...@googlegroups.com.
> To post to this group, send email to pylons-...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/e1f81475-8b31-44ca-b0cf-af3448a3b131%40googlegroups.com.

[Bert JW Regeer]

The fool proof way would be to not use dbsession inside of your config cycle. You are more than welcome to implement whatever functionality you need though, but it's up to you to do so safely.

I don't believe it is worth the time for us to implement/test/and maintain that code as part of Pyramid or anywhere else, if you are accessing the database outside of a request lifecycle we can't provide all of the measures to do so safely.

Bert

> 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 pylons-discus...@googlegroups.com.

> To post to this group, send email to pylons-...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAKw-smAQ5QWad0eBA60MTDNS9EBxCrxV%3D4wDQT%3DxvnERP%2BUOhw%40mail.gmail.com.

[Zsolt Ero]

OK, I agree with that. Still, storing config values in the database is
a common pattern for medium to large web apps, so it at least makes
sense to have some kind of resource about how to do it. I hope that if
nothing else at least this thread will be useful for someone in the future.

[Jonathan Vanasco]

On Monday, April 2, 2018 at 12:09:26 PM UTC-4, Zsolt Ero wrote:

 

So if I understand right, the engine in a gunicorn / process worker 
Pyramid app is global / shared between all processes, right? And at 
the time of forking, there should be no dbsessions being active and no 
connections in the connection pool, right? 

Sort of. I'll try to explain this simply and using terms closer to you. The engine is global but you DON'T want to share it between processes because of Python's copy-on-write behavior and how connections are handled as file-descriptors.  When each process creates a new connection (or otherwise updates the pool), the pool is basically copied into a per-worker variable then updated.  If you have active connections in the pool *before* the fork, everyone gets a copy of that connection and thinks it is theirs -- but if the pool is empty, everyone gets an empty pool.

 

 

Where is the forking happening in a normal Pyramid app? After the main
function finishes / "return config.make_wsgi_app()"?

Somewhere after there, i believe.  It depends on the server.  uwsgi and unicorn do it in different places.  i think waitress essentially does it too.  gunicorn offers a post_fork hook (http://docs.gunicorn.org/en/stable/settings.html) which you can use to trigger a dispose.   

If you connect within the __main__ to pull config settings out of the db, you generally want to immediately drop the connection.  Doing it within your startup routine and before the fork is fine - but you can also do it after the fork.  All that matters is that the engine is disposed before you use it.

I'm checking with SqlAlchemy, but it looks like `dispose` doesn't explicitly close the session - it just removes it from the connection pool which will effectively close it during garbage collection.

calling close()+dispose() in your startup should be fine.  

there's another technique to protect against forks that mike (bayer of sqlalchemy) has been using in openstack and may bring to the main project, but I'm not sure if/when that will happen.

if you think you may jump between platforms and use multiple libraries/patterns that aren't fork-safe, I wrote a library that turns uwsgi & gunicorn events into subscribers (https://github.com/jvanasco/pyramid_forksafe) that make the cleanup work a little more portable.  We use sqlalchemy, pymongo and pycrypto which are all not forksafe.

[Michael Merickel]

Using the pyramid-cookiecutter-alchemy setup you can access config data at config time using a pattern like this:

from transaction import TransactionManager

from myapp.models import get_tm_session

def main(global_config, **settings):

    config = Configurator(settings=settings)

    config.include('myapp.models')

    tm = TransactionManager(explicit=True)

    with tm:

        dbsession = get_tm_session(config.registry['dbsession_factory'], tm)

        ...  # do queries and stuff

    return config.make_wsgi_app()

This will properly handle the lifecycle of a session for you, same as when serving a request. Be sure if you keep any objects around that you expunge them from the session and re-attach/merge them into any session where you use them.. ORM objects are only valid on the dbsession they were loaded from.

- Michael

--
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 pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBuH5hKGpNgs-6dYkNiAU%3DGGUUOF7aLHAzHe0AE25T7qg%40mail.gmail.com.

[Zsolt Ero]

Michael, I've updated the code to your recommendation.

https://github.com/hyperknot/pyramid_connections_bug

It still requires the explicit engine.dispose() line, otherwise it
does bring the connection to the forked processes.

This is with and without the explicit manager. What does the explicit
manager protect us from?

Zsolt

>> email to pylons-discus...@googlegroups.com.
>> To post to this group, send email to pylons-...@googlegroups.com.

>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBuH5hKGpNgs-6dYkNiAU%3DGGUUOF7aLHAzHe0AE25T7qg%40mail.gmail.com.
>> For more options, visit https://groups.google.com/d/optout.
>
>

> --
> 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
> pylons-discus...@googlegroups.com.

> To post to this group, send email to pylons-...@googlegroups.com.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGoPbgM7gDWBLxdanT%3DhcJQR8sT01E2xwWFxw1zxpCD1A%40mail.gmail.com.

[Michael Merickel]

The forking issue is likely because you're using a connection pool and so once a connection is opened at config-time, even though the session is properly closed the connection is just returned to the pool. The pool here is shared across the fork which is bad. The basic solution here is to add some sort of pre-fork hook that closes out every existing connection in the pool. I personally have not used forking wsgi servers in production and thus have not needed to deal with this problem but I'm pretty sure my advice hits at the core issue you're experiencing. Calling dispose() is one way to make sure that the underlying connection is closed which is why it's working for you. If I recall, gunicorn has some way to fork before config is called such that each subprocess is loaded independently which can help with this as well... If memory serves it was something like turning off the preloading feature.

The explicit manager is about weeding out situations where you use a connection after it's closed [1]. Basically without it you could use something joined to the "tm" after the "with" block is done and you might not see an error. This is more likely to be an issue in web requests where your entire request lifecycle is not actually protected by a transaction.

[1] https://docs.pylonsproject.org/projects/pyramid-tm/en/latest/#custom-transaction-managers

>> email to pylons-discuss+unsubscribe@googlegroups.com.
>> To post to this group, send email to pylons-discuss@googlegroups.com.

>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBuH5hKGpNgs-6dYkNiAU%3DGGUUOF7aLHAzHe0AE25T7qg%40mail.gmail.com.
>> For more options, visit https://groups.google.com/d/optout.
>
>
> --

> 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

> pylons-discuss+unsubscribe@googlegroups.com.
> To post to this group, send email to pylons-discuss@googlegroups.com.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGoPbgM7gDWBLxdanT%3DhcJQR8sT01E2xwWFxw1zxpCD1A%40mail.gmail.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 pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBMmRqu2ZTPSp%3Djh%2BY0RriEwvxgdYQnwq--k4abeUkd-g%40mail.gmail.com.

[Zsolt Ero]

Thanks. So the key point for me is to just use engine.dispose() (I
don't want to dig into gunicorn preload, this seems much cleaner).

About explicit manager: in practice it's as simple as
1. enabling it in models/__init__.py
2. using "with request.tm" everywhere instead of "with
transaction.manager", right?

> This is more likely to be an issue in web requests where your entire request lifecycle is not actually protected by a transaction.

About your final sentence, I'm not sure I understand it. If I'm always
using request.dbsession, by definition I'm protected, to a request's
lifecycle am I not?

Zsolt

>> >> email to pylons-discus...@googlegroups.com.
>> >> To post to this group, send email to pylons-...@googlegroups.com.

>> >> To view this discussion on the web visit
>> >>
>> >> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBuH5hKGpNgs-6dYkNiAU%3DGGUUOF7aLHAzHe0AE25T7qg%40mail.gmail.com.
>> >> For more options, visit https://groups.google.com/d/optout.
>> >
>> >
>> > --
>> > 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

>> > pylons-discus...@googlegroups.com.
>> > To post to this group, send email to pylons-...@googlegroups.com.

>> > To view this discussion on the web visit
>> >
>> > https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGoPbgM7gDWBLxdanT%3DhcJQR8sT01E2xwWFxw1zxpCD1A%40mail.gmail.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 pylons-discus...@googlegroups.com.
>> To post to this group, send email to pylons-...@googlegroups.com.

>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBMmRqu2ZTPSp%3Djh%2BY0RriEwvxgdYQnwq--k4abeUkd-g%40mail.gmail.com.
>> For more options, visit https://groups.google.com/d/optout.
>
>

> --
> 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

> pylons-discus...@googlegroups.com.
> To post to this group, send email to pylons-...@googlegroups.com.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGFjP7-aYzz4NEewWv1kdbMxhH25hrX4JC3oR4ZizqYeg%40mail.gmail.com.

[Jonathan Vanasco]

I confirmed with mike @ sqlalchemy.  calling `dispose()` will not explicitly `close()` connections that are already checked out. It just gets rid of the the connection pool instance, so you have fresh connections in the child processes-- the pre-fork connections themselves will be garbage collected out.  You shouldn't need to call "close()" even through the connection will still be open in your scope.

[Michael Merickel]

You almost never want to use "with request.tm". This cannot be nested with another call to request.tm.begin() and pyramid_tm already does request.tm.begin() for you. You can basically think of pyramid_tm as doing a "with request.tm" in a tween around all of your views except for some exception views in very rare circumstances (read the pyramid_tm docs for more about that).

The explicit vs implicit transaction managers is this: If you call tm.begin() twice in a row with the implicit transaction manager then it will be basically like calling tm.begin(), tm.abort() tm.begin(). The abort is implicit. With the explicit manager if you call tm.begin() twice in a row then the second call will raise an exception. It requires an explicit call to abort and an explicit call to begin(). This is probably what you expect, whereas the implicit workflow is the default bw-compat behavior that is very likely not what you expect if you're coming from a more traditional RDBMS background.

You'll notice that in normal operation you never call tm.begin() or tm.abort()... pyramid_tm does this for you. The explicit manager weeds out issues where you might use the database before or after pyramid_tm is controlling the lifecycle whereas the implicit manager would silently hide some bugs where you call tm.begin() twice without realizing it - thus causing some objects to be detached from the first session because of the tm.abort() that was called alongside the second tm.begin()... Sorry I can't think of a more succinct way of saying it other than "just use the explicit manager".

The only time you really need to think about transaction stuff is when pyramid_tm is not active which is basically at config-time and in console scripts.

- Michael

>> >> email to pylons-discuss+unsubscribe@googlegroups.com.
>> >> To post to this group, send email to pylons-discuss@googlegroups.com.

>> >> To view this discussion on the web visit
>> >>
>> >> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBuH5hKGpNgs-6dYkNiAU%3DGGUUOF7aLHAzHe0AE25T7qg%40mail.gmail.com.
>> >> For more options, visit https://groups.google.com/d/optout.
>> >
>> >
>> > --
>> > 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

>> > pylons-discuss+unsubscribe@googlegroups.com.
>> > To post to this group, send email to pylons-discuss@googlegroups.com.

>> > To view this discussion on the web visit
>> >
>> > https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGoPbgM7gDWBLxdanT%3DhcJQR8sT01E2xwWFxw1zxpCD1A%40mail.gmail.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 pylons-discuss+unsubscribe@googlegroups.com.
>> To post to this group, send email to pylons-discuss@googlegroups.com.

>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBMmRqu2ZTPSp%3Djh%2BY0RriEwvxgdYQnwq--k4abeUkd-g%40mail.gmail.com.
>> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> 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

> pylons-discuss+unsubscribe@googlegroups.com.
> To post to this group, send email to pylons-discuss@googlegroups.com.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGFjP7-aYzz4NEewWv1kdbMxhH25hrX4JC3oR4ZizqYeg%40mail.gmail.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 pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAKw-smDuP2324T0GLi-J83UH-a5mVVvtPC3HyUAgB65aRSGQ5Q%40mail.gmail.com.

[Zsolt Ero]

Thanks for the detailed explanation. Yes I was talking about console scripts.

1. First, I had my console scripts based on initializedb.py (with no
explicit manager in models/__init__.py)
https://github.com/Pylons/pyramid-cookiecutter-alchemy/blob/latest/%7B%7Bcookiecutter.repo_name%7D%7D/%7B%7Bcookiecutter.repo_name%7D%7D/scripts/initializedb.py

2. Then I added explicit manager to models/__init__.py which broke my
console scripts running in pshell, so I reverted to implicit.
https://github.com/Pylons/pyramid/issues/3083

3. I migrated my console scripts to use to a request based config,
using this pattern:

setup_logging(config_uri)
settings = get_appsettings(config_uri)
request_dummy = Request.blank('/', base_url=base_url)
env = bootstrap(config_uri, request=request_dummy)
request = env['request']

do_things() # using "with transaction.manager:"

env['closer']()

4. Now, using this new console script structure allowed me to replace
transaction.manager with request.tm and enable explicit manager.

Does my new console script make any sense? Maybe it'd make sense to
modernize initializedb.py like this, as most new users would probably
go through the same path as I did.

Zsolt

>> >> >> email to pylons-discus...@googlegroups.com.

>> >> >> To post to this group, send email to

>> >> >> pylons-...@googlegroups.com.

>> >> >> To view this discussion on the web visit
>> >> >>
>> >> >>
>> >> >> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBuH5hKGpNgs-6dYkNiAU%3DGGUUOF7aLHAzHe0AE25T7qg%40mail.gmail.com.
>> >> >> For more options, visit https://groups.google.com/d/optout.
>> >> >
>> >> >
>> >> > --
>> >> > 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

>> >> > pylons-discus...@googlegroups.com.
>> >> > To post to this group, send email to pylons-...@googlegroups.com.

>> >> > To view this discussion on the web visit
>> >> >
>> >> >
>> >> > https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGoPbgM7gDWBLxdanT%3DhcJQR8sT01E2xwWFxw1zxpCD1A%40mail.gmail.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 pylons-discus...@googlegroups.com.
>> >> To post to this group, send email to pylons-...@googlegroups.com.

>> >> To view this discussion on the web visit
>> >>
>> >> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBMmRqu2ZTPSp%3Djh%2BY0RriEwvxgdYQnwq--k4abeUkd-g%40mail.gmail.com.
>> >> For more options, visit https://groups.google.com/d/optout.
>> >
>> >
>> > --
>> > 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

>> > pylons-discus...@googlegroups.com.
>> > To post to this group, send email to pylons-...@googlegroups.com.

>> > To view this discussion on the web visit
>> >
>> > https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGFjP7-aYzz4NEewWv1kdbMxhH25hrX4JC3oR4ZizqYeg%40mail.gmail.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 pylons-discus...@googlegroups.com.
>> To post to this group, send email to pylons-...@googlegroups.com.

>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smDuP2324T0GLi-J83UH-a5mVVvtPC3HyUAgB65aRSGQ5Q%40mail.gmail.com.
>> For more options, visit https://groups.google.com/d/optout.
>
>

> --
> 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

> pylons-discus...@googlegroups.com.
> To post to this group, send email to pylons-...@googlegroups.com.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwG8OPmtCDZ9w%3DpJdLEesCiQRbwoRFnDXYXLsyxxV20-4g%40mail.gmail.com.

[Michael Merickel]

The best answer is to use "with request.tm" in your console script as it will then use the same manager config as your requests... which I suggest to be explicit of course.

We need to update the cookiecutter with a better example here. The initialize_db script is a bad example because it is in the unique position of preparing the schema and thus you do *not* usually want to use bootstrap() here (imagine that your main was doing database queries requiring a particular schema that wasn't there yet)... whereas you do in other scripts. In a normal console script you likely want to do:

setup_logging(config_uri)

with bootstrap(config_uri) as env:

    request = env['request']

    with request.tm:

        request.dbsession.query(...)

- Michael

>> >> >> email to pylons-discuss+unsubscribe@googlegroups.com.

>> >> >> To post to this group, send email to

>> >> >> pylons-discuss@googlegroups.com.

>> >> >> To view this discussion on the web visit
>> >> >>
>> >> >>
>> >> >> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBuH5hKGpNgs-6dYkNiAU%3DGGUUOF7aLHAzHe0AE25T7qg%40mail.gmail.com.
>> >> >> For more options, visit https://groups.google.com/d/optout.
>> >> >
>> >> >
>> >> > --
>> >> > 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

>> >> > pylons-discuss+unsubscribe@googlegroups.com.
>> >> > To post to this group, send email to pylons-discuss@googlegroups.com.

>> >> > To view this discussion on the web visit
>> >> >
>> >> >
>> >> > https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGoPbgM7gDWBLxdanT%3DhcJQR8sT01E2xwWFxw1zxpCD1A%40mail.gmail.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 pylons-discuss+unsubscribe@googlegroups.com.
>> >> To post to this group, send email to pylons-discuss@googlegroups.com.

>> >> To view this discussion on the web visit
>> >>
>> >> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smBMmRqu2ZTPSp%3Djh%2BY0RriEwvxgdYQnwq--k4abeUkd-g%40mail.gmail.com.
>> >> For more options, visit https://groups.google.com/d/optout.
>> >
>> >
>> > --
>> > 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

>> > pylons-discuss+unsubscribe@googlegroups.com.
>> > To post to this group, send email to pylons-discuss@googlegroups.com.

>> > To view this discussion on the web visit
>> >
>> > https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwGFjP7-aYzz4NEewWv1kdbMxhH25hrX4JC3oR4ZizqYeg%40mail.gmail.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 pylons-discuss+unsubscribe@googlegroups.com.
>> To post to this group, send email to pylons-discuss@googlegroups.com.

>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/pylons-discuss/CAKw-smDuP2324T0GLi-J83UH-a5mVVvtPC3HyUAgB65aRSGQ5Q%40mail.gmail.com.
>> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> 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

> pylons-discuss+unsubscribe@googlegroups.com.
> To post to this group, send email to pylons-discuss@googlegroups.com.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwG8OPmtCDZ9w%3DpJdLEesCiQRbwoRFnDXYXLsyxxV20-4g%40mail.gmail.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 pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAKw-smAHfxzAWddrRFRsmOTn8eu%2BqxmVWhtBEhdNhyECDhqoew%40mail.gmail.com.

[Tres Seaver]

On 04/02/2018 01:16 PM, Jonathan Vanasco wrote:
>> Where is the forking happening in a normal Pyramid app? After the main
>> function finishes / "return config.make_wsgi_app()"?
>>
> Somewhere after there, i believe. It depends on the server. uwsgi and
> unicorn do it in different places. i think waitress essentially does it
> too.

Waitress is not a forking implementation: it uses threads.


Tres.
--
===================================================================
Tres Seaver +1 540-429-0999 tse...@palladion.com
Palladion Software "Excellence by Design" http://palladion.com

[Jonathan Vanasco]

On Monday, April 9, 2018 at 8:45:45 AM UTC-4, Tres Seaver wrote:

> Somewhere after there, i believe.  It depends on the server.  uwsgi and
> unicorn do it in different places.  i think waitress essentially does it
> too.

Waitress is not a forking implementation:  it uses threads.

Yes, Sorry, I should have been more clear. The SqlAlchemy session/pool/connection is not forksafe and not threadsafe. For the purpose of dealing with this type of situation (database connection in main routine), forked and threaded workers are affected similarly and essentially the same.  I was hoping to convey that switching from gunicorn/uwsgi to the waitress won't necessarily address this – one needs to `dispose()` of the pool.