int lists dumped as `numeric[]` regardless of their content [was: ANY not working]

[daxpryce]

I'm trying to make use of the SELECT * FROM foo WHERE id = ANY(%s) syntax and my query execution never seems to return.

An example query:

import os
import psycopg

with psycopg.connect(os.environ["PGSQL_CONN"]) as conn:
    with conn.cursor() as cursor:
        results = conn.execute("SELECT id, url, vector FROM point WHERE id = ANY(%s)", [[1, 2, 3, 4]])

Executing that, it doesn't seem to return

However, the following DOES work:

import os
import psycopg

with psycopg.connect(os.environ["PGSQL_CONN"]) as conn:
    with conn.cursor() as cursor:
        results = conn.execute("SELECT id, url, vector FROM point WHERE id = ANY('{1, 2, 3, 4}')")

It comes back nearly instantly too.

If I EXPLAIN the first query, the cursor returns:

[('Gather  (cost=1000.00..36531388.10 rows=3511110 width=1367)',),
 ('  Workers Planned: 2',),
 ('  ->  Parallel Seq Scan on point  (cost=0.00..36179277.10 rows=1462962 width=1367)',),
 ("        Filter: ((id)::numeric = ANY ('{1,2,3,4}'::numeric[]))",),
 ('JIT:',),
 ('  Functions: 2',),
 ('  Options: Inlining true, Optimization true, Expressions true, Deforming true',)]

If I EXPLAIN the working query, the cursor returns:

[('Index Scan using points_pkey1 on point  (cost=0.57..22.35 rows=4 width=1367)',),
 ("  Index Cond: (id = ANY ('{1,2,3,4}'::integer[]))",)]

I don't know enough to know for sure that any of that is helpful, but I thought it was worth reporting just in case.

[dvarrazzo]

Very useful, yes, thank you for the report.

Can you try if you can work around the problem using WHERE id = ANY(%s::integer[]) in your filter?

I can confirm that the list of ints is dumped with the oid of the numeric array, even if it contains only smaller items, which are dumped with smaller types:

>>> cnn = psycopg.connect()
>>> cur = cnn.execute("select %s, %s", [1, [1,2,3]])
>>> cur._query.types
(21, 1231)

>>> cur.adapters.types[21]
<TypeInfo: int2 (oid: 21, array oid: 1005)>

>>> cur.adapters.types[1231]
<TypeInfo: numeric (oid: 1700, array oid: 1231)>

I have to dig down in the code to figure out if that's what expected, because $REASONS, or if it can be improved.

[daxpryce]

Not only did it work, but the explain now matches the working query as well!

[('Index Scan using points_pkey1 on point  (cost=0.57..22.35 rows=4 width=1367)',),
 ("  Index Cond: (id = ANY ('{1,2,3,4}'::integer[]))",)]

[dvarrazzo]

Cool, thank you for checking.

Let's see if it's an adaptation shortcoming which can be improved or if it's a limitation that can't be overcome, in which case it needs documentation.

[dvarrazzo]

The text dumper (which is the default for lists) does it on purpose. Probably, when I wrote it, it seemed good enough, but of course this problem shows that it's not.

The binary dumper does the right thing: it finds the largest element in the list and picks the smallest Postgres type which can represent it. The text dumper can be probably fixed to do the same.

[dvarrazzo]

Fix release in psycopg 3.0.13. Thank you for the report!