Postgres doesn't reconnect after idle time

[Korayem]

Describe the bug
connect_to_postgres() code aparently doesn't handle situations where connection is closed after some idle time

To Reproduce
Steps to reproduce the behavior:

1. Run Vanna using postgres
2. Try to chat in any interface, things work
3. Leave it running for a few minutes
4. Try to chat again
5. Error: psycopg2.InterfaceError: connection already closed

Expected behavior
App should detect connection is closed and re-establish a new connection

Error logs/Screenshots
psycopg2.InterfaceError: connection already closed

Desktop (please complete the following information where):

• OS: Mac OS
• Version: Sonoma 14.3.1
• Python: 3.11
• Vanna: 0.6.2

Additional context
Potential Solution: https://stackoverflow.com/a/74328338/80434

try:
    cursor = conn.cursor()
except psycopg2.InterfaceError as e:
    print('{} - connection will be reset'.format(e))
    # Close old connection 
    if conn:
        if cursor:
            cursor.close()
        conn.close()
    conn = None
    cursor = None
    
    # Reconnect 
    conn = psycopg2.connect(user=user,
                            password=password,
                            host=host,
                            port=port,
                            database=database,
                            sslmode=sslmode)
    cursor = conn.cursor()

[alexfire68]

+1

[pygeek]

This error is also raised when one attempts to close an already closed connection. Re-establishing a connection, in this case, would be undesired behavior. This is likely also a feature we would want to apply across the board. I recommend an implementation similar to: https://github.com/django/django/blob/main/django/db/backends/base/base.py

Also, what are your configuration settings/plugins? Postgres should be keeping connections alive indefinitely by default

[Korayem]

Fixed here #607