public inbox for [email protected]
help / color / mirror / Atom feedRe: Getting error 42P02, despite query parameter being sent
2+ messages / 2 participants
[nested] [flat]
* Re: Getting error 42P02, despite query parameter being sent
@ 2024-11-17 19:44 Achilleas Mantzios <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Achilleas Mantzios @ 2024-11-17 19:44 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected]
Στις 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:[email protected]: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
>>>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Getting error 42P02, despite query parameter being sent
@ 2024-11-17 22:02 Adrian Klaver <[email protected]>
parent: Achilleas Mantzios <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2024-11-17 22:02 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; [email protected]
On 11/17/24 11:44, Achilleas Mantzios wrote:
>
> Στις 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:[email protected]: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.
>
The second example is not and was not meant to be. It was meant to show
how you could dynamically create an SQL statement when it will not
accept parameters.
FYI, this was run using psycopg(3) which does things differently, by
default, then psycopg2. For full explanation see:
https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html
Server-side binding
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-11-17 22:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-17 19:44 Re: Getting error 42P02, despite query parameter being sent Achilleas Mantzios <[email protected]>
2024-11-17 22:02 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox