public inbox for [email protected]  

help / color / mirror / Atom feed

From: Max Ulidtko <[email protected]>
To: Tom Lane <[email protected]>
To: Achilleas Mantzios <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: Getting error 42P02, despite query parameter being sent
Date: Sun, 17 Nov 2024 11:09:28 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

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 
<[email protected]> wrote:
> Achilleas Mantzios <[email protected] 
> <mailto:[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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Getting error 42P02, despite query parameter being sent
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox