Okay with some small tweaks to your original code, I am able to show the issue I am having. comment out flush to see issue:
from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Title(Base): __tablename__ = 'title' id = Column(Integer, primary_key=True) department_id = Column(ForeignKey('department.id'), nullable=False) department = relationship(lambda: Department) class Department(Base): __tablename__ = 'department' id = Column(Integer, primary_key=True) class Fund(Base): __tablename__ = 'fund' id = Column(Integer, primary_key=True) title_id = Column(ForeignKey('title.id'), nullable=False) department_id = Column(ForeignKey('department.id'), nullable=False) department = relationship("Department") title = relationship("Title") class FundTitle(Base): __tablename__ = 'fund_title' id = Column(Integer, primary_key=True) title_id = Column(ForeignKey('title.id'), nullable=False) department_id = Column(ForeignKey('department.id'), nullable=False) fund_id = Column(ForeignKey('fund.id'), nullable=False) department = relationship("Department") title = relationship("Title") fund = relationship("Fund") __table_args__ = ( UniqueConstraint( title_id, department_id, fund_id ), ) class Employee(Base): __tablename__ = 'employee' id = Column(Integer, primary_key=True) title_id = Column(ForeignKey('title.id'), nullable=False) department_id = Column(ForeignKey('department.id'), nullable=False) fund_id = Column(ForeignKey('fund.id'), nullable=False) department = relationship(lambda: Department) title = relationship("Title") fund = relationship("Fund") fund_title = relationship(FundTitle, viewonly=True) __table_args__ = ( ForeignKeyConstraint( (title_id, department_id, fund_id), (FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id) ), ) e = create_engine("postgresql://localhost/test_issue", echo=False) # Base.metadata.drop_all(e) Base.metadata.create_all(e) s = Session(e) # s.rollback() while True: d1 = Department() t1 = Title(department=d1) f1 = Fund(department=d1, title=t1) ft1 = FundTitle(title=t1, department=d1, fund=f1) s.add_all([d1, t1, f1, ft1]) s.flush() e1 = Employee(title=t1, department=d1, fund=f1) s.add_all([e1,]) s.commit() On Tuesday, October 9, 2018 at 12:20:30 PM UTC-4, Mike Bayer wrote: > > On Tue, Oct 9, 2018 at 10:44 AM Alex Rothberg <agrot...@gmail.com > <javascript:>> wrote: > > > > In looking at what you wrote doesn't this cause an fk violation (it does > for me): > > 2018-10-08 10:18:38,760 INFO sqlalchemy.engine.base.Engine INSERT INTO > employee (title_id, department_id, fund_id) VALUES (%(title_id)s, > %(department_id)s, %(fund_id)s) RETURNING employee.id > > 2018-10-08 10:18:38,763 INFO sqlalchemy.engine.base.Engine INSERT INTO > fund_title (title_id, department_id, fund_id) VALUES (%(title_id)s, > %(department_id)s, %(fund_id)s) RETURNING fund_title.id > > > > in that a a (non deferred) fk is violated between employee and > fund_title ? > > see we need to see how youve laid out your ForeignKeyConstraints, if > they are composite and overlapping, there are additional options that > may be needed (specifically the post_update flag). you'll note I laid > out all FKs as single column. > > > > > On Mon, Oct 8, 2018 at 10:20 AM Mike Bayer <mik...@zzzcomputing.com > <javascript:>> wrote: > >> > >> On Sun, Oct 7, 2018 at 7:11 PM Alex Rothberg <agrot...@gmail.com > <javascript:>> wrote: > >> > > >> > Okay so I investigated / thought about this further. The issue is > that while I do have a relationship between the various models, some of the > relationships are viewonly since I have overlapping fks. > >> > > >> > For example I have a model Employee, which has fks: department_id, > title_id, and fund_id. The related models are Department (fk > department_id), Title (fk department_id and title_id) , Fund (fk fund_id) > and FundTitle (fk department_id, title_id and fund_id). I have set > FundTitle to viewonly. When updating / creating an Employee, I do create > and add a new FundTitle to the session, however I don't assign it to the > employee as the relationship is viewonly. If I don't flush before making > the assignment, the final flush / commit attempts to update / create the > employee before creating the FundTitle. > >> > >> let's work with source code that is runnable (e.g. MCVE). Below is > >> the model that it seems you are describing, and then there's a > >> demonstration of assembly of all those components using relationships, > >> a single flush and it all goes in in the correct order, all FKs are > >> nullable=False. > >> > >> from sqlalchemy import * > >> from sqlalchemy.orm import * > >> from sqlalchemy.ext.declarative import declarative_base > >> > >> Base = declarative_base() > >> > >> > >> class Employee(Base): > >> __tablename__ = 'employee' > >> id = Column(Integer, primary_key=True) > >> title_id = Column(ForeignKey('title.id'), nullable=False) > >> department_id = Column(ForeignKey('department.id'), > nullable=False) > >> fund_id = Column(ForeignKey('fund.id'), nullable=False) > >> department = relationship("Department") > >> title = relationship("Title") > >> fund = relationship("Fund") > >> > >> > >> class Title(Base): > >> __tablename__ = 'title' > >> id = Column(Integer, primary_key=True) > >> department_id = Column(ForeignKey('department.id'), > nullable=False) > >> department = relationship("Department") > >> > >> > >> class Department(Base): > >> __tablename__ = 'department' > >> id = Column(Integer, primary_key=True) > >> > >> > >> class Fund(Base): > >> __tablename__ = 'fund' > >> id = Column(Integer, primary_key=True) > >> title_id = Column(ForeignKey('title.id'), nullable=False) > >> department_id = Column(ForeignKey('department.id'), > nullable=False) > >> department = relationship("Department") > >> title = relationship("Title") > >> > >> > >> class FundTitle(Base): > >> __tablename__ = 'fund_title' > >> id = Column(Integer, primary_key=True) > >> title_id = Column(ForeignKey('title.id'), nullable=False) > >> department_id = Column(ForeignKey('department.id'), > nullable=False) > >> fund_id = Column(ForeignKey('fund.id'), nullable=False) > >> department = relationship("Department") > >> title = relationship("Title") > >> fund = relationship("Fund") > >> > >> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) > >> Base.metadata.create_all(e) > >> > >> s = Session(e) > >> > >> d1 = Department() > >> t1 = Title(department=d1) > >> f1 = Fund(department=d1, title=t1) > >> ft1 = FundTitle(title=t1, department=d1, fund=f1) > >> e1 = Employee(title=t1, department=d1, fund=f1) > >> > >> s.add_all([d1, t1, f1, ft1, e1]) > >> s.commit() > >> > >> > >> the INSERTs can be ordered naturally here and the unit of work will do > >> that for you if you use relationship: > >> > >> BEGIN (implicit) > >> 2018-10-08 10:18:38,750 INFO sqlalchemy.engine.base.Engine INSERT INTO > >> department DEFAULT VALUES RETURNING department.id > >> 2018-10-08 10:18:38,750 INFO sqlalchemy.engine.base.Engine {} > >> 2018-10-08 10:18:38,753 INFO sqlalchemy.engine.base.Engine INSERT INTO > >> title (department_id) VALUES (%(department_id)s) RETURNING title.id > >> 2018-10-08 10:18:38,753 INFO sqlalchemy.engine.base.Engine > {'department_id': 1} > >> 2018-10-08 10:18:38,757 INFO sqlalchemy.engine.base.Engine INSERT INTO > >> fund (title_id, department_id) VALUES (%(title_id)s, > >> %(department_id)s) RETURNING fund.id > >> 2018-10-08 10:18:38,757 INFO sqlalchemy.engine.base.Engine > >> {'title_id': 1, 'department_id': 1} > >> 2018-10-08 10:18:38,760 INFO sqlalchemy.engine.base.Engine INSERT INTO > >> employee (title_id, department_id, fund_id) VALUES (%(title_id)s, > >> %(department_id)s, %(fund_id)s) RETURNING employee.id > >> 2018-10-08 10:18:38,761 INFO sqlalchemy.engine.base.Engine > >> {'title_id': 1, 'department_id': 1, 'fund_id': 1} > >> 2018-10-08 10:18:38,763 INFO sqlalchemy.engine.base.Engine INSERT INTO > >> fund_title (title_id, department_id, fund_id) VALUES (%(title_id)s, > >> %(department_id)s, %(fund_id)s) RETURNING fund_title.id > >> 2018-10-08 10:18:38,764 INFO sqlalchemy.engine.base.Engine > >> {'title_id': 1, 'department_id': 1, 'fund_id': 1} > >> 2018-10-08 10:18:38,766 INFO sqlalchemy.engine.base.Engine COMMIT > >> > >> > >> > >> > >> > >> > > >> > On Tuesday, September 18, 2018 at 9:02:30 AM UTC-4, Mike Bayer wrote: > >> >> > >> >> if there are no dependencies between two particular objects of > >> >> different classes, say A and B, then there is no deterministic > >> >> ordering between them. For objects of the same class, they are > >> >> inserted in the order in which they were added to the Session. > >> >> > >> >> the correct way to solve this problem in SQLAlchemy is to use > >> >> relationship() fully. I know you've stated that these objects have > a > >> >> relationship() between them but you have to actually use it, that > is: > >> >> > >> >> obj_a = A() > >> >> obj_b = B() > >> >> > >> >> obj_a.some_relationship = obj_b # will definitely flush correctly > >> >> unless there is a bug > >> >> > >> >> OTOH if you are only using foreign key attributes, the ORM does > *not* > >> >> have any idea in how it should be flushing these: > >> >> > >> >> obj_a = A() > >> >> obj_b = B() > >> >> > >> >> obj_a.some_fk = obj_b.some_id # ORM doesn't care about this, no > >> >> ordering is implied > >> >> > >> >> > >> >> since you said you're not setting any IDs, I'm not sure how you > could > >> >> be doing the above. > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> On Tue, Sep 18, 2018 at 5:53 AM Simon King <si...@simonking.org.uk> > wrote: > >> >> > > >> >> > It's not something I've ever looked into, but I'm not aware of any > >> >> > debugging options here, no. You'd probably want to start by > scattering > >> >> > print statements around the UOWTransaction class > >> >> > ( > https://bitbucket.org/zzzeek/sqlalchemy/src/c94d67892e68ac317d72eb202cca427084b3ca74/lib/sqlalchemy/orm/unitofwork.py?at=master&fileviewer=file-view-default#unitofwork.py-111) > > > >> >> > > >> >> > Looking at that code made me wonder whether you've set any > particular > >> >> > cascade options on your relationship; I'm not sure if cascade > options > >> >> > affect the dependency calculation. > >> >> > > >> >> > Simon > >> >> > > >> >> > On Tue, Sep 18, 2018 at 5:28 AM Alex Rothberg <agrot...@gmail.com> > wrote: > >> >> > > > >> >> > > In order to guide me in stripping down this code to produce an > example for positing, are there any options / flags / introspections I can > turn on to understand how sql makes decisions about the order in which is > writes statements to the DB? > >> >> > > > >> >> > > On Friday, September 14, 2018 at 10:13:45 AM UTC-4, Simon King > wrote: > >> >> > >> > >> >> > >> In that case can you show us the code that is causing the > problem? > >> >> > >> On Fri, Sep 14, 2018 at 2:55 PM Alex Rothberg < > agrot...@gmail.com> wrote: > >> >> > >> > > >> >> > >> > I am not generating any IDs myself and I already have > relationships between the models. > >> >> > >> > > >> >> > >> > On Friday, September 14, 2018 at 4:33:08 AM UTC-4, Simon King > wrote: > >> >> > >> >> > >> >> > >> >> On Thu, Sep 13, 2018 at 10:50 PM Alex Rothberg < > agrot...@gmail.com> wrote: > >> >> > >> >> > > >> >> > >> >> > Is it possible to hint at sqla the order in which it > should write out changes to the DB? > >> >> > >> >> > > >> >> > >> >> > I am having issues in which I add two new objects to a > session, a and b where a depends on b, but sqla is flushing a before b > leading to an fk issue. I can solve this a few ways: explicitly calling > flush after adding b, or changing the fk constraint to be initially > deferred. Ideally I would not have to do either of these. > >> >> > >> >> > > >> >> > >> >> > >> >> > >> >> If you have configured a relationship between the two > classes > >> >> > >> >> ( > http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#building-a-relationship), > > > >> >> > >> >> and you've linked the objects together using that > relationship (a.b = > >> >> > >> >> b), then SQLAlchemy will flush them in the correct order. If > you are > >> >> > >> >> generating your IDs in Python and assigning them to the > primary and > >> >> > >> >> foreign key columns directly, SQLAlchemy probably won't > understand the > >> >> > >> >> dependency. > >> >> > >> >> > >> >> > >> >> Does using a relationship fix your problem? > >> >> > >> >> > >> >> > >> >> 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. > >> >> > > > >> >> > > -- > >> >> > > 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. > >> >> > > >> >> > -- > >> >> > 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. > >> > > >> > -- > >> > 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 <javascript:>. > >> > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > >> > Visit this group at https://groups.google.com/group/sqlalchemy. > >> > For more options, visit https://groups.google.com/d/optout. > >> > >> -- > >> 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 a topic in the > Google Groups "sqlalchemy" group. > >> To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/fZMJQoI2qkY/unsubscribe. > >> To unsubscribe from this group and all its topics, send an email to > sqlalchemy+...@googlegroups.com <javascript:>. > >> To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > >> Visit this group at https://groups.google.com/group/sqlalchemy. > >> For more options, visit https://groups.google.com/d/optout. > > > > -- > > 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 <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.