Postgres cascade error ?
Dave Harrison
The below code establishes 3 tables (house, dog, owner) and a
mapper table to associate owners and dogs (friendships).
When I use either MySQL (5.0.51) or SQLite (3.4.2) as the backend,
this code works correctly. However when I use Postgres (either 8.2.7
or 8.3.1) I get the following integrity error:
sqlalchemy.exceptions.IntegrityError: (IntegrityError) update or
delete on table "dog" violates foreign key constraint
"friendship_dog_id_fkey" on table "friendship" DETAIL: Key (id)=(1)
is still referenced from table "friendship". 'DELETE FROM dog WHERE
dog.id = %(id)s' [{'id': 1}, {'id': 2}]
Interestingly, if I change the "dog" relation on the "Friendship"
mapper to not have a cascade rule, the delete works correctly -
however that means dog records won't get deleted if they get orphaned.
Just for reference, I run this script as follows:
# to build the database
% ./db.py --build
# to find out the id's I want to be watching using the db cli
% ./db.py
% ./db.py --del
Cheers
Dave
---
#!/usr/bin/python
import sys
import sqlalchemy as sa
import sqlalchemy.orm
session = sa.orm.scoped_session(
sa.orm.sessionmaker(autoflush=False, transactional=True)
)
mapper = session.mapper
metadata = sa.MetaData()
houseTable = sa.Table(
'house',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
)
ownerTable = sa.Table(
'owner',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('house_id', sa.Integer, sa.ForeignKey('house.id')),
)
dogTable = sa.Table(
'dog',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('house_id', sa.Integer, sa.ForeignKey('house.id')),
)
friendshipTable = sa.Table(
'friendship',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('owner_id', sa.Integer, sa.ForeignKey('owner.id')),
sa.Column('dog_id', sa.Integer, sa.ForeignKey('dog.id')),
)
class House(object): pass
class Owner(object): pass
class Dog(object): pass
class Friendship(object): pass
mapper(
House,
houseTable,
properties = {
"owners" : sa.orm.relation(
Owner, cascade="delete-orphan"
),
"dogs" : sa.orm.relation(
Dog, cascade="delete-orphan"
),
},
)
mapper(
Owner,
ownerTable,
properties = {
"friendships" : sa.orm.relation(
Friendship, cascade="delete"
),
},
)
mapper(
Friendship,
friendshipTable,
properties = {
"dog" : sa.orm.relation(
Dog, uselist=False, cascade="all, delete-orphan"
),
},
)
mapper(Dog, dogTable)
if __name__ == "__main__":
from optparse import OptionParser
parser = OptionParser()
parser.add_option("--build", dest="build", action="store_true",
default=False)
parser.add_option("--del", dest="delete", action="store_true",
default=False)
(options, args) = parser.parse_args()
engine = sa.create_engine(
"postgres://dave:dave@localhost:5432/satest",
#"mysql://dave:dave@localhost:3306/satest",
#"sqlite:///:memory:",
strategy="threadlocal",
echo=True
)
metadata.bind = engine
session.configure(bind=engine)
if options.build:
print "Creating tables"
metadata.create_all()
print "Seeding database"
for i in range(10): House()
session.flush()
for house in sa.orm.Query(House).all():
for i in range(2):
owner = Owner()
house.owners.append(owner)
session.flush()
for house in sa.orm.Query(House).all():
for i in range(2):
dog = Dog()
house.dogs.append(dog)
session.flush()
for owner in sa.orm.Query(Owner).all():
for dog in sa.orm.Query(Dog).filter_by(house_id =
owner.house_id).all():
friendship = Friendship()
friendship.dog = dog
owner.friendships.append(friendship)
session.commit()
print "HERE HERE HERE ================================="
owner = sa.orm.Query(Owner).first()
for f in owner.friendships:
print "FRIENDSHIP: %s || DOG: %s" % (f.id, f.dog.id)
if options.delete:
print "HERE HERE HERE ================================="
session.delete(owner)
session.flush()
session.commit()
Michael Bayer
On Apr 15, 2008, at 10:32 PM, Dave Harrison wrote:
>
> Hey all,
>
> The below code establishes 3 tables (house, dog, owner) and a
> mapper table to associate owners and dogs (friendships).
>
> When I use either MySQL (5.0.51) or SQLite (3.4.2) as the backend,
> this code works correctly. However when I use Postgres (either 8.2.7
> or 8.3.1) I get the following integrity error:
>
> sqlalchemy.exceptions.IntegrityError: (IntegrityError) update or
> delete on table "dog" violates foreign key constraint
> "friendship_dog_id_fkey" on table "friendship" DETAIL: Key (id)=(1)
> is still referenced from table "friendship". 'DELETE FROM dog WHERE
> dog.id = %(id)s' [{'id': 1}, {'id': 2}]
always use "delete" cascade in conjunction with "delete-orphan". It
doesnt make much sense to have delete-orphan only and not "delete"
cascade. If that doesn't solve your problem here, let me know and Ill
try running the example script.
David Harrison
If I use "delete, delete-orphan" I get the same errors
Michael Bayer
On Apr 16, 2008, at 6:21 PM, David Harrison wrote:
>
> If I use "delete, delete-orphan" I get the same errors
Like I mentioned, "delete-orphan" doesn't work very well separated
from "delete" cascade - which means that its only appropriate for a
one-to-many or one-to-one relation. When many "Friendship" rows
reference the same "Dog" row, the deletion of any "Friendship" row
cascades to the "Dog" row (since delete cascade is implied by delete-
orphan cascade) and the constraint fails.
"delete-orphan" was never designed to track many parents, its just a
convenience feature for the typical "owned by one parent" use case.
David Harrison
Why does MySQL and SQLite behave as I expect, but Postgres doesn't though ?
Michael Bayer
they dont behave like you expect; they dont respect foreign keys, so
your database is in a slightly corrupted state after the operation
completes.
Eric Ongerth
On Apr 16, 7:24 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> always use "delete" cascade in conjunction with "delete-orphan". It
> doesnt make much sense to have delete-orphan only and not "delete"
> cascade.
seeing this (or at least I don't remember taking this sense of things
away after reading) in the documentation. Maybe I developed a blind
spot back around 3.something and never got past it? I have simply
been avoiding delete-orphan although I looked forward to figuring out
how to use it without errors some day. I think this was the key fact
that I missed, even though as you pointed out it's kind of the only
way that makes sense.
Michael Bayer
it *could* make sense as this thing that will scan a whole set of
referenced entities for a link, but thats just not what we have
implemented right now (and also im less certain about what the real
use case there is).
David Harrison
For my part, my-use case is that I want to be able to delete an entry
from my mapper table (the friendshipTable in my example code), and
have it collect any of the mapped entries (dogTable) that are no
longer mapped. Not sure if it's a common use-case.