Issue: Error if user closes database inside transaction.
Author: vikt0rs
State: closed
Created: 2018-02-22 15:36:51

If a user tries to close database inside a transaction it gets an `IndexError: pop from empty list` in 3X version, not in 2X Please, check console output below and suggest if I do something wrong. Thanks! 


```
(postman) macmini987:docs-api vserhei$ pip freeze | grep peewee
peewee==2.10.2
peewee-async==0.5.10
(postman) macmini987:docs-api vserhei$ python test_schema_change.py
('SELECT "t1"."document_uuid", "t1"."name" FROM "user_1"."documents_metadata" AS t1 WHERE ("t1"."name" = %s)', ['test'])
(postman) macmini987:docs-api vserhei$
(postman) macmini987:docs-api vserhei$ pip install peewee==3.0.18
Collecting peewee==3.0.18
Installing collected packages: peewee
  Found existing installation: peewee 2.10.2
    Uninstalling peewee-2.10.2:
      Successfully uninstalled peewee-2.10.2
Successfully installed peewee-3.0.18
(postman) macmini987:docs-api vserhei$
(postman) macmini987:docs-api vserhei$ python test_schema_change.py
('SELECT "t1"."document_uuid", "t1"."name" FROM "user_1"."documents_metadata" AS "t1" WHERE ("t1"."name" = %s)', ['test'])
Traceback (most recent call last):
  File "test_schema_change.py", line 33, in <module>
    DB.close()
  File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/contextlib.py", line 88, in __exit__
    next(self.gen)
  File "test_schema_change.py", line 27, in context
    DocumentsModel._meta.schema = None
  File "/Users/vserhei/src/docs-api/.tox/postman/lib/python3.6/site-packages/peewee.py", line 3251, in __exit__
    return self._helper.__exit__(exc_type, exc_val, exc_tb)
  File "/Users/vserhei/src/docs-api/.tox/postman/lib/python3.6/site-packages/peewee.py", line 3292, in __exit__
    self.db.pop_transaction()
  File "/Users/vserhei/src/docs-api/.tox/postman/lib/python3.6/site-packages/peewee.py", line 2540, in pop_transaction
    return self._state.transactions.pop()
IndexError: pop from empty list
(postman) macmini987:docs-api vserhei$
(postman) macmini987:docs-api vserhei$ cat test_schema_change.py
from contextlib import contextmanager

import peewee


DB = peewee.PostgresqlDatabase(
    "my_documents",
    user="test_user", password="top_secret"
)



class DocumentsModel(peewee.Model):

    uuid = peewee.UUIDField(primary_key=True, db_column="document_uuid")
    name = peewee.CharField(256)

    class Meta:
        db_table = "documents_metadata"
        database = DB


@contextmanager
def context(schema_name):
    with DB.atomic() as transaction:
        DocumentsModel._meta.schema = schema_name
        yield transaction
        DocumentsModel._meta.schema = None


with context('user_1'):
    print(DocumentsModel.filter(name="test").sql())
    if not DB.is_closed():
        DB.close()
(postman) macmini987:docs-api vserhei$```

Out of curiosity, why are you closing the database inside a transaction? That seems incorrect.

FYI: you ought to change that `db_column="document_uuid"` to `column_name="document_uuid"`.

> Out of curiosity, why are you closing the database inside a transaction? That seems incorrect.

Well, this logic was implemented a long time ago and I'm pretty sure, that some reasons exist :) Basically, it depends on `cleanup()`/`on_finish()`/`after_request()` methods inside of the application, and in some cases transaction closes after DB connection has already gone.
Please, suggest if peewee 3.X can handle this case (2.X version could).

> FYI: you ought to change that db_column="document_uuid" to column_name="document_uuid".

thanks for the suggestion, will do



I'm not quite sure how to handle this... I kind of think that Peewee *should* report an error if you close the DB while a transaction is open (although if that's the case, the error message should indicate the problem).

ok, thanks for suggestions

f40ec035d2b6f13f4bb5f517ff71e08cf66a1ea1

FYI `ReconnectMixin` can attempt to close and reopen a DB connection while being inside a transaction, I've had this situation:

```python
Traceback (most recent call last):
  …
  File "/usr/local/lib/python3.9/site-packages/peewee.py", line 6884, in get
    return clone.execute(database)[0]
  File "/usr/local/lib/python3.9/site-packages/peewee.py", line 1907, in inner
    return method(self, database, *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/peewee.py", line 1978, in execute
    return self._execute(database)
  File "/usr/local/lib/python3.9/site-packages/peewee.py", line 2150, in _execute
    cursor = database.execute(self)
  File "/usr/local/lib/python3.9/site-packages/peewee.py", line 3157, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/usr/local/lib/python3.9/site-packages/playhouse/shortcuts.py", line 226, in execute_sql
    self.close()
  File "/usr/local/lib/python3.9/site-packages/peewee.py", line 3098, in close
    raise OperationalError('Attempting to close database while '
peewee.OperationalError: Attempting to close database while transaction is open.
```

The funny thing is that it then tries to rollback, but fails because the connection is not working:

```python
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/peewee.py", line 3301, in rollback
    return self._state.conn.rollback()
MySQLdb._exceptions.OperationalError: (2006, 'MySQL server has gone away')
```

I don't know how to properly handle mid-transaction connection errors. I will probably have to reconnect to the SQL server for each request (even if it seems inefficient to not re-use a connection).

>I don't know how to properly handle mid-transaction connection errors. I will probably have to reconnect to the SQL server for each request (even if it seems inefficient to not re-use a connection).

You have to reconnect and rerun all your queries. If the connection is lost, so is any TX you had open.