Python FlightSQL Driver stuck on query execution

[kunjmehta]

What would you like help with?

Hi,

I am running a Python 3.12 script following the recipes to try and use FlightSQL Postgresql adapter (v0.1.0) with the Python FlightSQL driver. This is on a Ubuntu WSL 1 VM on Windows 10 with Postgres 15 and FlightSQL adapter extension added to PG config as required from here

Authentication method on PG is set to password, that is, config is changed in pg_hba.conf

Script:

import adbc_driver_flightsql.dbapi

uri = "grpc://localhost:15432/"
username = 'postgres'
password = 'postgres'

conn = adbc_driver_flightsql.dbapi.connect(uri, 
                                        db_kwargs={'username': username, 'password': password,
                                   'adbc.flight.sql.rpc.call_header.x-flight-sql-database': 'postgres'
                                   })

cur = conn.cursor()
cur.execute("SELECT 1;")   # stuck on this line
print(cur.fetchall())

This is what I see when I debug the code with a breakpoint on the call to execute:

The detail stacktrace for adbc_current_catalog:

Traceback (most recent call last):\n File "/home/kunjmehta10/.vscode-server/extensions/ms-python.python-2021.5.926500501/pythonFiles/lib/python/debugpy/_vendored/pydevd/_pydevd_bundle/pydevd_resolver.py", line 193, in _get_py_dictionary\n attr = getattr(var, name)\n ^^^^^^^^^^^^^^^^^^\n File "/home/kunjmehta10/miniconda3/lib/python3.12/site-packages/adbc_driver_manager/dbapi.py", line 531, in adbc_current_catalog\n return self._conn.get_option(key)\n ^^^^^^^^^^^^^^^^^^^^^^^^^^\n File "adbc_driver_manager/_lib.pyx", line 812, in adbc_driver_manager._lib.AdbcConnection.get_option\n File "adbc_driver_manager/_lib.pyx", line 237, in adbc_driver_manager._lib.check_error\nadbc_driver_manager.ProgrammingError: NOT_FOUND: [Flight SQL] failed to get current catalog: Not Found: [Flight SQL] current catalog not supported\n'

I am not sure if I am doing anything wrong or if I missed something but any help is appreciated!

[lidavidm]

Hey - sorry, it may be a few days before I can get to this

In the meantime the env var ADBC_DRIVER_FLIGHTSQL_LOG_LEVEL=debug might show which call precedes the one it hangs on

[kunjmehta]

Hi, no worries! I did enable the env variable and it does debug like in cases where connection not available. However, in the case of query execution getting stuck, it doesn't log anything until I force quit some time after timeout when it shows the screenshot below

Think it is just stuck without any error. Any idea?

Also checked if RPC URL is accessible via telnet, and it is.

[kunjmehta]

I was able to get it unstuck following this parameter setting idea from here and this is the stack trace I got

Traceback (most recent call last): File "/mnt/d/Projects/Timescale/sqlflight.py", line 53, in cur.execute("SELECT $1;", parameters=(42,)) File "/home/kunjmehta10/miniconda3/envs/arrow/lib/python3.12/site-packages/adbc_driver_manager/dbapi.py", line 698, in execute handle, self._rowcount = _blocking_call( ^^^^^^^^^^^^^^^ File "adbc_driver_manager/_lib.pyx", line 1552, in adbc_driver_manager._lib._blocking_call_impl File "adbc_driver_manager/_lib.pyx", line 1545, in adbc_driver_manager._lib._blocking_call_impl File "adbc_driver_manager/_lib.pyx", line 1195, in adbc_driver_manager._lib.AdbcStatement.execute_query File "adbc_driver_manager/_lib.pyx", line 237, in adbc_driver_manager._lib.check_error adbc_driver_manager.OperationalError: IO: [FlightSQL] error reading from server: EOF (Unavailable; ExecuteQuery) During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/mnt/d/Projects/Timescale/sqlflight.py", line 52, in with conn.cursor() as cur: File "/home/kunjmehta10/miniconda3/envs/arrow/lib/python3.12/site-packages/adbc_driver_manager/dbapi.py", line 235, in exit self.close() File "/home/kunjmehta10/miniconda3/envs/arrow/lib/python3.12/site-packages/adbc_driver_manager/dbapi.py", line 637, in close self._stmt.close() File "adbc_driver_manager/_lib.pyx", line 1167, in adbc_driver_manager._lib.AdbcStatement.close File "adbc_driver_manager/_lib.pyx", line 1173, in adbc_driver_manager._lib.AdbcStatement.close File "adbc_driver_manager/_lib.pyx", line 237, in adbc_driver_manager._lib.check_error adbc_driver_manager.OperationalError: IO: [FlightSQL] connection error: desc = "transport: Error while dialing: dial tcp 127.0.0.1:15432: connect: connection refused" (Unavailable; ClosePreparedStatement) Exception ignored in: <function Cursor.del at 0x7f95a7df82c0> Traceback (most recent call last): File "/home/kunjmehta10/miniconda3/envs/arrow/lib/python3.12/site-packages/adbc_driver_manager/dbapi.py", line 795, in del File "/home/kunjmehta10/miniconda3/envs/arrow/lib/python3.12/site-packages/adbc_driver_manager/dbapi.py", line 637, in close File "adbc_driver_manager/_lib.pyx", line 1166, in adbc_driver_manager._lib.AdbcStatement.close File "adbc_driver_manager/_lib.pyx", line 303, in adbc_driver_manager._lib._AdbcHandle._close_child File "adbc_driver_manager/_lib.pyx", line 305, in adbc_driver_manager._lib._AdbcHandle._close_child RuntimeError: Underflow in closing this AdbcStatement

[jduo]

Hi @kunjmehta ,

It looks like 0.1.0 is a fairly old version of the PostgreSQL Flight SQL Adapter. There were improvements made, including those around prepared statements afterwards but you'll need to build from source.

[jduo]

Oh, I thought the problem was still around ClosePreparedStatement but then saw this:

cur.execute("SELECT $1;", parameters=(42,))

The Flight SQL adapter for PostgreSQL doesn't support bound parameters. It supports only prepared statements without parameters.

[kunjmehta]

@jduo I did build from source following instructions from here. It says to use version 0.1.0

If you can tell me what the latest is, I can rebuild

[jduo]

The latest is actually just the head of the main branch