pool_size and leaking connections on PostgreSQL

[zejdad]

Number of PostgreSQL connections from apps keeps growing even to hundreds.

All connections are entering Idle state after COMMIT or ROLLBACK query. It seems that the connection pool does not pick them properly, but keeps opening new ones. Not sure if any connections are ever closed. As soon as MAX_CONNECTIONS set by PostgreSQL is reached, the db starts refusing further connections, crashing the app. Problem occurs regardless of the pool_size (tried 0 and 10).

Experienced for pydal used by py4web, not sure if it is py4web-specific.

The problem disappears if I use pool_size=0 and the custom DAL fixture:

class ClosingDAL(pydal.DAL, Fixture):

  reconnect_on_request = True

  def on_request(self, context):
    if self.reconnect_on_request:
      self._adapter.reconnect()
    threadsafevariable.ThreadSafeVariable.restore(ICECUBE)

  def __close_silently(self):
    #to ignore AttributeError: '_thread._local' object has no attribute '_pydal_db_instances_'
    try: self.close()
    except Exception as e: print(f"[DAL] closing error {e}")

  def on_error(self, context):
    self.rollback()
    self.__close_silently()

  def on_success(self, context):
    self.commit()
    self.__close_silently()

For further investigation: Since DAL from core.py performs either commit() or rollback() only, which part of pydal is responsible for closing idling connections above pool_size?

Related discussion thread: https://groups.google.com/g/py4web/c/6pyLn6MsHus