"row switch" concept is broken

[Mike Bayer]

we detect the delete() of one object with the same PK as another which was just add()ed, and convert what would be a DELETE and INSERT into an UPDATE. The main reason for this was that we issue INSERT statements first, then DELETEs, and there was no obvious way to get the unit of work to issue the DELETE first for this one particular record.

The expected behavior of add() is INSERT. The table may have triggers and other defaults that only trigger on INSERT and not UPDATE. Fields which are undefined on the instance when it is added are expected to be their default values in the DB, which is usually NULL. The current logic takes none of this into account. I'm not sure what the current behavior is for related objects/foreign keys.

The change here must be that the whole "convert INSERT/DELETE to an UPDATE" must be removed. The unitofwork must issue the DELETE for any row switch object before it issues the INSERTs for that mapper. The most obvious way to do this would be to flip the order of DELETEs and INSERTs wholesale for the UOW. Some experimentation is suggesting that this is much harder than it sounds.

[Mike Bayer]

the hibernate folks have sort of an "interesting" conversation here:

http://forum.hibernate.org/viewtopic.php?t=934483&postdays=0&postorder=asc&start=0

although I don't know what to make of Gavin's non-response. "Using hibernate the wrong way", and then he lets it twist in the wind forever, nobody knows what he's talking about. Kind of weird. Lots of folks having the problem over there, mostly related to scalar collections - our own "row switch" handles most of those issues nicely. But then, the original poster did have the exact problem I describe here, which was that he really did want an INSERT in order to issue triggers.

There is an obvious workaround for those who really want the INSERT, which is to issue flush() after the delete().

[Mike Bayer]

it actually requires doing the order as UPDATE, DELETE, INSERT. we definitely have DELETEs that are dependent on UPDATES which null out foreign key columns or update them to something else. There are UPDATES which are dependent on INSERT, but these are already resolved as topological dependencies which would remain that way, with the exception of post_update which is on the same table and remains after the INSERT. there's no DELETE that is dependent on a previous INSERT.

we can do this mostly within unitofwork.py by breaking "tosave" and "todelete" into "toinsert", "toupdate", and "todelete". uow will need to do the "isinsert" check that is currently in mapper._save_obj(). dependency.py can stay the same. mapper._save_obj() can stay the same to start, and later receive the "isinsert" flag at the top of the method thus simplifying its job as well. the current "row switch" check just gets thrown away.

[Mike Bayer]

we still would need UPDATE and INSERT steps intermixed (update an fk to point to a newly inserted row), and breaking apart UPDATE and INSERT into separate phases would prevent that. this whole change cant happen without removing the "batching" nature of the UOW and dropping down to a pure inter-row sort, taking into account fks and insert/update/delete for the sort, from the start. that's a bigger behavioral change than I think we need to get into so i think we have to leave "row switch" as one of SQLA's expected behaviors for now.

[Mike Bayer]

the issue here of emitting DELETE before INSERT is addressed by the patch in #2501. However, it requires that the feature be turned on explicitly. this is likely OK because DELETE before INSERT raises all kinds of unresolvable conflicts - it needs to be something someone would turn on. the feature needs tests and polish but is mostly ready to go.