You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@arrow.apache.org by "michalc (via GitHub)" <gi...@apache.org> on 2025/05/26 16:32:49 UTC
[D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
GitHub user michalc created a discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Running the following in Python and a PostgreSQL database, querying generate_subscripts with the dimension parameter from a bound argument:
```python
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
```python
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?)
```python
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.
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc edited a comment on the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Ah thanks all!
So just for my own curiosity really on how things work/will work after https://github.com/apache/arrow-adbc/pull/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?
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13278696
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc edited a comment on the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Ah thanks all!
So just for my own curiosity really on how things work/will work after https://github.com/apache/arrow-adbc/pull/2881...
All statements get "prepared", and the database itself workw 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?
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13278696
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
GitHub user lidavidm added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Ah, if that's not implemented, we should do that...
The other thing we should do is, if the parameter schema is available, we can try to apply it to the Python->Arrow conversion so that this "just works"
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13277201
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
GitHub user lidavidm added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Isn't that very out of date?
https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQDESCRIBEPREPARED
> [PQdescribePrepared](https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-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](https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQNPARAMS) and [PQparamtype](https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQPARAMTYPE) can be applied to this PGresult to obtain information about the parameters of the prepared statement, and the functions [PQnfields](https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQNFIELDS), [PQfname](https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQFNAME), [PQftype](https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQFTYPE), etc. provide information about the result columns (if any) of the statement.
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13277664
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "paleolimbot (via GitHub)" <gi...@apache.org>.
GitHub user paleolimbot added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
> 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?)
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13277565
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
GitHub user lidavidm added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
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.)
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13279020
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "paleolimbot (via GitHub)" <gi...@apache.org>.
GitHub user paleolimbot added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
> 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:
https://github.com/apache/arrow-adbc/blob/5721207931003695162bbe246991144313f40c48/python/adbc_driver_manager/adbc_driver_manager/dbapi.py#L707
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):
https://github.com/apache/arrow-adbc/blob/5721207931003695162bbe246991144313f40c48/c/driver/postgresql/statement.cc#L763-L766
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13275239
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
@lidavidm
> (I think the Python side still needs updating to use that info when converting Python objects to Arrow data, though.)
Thanks for all of this (and others involved in the code changes). I have been poking around the Python side and not exactly sure where to begin. Can I ask, where in the Python side would things need to be changed and how?
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-15110515
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc edited a discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Running the following in Python for a PostgreSQL database, querying generate_subscripts with the dimension parameter from a bound argument:
```python
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
```python
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?)
```python
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.
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Ah thanks all!
So just for my own curiosity really on things work/will work after https://github.com/apache/arrow-adbc/pull/2881...
All statements get "prepared", and the database itself workw 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?
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13278696
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
GitHub user lidavidm added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Filed https://github.com/apache/arrow-adbc/issues/2880
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-13277683
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "paleolimbot (via GitHub)" <gi...@apache.org>.
GitHub user paleolimbot added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
I believe David may have been referring to the place where we call `pyarrow.record_batch()`:
https://github.com/apache/arrow-adbc/blob/fdf92e0443f5000a1fe94ae08a30bca849fb0cc3/python/adbc_driver_manager/adbc_driver_manager/_dbapi_backend.py#L229-L237
(there may be others). That function accepts a `schema` argument, and in theory we could query the driver for what the schema should be and use it.
The problem with that is that schema inference isn't perfect (like `SELECT $1`) since there's no way to signal the "the driver has no sweet clue" in the current interface.
The workaround today would be to query the bind schema yourself and call `pa.record_batch(..., schema)` manually.
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-15126374
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
GitHub user lidavidm added a comment to the discussion: Querying PostgreSQL generate_subscripts from Python with a dimension from bound parameters
Yeah, I think for now it would be best to query that schema then build the batch by hand. Eventually I think the PostgreSQL driver should internally reconcile the parameter types with the Arrow types (with the caveat that PostgreSQL doesn't, to my knowledge, indicate when multiple types are possible for a parameter so this may force certain values to an unexpected type - this is also why I'm hesitant to change the current behavior)
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865#discussioncomment-15223426
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org