Can use on_conflict to backup and update record?

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.

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.

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.

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

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