Ent API: upsert*()
Among all Ent API calls, upsert*()
is probably the most limited one. This is due to it being optimized for batching: in SQL, there are limitations on what can be achieved in this area. (Some restrictions may later be lifted though.)
Ent.upsert(vc, { field: "...", ... }): string
This call tries to insert a new row in the database. In case there was a "duplicate key" error (i.e. if a row the unique key defined in Ent schema exists), updates that existing row.
Returns ID of the updated (or inserted) row.
Don't use upsert()
too often, because it rolls the ID sequence on the Ent even when the object is updated, not inserted. E.g. if you upsert the same exact row with the same exact field values a billion times, then the DB sequence backing the table will be incremented one billion times, even though the row remains unchanged. There is currently no good ways to solve this, although it may appear in the future.
Upsert can't work if some triggers are defined for the Ent, because we don't know Ent ID in advance (whether the upsert succeeds or skips on duplication).
Also, upsert()
will refuse to run if there are Inverses defined on some Ent fields (same reason: Inverses operations run in a different microshard strictly before the main Ent operation, and they must know the row's ID in advance).
Currently, upsert()
ignores autoInsert
fields when it updates an existing row (such fields are only populated on the very first insert). Fields with autoInsert
are not updated, even if their values are explicitly provided in the input row. Partially, the motivation here is that don't want e.g. created_at
to be updated on each upsert()
call (and it's an autoInsert
field). And partially, it's just the current implementation limitation.
All of the above issues may be solved in the future.
Ent.upsertReturning(vc, { ... }): Ent
This call is very similar to upsert()
, but in the end, it loads the updated (or inserted) Ent back from the datbase using loadX()
.
Since upsert()
is meant to always succeed (except when there is a transport error, or when some database constraint check unrelated to the main Ent's unique key fails), there are no "X" and "Nullable" variations of this method.
Batching
Multiple upsert*()
calls running in parallel are batched by Ent Framework:
Notice that in the above example, we explicitly passed a value "2020-01-01"
to an autoInsert
field created_at
to illustrate the problem with updating it.
Here is the batched query:
We see that in this batched query, the first VALUES
row has created_at
equals to now()
(automatically generated by PostgreSQL on each run), and the second row has 2020-01-01'
there. It explains why there is no created_at=EXCLUDED.created_at
part in the ON CONFLICT DO UPDATE
clause: by the time the query runs, we don't know whether a row processed by ON CONFLICT
correspond to case 1 in VALUES
(where we should not touch it) or to case 2 (where we should update it).
Last updated
Was this helpful?