cherrypy server doesn't recover from psycopg2.error - Idle InTransaction Session Timeout

[hppritcha]

Occassionally one of the cherrypy SQL query operations times out with this kind of traceback:

[17/Aug/2021:03:21:05] HTTP
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/cherrypy/_cprequest.py", line 670, in respond
    response.body = self.handler()
  File "/usr/lib/python3/dist-packages/cherrypy/lib/encoding.py", line 220, in __call__
    self.body = self.oldhandler(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/cherrypy/lib/jsontools.py", line 61, in json_handler
    value = cherrypy.serving.request._json_inner_handler(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/cherrypy/_cpdispatch.py", line 60, in __call__
    return self.callable(*self.args, **self.kwargs)
  File "/mnt/data/mtt.open-mpi.org/mtt/server/php/cherrypy/src/webapp/dispatchers.py", line 337, in POST
    submit_info = self._db.get_submit_id(data['metadata'])
  File "/mnt/data/mtt.open-mpi.org/mtt/server/php/cherrypy/src/webapp/db_pgv3.py", line 664, in get_submit_id
    submit_id = self._select_insert("submit", "submit_id", fields, values)
  File "/mnt/data/mtt.open-mpi.org/mtt/server/php/cherrypy/src/webapp/db_pgv3.py", line 565, in _select_insert
    cursor.execute("SELECT * FROM %s LIMIT 0" % table)
psycopg2.errors.IdleInTransactionSessionTimeout: terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly

The way its currently implemented, the cherrypy server never tries to reconnect to the SQL database, so subsequent operations always fail with this kind of error message:

[17/Aug/2021:05:13:39] HTTP
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/cherrypy/_cprequest.py", line 670, in respond
    response.body = self.handler()
  File "/usr/lib/python3/dist-packages/cherrypy/lib/encoding.py", line 220, in __call__
    self.body = self.oldhandler(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/cherrypy/lib/jsontools.py", line 61, in json_handler
    value = cherrypy.serving.request._json_inner_handler(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/cherrypy/_cpdispatch.py", line 60, in __call__
    return self.callable(*self.args, **self.kwargs)
  File "/mnt/data/mtt.open-mpi.org/mtt/server/php/cherrypy/src/webapp/dispatchers.py", line 337, in POST
    submit_info = self._db.get_submit_id(data['metadata'])
  File "/mnt/data/mtt.open-mpi.org/mtt/server/php/cherrypy/src/webapp/db_pgv3.py", line 664, in get_submit_id
    submit_id = self._select_insert("submit", "submit_id", fields, values)
  File "/mnt/data/mtt.open-mpi.org/mtt/server/php/cherrypy/src/webapp/db_pgv3.py", line 563, in _select_insert
    cursor = self.get_cursor()
  File "/mnt/data/mtt.open-mpi.org/mtt/server/php/cherrypy/src/webapp/db_pgv3.py", line 305, in get_cursor
    return self._connection.cursor()
psycopg2.InterfaceError: connection already closed

The only way currently to fix this is to restart the cherrypy server.

[hppritcha]

@jjhursey this is the MTT cherrypy server error I brought up a couple of OMPI devel calls ago.