refresh_flush instance event not called for PK attributes

[Chris Wilson]

Dear Mike and SQLAlchemy users,

 

I think I’ve discovered a confusing (and undocumented) limitation of the refresh_flush event. It’s called when non-PK columns are populated after an INSERT or UPDATE (e.g. from a server-side default), but not for PK values.

 

The documentation says:

 

“This event is the same as InstanceEvents.refresh() except it is invoked within the unit of work flush process, and the values here typically come from the process of handling an INSERT or UPDATE, such as via the RETURNING clause or from Python-side default values.”

 

With Postgres and SQLite at least, the primary key (e.g. the ID column) of a newly created object is returned with a RETURNING clause. But it doesn’t trigger a refresh_flush event, because it’s skipped by this code in _postfetch:

 

    if returning_cols:

        row = result.context.returned_defaults

        if row is not None:

            for col in returning_cols:

                # pk cols returned from insert are handled

                # distinctly, don't step on the values here

                if col.primary_key and result.context.isinsert:

                   continue

 

I can see that the ID is assigned to the object’s state in the caller (_emit_insert_statements):

                primary_key = result.context.inserted_primary_key

                if primary_key is not None:

                    # set primary key attributes

                    for pk, col in zip(

                        primary_key, mapper._pks_by_table[table]

                    ):

                        prop = mapper_rec._columntoproperty[col]

                        if state_dict.get(prop.key) is None:

                            state_dict[prop.key] = pk

But no event is called when this happens (AFAICS). The after_flush and after_flush_postexec events are called soon after that.

 

It would be nice if at least the documentation made this clear, and even better if we could use refresh_flush for all flush-context events, including PK assignment. What do you think?

 

If an example is useful, here is a trivial one. The receive_refresh_flush handler is never called:

 

from sqlalchemy import Column, Integer, Text, create_engine, event

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Dog(Base):

    __tablename__ = 'dog'

    id = Column(Integer, primary_key=True)

    name = Column(Text)

engine = create_engine('sqlite://')

# engine.echo = True

Base.metadata.create_all(engine)

DBSession = sessionmaker(bind=engine)

session = DBSession(autocommit=True)

@event.listens_for(Dog, 'refresh_flush')

def receive_refresh_flush(target, flush_context, attrs):

    print("Dog was assigned an ID: {attrs}")

with session.begin() as trans:

    session.add(Dog(name="fido"))

 

Thanks, Chris.

---

This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email-disclosure-EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice

---

[Mike Bayer]

On Thu, Dec 19, 2019, at 8:51 AM, Chris Wilson wrote:

Dear Mike and SQLAlchemy users,

 

I think I’ve discovered a confusing (and undocumented) limitation of the refresh_flush event. It’s called when non-PK columns are populated after an INSERT or UPDATE (e.g. from a server-side default), but not for PK values.

 

The documentation says:

 

“This event is the same as InstanceEvents.refresh() except it is invoked within the unit of work flush process, and the values here typically come from the process of handling an INSERT or UPDATE, such as via the RETURNING clause or from Python-side default values.”

 

With Postgres and SQLite at least, the primary key (e.g. the ID column) of a newly created object is returned with a RETURNING clause. But it doesn’t trigger a refresh_flush event, because it’s skipped by this code in _postfetch:

 

    if returning_cols:

        row = result.context.returned_defaults

        if row is not None:

            for col in returning_cols:

                # pk cols returned from insert are handled

                # distinctly, don't step on the values here

                if col.primary_key and result.context.isinsert:

                   continue

the refresh_flush event is intended more towards getting access to columns that have server defaults or triggers on them, not including the primary key.   It's mostly an artifact that this event applies to INSERT operations at all.

To get at the state of a newly inserted row you should use the pending_to_persistent event: https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=refresh_flush#sqlalchemy.orm.events.SessionEvents.pending_to_persistent

 

I can see that the ID is assigned to the object’s state in the caller (_emit_insert_statements):

                primary_key = result.context.inserted_primary_key

                if primary_key is not None:

                    # set primary key attributes

                    for pk, col in zip(

                        primary_key, mapper._pks_by_table[table]

                    ):

                        prop = mapper_rec._columntoproperty[col]

                        if state_dict.get(prop.key) is None:

                            state_dict[prop.key] = pk

But no event is called when this happens (AFAICS). The after_flush and after_flush_postexec events are called soon after that.

 

It would be nice if at least the documentation made this clear, and even better if we could use refresh_flush for all flush-context events, including PK assignment. What do you think?

i will update the docs to state that refresh_flush is oriented towards UPDATE, not INSERT, and that primary key values are explicitly not part of this event.

 

If an example is useful, here is a trivial one. The receive_refresh_flush handler is never called:

 

from sqlalchemy import Column, Integer, Text, create_engine, event

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Dog(Base):

    __tablename__ = 'dog'

    id = Column(Integer, primary_key=True)

    name = Column(Text)

engine = create_engine('sqlite://')

# engine.echo = True

Base.metadata.create_all(engine)

DBSession = sessionmaker(bind=engine)

session = DBSession(autocommit=True)

@event.listens_for(Dog, 'refresh_flush')

def receive_refresh_flush(target, flush_context, attrs):

    print("Dog was assigned an ID: {attrs}")

with session.begin() as trans:

    session.add(Dog(name="fido"))

 

Thanks, Chris.

---

This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email-disclosure-EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice

---

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/BCCA73C2165E8947A2E786EC482564DE0167FDC820%40CCPMAILDAG03.cantab.local.