Thank you for the explanation! It is indeed deeper than I first thought, but I understand it now.
Finally, consider question 4. (multiple select on delete) to be a bug report, even if harmless. On 15 February 2017 at 17:07, mike bayer <mike...@zzzcomputing.com> wrote: > a "delete" cascade can be done on the client side because it involves a > simple ordering of steps: > > 1. DELETE the rows that depend on the target row > > 2. DELETE the target row. > > > an "update" cascade, OTOH, can't work this way. Because the dependent row > remains existing and needs to have a primary key value at all times, and > that primary key needs to correspond to a row in the target table. The only > way to do this client side (without disabling or dropping the constraints > themselves) would be: > > 1. INSERT a new row into the target table with the new primary key value > > 2. UPDATE the rows that depend on the target row > > 3. DELETE the old row from the target table > > For a simple UPDATE of the target table, this is not feasible; INSERT/DELETE > is a very different operation than an UPDATE at many levels; data wise, > isolation/concurrency-wise, etc. Only "ON UPDATE CASCADE" configured on the > server level can accommodate the flow as an UPDATE on the target table, > where Postgresql internally handles the cascading of the primary key change > to all dependent tables without violating referential integrity. > > > > > On 02/15/2017 11:02 AM, Zsolt Ero wrote: >> >> Thanks a lot! I would be still interested in your answer for 3. and 4. >> >> Especially, what is the difference between update and delete from's >> behaviour here? Why >> does SQLAlchemy know how to "cascade" a delete just on the client >> side, while for update it needs server side CASCADE support? >> >> >> >> >> On 15 February 2017 at 16:51, mike bayer <mike...@zzzcomputing.com> wrote: >>> >>> onupdate=CASCADE is an option of ForeignKey, not Column: >>> Table( >>> 'collections_images', Base.metadata, >>> Column('collection_id', >>> ForeignKey('collections.id', onupdate='CASCADE'), >>> primary_key=True, ), >>> Column('image_id', ForeignKey('images.id'), primary_key=True)) >>> >>> >>> >>> >>> On 02/15/2017 09:45 AM, Zsolt Ero wrote: >>>> >>>> >>>> Thanks Mike for looking into this. >>>> >>>> I've created a minimal program which reproduces my error. As a >>>> context, this is a init script for a Pyramid app, but is run from >>>> command line, not in a request loop. The tables are dropped and >>>> recreated at start. Inklesspen helped me figure out the transaction >>>> manager over IRC and I've simplified it into one single block which is >>>> both simpler and more reliable. >>>> >>>> So about SQLAlchemy's behaviour: >>>> >>>> 1. I do not see anything related to CASCADE in echo when I use >>>> onupdate='CASCADE'. >>>> 2. Update does not work in my case, logs attached. >>>> 3. A manual hack of creating a new collection and deleting the old one >>>> does work. It means that delete does not need CASCADE, but SQLAlchemy >>>> can calculate the order of calls, if I understand right? >>>> 4. An interesting thing is that SQLAlchemy does 3 select calls in the >>>> delete case, even if 1 would be enough. Can be seen in the logs. >>>> >>>> Zsolt >>>> >>>> >>>> >>>> >>>> >>>> On 15 February 2017 at 04:17, mike bayer <mike...@zzzcomputing.com> >>>> wrote: >>>>> >>>>> >>>>> >>>>> >>>>> On 02/14/2017 08:15 PM, Zsolt Ero wrote: >>>>>> >>>>>> >>>>>> >>>>>> I would like to change a primary key's value, to be deterministic, >>>>>> based >>>>>> on a multi-to-multi relation. Thus I'm populating the tables with a >>>>>> temporary ids (just random strings), then calculating the right, >>>>>> unique >>>>>> id, and changing it afterwards. >>>>> >>>>> >>>>> >>>>> >>>>> the examples seem to move "transaction.manager" around, which we assume >>>>> is >>>>> the Zope transaction manager and that by using the context manager the >>>>> Session.commit() method is ultimately called, which raises this error. >>>>> One >>>>> guess is that in the second two examples, the Session is not actually >>>>> getting committed, because no invocation of "dbsession" is present >>>>> within >>>>> the "with transaction.manager" block and I have a vague recollection >>>>> that >>>>> zope.transaction might work this way. Another guess is that in the >>>>> second >>>>> two examples, maybe you already changed the data in the DB and the >>>>> operation >>>>> you're doing has no net change to the rows. >>>>> >>>>> In any case, all three examples you should echo the SQL emitted so you >>>>> can >>>>> see what it's doing. Setting up the onupdate="CASCADE" should fix >>>>> this >>>>> problem. As to why that didn't work from you, keep in mind that is a >>>>> CREATE >>>>> TABLE directive so if you just changed it in your model and didn't >>>>> recreate >>>>> the tables, or at least recreate the foreign key constraints using >>>>> ALTER >>>>> to >>>>> drop and create them again with the CASCADE rule set up; this is a >>>>> server >>>>> side rule. >>>>> >>>>> Here's the MCVE to demonstrate: >>>>> >>>>> from sqlalchemy import * >>>>> from sqlalchemy.orm import * >>>>> from sqlalchemy.ext.declarative import declarative_base >>>>> import md5 as _md5 >>>>> import random >>>>> import string >>>>> >>>>> >>>>> def md5(text): >>>>> return str(_md5.md5(text)) >>>>> >>>>> >>>>> def random_string(num): >>>>> return ''.join(random.choice( >>>>> string.ascii_uppercase + string.digits) for _ in range(num)) >>>>> >>>>> Base = declarative_base() >>>>> >>>>> >>>>> class Image(Base): >>>>> __tablename__ = 'images' >>>>> id = Column(String, primary_key=True, default=lambda: >>>>> random_string(16)) >>>>> collections = relationship( >>>>> 'Collection', secondary='collections_images', >>>>> back_populates='images') >>>>> date_created = Column(DateTime, default=func.now()) >>>>> >>>>> >>>>> class Collection(Base): >>>>> __tablename__ = 'collections' >>>>> id = Column(String, primary_key=True, default=lambda: >>>>> random_string(16)) >>>>> name = Column(String) >>>>> images = relationship( >>>>> 'Image', secondary='collections_images', >>>>> back_populates='collections', >>>>> order_by='desc(Image.date_created)', >>>>> lazy='dynamic') >>>>> >>>>> >>>>> collections_images = Table( >>>>> 'collections_images', Base.metadata, >>>>> Column('collection_id', >>>>> ForeignKey('collections.id', onupdate="CASCADE"), >>>>> primary_key=True), >>>>> Column('image_id', ForeignKey('images.id'), primary_key=True) >>>>> ) >>>>> >>>>> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) >>>>> >>>>> Base.metadata.drop_all(e) >>>>> Base.metadata.create_all(e) >>>>> >>>>> s = Session(e) >>>>> >>>>> with s.transaction: >>>>> s.add(Collection(name='c1', images=[Image(), Image(), Image()])) >>>>> >>>>> with s.transaction: >>>>> collections = s.query(Collection).all() >>>>> >>>>> for collection in collections: >>>>> image_ids = [i.id for i in collection.images.all()] >>>>> image_ids_string = ','.join(sorted(image_ids)) + >>>>> collection.name >>>>> collection.id = md5(image_ids_string)[:16] >>>>> >>>>> >>>>> >>>>> >>>>>> >>>>>> I have the following models: >>>>>> >>>>>> |classImage(Base):id >>>>>> >>>>>> >>>>>> >>>>>> =Column(String,primary_key=True,default=lambda:random_string(16))collections >>>>>> >>>>>> >>>>>> >>>>>> =relationship('Collection',secondary='collections_images',back_populates='images')classCollection(Base):id >>>>>> >>>>>> =Column(String,primary_key=True,default=lambda:random_string(16))images >>>>>> >>>>>> >>>>>> >>>>>> =relationship('Image',secondary='collections_images',back_populates='collections',order_by='desc(Image.date_created)',lazy='dynamic')Table('collections_images',Base.metadata,Column('collection_id',ForeignKey('collections.id'),primary_key=True),Column('image_id',ForeignKey('images.id'),primary_key=True))| >>>>>> >>>>>> My problem is the following: >>>>>> >>>>>> 1. >>>>>> >>>>>> Out of the 3 examples below, only one triggers an integrity >>>>>> exception, the other two does not. >>>>>> >>>>>> Why? >>>>>> >>>>>> In all three I'm trying to write to this primary key, which is >>>>>> referenced, thus should produce an exception. Yet, in 2. and 3. it >>>>>> seem nothing is happening when |collection.id =| is set. When I >>>>>> debug via SQL queries it shows absolutely nothing called for >>>>>> the |collection.id =| line. >>>>>> >>>>>> 2. >>>>>> >>>>>> How can I solve this problem? I mean how can I change a primary >>>>>> key's value which is also used in a multi-to-multi relation? >>>>>> >>>>>> The DB is PostgreSQL 9.5 with psycopg2. >>>>>> >>>>>> The examples are: >>>>>> >>>>>> >>>>>> A. triggers exception: >>>>>> >>>>>> |withtransaction.manager:collections >>>>>> =dbsession.query(Collection).all()forcollection >>>>>> incollections:image_ids >>>>>> =[i.id fori incollection.images.all()]image_ids_string >>>>>> =','.join(sorted(image_ids))+collection.name collection.id >>>>>> =md5(image_ids_string)[:16]| >>>>>> >>>>>> >>>>>> B. does not trigger exception >>>>>> >>>>>> |collections =dbsession.query(Collection).all()# ^ and v only these >>>>>> two >>>>>> lines are swapped withtransaction.manager:forcollection >>>>>> incollections:image_ids =[i.id fori >>>>>> incollection.images.all()]image_ids_string >>>>>> =','.join(sorted(image_ids))+collection.name collection.id >>>>>> =md5(image_ids_string)[:16]| >>>>>> >>>>>> >>>>>> C. also does not trigger exception >>>>>> >>>>>> |collections =dbsession.query(Collection).all()forcollection >>>>>> incollections:image_ids =[i.id fori >>>>>> incollection.images.all()]image_ids_string >>>>>> =','.join(sorted(image_ids))+collection.name >>>>>> withtransaction.manager:collection.id =md5(image_ids_string)[:16]| >>>>>> >>>>>> >>>>>> The exception for the first one is: >>>>>> >>>>>> | >>>>>> sqlalchemy.exc.IntegrityError:(psycopg2.IntegrityError)update >>>>>> ordeleteon >>>>>> table "collections"violates foreign key constraint >>>>>> "fk_collections_images_collection_id_collections"on table >>>>>> "collections_images"DETAIL:Key(id)=(jC3sN8952urTGrqz)isstill >>>>>> referenced >>>>>> fromtable "collections_images". >>>>>> >>>>>> | >>>>>> >>>>>> I've also tried onupdate='CASCADE' for both columns >>>>>> in collections_images but didn't change anything. >>>>>> >>>>>> -- >>>>>> 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 >>>>>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >>>>>> To post to this group, send email to sqlalchemy@googlegroups.com >>>>>> <mailto:sqlalchemy@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 a topic in >>>>> the >>>>> Google Groups "sqlalchemy" group. >>>>> To unsubscribe from this topic, visit >>>>> https://groups.google.com/d/topic/sqlalchemy/breBc7iStF0/unsubscribe. >>>>> To unsubscribe from this group and all its topics, 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. >>>> >>>> >>>> >>> >>> -- >>> 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/breBc7iStF0/unsubscribe. >>> To unsubscribe from this group and all its topics, 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. >> >> > > -- > 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/breBc7iStF0/unsubscribe. > To unsubscribe from this group and all its topics, 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. -- 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.