On Jul 27, 2011, at 3:34 AM, ammar azif wrote: > Hi, > > I am using SQLAlchemy 0.6.4 with postgres db. I have two tables - users and > addresses tables with addresses table having a foreign key constraint > referencing the users table. Each address record is identified by a unique > constraint key 'email_address'. > In my test case, each user instance have a collection of addresses. For each > user instance, I want to delete every address instance in the addresses > collection that the user instance has and then add new address instances > (they may have the same unique key that the previously deleted address > instance had). The problem I am having now is that at the end of the flush > call, unique constraint error for 'email_address' from addresses table is > thrown even though delete operation is done earlier than insert. Looking at > the echo output, INSERTs are indeed done first than DELETEs. The work around > that I have now is to call flush() right after the deletion of address > instances in each user. > > My question is - what is the precedence of insert, delete and update in > session flush? It would also be helpful if someone can explain the overview > of the mechanics of flushing in SQLAlchemy. > Attached is the python script that I wrote to understand why this problem > mentioned above happens.
Right so, the unit of work was rewritten in version 0.6, because it was desperately needed, but also one issue I wanted to see if it could be solved was the one case that the UOW can't handle currently without direct intervention, that of the unique constraint that needs to be deleted before a new one is inserted. It should be noted that for an object where the primary key itself is the thing that might conflict for an outgoing / incoming situation, the UOW turns that operation into an UPDATE. But for an arbitrary column with a unique on it we don't have the mechanics in place to do it that way, nor would I want to . Id much rather have a delete + insert be a DELETE + INSERT in all cases. So with the UOW rewrite, its very clear cut how the order of steps is determined, and the architecture is fairly amenable to a strategy that would freely mix DELETE, INSERT and UPDATE. However, I didn't go this far with the rewrite. I tried a bit, trying to have the topological sort also take into account individual INSERTS that need to come before DELETES, instead of it being an overarching "two step" process, but there seemed to be a lot of really mindbendy types of cases where the fact that DELETES are unconditionally after the INSERT/UPDATEs makes things work out really well. The 0.6 rewrite had the highest priority on not introducing any new bugs vs. the previous version, which had been very stable for a long time (although impossible to work with) so I didn't go further with that idea, at that time. (There of course is no reason someone can't try to work with it some more) The current scheme is INSERT/UPDATES first, DELETES second, and the original idea is mostly from Hibernate's procedure (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/objectstate.html#objectstate-flushing), which I seem to recall was not as verbose as that particular description is now. I have an architectural document I can send you under separate cover (it is not public yet). -- 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.
