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.
