1. Mike Bayer Avatar Mike Bayer
  2. Untitled project
  3. sqlalchemy_old
  4. Issues

Premature autoflush in _AssociationList.append

Issue #3941 resolved
Bartek Wójcicki created an issue

Appending to association list fails, because object is flushed before it is appended (and appending sets primary key value).

Minimal example of this issue below, tested with sqlalchemy-1.1.6:

from sqlalchemy import (
    Boolean,
    Column,
    ForeignKey,
    Integer,
    String,
    engine_from_config,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import (
    backref,
    relationship,
    scoped_session,
    sessionmaker,
)

CONFIG = {
    'sqlalchemy.url': 'postgresql+psycopg2://postgres:postgres@localhost/dummy'
}

engine = engine_from_config(CONFIG)
DBSession = scoped_session(sessionmaker())
DBSession.configure(bind=engine)
Base = declarative_base()


class UserAlias(Base):
    __tablename__ = 'user_aliases'
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    alias_id = Column(Integer, ForeignKey('aliases.id'), primary_key=True)
    valid = Column(Boolean)

    alias = relationship('Alias', backref=backref('user_assocs', uselist=False))
    user = relationship('User', backref=backref('alias_assocs'))

    def __init__(self, alias, valid=True):
        self.alias = alias
        self.valid = valid


class Alias(Base):
    __tablename__ = 'aliases'
    id = Column(Integer, primary_key=True)
    name = Column('name', String(64))

    user = association_proxy('user_assocs', 'user')

    def __init__(self, name):
        self.name = name


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

    aliases = association_proxy('alias_assocs', 'alias')

    def __init__(self, name):
        self.name = name


def test_assoc():
    user = User(name='John')
    DBSession.add(user)
    DBSession.commit()

    johny = Alias(name='Johny')
    user.aliases.append(johny)
    DBSession.commit()

    jan = Alias(name='Jan')
    DBSession.add(jan)  # in my real use case proxied object is already attached to session before appending, here I simulate it with DBSession.add

    # with DBSession.no_autoflush:  # works with autoflush disabled
    #     user.aliases.append(jan)

    user.aliases.append(jan)  # autoflush fails here

    DBSession.commit()
    return user


test_assoc()

and stacktrace:

#!

/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/sql/crud.py:692: SAWarning: Column 'user_aliases.user_id' is marked as a member of the primary key for table 'user_aliases', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed.  Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends.
  util.warn(msg)
Traceback (most recent call last):
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
psycopg2.IntegrityError: null value in column "user_id" violates not-null constraint
DETAIL:  Failing row contains (null, 82, t).


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/bartek/dev/sqlalchemy-test/sqlalchemy_test/models.py", line 85, in <module>
    test_assoc()
  File "/home/bartek/dev/sqlalchemy-test/sqlalchemy_test/models.py", line 79, in test_assoc
    user.aliases.append(jan)  # autoflush fails here
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/ext/associationproxy.py", line 610, in append
    self.col.append(item)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/ext/associationproxy.py", line 509, in <lambda>
    col = property(lambda self: self.lazy_collection())
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/ext/associationproxy.py", line 467, in __call__
    return getattr(obj, self.target)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/attributes.py", line 237, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/attributes.py", line 584, in get
    value = self.callable_(state, passive)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/strategies.py", line 557, in _load_for_state
    return self._emit_lazyload(session, state, ident_key, passive)
  File "<string>", line 1, in <lambda>
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/strategies.py", line 635, in _emit_lazyload
    result = q.all()
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2679, in all
    return list(self)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2830, in __iter__
    self.session._autoflush()
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1375, in _autoflush
    util.raise_from_cause(e)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1365, in _autoflush
    self.flush()
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2139, in flush
    self._flush(objects)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush
    flush_context.execute()
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute
    rec.execute(self)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute
    uow
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements
    execute(statement, params)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.IntegrityError) null value in column "user_id" violates not-null constraint
DETAIL:  Failing row contains (null, 82, t).
 [SQL: 'INSERT INTO user_aliases (alias_id, valid) VALUES (%(alias_id)s, %(valid)s)'] [parameters: {'alias_id': 82, 'valid': True}]

Autoflush occurs in _AssociationList append method:

    def append(self, value):
        item = self._create(value)
        self.col.append(item)

If _create was called before self.col is evaluted created object would not be autoflushed prematurely.

Comments (3)

  1. Mike Bayer repo owner
    • changed milestone to 1.2

    this wont stop all autoflush situations, but certainly the one where "col" gets lazy loaded is an easy win. tentative for 1.2.

  3. Mike Bayer repo owner

    Call proxied collection before invoking creator in associationlist.append()

    Improved the association proxy list collection so that premature autoflush against a newly created association object can be prevented in the case where list.append() is being used, and a lazy load would be invoked when the association proxy accesses the endpoint collection. The endpoint collection is now accessed first before the creator is invoked to produce the association object.

    Change-Id: I008a6dbdfe5b1c0dfd02189c3d954d83a65f3fc5 Fixes: #3941

    → <<cset 36275b0c2d8d>>

  4. Log in to comment
Assignee
–
Type
bug
Priority
minor
Status
resolved
Component
ext
Milestone
1.2
Votes
0
Watchers
1
Jira: the preferred issue tracker for Bitbucket. Join the team!