One-to-One Association keeps old records

[mereghost]

Heya folks,

I was working on hanami/model support for one-to-one associations when I noticed that when I associate a new record the old one is kept in the database.

What I expected was that issuing a create command on a one-to-one association would remove the old record (if any) and insert a new one. While it does retrieve the correct one, it leaves the database a mess and might generate constraint violation errors.

[solnic]

Thanks for reporting this. We'll address this in the next version.

[mereghost]

By next version you mean rom-4.0? =)

[solnic]

Nope, next bug fix release. @flash-gordon you think you could take care of this?

[flash-gordon]

@solnic sorry for being stupid here, but how is it supposed to work? Remove existing references in a before hook?
@mereghost I'd say it should trigger a constraint violation error, even if we remove the existing record this operation is not going to be atomic, leaving room for possible duplications. FWIW I'd recommend creating one-to-one records along simultaneously, in a single transaction, and use updates later on.

[solnic]

@flash-gordon I thought about wrapping it in a transaction as a special way of handling this type of association

[flash-gordon]

yes, this should be run inside a transaction, so that possible errors won't pollute the database with partial writes, but this won't prevent from having duplicates anyway, consider this

session 1	session 2
starts a command	starts a command
deletes associated records -> 0	...
...	deleted associated records -> 0
inserts a new record	...
...	inserts a new record
commit	commit

Having a unique constraint will pause session 2 on the insert step until session 1 is completed (or rolled back). So we actually need a DB constraint. And yes, transaction is a must too :)
Another option is using pessimistic locking on the parent record, i.e. SELECT FOR UPDATE, this way both sessions will be synchronized before the delete phase starts. Unfortunately, not all databases support this mechanism, for instance, SQL does not.

[mereghost]

@flash-gordon While I do agree that having a constraint at the database level is the right thing to do, I see no way to enforce it to the end users.

Guarding against partial writes and then warning users that duplicates may end in the database, due to race conditions, if no unique constraint is in place seems more "user friendly" to me.