Update of orderinglist relationship when position is used in PK

[Denis Otkidach]

Sometimes the only choice for primary key of objects in private relationship is parent ID and position in list pair. But update of such property doesn't work: property after flush contains other objects than where assigned to it.

[Mike Bayer]

Haven't looked deeply yet, but I see "ordering list" and "primary key", a lot of these issues aren't fixable, I'm sure you've seen http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/orderinglist.html :

Warning

OrderingList only provides limited functionality when a primary key column or unique column is the target of the sort. Since changing the order of entries often means that two rows must trade values, this is not possible when the value is constrained by a primary key or unique constraint, since one of the rows would temporarily have to point to a third available value so that the other row could take its old value. OrderingList doesn’t do any of this for you, nor does SQLAlchemy itself.

is this issue just another restatement of that? orderinglist only goes so far and I don't really have any great way to improve it in this regard.

[Denis Otkidach]

Sorry, I havn't seen the warning. I agree with you that sometimes it's not possible to meet unique constraint when order is changed (it's agruable for PK: we can see it as order change or just values swap). In my test case all objects in the assigned list are new, so I see no reason to handle it as reorder.

[Mike Bayer]

OK, it would take me some time to understand the issue and at the end it's probably some variant of that problem in any case, is there no way you can just work around this or attempt to fix ? that would be very helpful.

[Denis Otkidach]

I didn't find workaround yet.

[Mike Bayer]

my first observation is that you're expecting the "d" value to stay the same? I'm reading what ordering list does (I didn't write it) and it says: "automatically synchronize changes in list position onto a target scalar attribute." Doesn't that mean you at the very least need to provide "ordering_func" to do alphabetical ordering, and that you'd want this?

p.children = [Child(data='d')]
assert p.children == [Child(data='a')]

[Denis Otkidach]

No, 'a', 'b', 'c', etc. are just handy values for testing to see what's going on. I need to keep the same order as it was when property was assigned. And I forced to use position is PK since there is no other candidate (all the rest fields are not unique, most are nullable) and using autoincremented id will increase database fragmentation on each update. Also don't want to use serialization (like pickle or JSON) since I have a field with foreign key constraint.

[Mike Bayer]

oh, data is not the ordering attribute. totally confusing example, still having trouble understanding what the problem is. this is taking me all morning

[Mike Bayer]

it seems like the deletes of old records fails because as they are removed, their ordering position changes, so the primary keys are wrong and the rows are missed. This is exactly why the warning is there, I don't know how to solve this and I didn't write this extension in the first place. Please don't use primary keys with ordering list.

[Mike Bayer]

there's an issue with PK-based update + delete that's independent of this. #3006

[Mike Bayer]

your test case passes as is with #3006 resolved.