Can use on_conflict to backup and update record?

Questions / Help
1

I’ve encountered this case：I want insert a record to ‘users’ table（columns: user_name, class, score), if the primary key: user_name is duplicated, I want to backup the record to table 'users_history‘ and update the ‘users’ table record, and further more, the ‘users_history’ table also has a primar key: user_name, while backuping record to ‘users_history’, if the user_name is duplicated, just update the ‘users_history’ too.
So, How can do this by Ecto? Thanks.

2

No, the on_conflict action can only update columns in the same table. I can think of two decent options here:

  1. In a transaction: try a normal insert, rescue the ConstraintError, upsert the history record, update the users record.
  2. Create a DB trigger to make the users_history changes on update (you’d have the trigger function perform an upsert), then you can just do your upsert on users and the DB will handle the history upsert.
3

That’s not going to work (as easily). You cannot rescue a constraint in a transaction as the db will roll back the transaction on encountering the constraint error. You could use savepoints, but by my understanding they come with tradeoffs.

4

Sorry, yeah. You can’t have a transaction here, you’d have to lock table(s?) if concurrency was an issue.

5

Thanks, I think I should take two steps to do this. step 1: upsert for users_history, step 2：upsert users.