Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tCcER-007w8L-NH for pgsql-general@arkaria.postgresql.org; Sun, 17 Nov 2024 10:10:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tCcEM-00HFA9-P2 for pgsql-general@arkaria.postgresql.org; Sun, 17 Nov 2024 10:10:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tCcEM-00HFA0-9h for pgsql-general@lists.postgresql.org; Sun, 17 Nov 2024 10:10:11 +0000 Received: from mail-wm1-x329.google.com ([2a00:1450:4864:20::329]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tCcEI-002OiE-8M for pgsql-general@lists.postgresql.org; Sun, 17 Nov 2024 10:10:10 +0000 Received: by mail-wm1-x329.google.com with SMTP id 5b1f17b1804b1-432d9bb168cso18306685e9.1 for ; Sun, 17 Nov 2024 02:10:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731838205; x=1732443005; darn=lists.postgresql.org; h=mime-version:references:in-reply-to:message-id:cc:to:subject:from :date:from:to:cc:subject:date:message-id:reply-to; bh=nt5MW8IYCn/74aos37uWPbC7r48qFvDeXo08NuPgQfU=; b=BVgYQhccME/6W1EhO606egJleYOQS/PsLpv82H5kvTUA+D6clqUqMfEpMmI4SR05o8 CS5ZxKiJ35qTx66ulx7xNo6Wvz+tiurVz9YOF+pCCjStTGNuw0gN7uxdN8Ztrghfa5p7 8Ds0JVfufRZv/We4TIc5QSEaWs7yHtuIoj3mhM1pfjmogyGRwmHFkfOYk8Wy0GWxVLAE v2gKmwyBs6QZhRW9L3UOM3QNu9XQy/ZuPQWFDhCBuge7cjo1Qw/2gbcWh7fA3lEhKtWl NJkKGAgG6D7UySLCx0Ovs6tjE0ZmdUdfvV16qyLIn1F1vU45XVFBfvWsR/ysemVwXQV4 bxiQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731838205; x=1732443005; h=mime-version:references:in-reply-to:message-id:cc:to:subject:from :date:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=nt5MW8IYCn/74aos37uWPbC7r48qFvDeXo08NuPgQfU=; b=sas2XVaFBgJ0n8wPdwp/XF4U0KZtCkkhJxdB9/C6x3ynPrgwXwVwZN0vg6fKBVkQcA cy8KA57H8xOHAfCkjvw82aMdQVdYqzg44kN3YSVVxY4z02tqooax5kzFjZ3wG/qCRADp iJu2JaaAEUf4xUY9DjqDaICyVFIsRF2LB6kmQ1lfchObyK6GX83O33R79+yKqb90VowO RpYA2IkLIwy9x7lyKxWU9aeoKIkrFK12M2wKT3CkuXLkSdG+qIJS5p+YXZdJzz9O7ILw uYsgrnTV58khQ7Ckb9V0PcbYPZuqDzAnAlMQfIa7gPTp8KJ3QbDHskmiwblKObxfocHS CGvA== X-Gm-Message-State: AOJu0YzOdte0p32r6xKhJT/hE8TuDbLkPWJDhiSwxGMZgfM10rcr+4qh dVav+PpaQFHKvhpifFXbxckWWz46aN25QQh+IdHyZmLhCuWcDnjVAGTZ29cV X-Google-Smtp-Source: AGHT+IEq0iDrdydJrBYOJ5NDnZLm66GSe6DV98uv48kQZshxpGQ/u1texbXGELswfK6pm6y8SpAUKg== X-Received: by 2002:a5d:47a7:0:b0:381:eba9:12c7 with SMTP id ffacd0b85a97d-382140750d9mr11657127f8f.27.1731838205036; Sun, 17 Nov 2024 02:10:05 -0800 (PST) Received: from ?IPv6:2a02:a31d:a1c4:4f80:b91d:66fe:804d:6ac? ([2a02:a31d:a1c4:4f80:b91d:66fe:804d:6ac]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-3821ae161d8sm9670503f8f.78.2024.11.17.02.10.04 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sun, 17 Nov 2024 02:10:04 -0800 (PST) Date: Sun, 17 Nov 2024 11:09:28 +0100 From: Max Ulidtko Subject: Re: Getting error 42P02, despite query parameter being sent To: Tom Lane , Achilleas Mantzios , Adrian Klaver Cc: pgsql-general@lists.postgresql.org Message-Id: In-Reply-To: <2757246.1731775878@sss.pgh.pa.us> References: <40d8beef-ff67-4c6c-828c-2941ca30fdef@cloud.gatewaynet.com> <2757246.1731775878@sss.pgh.pa.us> X-Mailer: geary/44.1 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=-JiCL4tkUR92fTArOtXog" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-JiCL4tkUR92fTArOtXog Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable Thanks for replies! I understand now. Just to clarify user-side motivation: I'm taught that concatenating=20 data into SQL query strings is bad practice and should be avoided. I=20 know how to do it safely in my particular case; but apparently the=20 author of this client library was taught the same, and so their=20 query-builder doesn't provide the "raw" quoted-interpolation=20 substitution (the analogue to sql.Literal from Adrian example). Instead=20 this query-builder relies on the parameters mechanism. Hence, this limitation forces me to rewrite my query into raw SQL, with=20 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=20 *at query time*, resulting in effectively VALUES ('md5',=20 'test-param-value') -- not persisted into the view definition. Which is=20 yes, the former option, Tom; it is sane because that's what $1 does in=20 every other query type. If I stare into the abyss regardless, and consider the latter option,=20 the one that makes no sense to me... I don't see how could it possibly=20 ever work. With substitution at some "later time" (expressly not CREATE VIEW query=20 time), how could this ever work? CREATE VIEW foobar_view (alg, hash) AS VALUES ('md5', $1); -- suppose=20 the Param is persisted into view (?!?) SELECT * from foobar_view where alg =3D $1; =E2=80=94 is this a 1- or 2-parameter query? =E2=80=94 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,=20 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=20 substitution-at-query-time is not supported. Nevertheless, acknowledging the "patches welcome" status quo sentiment.=20 This is helpful; thanks again. Max On =D1=81=D0=B1, =D0=BB=D0=B8=D1=81 16 2024 at 11:51:18 -05:00:00, Tom Lane= =20 wrote: > Achilleas Mantzios > writes: >> =CE=A3=CF=84=CE=B9=CF=82 16/11/24 12:55, =CE=BF/=CE=B7 Max Ulidtko =CE= =AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: >>> The issue I'm hitting with it is exemplified by server logs like=20 >>> this: >>>=20 >>> 2024-11-16 10:28:19.928 UTC [46] LOG: execute : CREATE=20 >>> VIEW >>> public.foobar (alg, hash) AS VALUES ('md5', $1); >>> 2024-11-16 10:28:19.928 UTC [46] DETAIL: parameters: $1 =3D >>> 'test-param-value' >>> 2024-11-16 10:28:19.928 UTC [46] ERROR: there is no parameter $1 at >>> character 57 >=20 >> At least for SQL level prepared statements the statement has to be=20 >> 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. >=20 > 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. >=20 > 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. >=20 > regards, tom lane --=-JiCL4tkUR92fTArOtXog Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thanks for replies! I understand n= ow.

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

Hence, this lim= itation forces me to rewrite my query into raw SQL, with hand-quoting of pa= rameter and query string concatenation.

> if CREATE VIEW sto= res 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 ot= her 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.
<= br>
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 = =3D $1;
= =E2=80=94 is this a 1- or 2-parameter query?
=E2=80=94 what do both $1's refer to e= xactly?
= * 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 diffe= rent 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.

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

Max

On =D1=81=D0=B1, =D0=BB=D0=B8=D1=81 16 20= 24 at 11:51:18 -05:00:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> writes:
=CE=A3=CF=84=CE=B9=CF=82 16/11/24 12:55, =CE=BF/=CE=B7 Max Uli= dtko =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
The issue I'm hitting with it is exemplified by server logs li= ke this: =20 2024-11-16 10:28:19.928 UTC [46] LOG: execute <unnamed>: CREATE VIEW= =20 public.foobar (alg, hash) AS VALUES ('md5', $1); 2024-11-16 10:28:19.928 UTC [46] DETAIL: parameters: $1 =3D=20 'test-param-value' 2024-11-16 10:28:19.928 UTC [46] ERROR: there is no parameter $1 at=20 character 57
At least for SQL level prepared statements the statement has t= o be one of : |SELECT|, |INSERT|, |UPDATE|, |DELETE|, |MERGE|, or |VALUES| |so CREATE is not valid, and I guess the extended protocol prepared=20 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
--=-JiCL4tkUR92fTArOtXog--