Pool Connection problem when max reached

[MarcusGros]

When I use the PoolManager and have reached the maximum available connections. Then the whole Python program stops because it waits for an available connection. The autmatism for searching out does not intervene here and the program is forever in wait mode.

I have now solved this by means of a retry that should only fetch new connections and when the maximum is reached only new connections are made.

My current solution to the problem:

def get_con(pool):
    def connect(retrys=0):
        try:
            purity = oracledb.PURITY_DEFAULT
            if retrys > 0 or pool.busy == pool.max:
                purity = oracledb.PURITY_NEW
            con = pool.acquire(purity=purity)
        except (oracledb.OperationalError, oracledb.DatabaseError) as e:
            log.error(e.args[0].message)
            retrys += 1
            log.info(f'{retrys}. try to reconnect')
            time.sleep(1)
            if retrys <= 3:
                return connect(retrys)
            else:
                raise DatabaseConnectionError('Please check database-server, can not connect')
        return con

    return connect()

Here I would wish myself two solutions. 1. the possibility that in the pool automatically at Max always new are created. 2. a pool that can grow automatically with. No maximum connection. Here it needs a good management when a connection is considered done.

[anthony-tuininga]

When you create the pool you can use a "get" mode of oracledb.POOL_GETMODE_FORCEGET. This will force a new connection to be created when the pool is full. That connection will be destroyed when it is "returned" to the pool as only "max" connections are retained. You can also create a pool with a large maximum number of connections. In this case, however, all of the connections will be retained and you will have to manually drop connections from the pool if you don't want that to occur.

[cjbj]

In your 'workaround' code what are you trying to do with the purity? That seems unnecessary and not related to the pool max size on the application tier. It also indicates to me you are using DRCP. Is your base issue with the python-oracledb connection pool size or with the DRCP pool size?

For the python-oracledb connection pool, if you set max high, but min low then the pool will shrink when the pool timeout value is exceeded for a connection.

Although allowing a pool to be effectively infinite may be good for the app, it isn't great for the overall database system. Review the Connection Pool Sizing documentation about using fixed size pools. Also review the POOL_GETMODE_TIMEDWAIT mode, which may be useful.