[Python/Snowflake] Connection sometimes hanging

[Niivii]

What happened?

Connection is sometimes hanging or not closing properly while querying data from Snowflake via Polars.
Everything is successful on Snowflake side.

On ADBC 1.7.0, Go driver 1.14.1 and Polars 1.32.3 this would trigger our timeout function wrappers but with
ADBC 1.9.0,Go driver 1.17.0 and Polars 1.35.2 this is just left hanging.

Stack Trace

File "/home/appuser/app/main.py", line 94, in job
    sql_connection.create_snowflake_connection(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        job_settings.db_snowflake) as snowflake_connection,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.13/site-packages/adbc_driver_manager/dbapi.py", line 249, in __exit__
    self.close()
    ~~~~~~~~~~^^
  File "/.venv/lib/python3.13/site-packages/adbc_driver_manager/dbapi.py", line 351, in close
    self._conn.close()
    ~~~~~~~~~~~~~~~~^^
  File "adbc_driver_manager/_lib.pyx", line 1138, in adbc_driver_manager._lib.AdbcConnection.close
  File "adbc_driver_manager/_lib.pyx", line 334, in adbc_driver_manager._lib._AdbcHandle._check_open_children
  File "adbc_driver_manager/_lib.pyx", line 336, in adbc_driver_manager._lib._AdbcHandle._check_open_children
RuntimeError: Cannot close AdbcConnection with open AdbcStatement

How can we reproduce the bug?

with (
    sql_connection.create_snowflake_connection(
        job_settings.db_snowflake) as snowflake_connection,
):
    try:
        snowflake_df = db.get_snowflake_current_data(
            snowflake_connection=snowflake_connection,
        )
    # ...

@func_set_timeout(timeout=360)
def get_snowflake_current_data(
        snowflake_connection: adbc_driver_snowflake.dbapi.Connection,
) -> pl.DataFrame:
    with Path.open(Path("./query_snowflake.sql")) as query_snowflake:
        stmt_snowflake = query_snowflake.read()
    snowflake_df = pl.read_database(query=stmt_snowflake, connection=snowflake_connection)

    return snowflake_df

Environment/Setup

latest versions of adbc, polars, pyarrow, etc.

[lidavidm]

Is there a particular kind of query, or amount of data, that tends to hang?

Is it possible to get a Go stack trace? (@zeroshade is there an easy way to do this? I think I might add a way to optionally enable the pprof server to make this easier...)

[Niivii]

Not really, we fetch a ~100000 rows every 5min from a container. Snowflake send the data in <1s .
After ~1h or more container crashes or hangs with this stacktrace. Memory and CPU usage seems fine.
Difficult to say where it's coming from for me.

[zeroshade]

if you attach gdb to the process, you probably could get access to the Go stack, I can probably add something to dump the go stack via a signal or something.

@Niivii in the case where it hangs, for the exact query where it hangs Snowflake still says that it returned the data in <1s?

Can you try using the same version of Polars in both cases so we can rule out any changes in Polars in the cause?

[Niivii]

I have been running some tests over the night and I can confirm that it occurs whatever the version of polars or adbc (at least the one I mentioned above).

@zeroshade Yes, Snowflake says that it returned the data in <1s.

[zeroshade]

@Niivii Can you try replicating the issue using adbc directly and using fetch_polars instead of through pl.read_database?

I'm just trying to narrow this down and work towards creating a reproducer that we can use to try to figure this out.

In the meantime, I'll see if i can create some kind of reproducer on our end by creating a container that fetches 100000 rows from snowflake every 5 minutes for an hour in a loop

[Niivii]

I will run some tests over the weekend in our test env and let you know ! Will get back at you next week.

[Niivii]

Connection definitely hanging at the .fetch_polars() step.

Snowflake side:

ADBC 1.9.0
Polars 1.35.2

[Niivii]

I can also confirm that it happens in previous versions

[lidavidm]

@zeroshade did you ever figure out what happened here?

[lidavidm]

The other things I think we could try:

• Does it happen with fetch_arrow_table too?
• Can you get a backtrace in gdb?
• How many columns is the dataset (so we can try to replicate, I saw you gave rows already)?
• Would you be willing to try a debug version of the driver that can show the goroutine stacks? (So we can see where it's stuck)

[zeroshade]

@Niivii a couple other questions:

• Are you keeping the connection open the whole time in between the queries every 5 minutes?
• You said you run this in a container, is the container using a script that wakes up every 5 minutes to call the python? or is the loop inside of the python?

I'm trying to replicate the setup as well as I can given the information I have in order to try to reproduce this on my end in a way that I can then debug or track down.

The stack trace you provided is likely just the timeout crashing the script triggering the error that happens if you close the connection without closing the cursor rather than anything actually useful. I'll keep trying to replicate the failure on my end in the meantime.

[zeroshade]

so far creating a loop myself that just made the query for 100k rows every 5 minutes for an hour didn't run into any hangs. So i haven't been able to replicate this yet