On Oct 26, 2009, at 8:58 PM, Mike Conley wrote:
>
>
> On Mon, Oct 26, 2009 at 11:18 AM, Michael Bayer <mike...@zzzcomputing.com
> > wrote:
>
>
>
> I haven't used ordering list much but perhaps you need to flush()
> before
> such an operation, so that the renumbering performed by ordering
> list is
> persisted and the previous object's identity key is updated to the new
> value, so that the new one coming in does not conflict.
>
>
> The issue here is not really ordering_list. It is "can we update a
> compound primary key in such a way that a new primary key appears to
> conflict with an old primary key, even if only temporarily".
>
> When the update to position is being performed on for primary key
> [user_id:1,position:1] to [user_id:1,position:2] the new record
> conflicts with the existing [user_id:1,position:2] that is going to
> be updated to [user_id:1,position:3]. In theory, the conflict will
> not exist IF we are able to process the position updates highest
> number first, but since the error occurs in flush() processing I'm
> not sure SA can identify that fact. Maybe Michael Baker can
> enlighten us on that.
SQLAlchemy can handle a change in primary key on a single object, and
can also handle a newly incoming primary key where the old holder of
that key is being deleted (it turns them into an UPDATE).
However, in the general case of "object A will now take on object B's
primary key, and object B will take on that of C", that conversation
can't be addressed generically AFAICT. An "empty" primary key value
must be available for one of the objects, which is updated to that
value first, freeing its previous value which then becomes available
to the next object. in this case your solution is to update the
"highest number first", but that is specific to this use case - the
ORM doesn't know that a certain positional number is "available", nor
does it assume that primary keys are even incrementing numbers and not
GUIDs, etc. if you take out the add of the new object here, the
operation fails more simply just on the renumbering operation with the
primary keys.
So to your point, yes if you can control how the reording is done,
you're fine. And here is that:
for blurb in reversed(u.topten):
if blurb.position >= new_blurb.position:
blurb.position = blurb.position + 1
session.flush()
u.topten.append(new_blurb)
session.commit()
or if the list is huge you might just hit it with an executemany update
() to save the overhead:
blurbs = Blurb.__table__
Session.execute(blurbs.update().
where(blurbs.c.position==bindparam('old_pos')).
where(blurbs.c.user_id==u.id).
values(position=bindparam('new_pos')),
params=[
dict(old_pos=x.position, new_pos=x.position+1)
for x in sorted(u.topten, key=lambda b:-b.position)
])
Session.expire(u, ['topten'])
Session.add(newblurb)
Session.commit()
>
>
> I was able to reproduce the problem without using ordering_list.
> http://pastebin.com/m5de2cfe
>
> The important differences from original post:
>
> # Blurb __init__() supplies position
> class Blurb(object):
> def __init__(self, blurb, position):
> self.blurb = blurb
> self.position = position
>
>
> # mapper does not use ordering_list
> mapper(User, users, properties={
> 'topten': relation(Blurb)})
>
>
> # simulate what ordering_list needs to accomplish
> u = session.query(User).get(uid)
> new_blurb = Blurb('I am the new Number Two.',1)
> for blurb in u.topten:
> if blurb.position >= new_blurb.position:
> blurb.position = blurb.position + 1
> u.topten.append(new_blurb)
> session.commit()
>
> Gives same exception.
> sqlalchemy.orm.exc.FlushError: New instance <Blurb at 0xeb9f50> with
> identity key (<class '__main__.Blurb'>, (99, 1)) conflicts with
> persistent instance <Blurb at 0xecd2d0>
>
>
> --
> Mike Conley
>
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
