upserts

[gavinking]

Some databases have the ability to do insert or update depending on whether a row already exists. This is something where we could pretty easily abstract the syntax differences between databases (and fall back to using two statements when there's no way to do it in one statement).

And I think this is something that's pretty useful at times. In particular I think it's very useful when processing batches of data.

So if we do this, the question is: how would we expose this to the user?

1. One very appealing (to me, at least) option would be to simply add upsert() to StatelessSession.
2. In principle we could have this be an annotation-driven global setting, but that really doesn't feel quite right to me.
3. Could we make merge() use upserts instead of fetching and then updating? I find the idea very appealing, but I strongly suspect it's very tricky. It runs into some of the same sorts of issues that I had to deal with for "delete without loading". But then, I managed to deal with those issues for remove(), so perhaps I could do similar tricks for merge().

Indeed, 1 and 2 together seem to sort-of make sense: upsert() is naturally the stateless-session version of merge()!

Thoughts?

(P.S. This was a feature suggestion from users who talked to me at Devoxx.)

[beikov]

So if we do this, the question is: how would we expose this to the user?

1. StatelessSession#upsert is a good idea
2. Using this for Session#merge is a bit tricky, as we might need to also load data I guess, but certainly doable. If the database also supports a "returning" clause, that allows returning data from a DML statement, then we could certainly do that with a single statement.
3. I would also like to add a on conflict clause (see the PostgreSQL documentation on that) to our insert statement, which we could emulate fairly easily.

IMO we have to first understand what Session#merge needs to do with respect to loading first (e.g. pre-update/persist listeners, orphan removal?), and then we can think through how to tackle these challenges.

Some thoughts:

• When pre-update or pre-persist listeners exist, we certainly have to load before doing the update, so no upsert there.
• When orphan removal is needed for some associations, we need to somehow select the old value in the statement that does the upsert. This is certainly doable with many databases. DB2 has select fk_col from old table (update ...) and PostgreSQL allows putting DML into a CTE so we can in one statement do e.g. with data as ( select * from tbl where id = ?), removal as(update ...) select fk_col from data

[gavinking]

Well, right, this is pretty similar to my optimizations for delete-without-loading: I transparently disable it if the entity has callbacks or any sort of cascade delete (which includes orphans).

[gavinking]

Note also that this potentially provides a (much better) replacement for Session.replicate() which has been deprecated in 6 without replacement.

[nielsvhaldvb]

Is there an update on an upsert / ON CONFLICT implementation in Hibernate?

[beikov]

You can follow https://hibernate.atlassian.net/browse/HHH-17506 for updates on this matter.