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

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

From

Achilleas Mantzios

Date:

16 November, 17:15:45

Στις 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

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

From

Adrian Klaver

Date:

16 November, 22:09:29

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@127.0.0.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')

> |
> 
>> 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
adrian.klaver@aklaver.com

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

From

Tom Lane

Date:

16 November, 22:51:18

Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> 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

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

From

Max Ulidtko

Date:

17 November, 16:09:28

Thanks for replies! I understand now.

Just to clarify user-side motivation: I'm taught that concatenating data into SQL query strings is bad practice and should be avoided. I know how to do it safely in my particular case; but apparently the author of this client library was taught the same, and so their query-builder doesn't provide the "raw" quoted-interpolation substitution (the analogue to sql.Literal from Adrian example). Instead this query-builder relies on the parameters mechanism.

Hence, this limitation forces me to rewrite my query into raw SQL, with hand-quoting of parameter and query string concatenation.

> if CREATE VIEW stores the Param as a Param

This makes zero sense to me... I assumed that $1 would get substituted *at query time*, resulting in effectively VALUES ('md5', 'test-param-value') -- not persisted into the view definition. Which is yes, the former option, Tom; it is sane because that's what $1 does in every other query type.

If I stare into the abyss regardless, and consider the latter option, the one that makes no sense to me... I don't see how could it possibly ever work.

With substitution at some "later time" (expressly not CREATE VIEW query time), how could this ever work?

CREATE VIEW foobar_view (alg, hash) AS VALUES ('md5', $1); -- suppose the Param is persisted into view (?!?)

SELECT * from foobar_view where alg = $1;

— is this a 1- or 2-parameter query?

— what do both $1's refer to exactly?

* there's $1 in select query referring to values in column alg, and

* there's $1 supposedly persisted into VALUES of view definition, referring to a different column with potentially different type.

This makes no sense to me.

So I'm a bit surprised that the (IMO) straightforward semantics of substitution-at-query-time is not supported.

Nevertheless, acknowledging the "patches welcome" status quo sentiment. This is helpful; thanks again.

Max

On сб, лис 16 2024 at 11:51:18 -05:00:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> 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

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

From

Adrian Klaver

Date:

17 November, 23:05:46

On 11/17/24 02:09, Max Ulidtko wrote:
> Thanks for replies! I understand now.
> 
> Just to clarify user-side motivation: I'm taught that concatenating data 
> into SQL query strings is bad practice and should be avoided. I know how 
> to do it safely in my particular case; but apparently the author of this 
> client library was taught the same, and so their query-builder doesn't 

Why not name the client?

> provide the "raw" quoted-interpolation substitution (the analogue to 
> sql.Literal from Adrian example). Instead this query-builder relies on 
> the parameters mechanism.

Per the docs:

https://www.psycopg.org/psycopg3/docs/api/sql.html

"
class psycopg.sql.Literal(obj: Any)

A Composable representing an SQL value to include in a query.

Usually you will want to include placeholders in the query and pass 
values as execute() arguments. If however you really really need to 
include a literal value in the query you can use this object.

The string returned by as_string() follows the normal adaptation rules 
for Python objects.

Example:

s1 = sql.Literal("fo'o")

s2 = sql.Literal(42)

s3 = sql.Literal(date(2000, 1, 1))

print(sql.SQL(', ').join([s1, s2, s3]).as_string(conn))
'fo''o', 42, '2000-01-01'::date

Changed in version 3.1: Add a type cast to the representation if useful 
in ambiguous context (e.g. '2000-01-01'::date)
"

It is meant to pass in a value not something else, say an identifier 
which is covered by sql.Identifier. The purpose of the sql module is to 
build dynamic SQL safely.

> 
> Hence, this limitation forces me to rewrite my query into raw SQL, with 
> hand-quoting of parameter and query string concatenation.
> 
>  > if CREATE VIEW stores the Param as a Param
> 
> This makes zero sense to me... I assumed that $1 would get substituted 
> *at query time*, resulting in effectively VALUES ('md5', 
> 'test-param-value') -- not persisted into the view definition. Which is 
> yes, the former option, Tom; it is sane because that's what $1 does in 
> every other query type.
> 
> If I stare into the abyss regardless, and consider the latter option, 
> the one that makes no sense to me... I don't see how could it possibly 
> ever work.
> 
> With substitution at some "later time" (expressly not CREATE VIEW query 
> time), how could this ever work?
> 
> CREATE VIEW foobar_view (alg, hash) AS VALUES ('md5', $1); -- suppose 
> the Param is persisted into view (?!?)
> 
> SELECT * from foobar_view where alg = $1;
> — is this a 1- or 2-parameter query?
> — what do both $1's refer to exactly?
> * there's $1 in select query referring to values in column alg, and
> * there's $1 supposedly persisted into VALUES of view definition, 
> referring to a different column with potentially different type.
> 
> This makes no sense to me.
> 
> So I'm a bit surprised that the (IMO) straightforward semantics of 
> substitution-at-query-time is not supported.
> 
> Nevertheless, acknowledging the "patches welcome" status quo sentiment. 
> This is helpful; thanks again.
> 
> Max
> 
> On сб, лис 16 2024 at 11:51:18 -05:00:00, Tom Lane <tgl@sss.pgh.pa.us> 
> wrote:
>> Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com 
>> <mailto:a.mantzios@cloud.gatewaynet.com>> 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 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

From

"David G. Johnston"

Date:

17 November, 23:18:22

On Sun, Nov 17, 2024 at 3:10 AM Max Ulidtko <ulidtko@gmail.com> wrote:

I assumed that $1 would get substituted *at query time*

The "select" portion of a "create view" query does not get executed during processing of "create view" and it is the "select" execution part of the executor code that performs parameter substitution. IOW, "at query time" never actually happens, the query is only considered as a literal to be parsed.

David J.

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

From

Achilleas Mantzios

Date:

18 November, 01:44:01

Στις 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@127.0.0.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
>>>
>

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

From

Adrian Klaver

Date:

18 November, 04:02:19

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:postgres@127.0.0.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.
> 

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
adrian.klaver@aklaver.com