Issue: PostgresqlExtDatabase last_insert_id w/ autocommit=True issues
Author: skewty
State: closed
Created: 2017-03-08 02:31:04

I think I may be fighting the below two issues (possible regression?):
issue #353
issue #356

I just upgraded to peewee 2.90 to ensure it wasn't a regression in the build I was using.

If I use `Model.insert().execute()` for the first Filter
insert then the subsequent code will never work without exception.

I need to use the `Model().save()` method for the first Filter if I want
the subsequent code to work without exception.

If I use the `Model().save()` method for the first Filter then I can use
either the `Model().save()` method or the `Model.insert().execute()` method
for the subsequent section without exception.

First Filter creation is:
```
    if Filter.select().count() == 0:
        # Filter(
        #     # id=DEFAULT_FILTER_ALARMS_ID,  # if you put this, it won't save
        #     gui_label=DEFAULT_FILTER_ALARMS_LABEL,
        #     supress_alarms=False,
        #     icon_group=IconGroup.Value.default,
        # ).save()
        Filter.insert(
            id=DEFAULT_FILTER_ALARMS_ID,
            gui_label=DEFAULT_FILTER_ALARMS_LABEL,
            supress_alarms=False,
            icon_group=IconGroup.Value.default,
        ).execute()
```

The subsequent code a bit later is:
```
    for filter_name in ['Staff', 'Patients', 'Media carts', 'Computers on wheels', 'Laptop computers',
                        'IV pumps', 'PCU pumps', 'SCD pumps', 'Stretchers', 'Beds', 'Wheelchairs', 'Comodes']:
        # Filter(
        #     icon_group=icon_group_sonitor.id,
        #     gui_label=filter_name,
        #     json_data_format={
        #         'owner': {'html_type': 'text'},
        #         'tag#': {'html_type': 'text'},
        #     },
        # ).save()
        Filter.insert(
                icon_group=icon_group_sonitor.id,
                gui_label=filter_name,
                json_data_format={
                    'owner': {'html_type': 'text'},
                    'tag#': {'html_type': 'text'},
                }
        ).execute()
```
The `PostgresqlExtDatabase` is shared by both code snippets.

The exception stack trace is:
```
  ...
  File "C:\Users\Scott\AppData\Local\Programs\Python\Python35-32\lib\site-packages\peewee.py", line 3499, in execute
    cursor = self._execute()
  File "C:\Users\Scott\AppData\Local\Programs\Python\Python35-32\lib\site-packages\peewee.py", line 2885, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "C:\Users\Scott\AppData\Local\Programs\Python\Python35-32\lib\site-packages\playhouse\postgres_ext.py", line 386, in execute_sql
    self.commit()
  File "C:\Users\Scott\AppData\Local\Programs\Python\Python35-32\lib\site-packages\peewee.py", line 3571, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "C:\Users\Scott\AppData\Local\Programs\Python\Python35-32\lib\site-packages\peewee.py", line 135, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\Scott\AppData\Local\Programs\Python\Python35-32\lib\site-packages\playhouse\postgres_ext.py", line 379, in execute_sql
    cursor.execute(sql, params or ())
peewee.IntegrityError: duplicate key value violates unique constraint "filter_pkey"
DETAIL:  Key (id)=(1) already exists.
```


It looks like the `id` field is not unique. Since I don't have your model definition, I'm not sure how you've got `id` defined, but that seems to be the issue. If it's the primary key, it needs to be unique.

The reason the calls to save() don't work, I'm guessing, is because you're using a non-auto-incrementing primary key. When this is the case, you must call save() with force_insert=True in order to create a new record.

Here are the models:
```
class BaseModel(Model):
    class Meta:
        database = get_database()


class Feature(BaseModel):
    class Meta:
        db_table = "feature"
    identifier = CharField(max_length=MAX_IDENTIFIER_LENGTH, null=False)
    json_data = JSONField(default={})
    filter = ForeignKeyField(Filter)
    icon_group = ForeignKeyField(IconGroup)
    icon_state = ForeignKeyField(IconState)
    tile_set = ForeignKeyField(TileSet)
    longitude = DoubleField()
    lattitude = DoubleField()
    zone_name = CharField(max_length=MAX_ZONE_NAME_LENGTH, null=True)
    battery_percentage = IntegerField(null=True)
    last_updated = DateTimeField()
```

The table is created in code by:
```
    model_classes = [getattr(sys.modules[__name__], model_name) for model_name in MODEL_NAMES]
    database.drop_tables(model_classes, safe=True, cascade=True)
    database.create_tables(model_classes)
```
and after it is created the id field is:
```
id INTEGER default nextval('feature_id_seq'::regclass)
```

I thought we were talking about the `Filter` model? could you include that models' definition?

Oops, sorry.  I totally included the wrong model..

```
class Filter(BaseModel):
    class Meta:
        db_table = "filter"

    gui_label = CharField(max_length=MAX_FILTER_NAME_LENGTH, null=False)
    supress_alarms = BooleanField(default=False)
    json_data_format = JSONField(null=True, default={})
    icon_group = ForeignKeyField(IconGroup, null=False)
```

The id field is identical:
```
id INTEGER default nextval('filter_id_seq'::regclass)
```


Thank you! Awesome, so you're using the default "id" field definition, which is an auto-incrementing primary key. Yet in your code examples, you're setting the "id" to a particular value. Is there a reason for that?

```python
Filter.insert(
            id=DEFAULT_FILTER_ALARMS_ID,
```

Check out the docs in this and the following related sections for more info: http://docs.peewee-orm.com/en/latest/peewee/models.html#non-integer-primary-keys-composite-keys-and-other-tricks

I need to ensure particular id values, because there are constants in the code that will reference it.
This code is all extracted from a database init script.  

Is including the id field causing the issue?

I guess I could throw an exception if the value returned from `Model.insert().execute()` doesn't match the expected id.

Eww. Why not add another column that contains a human-readable, non opaque value and key off of that?

The linked documentation (thanks, it is great) seems to mention `Model.save()` only.  My failed examples were not using `Model.save()` and the id numbers being specified were matching those that autoincrement would have provided.  

Is `Filter._meta.auto_increment = False` required if I am trying to use `Model.insert()` with a value specified for an autoincrementing field set to a value that would match the value returned?

```
Filter.insert(id=4, ...).execute()
Filter.insert(id=24, ...).execute()
Filter.insert(id=64, ...).execute()
```
works without error without using `Filter._meta.auto_increment = False` first

It would seem to me something isn't quite right with `Model.insert()` because it seems to bypass the `Model._meta.auto_increment` value.

FYI

```
        if DEFAULT_FILTER_ALARMS_ID != Filter.insert(
                gui_label=DEFAULT_FILTER_ALARMS_LABEL,
                supress_alarms=False,
                icon_group=IconGroup.Value.default,
                ).execute():
            raise ValueError("Filter id for {} is incorrect".format(DEFAULT_FILTER_ALARMS_LABEL))
```

works without causing exception in subsequent code.

If you truly wish to manually specify the IDs, then they shouldn't be auto-incrementing. You can change this by specifying

id = IntegerField (primary_key=True)

Honestly I think you're going down the wrong path by specifying the id like this. Just add a column that contains a human-readable key and use that as your constant value.

I will take your advice and change the field type to: `id = IntegerField (primary_key=True)`

Thank you so much for the prompt responses!