Issue: Upsert with Model.save()
Author: tuukkamustonen
State: closed
Created: 2021-01-28 12:01:49

In https://github.com/coleifer/peewee/issues/2275 you explain why `.bulk_insert()` cannot support upsert:

> The problem arises w/postgres where the insert cursor returns IDs only for the rows that are created, which can't be mapped back to the actual objects that were inserted.

I'm not sure how to interpret that. Could it be done with single-entry:

```py
bike = Bike(...)
bike.save(on_conflict=OnConflict(...))
```

Or similar? I guess as a workaround I could maybe:

```py
bike.insert(**bike.__data__).on_conflict(...).insert()
bike.insert(model_to_dict(bike)).on_conflict(...).insert()
```

(Didn't try, I'm fairly sure what's above is not sufficient...)

It's hacky.

Docs state that:

> If you simply wish to insert data and do not need to create a model instance, you can use `Model.insert()`

In this case, I do want to also create the model instance, while being able to use `on_conflict`.

First, it's not `bulk_insert()` - it's `bulk_create()`. This method takes a list of unsaved models and inserts them into the db, then maps the returned IDs back to the model instances for Postgres. When you add "on conflict" into the mix, then you can get the unpredictable scenario of some model pks being returned (for inserted rows) while the already-existing ids are not included. As I said, the best way to handle it is to use `insert_many()` since this does not need to correlate returned ids w/model instances.

Pardon my mistake, never used that interface.

> some model pks being returned (for inserted rows) while the already-existing ids are not included

But doesn't it make a difference that there would be only single entry here? Only one PK, one row. Either the ID is returned or not. In either case, user can check after the call.

That is _assuming_ there are no references and related objects to create on the same go (not sure how that would impact it).

I think it's just not worth adding -- you can just rely on well-established workflows for create-or-get in the case of a single row.

http://docs.peewee-orm.com/en/latest/peewee/querying.html#create-or-get

Get or create ain't suitable here, because it adds one more call to the DB. I'm trying to avoid that.

The problem is that violating a constraint generates annoying noise in (at least Postgres) logs:

```
2021-01-28 12:34:56 UTC:10.97.17.111(45384):stable@stable:[1793]:ERROR: -- duplicate key value violates unique constraint
"node_unique_client_uuid_and_product"
2021-01-28 12:34:56 UTC:10.97.17.111(45384):stable@stable:[1793]:DETAIL:  Key (client_uuid, product)=(8180774e-e1e8-48fa-b557-98c0e894c00c, foobar) already exists.
```

With lots of (unsuccessful) creations, logs get bloated with these. Why do we bump into this so often? Well, we have a system, where components are often clueless about their state in another component, so they send opportunistic calls here and there. I think this is a common problem in distributed systems.

Being able to `ON CONFLICT ... IGNORE` would allow to avoid the log entries.

If you want, you can put something like this:

```python

class BaseModel(Model):
    def save_ignore_conflict(self):
        pk = self.insert(**self.__data__).on_conflict_ignore().execute()
        if pk is not None:
             self._pk = pk
        return pk is not None
```

The problem with this should be evident, though:

```python

class User(BaseModel):
    username = TextField(unique=True)
    nickname = TextField()


db.create_tables([User])
User.create(username='u1', nickname='n1')

# here we will not insert a new row because conflict on username unique.
u2 = User(username='u1', nickname='new-n1')
u2.save_ignore_conflict()

# the nickname was not updated to "new-n1"
u_db = User.get(User.username == 'u1')
print(u_db.nickname)
```

The problem is that you have your "stateful" model instance `u2` which has a "nickname" value that is not actually stored in the database.

In this case I will actually do something like:

```py
class BaseModel(Model):
    def save_ignore_conflict(self):
        pk = self.insert(**self.__data__).on_conflict_ignore().execute()
        if pk is None:
             raise SomeError()
```

The HTTP API is behind `POST` method, so I need to return `409 Conflict` in case the entry already exists (instead of `200 OK` if it was `PUT`).

It is a bit twisted as I'm telling the database layer to _ignore_ a constraint violation error, yet willing to raise en error to the caller of the same. But, that's just how it needs to go here.

I'll try that and look closer in the details of `save()`.

But yeah, `ON CONFLICT` is not always used for create-or-get, here the idea is to simply create entries _without_ getting them, to simply ignore (and raise) for what was already there.

Thanks for your help and providing this great nifty library, as always.