Skip to content

warn_on_conflicting_targets seems to work for "secondary" conflicts now but we have no tests for this? #6400

New issue
New issue
Closed
Closed
warn_on_conflicting_targets seems to work for "secondary" conflicts now but we have no tests for this?#6400
Labels
ormquestionissue where a "fix" on the SQLAlchemy side is unlikely, hence more of a usage questiontests
@Rogalek

Description

@Rogalek
Rogalek
opened on Apr 29, 2021

Hey, I have three models:

class Prelegent(Base, TimeStampedModel):
    __tablename__ = 'prelegents'

    id = Column(Integer, primary_key=True)
    first_name = Column(Unicode(64), nullable=False)
    last_name = Column(Unicode(64), nullable=False)

    events = relationship(
        "Event", backref=backref("prelegents", lazy='dynamic'),
        secondary="prelegents_events")


class Event(Base):
    __tablename__ = 'events'

    id = Column(Integer, primary_key=True, autoincrement=True)


class PrelegentEvent(Base):
    __tablename__ = 'prelegents_events'

    event_id = Column(
        Integer, ForeignKey('events.id', ondelete='cascade'),
        index=True, nullable=False, primary_key=True)
    prelegent_id = Column(
        Integer, ForeignKey('prelegents.id', ondelete='cascade'),
        index=True, nullable=False, primary_key=True)

    event = relationship("Event", backref="prelegents_event")
    prelegent = relationship("Prelegent", lazy="joined")

and in sqlalchemy 1.3+ all was working without any working, now after switching to 1.4+ I am getting warnings after my test case run:

/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'Event.prelegents_event' will copy column events.id to column prelegents_events.event_id, which conflicts with relationship(s): 'Event.prelegents' (copies events.id to prelegents_events.event_id), 'Prelegent.events' (copies events.id to prelegents_events.event_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'PrelegentEvent.event' will copy column events.id to column prelegents_events.event_id, which conflicts with relationship(s): 'Event.prelegents' (copies events.id to prelegents_events.event_id), 'Prelegent.events' (copies events.id to prelegents_events.event_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'PrelegentEvent.prelegent' will copy column prelegents.id to column prelegents_events.prelegent_id, which conflicts with relationship(s): 'Event.prelegents' (copies prelegents.id to prelegents_events.prelegent_id), 'Prelegent.events' (copies prelegents.id to prelegents_events.prelegent_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

URL inside warning suggests that I am not using proper back_populates. In the example there, there is no backref or backpopulates but I am having backref here and still getting this warning. Also in the documentation, you can read that:

Remember, when the relationship.backref keyword is used on a single relationship, it’s exactly the same as if the above two relationships were created individually using relationship.back_populates on each.

Anyway I tried and switch my version to use back_populates:

class Prelegent(Base, TimeStampedModel):
    __tablename__ = 'prelegents'

    id = Column(Integer, primary_key=True)
    first_name = Column(Unicode(64), nullable=False)
    last_name = Column(Unicode(64), nullable=False)

    events = relationship(
        "Event", backref=backref("prelegents", lazy='dynamic'),
        secondary="prelegents_events")


class Event(Base):
    __tablename__ = 'events'

    id = Column(Integer, primary_key=True, autoincrement=True)
    prelegents_event = relationship("PrelegentEvent", back_populates="event")


class PrelegentEvent(Base):
    __tablename__ = 'prelegents_events'

    event_id = Column(
        Integer, ForeignKey('events.id', ondelete='cascade'),
        index=True, nullable=False, primary_key=True)
    prelegent_id = Column(
        Integer, ForeignKey('prelegents.id', ondelete='cascade'),
        index=True, nullable=False, primary_key=True)

    event = relationship("Event", back_populates="prelegents_event")
    prelegent = relationship("Prelegent", lazy="joined")

After that, I am getting only 2 warnings instead of 3.

/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'PrelegentEvent.event' will copy column events.id to column prelegents_events.event_id, which conflicts with relationship(s): 'Event.prelegents' (copies events.id to prelegents_events.event_id), 'Prelegent.events' (copies events.id to prelegents_events.event_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

 /usr/local/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'PrelegentEvent.prelegent' will copy column prelegents.id to column prelegents_events.prelegent_id, which conflicts with relationship(s): 'Event.prelegents' (copies prelegents.id to prelegents_events.prelegent_id), 'Prelegent.events' (copies prelegents.id to prelegents_events.prelegent_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

I think this is because of the Prelegent model but I don't know how to implement this correctly here:

    events = relationship(
        "Event", backref=backref("prelegents", lazy='dynamic'),
        secondary="prelegents_events")

Viewonly will not work here because I am using these columns also in my application.

Could you help me with this?

Versions

  • OS: Ubuntu 20.04
  • Python: 3.8.0
  • SQLAlchemy: 1.4.11
  • Database: postgres

Activity

Rogalek
added
requires triageNew issue that requires categorization
on Apr 29, 2021
zzzeek

zzzeek commented on Apr 29, 2021

@zzzeek
zzzeek
on Apr 29, 2021
Member

from a quick look it seeems like you should be placing viewonly on:

events = relationship(
"Event", backref=backref("prelegents", lazy='dynamic', viewonly=True),
secondary="prelegents_events", viewonly=True)

Viewonly will not work here because I am using these columns also in my application.

viewonly doesn't imply that you can't "use" a column, above it just means you shouldn't mutate the Prelegent.events or Event.prelegents collections. If you want to add new rows in the "prelegent_events" table, you would construct and add new PrelegentEvent objects.

and in sqlalchemy 1.3+ all was working without any working, now after switching to 1.4+ I am getting warnings after my test case run:

that's why I'm leaving this here, as we have made some adjustments in this area in #5171 we want to make sure it's not doing anything wrong. It's a little bit of a surprise that 1.3 wasn't warning for this but it looks like these checks were very narrow in 1.3.

zzzeek
added
orm
questionissue where a "fix" on the SQLAlchemy side is unlikely, hence more of a usage question
and removed
requires triageNew issue that requires categorization
on Apr 29, 2021
zzzeek
changed the title [-]solution to relationship X will copy column Q to column P, which conflicts with relationship(s): ‘Y’[/-] [+]warn_on_conflicting_targets seems to work for "secondary" conflicts now but we have no tests for this?[/+] on Apr 29, 2021
zzzeek
added
tests
on Apr 29, 2021
Rogalek

Rogalek commented on Apr 29, 2021

@Rogalek
Rogalek
on Apr 29, 2021
Author

so, when I change viewonly to True, this will not work - that was what I meant by saying that I am using these columns:

        prelegents = event.prelegents

        for p in prelegents:
            event.prelegents.remove(p)
            p_id = p.id

            # copy object
            self.session.expunge(p)
            make_transient(p)
            p.id = None
            self.session.add(p)
            self.session.flush()
            event.prelegents.append(p)

so I have to change it delete each object and then create a new one, yes? Just double-checking if I understood correctly. Thank you for your help.

Yeah, in 1.3 there were no warnings about that and now most of my models are returning this kind of warnings.

zzzeek

zzzeek commented on Apr 29, 2021

@zzzeek
zzzeek
on Apr 29, 2021
Member

so, when I change viewonly to True, this will not work - that was what I meant by saying that I am using these columns:

        prelegents = event.prelegents

        for p in prelegents:
            event.prelegents.remove(p)
            p_id = p.id

            # copy object
            self.session.expunge(p)
            make_transient(p)
            p.id = None
            self.session.add(p)
            self.session.flush()
            event.prelegents.append(p)

so I have to change it delete each object and then create a new one, yes? Just double-checking if I understood correctly. Thank you for your help.

well you can use one or the other, if you put viewonly on the "event" and "prelagent_event" relationships, that would work too.

the warnings are trying to prevent you from making conflicting changes in the "prelegents_events" table, since you have essentially mapped to this table twice in two different ways. SQLAlchemy wants you to write rows into this table using only one of those ways. technically, even if you set viewonly on the PrelegentEvent relationships, you can still create conflicting rows in these two tables, but in this case there are actual relationships overlapping on these columns.

if you really want to leave everything the same and not have warnings then you can use the overlaps parameter, which should be mentioned in the linked doc. overlaps means, "yes it's fine, two ways to write to this column"

Yeah, in 1.3 there were no warnings about that and now most of my models are returning this kind of warnings.

Rogalek

Rogalek commented on Apr 30, 2021

@Rogalek
Rogalek
on Apr 30, 2021
Author

Thanks, I used viewonly=True and changed code a bit and everything works well in version 1.4.11, no more warning.

Rogalek

Rogalek commented on May 10, 2021

@Rogalek
Rogalek
on May 10, 2021
Author

just for the purpose of knowing could you please show me how to change this:

    events = relationship(
        "Event", backref=backref("prelegents", lazy='dynamic'),
        secondary="prelegents_events")

to overlaps?

zzzeek

zzzeek commented on May 10, 2021

@zzzeek
zzzeek
on May 10, 2021
Member

keeping in mind the "overlaps" case wasn't originally something I had noted for this case, and that it would be nice to reduce the verbosity here somehow, for the moment every relationship or backref() has to name every other relationship it conflicts with, so like this:

class Prelegent(Base):
    __tablename__ = "prelegents"

    id = Column(Integer, primary_key=True)
    first_name = Column(Unicode(64), nullable=False)
    last_name = Column(Unicode(64), nullable=False)

    events = relationship(
        "Event",
        backref=backref(
            "prelegents", lazy="dynamic", overlaps="prelegents_event,event"
        ),
        secondary="prelegents_events",
        overlaps="prelegents_event,event",
    )


class Event(Base):
    __tablename__ = "events"

    id = Column(Integer, primary_key=True, autoincrement=True)


class PrelegentEvent(Base):
    __tablename__ = "prelegents_events"

    event_id = Column(
        Integer,
        ForeignKey("events.id", ondelete="cascade"),
        index=True,
        nullable=False,
        primary_key=True,
    )
    prelegent_id = Column(
        Integer,
        ForeignKey("prelegents.id", ondelete="cascade"),
        index=True,
        nullable=False,
        primary_key=True,
    )

    event = relationship(
        "Event", backref="prelegents_event", overlaps="events, prelegents"
    )
    prelegent = relationship(
        "Prelegent", lazy="joined", overlaps="events, prelegents"
    )
Rogalek

Rogalek commented on May 11, 2021

@Rogalek
Rogalek
on May 11, 2021
Author

hey, I wanted to use this also on my other models but I am struggling with one relationship.

I have models like this:

class Exhibitor(Base):
    __tablename__ = 'exhibitors'
​
    categories = relationship(
        "ExhibitorCategory", overlaps="exhibitors_x_exhibitor_categories, exhibitor_categories",
        backref=backref(
            'exhibitors', lazy='dynamic', overlaps="exhibitors_x_exhibitor_categories, exhibitor_categories"),
        secondary="exhibitors_x_exhibitor_categories",
        order_by="ExhibitorCategory.sort")
​
​
class ExhibitorCategory(Base):
    __tablename__ = 'exhibitor_categories'
​
    id = Column(Integer, primary_key=True)
    sort = Column(Integer, nullable=True)
    event_id = Column(
        Integer, ForeignKey('events.id', ondelete='restrict'),
        index=True, nullable=False)
​
    event = relationship(
        "Event", backref=backref('exhibitor_categories', lazy='dynamic'))
​
​
class ExhibitorCategoryAssociation(Base):
    """ Association model between Exhibitors and Exhibitor Categories """
    __tablename__ = 'exhibitors_x_exhibitor_categories'
​
    exhibitor_id = Column(
        Integer, ForeignKey('exhibitors.id', ondelete='cascade'),
        nullable=False, primary_key=True)
    exhibitor_category_id = Column(
        Integer, ForeignKey('exhibitor_categories.id', ondelete='cascade'),
        nullable=False, primary_key=True)
​
    exhibitor = relationship("Exhibitor", overlaps="exhibitors, exhibitor_categories")
    exhibitor_category = relationship(
        "ExhibitorCategory", overlaps="exhibitors, exhibitor_categories",
        backref=backref(
            'attached_exhibitors', lazy='dynamic', overlaps="exhibitors, exhibitor_categories",
            cascade='save-update, merge, delete, delete-orphan'))

but I am still getting:

 /python_modules/sqlalchemy/lib/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'ExhibitorCategoryAssociation.exhibitor' will copy column exhibitors.id to column exhibitors_x_exhibitor_categories.exhibitor_id, which conflicts with relationship(s): 'Exhibitor.categories' (copies exhibitors.id to exhibitors_x_exhibitor_categories.exhibitor_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

/python_modules/sqlalchemy/lib/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'ExhibitorCategory.attached_exhibitors' will copy column exhibitor_categories.id to column exhibitors_x_exhibitor_categories.exhibitor_category_id, which conflicts with relationship(s): 'Exhibitor.categories' (copies exhibitor_categories.id to exhibitors_x_exhibitor_categories.exhibitor_category_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

python_modules/sqlalchemy/lib/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'ExhibitorCategoryAssociation.exhibitor_category' will copy column exhibitor_categories.id to column exhibitors_x_exhibitor_categories.exhibitor_category_id, which conflicts with relationship(s): 'Exhibitor.categories' (copies exhibitor_categories.id to exhibitors_x_exhibitor_categories.exhibitor_category_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

what am I doing wrong? Thanks.

zzzeek

zzzeek commented on May 11, 2021

@zzzeek
zzzeek
on May 11, 2021
Member

might be a good idea to make the warning spell this out but you can get the overlaps instruction from each warning:

/python_modules/sqlalchemy/lib/sqlalchemy/orm/relationships.py:3441: SAWarning: relationship 'ExhibitorCategoryAssociation.exhibitor' will copy column exhibitors.id to column exhibitors_x_exhibitor_categories.exhibitor_id, which conflicts with relationship(s): 'Exhibitor.categories' (copies exhibitors.id to exhibitors_x_exhibitor_categories.exhibitor_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)

you would go to the ExhibitorCategoryAssociation.exhibitor relationship, and set overlaps="categories".

that is. pull the instructions straight from each warning message.

Rogalek

Rogalek commented on May 12, 2021

@Rogalek
Rogalek
on May 12, 2021
Author

Ok, thanks for the explanation.

sqla-tester

sqla-tester commented on Jun 1, 2021

@sqla-tester
sqla-tester
on Jun 1, 2021
Collaborator

Mike Bayer has proposed a fix for this issue in the master branch:

improve "overlaps" warning; test for m2m https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2851

sqlalchemy-bot
closed this as completedin 0c46a57on Jun 1, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    ormquestionissue where a "fix" on the SQLAlchemy side is unlikely, hence more of a usage questiontests

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @zzzeek@sqla-tester@Rogalek

        Issue actions
          warn_on_conflicting_targets seems to work for "secondary" conflicts now but we have no tests for this? · Issue #6400 · sqlalchemy/sqlalchemy