public inbox for [email protected]
help / color / mirror / Atom feedGetting error 42P02, despite query parameter being sent
4+ messages / 4 participants
[nested] [flat]
* Getting error 42P02, despite query parameter being sent
@ 2024-11-16 10:55 Max Ulidtko <[email protected]>
2024-11-16 11:15 ` Re: Getting error 42P02, despite query parameter being sent Achilleas Mantzios <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Max Ulidtko @ 2024-11-16 10:55 UTC (permalink / raw)
To: [email protected]
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);
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
Attachments:
[application/vnd.tcpdump.pcap] query42P02-with-prepstatement-on.pcap (2.5K, 3-query42P02-with-prepstatement-on.pcap)
download
[application/vnd.tcpdump.pcap] query42P02-with-prepstatement-off.pcap (2.3K, 4-query42P02-with-prepstatement-off.pcap)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting error 42P02, despite query parameter being sent
2024-11-16 10:55 Getting error 42P02, despite query parameter being sent Max Ulidtko <[email protected]>
@ 2024-11-16 11:15 ` Achilleas Mantzios <[email protected]>
2024-11-16 16:09 ` Re: Getting error 42P02, despite query parameter being sent Adrian Klaver <[email protected]>
2024-11-16 16:51 ` Re: Getting error 42P02, despite query parameter being sent Tom Lane <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Achilleas Mantzios @ 2024-11-16 11:15 UTC (permalink / raw)
To: [email protected]
Στις 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.
|
> 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] 4+ messages in thread
* Re: Getting error 42P02, despite query parameter being sent
2024-11-16 10:55 Getting error 42P02, despite query parameter being sent Max Ulidtko <[email protected]>
2024-11-16 11:15 ` Re: Getting error 42P02, despite query parameter being sent Achilleas Mantzios <[email protected]>
@ 2024-11-16 16:09 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2024-11-16 16:09 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; [email protected]
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')
> |
>
>> 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
>>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting error 42P02, despite query parameter being sent
2024-11-16 10:55 Getting error 42P02, despite query parameter being sent Max Ulidtko <[email protected]>
2024-11-16 11:15 ` Re: Getting error 42P02, despite query parameter being sent Achilleas Mantzios <[email protected]>
@ 2024-11-16 16:51 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2024-11-16 16:51 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; +Cc: Max Ulidtko <[email protected]>; [email protected]
Achilleas Mantzios <[email protected]> writes:
> Στις 16/11/24 12:55, ο/η Max Ulidtko έγραψε:
>> The issue I'm hitting with it is exemplified by server logs like this:
>>
>> 2024-11-16 10:28:19.928 UTC [46] LOG: execute <unnamed>: CREATE VIEW
>> public.foobar (alg, hash) AS VALUES ('md5', $1);
>> 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
> 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.
Indeed. To some extent this is an implementation limitation: the
parameter is received (and printed if you have logging enabled),
but it's not passed down to utility statements such as CREATE VIEW.
But the reason nobody's been in a hurry to lift that restriction
is that doing so would open a large can of semantic worms. In a
case like CREATE VIEW, exactly what is this statement supposed to
mean? I assume you were hoping that it would result in replacement
of the Param by a Const representing the CREATE-time value of the
parameter, but why is that a sane definition? It's certainly not
what a Param normally does. On the other hand, if CREATE VIEW
stores the Param as a Param (which is what I think would happen
if we just extended the parameter-passing plumbing), that's unlikely
to lead to a good outcome either. There might not be any $1 available
when the view is used, and if there is one it's not necessarily of
the right data type.
So, pending some defensible design for what should happen and a patch
implementing that, we've just left it at the status quo, which is that
Params are only available to the DML statements Achilleas mentioned.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-11-16 16:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-16 10:55 Getting error 42P02, despite query parameter being sent Max Ulidtko <[email protected]>
2024-11-16 11:15 ` Achilleas Mantzios <[email protected]>
2024-11-16 16:09 ` Adrian Klaver <[email protected]>
2024-11-16 16:51 ` Tom Lane <[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