Issue: question: InternalError: current transaction is aborted
Author: nskalis
State: closed
Created: 2017-05-16 09:39:29

Hi,

I was wondering if you could advise on the following: when i make a wrong insert, I am getting `InternalError: current transaction is aborted, commands ignored until end of transaction block`

For example, the database connector is initialised like this:

`database = PostgresqlDatabase('ip_spotlight', autocommit=True, autorollback=True, **{'password': 'xxx', 'host': 'x.x.x.x', 'port': 5432, 'user': 'xxx'})  `
and I try to do the following insert

```
try:
    BgpCommunities.create(comm_name="BBB", comm_value="1111:2222", label="bbb", used_as="")
except Exception as e:
    database.rollback()
```
the database connection is locked, thus all sub-sequent queries will fail inevitably.

There is a related discussion over here: https://github.com/coleifer/peewee/issues/240

But I am not abel to figure it how to deal with it. Could you please advise ?

You'll want to be in a transaction in order to rollback, so:

```python
with database.atomic():
    try:
        BgpCommunities.create(...)
    except IntegrityError:
        # handle exception however.
```

thanks @coleifer but could you advise bit more, still the connections is locked
```
In [3]: with database.atomic():
   ...:     try:
   ...:         BgpCommunities.create(comm_name="BBB", comm_value="6830:2222", label="bbb", used_as="")
   ...:     except IntegrityError:
   ...:         pass
   ...:     

In [4]: BgpCommunities.create(comm_name="CCC", comm_value="6830:3333", label="ccc", used_as="generic")
---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
/usr/lib/python3.4/site-packages/peewee.py in execute_sql(self, sql, params, require_commit)
   3767             try:
-> 3768                 cursor.execute(sql, params or ())
   3769             except Exception:

InternalError: current transaction is aborted, commands ignored until end of transaction block


During handling of the above exception, another exception occurred:

InternalError                             Traceback (most recent call last)
<ipython-input-4-d4d4f5a290c0> in <module>()
----> 1 BgpCommunities.create(comm_name="CCC", comm_value="6830:3333", label="ccc", used_as="generic")

/usr/lib/python3.4/site-packages/peewee.py in create(cls, **query)
   4913     def create(cls, **query):
   4914         inst = cls(**query)
-> 4915         inst.save(force_insert=True)
   4916         inst._prepare_instance()
   4917         return inst

/usr/lib/python3.4/site-packages/peewee.py in save(self, force_insert, only)
   5106             rows = 1
   5107         else:
-> 5108             pk_from_cursor = self.insert(**field_dict).execute()
   5109             if pk_from_cursor is not None:
   5110                 pk_value = pk_from_cursor

/usr/lib/python3.4/site-packages/peewee.py in execute(self)
   3524             return self._qr
   3525         else:
-> 3526             cursor = self._execute()
   3527             if not self._is_multi_row_insert:
   3528                 if self.database.insert_returning:

/usr/lib/python3.4/site-packages/peewee.py in _execute(self)
   2910     def _execute(self):
   2911         sql, params = self.sql()
-> 2912         return self.database.execute_sql(sql, params, self.require_commit)
   2913 
   2914     def execute(self):

/usr/lib/python3.4/site-packages/peewee.py in execute_sql(self, sql, params, require_commit)
   3773             else:
   3774                 if require_commit and self.get_autocommit():
-> 3775                     self.commit()
   3776         return cursor
   3777 

/usr/lib/python3.4/site-packages/peewee.py in __exit__(self, exc_type, exc_value, traceback)
   3596             else:
   3597                 exc_args = exc_value.args
-> 3598             reraise(new_type, new_type(*exc_args), traceback)
   3599 
   3600 class _BaseConnectionLocal(object):

/usr/lib/python3.4/site-packages/peewee.py in reraise(tp, value, tb)
    133     def reraise(tp, value, tb=None):
    134         if value.__traceback__ is not tb:
--> 135             raise value.with_traceback(tb)
    136         raise value
    137 elif PY2:

/usr/lib/python3.4/site-packages/peewee.py in execute_sql(self, sql, params, require_commit)
   3766             cursor = self.get_cursor()
   3767             try:
-> 3768                 cursor.execute(sql, params or ())
   3769             except Exception:
   3770                 if self.autorollback and self.get_autocommit():

InternalError: current transaction is aborted, commands ignored until end of transaction block

```

Can you share some code I can run locally to reproduce the error?

Thanks a lot !

the table is defined as:
```
# \d+ bgp_communities 
                                                         Table "doc.bgp_communities"
   Column    |           Type           |                          Modifiers                          | Storage  | Stats target | Description 
-------------+--------------------------+-------------------------------------------------------------+----------+--------------+-------------
 i           | integer                  | not null default nextval('bgp_communities_i_seq'::regclass) | plain    |              | 
 comm_name   | text                     | not null                                                    | extended |              | 
 comm_value  | text                     | not null                                                    | extended |              | 
 label       | text                     | not null                                                    | extended |              | 
 used_as     | text                     | not null                                                    | extended |              | 
 modified_on | timestamp with time zone | default now()                                               | plain    |              | 
Indexes:
    "bgp_communities_pkey" PRIMARY KEY, btree (i)
    "bgp_communities_comm_value_key" UNIQUE CONSTRAINT, btree (comm_value)
Check constraints:
    "bgp_communities_comm_value_check" CHECK (comm_value ~ similar_escape('%:%'::text, NULL::text) OR comm_value ~ similar_escape('%:%:%'::text, NULL:
:text))
    "bgp_communities_used_as_check" CHECK (used_as = ANY (ARRAY['bgp-customer'::text, 'destination'::text, 'dtv-peer'::text, 'free-peer'::text, 'gener
ic'::text, 'infrastructure'::text, 'origin'::text, 'private-peer'::text, 'public-peer'::text, 'traffic-eng'::text, 'voip-peer'::text]))
Has OIDs: no
```

and the model generator produces:
```
$ python3 -m pwiz --engine=postgresql --host= --port=5432 --user= --password  --schema=doc --tables=bgp_communities ip_spotlight
Password: 
from peewee import *

database = PostgresqlDatabase('ip_spotlight', **{'password': '', 'host': '', 'port': 5432, 'user': ''})

class UnknownField(object):
    def __init__(self, *_, **__): pass

class BaseModel(Model):
    class Meta:
        database = database

class BgpCommunities(BaseModel):
    comm_name = TextField()
    comm_value = TextField(unique=True)
    i = PrimaryKeyField()
    label = TextField()
    modified_on = DateTimeField(null=True)
    used_as = TextField()

    class Meta:
        db_table = 'bgp_communities'
        schema = 'doc'

```

this query is correct:
`BgpCommunities.create(comm_name="CCC", comm_value="6830:3333", label="ccc", used_as="generic")`
this query is wrong:
`BgpCommunities.create(comm_name="BBB", comm_value="6830:2222", label="bbb", used_as="")`

and the `database` is initialised like ` database = PostgresqlDatabase('ip_spotlight', autocommit=True, autorollback=True, **`

Hello, 

Any updates on this topic? There had beed no answer and the question is closed. I am having the same issue with connection to postgres being locked even after calling db.rollback() like suggested + db.close() at the end of the function does not release the connection.

Any further suggestions?

I cannot advise what the issue may be, but I don't think it's a peewee issue in that case. It doesn't make sense to me why rolling-back and closing the connection would have no effect, unless you're also using the connection-pool or have omitted other important information.

I was having an issue with the same symptom. In my case, the problem was solved by specifying `autorollback=True`  when creating the database.

This error usually surfaces after an `IntegrityError` has been raised. When using postgres and a constraint is violated, you need to abort the transaction by calling `db.rollback()`. This is handled automatically if you are using the `atomic()` / `transaction()` etc context-managers.