Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters

[michalc]

Running the following in Python for a PostgreSQL database, querying generate_subscripts with the dimension parameter from a bound argument:

import adbc_driver_postgresql.dbapi

with adbc_driver_postgresql.dbapi.connect("postgresql://postgres:password@127.0.0.1:5432/") as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], $1)", [1,])

results in an error:

Traceback (most recent call last):
  File "[...]", line 5, in <module>
    cursor.execute("SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], $1)", [1,])
  File "[...]/python3.12/site-packages/adbc_driver_manager/dbapi.py", line 730, in execute
    handle, self._rowcount = _blocking_call(
                             ^^^^^^^^^^^^^^^
  File "adbc_driver_manager/_lib.pyx", line 1606, in adbc_driver_manager._lib._blocking_call_impl
  File "adbc_driver_manager/_lib.pyx", line 1599, in adbc_driver_manager._lib._blocking_call_impl
  File "adbc_driver_manager/_lib.pyx", line 1241, in adbc_driver_manager._lib.AdbcStatement.execute_query
  File "adbc_driver_manager/_lib.pyx", line 260, in adbc_driver_manager._lib.check_error
adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: Failed to prepare query: ERROR:  function generate_subscripts(integer[], bigint) does not exist
LINE 1: SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], $1)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Query was:SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], $1). SQLSTATE: 42883

But when I query without the bound parameter

import adbc_driver_postgresql.dbapi

with adbc_driver_postgresql.dbapi.connect("postgresql://postgres:password@127.0.0.1:5432/") as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1)")

there is no error.

Or when I use a bound parameter with psycopg (that apparently uses server-side binding and so presumably has to assign a PostgreSQL type to parameters?)

import psycopg
with psycopg.connect("postgresql://postgres:password@127.0.0.1:5432/") as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], %s)", [1,])

there is no error as well

It looks like adbc_driver_postgresql might be assuming that the 1 parameter is a BIGINT, and since there isn't a matching generate_subscripts function https://www.postgresql.org/docs/current/functions-srf.html, it errors? How can I get ADBC to assume that it's just an INT? I know I can put a cast into the SQL itself, but...

The context is that I'm making a SQLAlchemy Dialect for PostgreSQL/ADBC https://github.com/michalc/pgarrow, and querying generate_subscripts is actually coming from SQLAlchemy's base PostgreSQL dialect that I'm extending from https://github.com/sqlalchemy/sqlalchemy/blob/4e4c5a76fce2c13891cb405fe6d2e06c5d3406c1/lib/sqlalchemy/dialects/postgresql/base.py#L4126. Ideally "it would just work" and I wouldn't have to override it to put in some casts.

[paleolimbot]

The context is that I'm making a SQLAlchemy Dialect for PostgreSQL/ADBC

Awesome!

How can I get ADBC to assume that it's just an INT?

I think in this case it's coming from pyarrow, which picks int64 as its default inference of an arbitrary Python integer:

arrow-adbc/python/adbc_driver_manager/adbc_driver_manager/dbapi.py

Line 707 in 5721207

rb = pyarrow.record_batch(

It's a great point that the default conversion to BIGINT is likely to cause some problems. I forget from when I was working on this whether libpq gives us the opportunity to query the parameter types that it thinks we should be using from an arbitrary query (it looks like it isn't implemented from our side, anyway):

arrow-adbc/c/driver/postgresql/statement.cc

Lines 763 to 766 in 5721207

	AdbcStatusCode PostgresStatement::GetParameterSchema(struct ArrowSchema* schema,
	struct AdbcError* error) {
	return ADBC_STATUS_NOT_IMPLEMENTED;
	}

[paleolimbot]

Ah, if that's not implemented, we should do that...

From the libpq docs ( https://www.postgresql.org/docs/8.1/libpq-exec.html )

At present, there is no way to determine the actual data type inferred for any parameters whose types are not specified in paramTypes[]. This is a libpq omission that will probably be rectified in a future release.

We could maybe expose binding things as strings instead of COPY and let Postgres figure it out, or have a custom inference per engine (e.g., Postgres infers Python integers as int32 by default, but sqlite3 infers them as int64s?)

[lidavidm]

Isn't that very out of date?

https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQDESCRIBEPREPARED

PQdescribePrepared allows an application to obtain information about a previously prepared statement.

stmtName can be "" or NULL to reference the unnamed statement, otherwise it must be the name of an existing prepared statement. On success, a PGresult with status PGRES_COMMAND_OK is returned. The functions PQnparams and PQparamtype can be applied to this PGresult to obtain information about the parameters of the prepared statement, and the functions PQnfields, PQfname, PQftype, etc. provide information about the result columns (if any) of the statement.

[lidavidm]

Filed #2880

[michalc]

Ah thanks all!

So just for my own curiosity really on how things work/will work after #2881...

All statements get "prepared", and the database itself works out the types of bound parameters, and this information is then used by the client to decide how to encode the parameters to send over the wire?

[lidavidm]

Essentially, yes. (I think the Python side still needs updating to use that info when converting Python objects to Arrow data, though.) Also note that the inference is not perfect. (SELECT $1 will result in $1 defaulting to varchar, even though in principle it could be anything.)