Feedback-ID: i76984098:Fastmail
Message-ID: <d40dad6e-0878-4cbb-bd88-bc7ab8028ee7@aklaver.com>
Date: Sun, 17 Nov 2024 09:05:46 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Getting error 42P02, despite query parameter being sent
To: Max Ulidtko <ulidtko@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
Cc: pgsql-general@lists.postgresql.org
References: <NCI1NS.TUGY51AF5VWQ3@gmail.com>
 <40d8beef-ff67-4c6c-828c-2941ca30fdef@cloud.gatewaynet.com>
 <2757246.1731775878@sss.pgh.pa.us> <SVA3NS.UZ3YSZXCQY6F@gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <SVA3NS.UZ3YSZXCQY6F@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d40dad6e-0878-4cbb-bd88-bc7ab8028ee7%40aklaver.com>
Precedence: bulk

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