Re: Getting error 42P02, despite query parameter being sent

Στις 16/11/24 18:09, ο/η Adrian Klaver έγραψε:
> On 11/16/24 03:15, Achilleas Mantzios wrote:
>>
>> Στις 16/11/24 12:55, ο/η Max Ulidtko έγραψε:
>>> Greetings, group!
>>>
>>> I'm trying to understand a low-level issue. Am evaluating a new
>>> client library for Postgres; it's not particularly popular /
>>> mainstream, and as I've understood so far, sports an independent
>>> implementation of PG binary protocol.
>>>
>>> The issue I'm hitting with it is exemplified by server logs like this:
>>>
>>> 2024-11-16 10:28:19.927 UTC [46] LOG: statement: SET client_encoding
>>> = 'UTF8';SET client_min_messages TO WARNING;
>>> 2024-11-16 10:28:19.928 UTC [46] LOG: execute <unnamed>: CREATE VIEW
>>> public.foobar (alg, hash) AS VALUES ('md5', $1);
>>
>> At least for SQL level prepared statements the statement has to be
>> one of :
>>
>> |SELECT|, |INSERT|, |UPDATE|, |DELETE|, |MERGE|, or |VALUES|
>>
>> |so CREATE is not valid, and I guess the extended protocol prepared
>> statements aint no different in this regard.
>
> It would seem so. Using psycopg:
>
> import psycopg
> from psycopg import sql
>
> con =
> psycopg.connect("postgresql://postgres:postgres(at)127(dot)0(dot)0(dot)1:5432/test")
> cur = con.cursor()
> cur.execute("CREATE VIEW public.foobar (alg, hash) AS VALUES ('md5',
> %s)", ['test'])
>
> IndeterminateDatatype: could not determine data type of parameter $1
>
> cur.execute(sql.SQL("CREATE VIEW public.foobar (alg, hash) AS VALUES
> ('md5', {})").format(sql.Literal('test')))
>
> con.commit()
>
> cur.execute("select * from foobar")
> cur.fetchone()
>
> ('md5', 'test')

I dont know python but this does not look like a solid prepared statement.

https://www.psycopg.org/psycopg3/docs/advanced/prepare.html

Does not seem to have used the prepared statement circuitry.

>
>> |
>>
>>> 2024-11-16 10:28:19.928 UTC [46] DETAIL: parameters: $1 =
>>> 'test-param-value'
>>> 2024-11-16 10:28:19.928 UTC [46] ERROR: there is no parameter $1 at
>>> character 57
>>>
>>> Of course, I /am/ passing a value for parameter $1; and I can trace
>>> that the client lib sends it out on the wire as expected. (Attaching
>>> packet captures.)
>>>
>>> Heck, even the PG server itself says, DETAIL: parameters: $1 =
>>> 'test-param-value' — so it sees the parameter! But then, immediately
>>> unsees it.
>>>
>>> Am I being hit by a PG bug? Is this a known issue?
>>>
>>> I'd retested with master version of that client library, and against
>>> 6 latest major versions of PostgreSQL server (12 throughout to 17).
>>> No difference across versions spotted; the result is consistently
>>> error 42P02.
>>>
>>> Is the client library doing something wrong? How can the server
>>> claim there's no parameter $1 immediately after logging its value it
>>> has received?
>>>
>>> I did minify a 100-line SSCCE that reproduces the issue and can be
>>> shared.
>>>
>>> Any advice, or pointers on what to check next besides delving into
>>> PG source, I'd greatly appreciate. Thanks in advance.
>>>
>>> Max
>>>
>