A "after flush before commit" event

[mi...@benchling.com]

I'm writing a handler for the "before_commit" event that requires that all objects have primary key ids, and realizing that "before_commit" runs before the flush preceding a commit. Is there a way to have the handler run after the initial flush preceding a commit? Or, alternatively, have an "after_flush" listener run only on the flush preceding a commit?

[Jonathan Vanasco]

Is there a reason why you can't `flush` as the first action the `before_commit` event ?

[Simon King]

Could you pair a before_commit handler with an after_flush_postexec
handler? The before_commit one would set a flag to say that you are
committing, and the after_flush_postexec one would look for that flag
before proceeding.

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

sure, implement before_commit() and put a token inside of session.info
- then implement after_flush and look for that token. you can pop
the token out there as well but you'd need to be mindful of error
handling, perhaps also pop the token out using after_transaction_end.

[mi...@benchling.com]

Awesome. Thanks everyone!

[Mike Bayer]

On Tue, Sep 19, 2017 at 12:19 AM, <mi...@benchling.com> wrote:
> Followup question -- this the following order of events guaranteed?
>
> before_commit --> after_flush --> after_flush_post_exec
>
> i.e., is an after_flush_post_exec guaranteed to run after the before_commit
> preceding a commit?

if the flush didn't raise then yes.

[mi...@benchling.com]

Just checking, but one scenario where "after flush before commit" would not work is when all changes are flushed immediately before a commit, right?

In that case, "commit" would not automatically be preceded by a "flush" (and the "after_flush_postexec" code would not run because no token from 
"before_commit" has been set).

e.g.,
# make some changes
session.flush()
session.commit()  # Does not kick off its own flush

[Mike Bayer]

On Tue, Sep 19, 2017 at 4:06 PM, <mi...@benchling.com> wrote:
> Just checking, but one scenario where "after flush before commit" would not
> work is when all changes are flushed immediately before a commit, right?
> In that case, "commit" would not automatically be preceded by a "flush" (and
> the "after_flush_postexec" code would not run because no token from
> "before_commit" has been set).

if there is nothing to flush, like there's no objects or no attributes
have been modified, then when commit() is called, it is usually the
case that no flush occurs and you would not see these events.

[vin...@benchling.com]

Hey Mike,

Thanks for taking the time to answer all of these questions! I think what we're looking for is a way to perform some actions immediately before "COMMIT" is sent to the database. The current solutions (as far as I can understand them) all have some drawbacks:

Perform actions in before_commit

This event fires before a flush event, so the following code performs the actions *before* the data has been flushed:

a = A()
a.name = 'newname'
db.session.commit()

Mark a boolean flag in before_commit, and do actions in after_flush if the boolean is set

This fixes the above problem, but runs into a new issue:

a = A()
a.name = 'newname'
db.session.flush()  # Boolean isn't set, so actions don't fire
db.session.commit()  # There is no dirty state, so after_flush never gets called here and actions don't fire

It looks like what we're looking for is a "immediately_before_commit_query" hook. Does this exist in SQLAlchemy right now? If not, another alternative we could go with is:

Perform actions in before_commit, but call db.session.flush() first (suggested by Jonathan Vanasco earlier in this thread)

We manually call db.session.flush() to ensure that we don't have any remaining "dirty" state, and in essence we can now perform actions "immediately before the COMMIT query". It would look something like:

@event.listens_for(SignallingSession, 'before_commit')
def actions(...):
    db.session.flush()
    # Do other actions

I'm not immediately confident that this is equivalent to the conceptual "immediately_before_commit_query" hook - mostly not sure if there are any gotchas with this approach. Do you think this is a safe thing to do?

[Mike Bayer]

On Wed, Sep 20, 2017 at 2:34 AM, <vin...@benchling.com> wrote:
> Hey Mike,
>
> Thanks for taking the time to answer all of these questions! I think what
> we're looking for is a way to perform some actions immediately before
> "COMMIT" is sent to the database. The current solutions (as far as I can
> understand them) all have some drawbacks:

"immediately before SQL / commit" means you want to use core events.

In this case you want to use the commit connection event:

http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=commit#sqlalchemy.events.ConnectionEvents.commit

this event is right before the SQL is logged and emitted (in this case
the DBAPI method connection.commit() is ultimately used).

if you want to coordinate session state with this event, when you have
the Session you can put tokens in connection.info:

session.connection().info['some_token'] = True

then in your event hook you can see it:

@event.listens_for(session.bind, "commit")
def intercept_commit(connection):
if connection.info.get('some_token', False):
...