self-referential / mutually dependent FK flush() should not require post_update for an UPDATE only query

[Mike Bayer]

i.e. when doing "whos-dependent-on-who", if both instances are already persistent, there should be no dependency between them and post_update should not be needed. ex:

from sqlalchemy import *
from sqlalchemy.orm import *

meta = MetaData(create_engine('sqlite://'))

contacts = Table('contacts', meta,
   Column('id', Integer, primary_key=True),
   Column('name', String(50)),
   Column('created_by', Integer),
   Column('updated_by', Integer),
   ForeignKeyConstraint(['created_by']('created_by'), ['contacts.id']('contacts.id')),
   ForeignKeyConstraint(['updated_by']('updated_by'), ['contacts.id']('contacts.id'))
)

meta.create_all()

class Contact(object):
    def __init__(self, name, created_by=None):
        self.name = name
        self.created_by = created_by

    def __eq__(self, other):
        return other.name == self.name

    def __repr__(self):
        return "Contact(created_by=%r, updated_by=%r)" % (self.created_by, self.updated_by)


mapper(Contact, contacts, properties={
   '_created_by': contacts.c.created_by,
   '_updated_by': contacts.c.updated_by,
   'created_by': relation(Contact, primaryjoin=contacts.c.created_by==contacts.c.id,
           remote_side=[contacts.c.id](contacts.c.id)),
   'updated_by': relation(Contact,primaryjoin=contacts.c.updated_by==contacts.c.id,
            remote_side=[contacts.c.id](contacts.c.id),
           backref=backref('updated_by_me', primaryjoin=contacts.c.updated_by==contacts.c.id),
    )
})

sess = create_session()

c1 = Contact('c1')

c2 = Contact('c2', created_by=c1)
c3 = Contact('c3', created_by=c1)
c3.updated_by = c2

sess.save(c1)
sess.save(c2)
sess.save(c3)
sess.flush()

c2.updated_by = c1
c3.updated_by = c1
sess.flush()

c2.updated_by=c3
c3.updated_by = c2
sess.flush()  # <--- fails.  no INSERTs are happening here so UPDATEs should handle it fine

[Mike Bayer]

Issue #3990 was marked as a duplicate of this issue.

[Mike Bayer]

test case for mutually dependent objects, if doing just an UPDATE, post_update should not be needed:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()


class Photo(Base):
    __tablename__ = 'photos'

    id = Column(Integer, primary_key=True)
    gallery_id = Column(ForeignKey('galleries.id'), nullable=True)

    gallery = relationship('Gallery', foreign_keys=gallery_id, back_populates='photos')
    cover_gallery = relationship('Gallery', foreign_keys=lambda: Gallery.cover_id, viewonly=True,
                                 back_populates='cover')

    def __repr__(self):
        return '<Photo {}>'.format(self.id)


class Gallery(Base):
    __tablename__ = 'galleries'

    id = Column(Integer, primary_key=True)
    cover_id = Column(ForeignKey('photos.id'), nullable=False)

    cover = relationship('Photo', foreign_keys=cover_id, back_populates='cover_gallery')
    photos = relationship('Photo', foreign_keys=Photo.gallery_id, back_populates='gallery')

    def __repr__(self):
        return '<Gallery {}>'.format(self.id)


e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
sess = Session(e, autoflush=False)


p = Photo()
sess.add(p)
sess.commit()

photos = sess.query(Photo).all()
gal = Gallery()
sess.add(gal)
gal.cover = p
sess.flush()
# sess.expire(gal, ['cover'])  # if i uncomment this it works
p.gallery = gal
sess.commit()

[Mike Bayer]

as tihs would also fix #3496 I'm motivated to work on this, but I'm struggling. https://gerrit.sqlalchemy.org/427