Issue: InterfaceError: connection already closed
Author: Olegt0rr
State: closed
Created: 2022-07-10 17:04:03

Autoconnect is not working :(

## Code

```python
from playhouse.pool import PooledPostgresqlExtDatabase

database = PooledPostgresqlExtDatabase(
    ...
    autoconnect=True,
)
```


## Traceback

```python
InterfaceError: connection already closed
...
  File "peewee.py", line 6609, in get_or_create
    return query.get(), False
  File "peewee.py", line 7024, in get
    return clone.execute(database)[0]
  File "peewee.py", line 1927, in inner
    return method(self, database, *args, **kwargs)
  File "peewee.py", line 1998, in execute
    return self._execute(database)
  File "peewee.py", line 2171, in _execute
    cursor = database.execute(self)
  File "playhouse/postgres_ext.py", line 494, in execute
    cursor = self.execute_sql(sql, params, commit=commit)
  File "peewee.py", line 3204, in execute_sql
    self.commit()
  File "peewee.py", line 2970, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "peewee.py", line 191, in reraise
    raise value.with_traceback(tb)
  File "peewee.py", line 3195, in execute_sql
    cursor = self.cursor(commit)
  File "playhouse/postgres_ext.py", line 484, in cursor
    return self._state.conn.cursor()
```

## Versions
```
psycopg2==2.9.3
peewee==3.15.0
```

Since it passed this part of code as `False`
```python
def cursor(self, commit=None):
    if self.is_closed():
        if self.autoconnect:
            self.connect()
        else:
            raise InterfaceError('Error, database connection not opened.')
    ...
```

It seems that `self.is_closed()` returns wrong value

Can you provide a short reproduction of the issue?

The reproduction can't be short, cause I'm using Docker Swarm and it seems to be killing idle connections between nodes. At least I can't explain these connectivity issues any other way.

But anyway peewee should reconnect properly in this situation.


>It seems that self.is_closed() returns wrong value

`is_closed()` refers to the presence or absence of a connection -- but does not indicate the health of that connection.

>and it seems to be killing idle connections between nodes

If this is, in fact, what is happening, then you probably want to review your use of connections -- especially the pool (which implies potential long-idle connections if you have low traffic to the db).

Do you have a way to reliably detect when a connection has been closed? Check the code for `playhouse.pool._PooledPostgresqlDatabase._is_closed()`. When a connection is checked-out from the connection pool, the pool will use the above to test whether the connection is live and usable. This means that if you are checking out a connection from the pool (by using `db.connect()`), Peewee checks to see if that connection can be used before returning it to you. So you may want to review your connection management, specifically when you check-out and return conns to the pool.

If the above check is *not* detecting the "connection already closed" error, however (though this seems very unlikely to me) you can try subclassing and extending the `PooledPostgresqlExtDatabase` with a better check.

My sense, however, is that you're probably checking out connections and then letting them sit idle for a long time without returning them to the pool. Thus they never receive the health checks.

Lastly, Peewee does not automatically reconnect upon receiving this InterfaceError.

Oh, you can also use `db.is_connection_usable()`, which has db-specific implementations for checking whether the underlying connection is in good shape.

It's not clear. Just read the texts

First you're checking `is_closed` and receive False. It means NOT CLOSED.
Then you're trying to execute statement and receive `connection already closed`. It means it CLOSED.

If the connection is unusable, why don't you check `db.is_connection_usable()` with autoconnect option? 

```python
def cursor(self, commit=None):
    if self.is_connection_usable():
        if self.autoconnect:
            self.connect()
        else:
            raise InterfaceError('Error, database connection not opened.')
    ...
```

`is_closed()` refers to the internal state of Peewee -- whether we have a connection or not. I get that it's confusing, but we also don't want to just blindly autoconnect in the event that the connection is killed at some random time -- what if you were in the middle of a transaction, or had uncommitted changes?

Similarly, `autoconnect` does not refer to automatically reconnecting in the event of some error -- rather it means that you don't have to explicitly call `connect()` when you want to start querying the database.

We don't check whether the connection is usable because that would add overhead to every single query executed by Peewee. Internally Peewee gets a cursor for each query it executes. The approach taken by peewee is to open the connection and keep it until `close()` is called. This is slightly different for the pool -- the pool will obviously retain connections and check whether they are usable only when they are initially checked-out.

As I stated earlier, I suggest checking up on your use of connections.