Connecting an existing child object to a new association object results in database errors

[Former user]

Following the example in the documentation, I created a many to many model with an association object mapper.

This worked fine when using new objects for parent/child, but when I started retrieving objects using query(Child).one() or query(Child).filter(...).first(), I started getting a ProgrammingError when appending the child to the relation on the Parent.

sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "association_left_id_seq" does not exist
 'select nextval(\'"association_left_id_seq"\')' None

This is using the postgresql engine.

When I switch to using an in memory sqlite engine, the error changes to:

sqlalchemy.exc.IntegrityError: (IntegrityError) association.left_id may not be NULL u'INSERT INTO association (right_id, data) VALUES (?, ?)' ['foo'](1,)

If I change my filter to use "get(pk)", the errors go away. However, my particular instance of this is selecting on non-primary key columns, so "get()" is not an acceptable work around for me.

nosklo in IRC said that adding a session.flush() after "s.add(l)" worked for him, but it didn't for me on either database platform.

Adding an explicit s.flush() after the "a.right = r" line triggers the error as well, with a different stack trace path.

[Former user]

test case

[jek]

concise test case for autoflush cascade ordering

[Mike Bayer]

there's no bug here, SQLAlchemy is doing exactly what its told. The documented default for "cascade" is "save-update, expire", so when the association is appended to a collection, the object is placed in the session. If a subsequent load of an unloaded collection is fired, autoflush fires off. your object gets cascaded into the session via the "lefts" collection when you set the other side, "right". then the lazyload of "rights" sets it off.

There are three solutions. One is to turn the default cascade off:

mapper(Left, left_table, properties={
    'rights':relation(Association, backref='left', cascade="none")
})

mapper(Association, association_table, properties={
    'right':relation(Right, backref=backref('lefts', cascade="none"))
})

second, is to create the association object more reasonably, with both ends at the same time (this is what I would do):

a = Association(left=left, right=right)

third, temporarily disable autoflush:

session.autoflush=False

For situations where a certain function populates an object graph, I'll use a decorator to accomplish this:

@disable_autoflush
def populate_my_objects(*arg, **kw):
    # ...

[Former user]

I suggest that the documentation be updated to use one of these "best practices" so that others don't go down a similar path.

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)