How to issue an INSERT ON CONFLICT instead of INSERT when using Session.add(my_object)

[Petar]

I want to override my `Session` such that it performs and `INSERT ON CONFLICT` whenever I `add` an object.

`merge` is too slow for my use-case: it has to perform a `SELECT` first, and I want to reduce my latency as much as I can.

Currently this is the standard behaviour:

    user = User(id=1, name='John')  # suppose we already have a record with id=1
    session.add(user)  # this tries to INSERT, which will raise an Integrity error
    session.commit()

I want to be able to do something like this:

    user = User(id=1, name='John', update=True)
    session.add(user)  # this now does INSERT ON CONFLICT DO UPDATE SET ...
    session.commit()

and if `update=False` then perform `DO NOTHING`

I assume I will need to monkey-patch around the Insert class but I am not 100% sure. Any idea how to achieve this in an elegant way?

[Mike Bayer]

it would not happen within the Session as it exists today. the
.add() method doesn't emit an INSERT, it just adds the object to a
collection. the INSERT occurs within the flush process. The good
news is that INSERT/UPDATEs are actually bundled very close together
within the flush process, but actually since this object is given as
pending, it would only be the INSERT process that needs to change.
so it starts first here:
https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/persistence.py#L167
then objects like this would go into here:
https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/persistence.py#L799
. it is possible that an _emit_insert_w_merge() type of function
could be produced here. However, a lot of the behavioral contracts
of the ORM would either be impossible, or be very difficult to
maintain, including the "versioning" feature, the ability to correctly
invoke Python-side column defaults (because we don't know if this is
an INSERT or UPDATE) as well as the ability to receive the values of
server-side defaults (same reason).

whether insert_w_merge() is called upon could depend upon various
factors. We could have a new session.add_for_merge() method that
specifies it at that level (probably best), a flag can be added to the
instance_state indicating the pending object is of this style.

Also, if this were to become a real feature, it would have to work
across the various implementations of "merge" for different databases,
e.g. postgresql vs. mysql vs. ?.

this would not be particularly easy to implement, especially for
someone unfamiliar with the codebase (I have 12 years w/ it :) ). So
if you just need this to work you might want to stick with your own
external function that just calls upon the Core INSERT ON CONFLICT.



>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

I don't know if this will work for your usecase, but we handle a few similar situations with a nested transaction/savepoints.  if an ntegrity error is raised on insert, an update is issued instead.

on conflicts it takes longer than your ideal method, but it's much faster than selecting the record/merging and is currently supported by sqlalchemy.