Controlling table dependency for flushing

[Alex Rothberg]

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.

[Simon King]

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

[Alex Rothberg]

I am not generating any IDs myself and I already have relationships between the models.

[Simon King]

In that case can you show us the code that is causing the problem?

> --
> 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.

[Alex Rothberg]

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?

[Simon King]

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

[Mike Bayer]

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.

[Alex Rothberg]

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.

[Mike Bayer]

On Sun, Oct 7, 2018 at 7:11 PM Alex Rothberg <agrot...@gmail.com> 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

[Alex Rothberg]

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 ?

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.

[Alex Rothberg]

 I should say, I didn't run your exact code but essentially that ordering is what is causing my issues with my code in that the new fund_title is inserted after the new employee.

[Simon King]

If you run Mike's code exactly, do you get the error? If not, can you
edit it to make it more like your code in order to trigger the error?
We need to figure out what is special about your situation before we
can suggest how to fix it.

Simon

[Mike Bayer]

On Tue, Oct 9, 2018 at 10:44 AM Alex Rothberg <agrot...@gmail.com> 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.

[Alex Rothberg]

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() 

[Mike Bayer]

On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg <agrot...@gmail.com> wrote:
>
> 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:

so what you're doing here is making Employee dependent on FundTitle,
which makes this a little out of the ordinary but this is fine. You
need to give the ORM a clue that this dependency exists, since it
never looks at foreign key constraints unless you tell it to.
Adding a relationship to FundTitle that doesn't have viewonly=True is
an easy way to do this, there's no need to ever make use of the
relationship otherwise:

class Employee(Base):
__tablename__ = 'employee'

# ...
fund_title = relationship(FundTitle, viewonly=True)

_ft_for_dependency = relationship(FundTitle)

__table_args__ = (
ForeignKeyConstraint(
(title_id, department_id, fund_id),
(FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id)
),
)

then you can take the flush() out and there's no issue, as long as
you're always making sure that FundTitle object is present either in
the current Session or the row in the database exists.

[Alex Rothberg]

So one minor issue and one big issue with that solution:

1. minor issue, I now get these: SAWarning: relationship 'XXXX' will copy columnYYYY to column ZZZZ, which conflicts with relationship(s): '....

2. major issue, I use raiseload("*") and now I start seeing: sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is not available due to lazy='raise'

[Alex Rothberg]

Is it possible to specific a non viewonly relationship in which I have a primary join specified in which none of the fk's are marked "foreign"? ie where I can mark the relationship dependancy but it wont set any columns? It looks like there may be some logic in sqla that assume all columns are fk if none are specified as foreign?

[Mike Bayer]

what's your stacktrace for the raise, the unit of work should not be
trying to load the items in this collection?

[Alex Rothberg]

let me get that. in the meantime, what are your thoughts on just removing the view only from the original relationship and then using an explicit primary join where none of the columns are marked foreign? Theoretically that should solve this problem, no?

[Alex Rothberg]

/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/flask_resty/view.py:484: in destroy
    self.commit()
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/flask_resty/view.py:403: in commit
    self.session.commit()
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py:153: in do
    return getattr(self.registry(), name)(*args, **kwargs)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:943: in commit
    self.transaction.commit()
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:467: in commit
    self._prepare_impl()
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:447: in _prepare_impl
    self.session.flush()
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:2254: in flush
    self._flush(objects)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:2380: in _flush
    transaction.rollback(_capture_exception=True)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py:66: in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/util/compat.py:249: in reraise
    raise value
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:2344: in _flush
    flush_context.execute()
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py:370: in execute
    postsort_actions = self._generate_actions()
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py:329: in _generate_actions
    if action.execute(self):
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py:463: in execute
    prop_has_changes(uow, delete_states, True) or
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/dependency.py:234: in prop_has_changes
    passive)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py:225: in get_attribute_history
    attributes.LOAD_AGAINST_COMMITTED)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:755: in get_history
    current = self.get(state, dict_, passive=passive)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:599: in get
    value = callable_(state, passive)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:834: in __call__
    return strategy._load_for_state(state, passive)
/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:589: in _load_for_state
    self._invoke_raise_load(state, passive, "raise")
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _


self = <sqlalchemy.orm.strategies.LazyLoader object at 0x11c4c1f20>
state = <sqlalchemy.orm.state.InstanceState object at 0x11b43a3c8>, passive = 51
lazy = 'raise'


    def _invoke_raise_load(self, state, passive, lazy):
        raise sa_exc.InvalidRequestError(
>           "'%s' is not available due to lazy='%s'" % (self, lazy)
        )
E       sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is not available due to lazy='raise'


/Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:564: InvalidRequestError

[Mike Bayer]

On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg <agrot...@gmail.com> wrote:
>
> let me get that. in the meantime, what are your thoughts on just removing the view only from the original relationship and then using an explicit primary join where none of the columns are marked foreign? Theoretically that should solve this problem, no?

is this just for the warning? I don't think the relationship() can
be set up with no columns marked as foreign, it takes that as a cue
that it should figure out the "foreign" columns on its own.

There's another way to make sure Employee is always dependent on
FundTitle but it's a little bit off-label. Add the dependency you
want directly into the unit of work:

from sqlalchemy.orm import unitofwork
from sqlalchemy import event


@event.listens_for(Session, "before_flush")
def _add_dep(session, context, objects):
context.dependencies.update([
(
unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
unitofwork.SaveUpdateAll(context, inspect(Employee))
)
])

[Mike Bayer]

that's a delete, turn those off by putting passive_deletes on the relationship:

_ft_for_dependency = relationship(FundTitle, passive_deletes=True)

[Alex Rothberg]

Not just for warning. Also this raise load issue. yes, i see that I can't mark none. If I could though, that would be awesome since I think it would solve this problem? I can test by setting one foreign and seeing if that works.

[Mike Bayer]

the raise load issue is because without passive_deletes, it has to
load the collection to make sure those objects are all updated.
passive_deletes fixes, now you just have a warning. or use the unit
of work recipe which is more direct.

[Alex Rothberg]

And I'll reiterate, not worth doing it all from the original single relationship (ie not needing to either add more relationships, have warnings or use the more obscure feature you outlined)? Seems like that would be cleaner in code. 

[Mike Bayer]

On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg <agrot...@gmail.com> wrote:
>
> And I'll reiterate, not worth doing it all from the original single relationship (ie not needing to either add more relationships, have warnings or use the more obscure feature you outlined)? Seems like that would be cleaner in code.

you mean take the viewonly=True off the existing relationship? sure
you can do that. but if you mutate the elements in that collection,
you can incur a change that is conflicting with the other objects.
that's why I suggested making the non-viewonly a private member, but
either way works.

[Alex Rothberg]

Well the other way doesn't quite work as if I mark none of the columns as foreign in the primary join, sqla then assumes / guesses all of them are.

Let me test with passive. 

[Mike Bayer]

On Wed, Oct 10, 2018 at 1:32 PM Alex Rothberg <agrot...@gmail.com> wrote:
>
> Well the other way doesn't quite work as if I mark none of the columns as foreign in the primary join, sqla then assumes / guesses all of them are.

that is the case, that is code that has changed a lot over the years
so it has a lot of baggage.

[Alex Rothberg]

Adding the passive delete fixes the raise load but adds yet another warning from sqla:

sqlalchemy/orm/relationships.py:1790: SAWarning: On Employee._ft_for_dependency, 'passive_deletes' is normally configured on one-to-many, one-to-one, many-to-many relationships only.

Looking at this:

@event.listens_for(Session, "before_flush")
def _add_dep(session, context, objects):
    context.dependencies.update([
        (
            unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
            unitofwork.SaveUpdateAll(context, inspect(Employee))
        )
    ])

do I not have to mark one Model as dependent on the other? Or is that implied by the order of the list?

[Mike Bayer]

the warnings here are really just to prevent a whole set of very
common mistakes - if the usage was never valid, then it would be
raising an error. You're doing something that nobody ever does which
is have two foreign keys on the same columns, so none of these
warnings apply. i dont know what other approach SQLAlchemy could
have for this kind of thing.

[Alex Rothberg]

I actually have that newest warning about "'passive_deletes' is normally configured on..." coming up in quite a few places in my codebase, In those cases I had added passive_deletes for the same reason as here: to avoid a load in the case of a delete. In some / many of those other places, I don't have overlapping fks.

I guess to make my earlier point more clear: is there any chance we could add a flag, etc allowing a viewonly=False but where all of the fks are not set by relationship?

[Mike Bayer]

On Wed, Oct 10, 2018 at 2:57 PM Alex Rothberg <agrot...@gmail.com> wrote:
>
> I actually have that newest warning about "'passive_deletes' is normally configured on..." coming up in quite a few places in my codebase, In those cases I had added passive_deletes for the same reason as here: to avoid a load in the case of a delete. In some / many of those other places, I don't have overlapping fks.
>
> I guess to make my earlier point more clear: is there any chance we could add a flag, etc allowing a viewonly=False but where all of the fks are not set by relationship?

If we are talking about changing the library, then we should work on
solving use cases, not allowing for hacks to not raise warnings.

[Alex Rothberg]

Yes, I am suggesting modifying the library such that I can specify a flush dependency ideally without needing to add dummy relationships or use what I am guessing is a pretty low level feature of the ORM (@event.listens_for(Session, "before_flush")). I agree that I can ignore the warnings.

[Mike Bayer]

so you want some kind of flag on relationship() that explicitly states
this purpose of the relationship(), which can co-exist with
viewonly=True such that it doesn't write data, but still represents a
record that is understood to be created in some other part of the
transaction. like enforce_dependency=True.

[Alex Rothberg]

I think so, yes.

I am open to other ideas, but that seems like the cleanest way to model the dep without having to add dummy stuff to the model.

[Mike Bayer]

On Wed, Oct 10, 2018 at 5:22 PM Alex Rothberg <agrot...@gmail.com> wrote:
>
> I think so, yes.
>
> I am open to other ideas, but that seems like the cleanest way to model the dep without having to add dummy stuff to the model.

OK so like some other issues you've raised in the past few days, this
is a nice to have but isn't high priority for me right now, since the
ORM can already do what you need and designing a good API for this new
feature may be difficult to get right. It's often very tough to
add support for a new use case without it being too arbitrary and
specific to exactly what someone needs at the moment. So the bigger
pattern of how you have these foreign keys set up and what it means to
be overlapping them like that needs to be understood more canonically.

[Mike Bayer]

for example why don't we like just using plain relationship() without
the viewonly=True? Shouldn't you be explicitly associating FundTitle
with Employee in any case? that is:

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)

__table_args__ = (
ForeignKeyConstraint(
(title_id, department_id, fund_id),
(FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id)
),
)

and then:

for i in range(5):

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])

e1 = Employee(title=t1, department=d1, fund=f1, fund_title=ft1)

there's still the warning you don't like, but then at least we can
make an optoin that is narrower in scope:

fund_title = relationship(
FundTitle, overlaps=('department', 'title', 'fund'))

e.g. we aren't saying viewonly=True but then still having the
relationship be related to the flush, nor are we making the claim that
fund_title doesn't populate the department_id, title_id, fund_id
columns because that seems to contradict what the relationship is
supposed to do. at least with "overlaps" the intent of what you are
trying to do is clearer. but im not really sure, because I'm still
not feeling like I fully understand the model you have. normally
you'd have employee->fundtitle as the FK, and you would *not* have a
foreign key from Employee to Department, Title, Fund individually.
it would be like this:

class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)

title_id = Column(nullable=False)
department_id = Column(nullable=False)
fund_id = Column(nullable=False)

department = association_proxy("fund_title", "department")
title = association_proxy("fund_title", "title")
fund = association_proxy("fund_title", "fund")

fund_title = relationship(FundTitle)

__table_args__ = (
ForeignKeyConstraint(
(title_id, department_id, fund_id),
(FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id)
),
)

ft1 = FundTitle(title=t1, department=d1, fund=f1)

e1 = Employee(fund_title=ft1)

e.g. a simple association object pattern. I don't see what the
redundant foreign keys solves.

[Alex Rothberg]

I'm not totally sure how "overlaps" are used in that example, but yes that might be fine to have viewonly=False (ie default) and then mark what is and isn't overlapped.

So here is the full model with some color:

Employee (all nullable [slight change from example above]):

 - department_id

 - title_id

 - fund_id

with the fks as:

department_id -> Department

fund_id -> Fund

(department_id, title_id) -> Title

(department_id, fund_id) -> FundDepartment # not shown in code snipped earlier, but I also have this too ;-)

(department_id, title_id, fund_id) -> FundTitle

relationships setup the best I can to avoid overlaps, etc.

An employee may have just a fund assigned, just a department, a department and a title, a department and fund or a department, title and a fund. 

Further I want to keep track of the department_id on the title (ie a title belongs to a department). I want to make sure that the department_id on the employee matches the department_id on the title, hence the potentially extraneous composite fk (ie I could just fk from Employee to title but then there is no constraint that the department matches; an fk from the title to department does not ensure that). I actually use this pattern quite a bit with tenancy throughout my models (ie where I use a composite fk of the standard pk + the tenent to ensure at the db level that the tenant matches between the two models).

Let met know if something seems totally silly here!

[Mike Bayer]

On Wed, Oct 10, 2018 at 7:54 PM Alex Rothberg <agrot...@gmail.com> wrote:
>
> I'm not totally sure how "overlaps" are used in that example, but yes that might be fine to have viewonly=False (ie default) and then mark what is and isn't overlapped.
>
> So here is the full model with some color:
>
> Employee (all nullable [slight change from example above]):
> - department_id
> - title_id
> - fund_id
>
> with the fks as:
> department_id -> Department
> fund_id -> Fund
> (department_id, title_id) -> Title
> (department_id, fund_id) -> FundDepartment # not shown in code snipped earlier, but I also have this too ;-)
> (department_id, title_id, fund_id) -> FundTitle
>
> relationships setup the best I can to avoid overlaps, etc.
>
>
> An employee may have just a fund assigned, just a department, a department and a title, a department and fund or a department, title and a fund.

so...the columns are all nullable and that means the Employee should
be flushable before the FundTitle?

> Further I want to keep track of the department_id on the title (ie a title belongs to a department). I want to make sure that the department_id on the employee matches the department_id on the title, hence the potentially extraneous composite fk (ie I could just fk from Employee to title but then there is no constraint that the department matches; an fk from the title to department does not ensure that). I actually use this pattern quite a bit with tenancy throughout my models (ie where I use a composite fk of the standard pk + the tenent to ensure at the db level that the tenant matches between the two models).>
> Let met know if something seems totally silly here!

given how this model is, I would think you would want just all normal
relationships and whichever one you happen to mutate is the one that
sets the foreign keys. because you might want to set
Employee.department alone or Employee.title which gives you department
also. "overlaps" here might want to actually assert the two FK
settings aren't conflicting. Otherwise if you set
Employee.department = d1 and Employee.title =Title(department=d2),
it's random which one "wins".

this is not a use case that's ever been considered.

[Alex Rothberg]

given how this model is,  I would think you would want just all normal 
relationships and whichever one you happen to mutate is the one that 
sets the foreign keys.   because you might want to set 
Employee.department alone or Employee.title which gives you department 
also.     "overlaps" here might want to actually assert the two FK 
settings aren't conflicting.   Otherwise if you set 
Employee.department = d1 and Employee.title =Title(department=d2), 
it's random which one "wins". 

So the issue comes up when setting any of the relationships to None. For example if I cease to have all of fund, department and title, then the  FundTitle is None. If i assign that to the Employee it then clears all of the other (overlapping) fks.

[Mike Bayer]

On Thu, Oct 11, 2018 at 4:32 PM Alex Rothberg <agrot...@gmail.com> wrote:
>>
>> given how this model is, I would think you would want just all normal
>> relationships and whichever one you happen to mutate is the one that
>> sets the foreign keys. because you might want to set
>> Employee.department alone or Employee.title which gives you department
>> also. "overlaps" here might want to actually assert the two FK
>> settings aren't conflicting. Otherwise if you set
>> Employee.department = d1 and Employee.title =Title(department=d2),
>> it's random which one "wins".
>
> So the issue comes up when setting any of the relationships to None. For example if I cease to have all of fund, department and title, then the FundTitle is None. If i assign that to the Employee it then clears all of the other (overlapping) fks.

that's where I think the use case is here. but within the ORM it's
better to consider it in terms of overlapping relationships and let it
figure out the FK aspect of it. it's an unusual case but there
should be support for this (at some point :) )