[sqlalchemy] Re: Unit Of work seems to be calling save/delete twice
Thank you. Glad it worked out easily. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit Of work seems to be calling save/delete twice
Hello,
* Apologies if this is a duplicate -- I attempted to post a few hours
earlier but the result vanished. Could be that the earlier effort went off
as a direct email to MB, instead of a post to this group as intended.
chris e posted about this topic in April and I was trying to respond on
that thread, but Google Groups won't take a response this long after.
Below, i've included a working test script that causes a
ConcurrentModificationError. I'm wondering if that is the right result. I
was working with this section of my code specifically to work out what
cascade relationships I wanted to use in my tables and mappers, and while
experimenting with various cascade options I fully expected to run into some
exceptions where I couldn't delete some object because of its
dependencies. But seeing a ConcurrentModificationError instead was a
surprise. So I'm wondering if I've just got something set up wrong.
I've done my best to trim this to a bare-bones minimal reproduction of the
error, though it's still not tiny. Can't really cut out any more
tables/classes/mappers without rendering the test script alien to what I'm
actually working on.
I'm modeling a rental reservation system. rentable Items are (multiple
table) polymorphic. In this example the subclasses of Item have all been
trimmed down to just one for clarity: Ski. Events are polymorphic, too;
here I've included only most obvious subclass of event: Reservation. Each
reservation has one customer associated with it. A reservation is
associated to one or more Gearsets; each gearset has one or more
Items. Items to Gearsets is actually many-to-many, and this is accomplished
via three secondary tables: historyfile, currentfile, and futurefile. In
this way an item has a distinct history of what sets (and thereby what
rentals) it has been a part of in the past; a record of what it's doing
right now, and what sets (and thereby rentals) it is reserved to be a part
of in the future.
If I had to guess, maybe this 3-way split of secondary/association tables is
the thing that might be causing problems. I set it up that way to keep a
strong, up-front distinction between events being in an item's future vs.
its present case vs. its past. But I suppose I could actually achieve the
same thing with all gearsetitem associations being in a single table, and
just map the item's future/current/past to separate selects on that
table. Right?
But that's semi-off-topic. Again, I thought maybe I'd encounter some
cascade problems but I didn't expect a ConcurrentModificationError to happen
where it's happening here.
Here's the test script and stack trace:
# semi-minimal test example for ConcurrentModificationError:
# Deleted rowcount 0 does not match number of objects deleted 1
import sqlalchemy
from sqlalchemy import Table, BoundMetaData, Column
from sqlalchemy import Integer, String, ForeignKey, PrimaryKeyConstraint
from sqlalchemy import ForeignKeyConstraint, polymorphic_union
from sqlalchemy import mapper, relation, backref
from sqlalchemy import create_engine, create_session
db = create_engine('postgres://postgres:[EMAIL PROTECTED]:5432/test2',
encoding='utf-8')
metadata = BoundMetaData(db)
items = Table('items', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('type', String(20)))
skis = Table('skis', metadata,
Column('id', Integer, primary_key=True),
Column('data', Integer),
ForeignKeyConstraint(['id'], ['items.id']))
events = Table('events', metadata,
Column('id', Integer, primary_key=True),
Column('etype', String(20)),
Column('data', Integer))
reservations = Table('reservations', metadata,
Column('id', Integer, ForeignKey('events.id'), primary_key=True),
Column('customer_id', Integer, ForeignKey('customers.id')),
Column('data', Integer))
gearsets = Table('gearsets', metadata,
Column('id', Integer, primary_key=True),
Column('event_id', Integer, ForeignKey('events.id')),
Column('data', Integer))
historyfile = Table('historyfile', metadata,
Column('item_id', Integer, ForeignKey('items.id'), primary_key=True),
Column('gearset_id', Integer, ForeignKey('gearsets.id'),
primary_key=True))
currentfile = Table('currentfile', metadata,
Column('item_id', Integer, ForeignKey('items.id'), primary_key=True),
Column('gearset_id', Integer, ForeignKey('gearsets.id'),
primary_key=True))
futurefile = Table('futurefile', metadata,
Column('item_id', Integer, ForeignKey('items.id'), primary_key=True),
Column('gearset_id', Integer, ForeignKey('gearsets.id'),
primary_key=True))
customers = Table('customers', metadata,
Column('id', Integer, primary_key = True, autoincrement=True),
Column('data', Integer))
class Item(object): pass
class Ski(Item): pass
class GearSet(object): pass
class Event(object): pass
class Reservation(Event): pass
class Customer(object): pass
item_join = polymorphic_union(
{
'ski':items.join(skis),
[sqlalchemy] Re: Unit Of work seems to be calling save/delete twice
p.s.: I'm curious whether this is the same issue mentioned in ticket 370, though that was apparently specific to Firebird's treatement of rowcounts; here I'm on postgresql 8.2. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit Of work seems to be calling save/delete twice
On Jun 20, 2007, at 2:52 AM, Eric Ongerth wrote: If I had to guess, maybe this 3-way split of secondary/association tables is the thing that might be causing problems. I set it up that way to keep a strong, up-front distinction between events being in an item's future vs. its present case vs. its past. But I suppose I could actually achieve the same thing with all gearsetitem associations being in a single table, and just map the item's future/current/past to separate selects on that table. Right? thats definitely where the issue is, since its a many-to-many delete raising the error. however the mapping seems fine; the secondary tables are distinct in each relationship. so its possibly something related to the backrefs upon a single m2m table, although the bi- directional m2m relationships communicate with each other to avoid this conflict. turn on echo_uow=True on your session to possibly shed some hints on this, as well as attempt removing the backrefs/other relationships to see the point at which it fails. else Ill have a look at it sometime tonight or tomorrow. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit Of work seems to be calling save/delete twice
Thanks. I'll be getting back to this tomorrow too. Meanwhile: please note -- easily overlooked, item.current has uselist=False in its relation to Gearset because an item can only *currently* be involved in, at most, one set of items / one in progress reservation. However, item.history and item.future both have uselist=True in their relations to Gearset, because items can have loads of future reservations and loads of events they participated in in the past. While it's not clear that this little scalar-vs-collection difference would cause problems when sqla is processing dependencies upon delete, conceivably it could? Also in the meantime, instinctively would you say keeping all the past/ present/future in one table would just be a better way to go? It only occurred to me very recently that I could do that, and map item.future to a select that collects only related gearsets connected to events with future dates, and map item.history to a different select on the same association table, etc. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit Of work seems to be calling save/delete twice
ok its fixed in 2750 and it was mega-dumb, yes it was the
uselist=False. lets pretend this one didnt happen this late in the
game
On Jun 20, 2007, at 2:52 AM, Eric Ongerth wrote:
items = Table('items', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('type', String(20)))
skis = Table('skis', metadata,
Column('id', Integer, primary_key=True),
Column('data', Integer),
ForeignKeyConstraint(['id'], ['items.id']))
events = Table('events', metadata,
Column('id', Integer, primary_key=True),
Column('etype', String(20)),
Column('data', Integer))
reservations = Table('reservations', metadata,
Column('id', Integer, ForeignKey('events.id'), primary_key=True),
Column('customer_id', Integer, ForeignKey('customers.id')),
Column('data', Integer))
gearsets = Table('gearsets', metadata,
Column('id', Integer, primary_key=True),
Column('event_id', Integer, ForeignKey('events.id')),
Column('data', Integer))
historyfile = Table('historyfile', metadata,
Column('item_id', Integer, ForeignKey('items.id'),
primary_key=True),
Column('gearset_id', Integer, ForeignKey('gearsets.id'),
primary_key=True))
currentfile = Table('currentfile', metadata,
Column('item_id', Integer, ForeignKey('items.id'),
primary_key=True),
Column('gearset_id', Integer, ForeignKey('gearsets.id'),
primary_key=True))
futurefile = Table('futurefile', metadata,
Column('item_id', Integer, ForeignKey(' items.id'),
primary_key=True),
Column('gearset_id', Integer, ForeignKey(' gearsets.id'),
primary_key=True))
customers = Table('customers', metadata,
Column('id', Integer, primary_key = True, autoincrement=True),
Column('data', Integer))
class Item(object): pass
class Ski(Item): pass
class GearSet(object): pass
class Event(object): pass
class Reservation(Event): pass
class Customer(object): pass
item_join = polymorphic_union(
{
'ski':items.join(skis),
'item':items.select(items.c.type=='item'),
}, None, 'ijoin')
item_mapper = mapper(Item, items,
select_table=item_join,
polymorphic_on=item_join.c.type,
polymorphic_identity='item',
properties = {
'history': relation(GearSet,
secondary=historyfile,
backref=backref('items_history', uselist=True),
uselist=True),
'current': relation(GearSet,
secondary=currentfile,
backref=backref('items_current', uselist=True),
uselist=False),
'future': relation(GearSet,
secondary=futurefile,
backref=backref('items_future', uselist=True),
uselist=True)})
customer_mapper = mapper(Customer, customers)
gearset_mapper = mapper(GearSet, gearsets,
properties = {
'event': relation(Event,
backref=backref('gearsets', uselist=True),
uselist=False)
})
event_join = polymorphic_union(
{
'reservation':events.join(reservations),
'event':events.select(events.c.etype=='event'),
}, None, 'ejoin')
event_mapper = mapper(Event, events,
select_table=event_join,
polymorphic_on=event_join.c.etype,
polymorphic_identity='event')
reservation_mapper = mapper(Reservation, reservations,
inherits=event_mapper,
polymorphic_identity='reservation',
properties = {
'customer': relation(Customer,
backref=backref('reservations', uselist=True),
uselist=False)
})
ski_mapper = mapper(Ski, skis,
inherits=item_mapper,
polymorphic_identity='ski')
if __name__ == '__main__':
metadata.create_all()
session = create_session()
ski = Ski()
gearset = GearSet()
customer = Customer()
reservation = Reservation()
reservation.customer = customer
gearset.items_future.append(ski)
gearset.event = reservation
session.save(reservation)
session.flush()
item = session.query(Item).selectfirst()
item.future = []
session.delete(item)
session.flush()
session.close()
metadata.drop_all()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit Of work seems to be calling save/delete twice
send me a reproducing test case, or at least let me see your
mappings. in particular, dont mix a mapping on a table that is also
used as secondary in a many-to-many relationship.
On Apr 17, 2007, at 5:47 PM, chris e wrote:
I'm not sure why. But when I do a delete/sql alchemy seems to be
running the save/delete operation twice. Could this be related to a
circular dependency in UOW that is undetected?? When deleting this is
causing the following error because the database delete is done twice:
sqlalchemy.exceptions.ConcurrentModificationError: Updated rowcount 0
does not match number of objects updated
To fix this I added the following, but it is a serious hack, and
probably needs to be addressed in the unit of work code, but I'm not
sure where to look. Code below. I am on 0.3.5, however I tested
against 0.3.6 as well and this bug appears to be present there as
well.
in orm.unitofwork.UnitOfWork
def _remove_deleted(self, obj):
if hasattr(obj, _instance_key):
# ADDED
# ignore key errors if the item has already been
deleted
try :
del self.identity_map[obj._instance_key]
except KeyError:
pass
try:
self.deleted.remove(obj)
except KeyError:
pass
try:
self.new.remove(obj)
except KeyError:
pass
in orm.mapper.Mapper
def delete_obj(self, objects, uowtransaction):
issue DELETE statements for a list of objects.
this is called within the context of a UOWTransaction during a
flush operation.
if self.__should_log_debug:
self.__log_debug(delete_obj() start)
connection = uowtransaction.transaction.connection(self)
[self.extension.before_delete(self, connection, obj) for obj
in objects]
deleted_objects = util.Set()
for table in self.tables.sort(reverse=True):
if not self._has_pks(table):
continue
delete = []
for obj in objects:
# ADDED
# 4/17/07
# this prevents items from being deleted twice
if hasattr(obj, '_has_been_deleted_') :
continue
params = {}
if not hasattr(obj, _instance_key):
continue
else:
delete.append(params)
for col in self.pks_by_table[table]:
params[col.key] = self.get_attr_by_column(obj,
col)
if self.version_id_col is not None:
params[self.version_id_col.key] =
self.get_attr_by_column(obj, self.version_id_col)
deleted_objects.add(obj)
if len(delete):
def comparator(a, b):
for col in self.pks_by_table[table]:
x = cmp(a[col.key],b[col.key])
if x != 0:
return x
return 0
delete.sort(comparator)
clause = sql.and_()
for col in self.pks_by_table[table]:
clause.clauses.append(col ==
sql.bindparam(col.key, type=col.type))
if self.version_id_col is not None:
clause.clauses.append(self.version_id_col ==
sql.bindparam(self.version_id_col.key, type=self.version_id_col.type))
statement = table.delete(clause)
c = connection.execute(statement, delete)
if c.supports_sane_rowcount() and c.rowcount !=
len(delete):
raise
exceptions.ConcurrentModificationError(Updated rowcount %d does not
match number of objects updated %d % (c.cursor.rowcount,
len(delete)))
# ADDED
# this prevents items from being deleted twice
for obj in deleted_objects :
obj._has_been_deleted_ = True
[self.extension.after_delete(self, connection, obj) for obj in
deleted_objects]
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
