Issue: psycopg2.InternalError when skipping duplicate records
Author: dzeban
State: closed
Created: 2015-10-23 07:43:10

Fedora 22
Python 3.4.2
Peewee version 2.6.4
PostgreSQL 9.4

I have a simple table with pwiz generated model

``` python
from peewee import *

database = PostgresqlDatabase('ribbo', **{'password': 'secret', 'host': 'localhost', 'user': 'ribbo'})

class UnknownField(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class Feed(BaseModel):
    description = TextField(null=True)
    htmlurl = TextField(null=True)
    title = TextField(null=True)
    url = TextField(null=True)

    class Meta:
        db_table = 'feed'
```

`url` field has unique constraint

``` sql
CONSTRAINT feed_url_key UNIQUE (url)
```

Now I'm trying to insert feeds with the following code

``` python
#!/usr/bin/env python3
from models import *
import peewee
import sys

def makedict(i):
    return {'url': str(i), 'title': 'foo', 'description': 'bar'}

for i in range(10):
    d = makedict(i)
    print('Trying to insert {}'.format(d))
    try:
        Feed.create(**d)
    except peewee.IntegrityError:
        print('Skipping duplicate')
```

First round it's good:

```
$ python3 peewee-bug.py 
Trying to insert {'url': '0', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '1', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '2', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '3', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '4', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '5', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '6', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '7', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '8', 'description': 'bar', 'title': 'foo'}
Trying to insert {'url': '9', 'description': 'bar', 'title': 'foo'}
```

But on the second round it fails like this:

```
$ python3 peewee-bug.py 
Trying to insert {'title': 'foo', 'description': 'bar', 'url': '0'}
Skipping duplicate
Trying to insert {'title': 'foo', 'description': 'bar', 'url': '1'}
Traceback (most recent call last):
  File "/usr/lib/python3.4/site-packages/peewee.py", line 3331, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "peewee-bug.py", line 13, in <module>
    Feed.create(**d)
  File "/usr/lib/python3.4/site-packages/peewee.py", line 4289, in create
    inst.save(force_insert=True)
  File "/usr/lib/python3.4/site-packages/peewee.py", line 4461, in save
    pk_from_cursor = self.insert(**field_dict).execute()
  File "/usr/lib/python3.4/site-packages/peewee.py", line 3113, in execute
    cursor = self._execute()
  File "/usr/lib/python3.4/site-packages/peewee.py", line 2535, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/usr/lib/python3.4/site-packages/peewee.py", line 3339, in execute_sql
    self.commit()
  File "/usr/lib/python3.4/site-packages/peewee.py", line 3185, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/lib/python3.4/site-packages/peewee.py", line 123, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3.4/site-packages/peewee.py", line 3331, in execute_sql
    cursor.execute(sql, params or ())
peewee.InternalError: current transaction is aborted, commands ignored until end of transaction block
```

Note, that exception occurs only after first row was skipped.

So, I understand that it fails because I skip iteration and not do proper cleanup in exception. So is there anything I can do in exception handler to properly skip duplicate row?

If I change constraint to be deferred

``` sql
CONSTRAINT feed_url_key UNIQUE (url)
  DEFERRABLE INITIALLY DEFERRED
```

it works well all the time, so I might be just stick to it, but anyway I would like to know other ways.

Thanks.


You need to recover, as your connection is in a bad state after the integrity error. One way is to write:

``` python
    try:
        with database.atomic():
            Feed.create(**d)
    except peewee.IntegrityError:
        print('Skipping duplicate')
```

Wrapping it this way will roll back the transaction in the event things get wild.