Pool connection closes if connection/cursor object is used AFTER RETURNING it from a function?

[Justin-Rice-unnpp-gov]

What am I missing?

The following scheme WORKS in python-oracledb:

def connect(
    sqlnet_dir: str, # <-- Points to my wallet file's directory
    host: str,
    port: str,
    name: str,
    sql_query: str
) -> oracledb.Connection:
    oracledb.init_oracle_client(config_dir=sqlnet_dir) # <-- (19, 10, 0, 0, 0)
    ez_connect = f"{host}:{port}/{name}"
    pool = oracledb.create_pool(
        dsn=ez_connect,
        homogenous=False,
        externalauth=True,
        encoding="UTF-8"
    )
    conn = pool.acquire()

    ###########################################
    ### The stuff below works just fine ... ###
    ###########################################

    with conn.cursor() as cursor:
        cursor.execute(sql_query)
        while True:
            rows = cursor.fetchmany(100)
            if not rows:
                break
            for row in rows:
                # Do stuff with row
                pass

    return conn

my_conn = connect(d["sqlnet_dir"], d["host"], d["port"], d["name"], some_sql_query) # <-- d is just a dict with params in it

But, the following scheme DOES NOT WORK in python-oracledb because it causes these errors:

DPY-4011: the database or network closed the connection
DPI-1010: not connected

def connect(
    sqlnet_dir: str, # <-- Points to my wallet file's directory
    host: str,
    port: str,
    name: str
) -> oracledb.Connection:
    oracledb.init_oracle_client(config_dir=sqlnet_dir) # <-- (19, 10, 0, 0, 0)
    ez_connect = f"{host}:{port}/{name}"
    pool = oracledb.create_pool(
        dsn=ez_connect,
        homogenous=False,
        externalauth=True,
        encoding="UTF-8"
    )
    conn = pool.acquire()
    return conn

my_conn = connect(d["sqlnet_dir"], d["host"], d["port"], d["name"]) # <-- d is just a dict with params in it

with my_conn.cursor() as cursor:
    cursor.execute(some_sql_query) # <-- Odd! Sometimes fails here the 1st time, sometimes the 2nd, but can trigger the DPY-4011 and DPI-1010 errors
    while True:
        rows = cursor.fetchmany(100) # <-- If it gets here, causes the DPY-4011 and DPI-1010 errors
        if not rows:
            break
        for row in rows:
            # Do stuff with row
            pass

Note that the 2nd scheme worked for me when using cx_Oracle.SessionPool(), but it doesn't with python-oracledb...

In case it's relevant, my sqlnet.ora file looks like this:

WALLET_LOCATION=
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = $HOME/.oracle/wallet)
    )
  )
SQLNET.WALLET_OVERRIDE=TRUE
NAMES.DIRECTORY_PATH=(EZCONNECT,TNSNAMES)
SSL_CLIENT_AUTHENTICATION=FALSE
SSL_VERSION=0

Thoughts?
Justin

[anthony-tuininga]

My initial thoughts are that the pool has been closed since it has gone out of scope. You need to leave it in scope for it to remain open! It is highly likely that cx_Oracle.SessionPool internally retained a reference to the pool on the connection that was acquired from the pool -- which prevented it from closing completely -- an internal implementation detail!

[Justin-Rice-unnpp-gov]

Thanks @anthony-tuininga --- It's easy enough for me to keep it in scope, so I'll just do that. It's certainly a difference I noticed from cx_Oracle, so I figured it was worth a mention. I assume the change is because it's better practice, but is it? Meaning, should oracledb be revised to retain the pool reference outside of scope?

[anthony-tuininga]

An excellent question! I'll discuss with @cjbj. If we do retain the pool reference on the returned connections we'll need to document that behavior as it isn't obvious, I think.

[cjbj]

I can't immediately think of a practical scenario where 'throwing away' the pool by not retaining a reference would be useful; it would seem better just to open a standalone connection. What were your actual apps gaining from this style?

The random behavior concerns me a little.

I see some overlap with a topic that is sometimes raised about doing implicit pooling. Comments and thoughts welcome. The idea is that the first oracledb.connect() would transparently open a connection pool which lived forever (or until some new function closed it). Subsequent connect() calls would use a connection from the pool. There are a bunch of drawbacks to this, and it's not something that could be enabled for every existing code base without breaking something somewhere, so I don't see it as a magic solution to improve scalability of multi-user apps that don't explicitly create a pool. Connections would remain open which might overload a DB; does the app even close() a connection to release it back to the pool for reuse; pooled connections in Thick mode don't support privileged connections; how big should the pool be, etc etc? So if an app wanted to make use of this new feature there might need to be some external configuration, or the app would need some logic changed. Fundamentally it seems simpler to rewrite those apps to explicitly create a pool, perhaps by dropping in some custom classes that inherit e.g. from Connection

PS the encoding option doesn't do anything and can be removed; check the release notes on it.