Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Fri, 2014-12-05 at 00:21 -0800, Peter Geoghegan wrote:
> On Thu, Dec 4, 2014 at 10:27 PM, Anssi Kääriäinen
> <anssi(dot)kaariainen(at)thl(dot)fi> wrote:
> > For Django's use case this is a requirement. We must inform the user if
> > the save() action created a new row or if it modified an existing one.
>
> Can you explain that in more detail, please?

Django has a post_save signal. The signal provide information of the
save operation. One piece of information is a created boolean flag. When
set to True the operation was an insert, on False it was an update.
See
https://docs.djangoproject.com/en/1.7/ref/signals/#django.db.models.signals.post_save
for details.

The created flag is typically used to perform some related action. An
example is User and UserProfile models. Each user must have an
UserProfile, so post_save can be used to create an empty userprofile on
creation of user.

If Django is going to use the INSERT ... ON CONFLICT UPDATE variant in
Django for the existing save() method, then it needs to know if the
result was an UPDATE or INSERT. If we are going to use this for other
operations (for example bulk merge of rows to the database), it would be
very convenient to have per-row updated/created information available so
that we can fire the post_save signals for the rows. If we don't have
that information available, it means we can't fire signals, and no
signals means we can't use the bulk merge operation internally as we
have to fire the signals where that happened before.

Outside of Django there are likely similar reasons to want to know if
the result of an operation was a creation of a new row. The reason could
be creation of related row, doing some action in application layer, or
just UI message telling "object created successfully" vs "object updated
successfully".

- Anssi