Date: Sat, 16 Nov 2024 11:55:35 +0100
From: Max Ulidtko <ulidtko@gmail.com>
Subject: Getting error 42P02, despite query parameter being sent
To: pgsql-general@lists.postgresql.org
Message-Id: <NCI1NS.TUGY51AF5VWQ3@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=-YUNuVVNIzo64+NnZQaQr"
Archived-At: <https://www.postgresql.org/message-id/NCI1NS.TUGY51AF5VWQ3%40gmail.com>
Precedence: bulk

--=-YUNuVVNIzo64+NnZQaQr
Content-Type: multipart/alternative; boundary="=-fndHcmB0wdM6wKOeb+mh"

--=-fndHcmB0wdM6wKOeb+mh
Content-Type: text/plain; charset=windows-1251; format=flowed
Content-Transfer-Encoding: quoted-printable

Greetings, group!

I'm trying to understand a low-level issue. Am evaluating a new client=20
library for Postgres; it's not particularly popular / mainstream, and=20
as I've understood so far, sports an independent implementation of PG=20
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 =3D=20
'UTF8';SET client_min_messages TO WARNING;
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

Of course, I /am/ passing a value for parameter $1; and I can trace=20
that the client lib sends it out on the wire as expected. (Attaching=20
packet captures.)

Heck, even the PG server itself says, DETAIL: parameters: $1 =3D=20
'test-param-value' =97 so it sees the parameter! But then, immediately=20
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=20
latest major versions of PostgreSQL server (12 throughout to 17). No=20
difference across versions spotted; the result is consistently error=20
42P02.

Is the client library doing something wrong? How can the server claim=20
there's no parameter $1 immediately after logging its value it has=20
received?

I did minify a 100-line SSCCE that reproduces the issue and can be=20
shared.

Any advice, or pointers on what to check next besides delving into PG=20
source, I'd greatly appreciate. Thanks in advance.

Max


--=-fndHcmB0wdM6wKOeb+mh
Content-Type: text/html; charset=windows-1251
Content-Transfer-Encoding: quoted-printable

<div id=3D"geary-body" dir=3D"auto">Greetings, group!<div><br></div><div>I'=
m trying to understand a low-level issue. Am evaluating a new client librar=
y for Postgres; it's not particularly popular / mainstream, and as I've und=
erstood so far, sports an independent implementation of PG binary protocol.=
</div><div><br></div><div>The issue I'm hitting with it is exemplified by s=
erver logs like this:</div><div><br></div><div><font face=3D"monospace">202=
4-11-16 10:28:19.927 UTC [46] LOG:  statement: SET client_encoding =3D 'UTF=
8';SET client_min_messages TO WARNING;</font></div><div><font face=3D"monos=
pace">2024-11-16 10:28:19.928 UTC [46] LOG:  execute &lt;unnamed&gt;: CREAT=
E VIEW public.foobar (alg, hash) AS VALUES ('md5', $1);</font></div><div><f=
ont face=3D"monospace">2024-11-16 10:28:19.928 UTC [46] DETAIL:  parameters=
: $1 =3D 'test-param-value'</font></div><div><font face=3D"monospace">2024-=
11-16 10:28:19.928 UTC [46] ERROR:  there is no parameter $1 at character 5=
7</font></div><div><br></div><div>Of course, I <i>am</i> passing a value fo=
r parameter <font face=3D"monospace">$1</font>; and I can trace that the cl=
ient lib sends it out on the wire as expected. (Attaching packet captures.)=
</div><div><br></div><div>Heck, even the PG server itself says,&nbsp;<span =
style=3D"font-family: monospace;">DETAIL: parameters: $1 =3D 'test-param-va=
lue'</span>&nbsp;=97 so it sees the parameter! But then, immediately unsees=
 it.</div><div><br></div><div>Am I being hit by a PG bug? Is this a known i=
ssue?</div><div><br></div><div>I'd retested with master version of that cli=
ent library, and against 6 latest major versions of PostgreSQL server (12 t=
hroughout to 17). No difference across versions spotted; the result is cons=
istently error 42P02.</div><div><br></div><div>Is the client library doing =
something wrong? How can the server claim there's no parameter $1 immediate=
ly after logging its value it has received?</div><div><br></div><div>I did =
minify a 100-line SSCCE that reproduces the issue and can be shared.</div><=
div><br></div><div>Any advice, or pointers on what to check next besides de=
lving into PG source, I'd greatly appreciate. Thanks in advance.</div><div>=
<br></div><div>Max</div><div><br></div></div>
--=-fndHcmB0wdM6wKOeb+mh--

--=-YUNuVVNIzo64+NnZQaQr
Content-Type: multipart/mixed; boundary="=-pUFfiwPgwDtQSojGKx+1"

--=-pUFfiwPgwDtQSojGKx+1
Content-Type: application/vnd.tcpdump.pcap
Content-Disposition: attachment; filename=query42P02-with-prepstatement-on.pcap
Content-Transfer-Encoding: base64

1MOyoQIABAAAAAAAAAAAAAAABAABAAAAEnM4Z7fTCQBKAAAASgAAAAAAAAAAAAAAAAAAAAgARQAA
PFMhQABABumYfwAAAX8AAAGn2BU4nn3J0wAAAACgAv/X/jAAAAIE/9cEAggKgPex/QAAAAABAwMH
EnM4Z8TTCQBKAAAASgAAAAAAAAAAAAAAAAAAAAgARQAAPAAAQABABjy6fwAAAX8AAAEVOKfYLaIr
A559ydSgEv/L/jAAAAIE/9cEAggKgPex/YD3sf0BAwMHEnM4Z9nTCQBCAAAAQgAAAAAAAAAAAAAA
AAAAAAgARQAANFMiQABABumffwAAAX8AAAGn2BU4nn3J1C2iKwSAEAIA/igAAAEBCAqA97H+gPex
/RJzOGcK1QkASgAAAEoAAAAAAAAAAAAAAAAAAAAIAEUAADxTI0AAQAbpln8AAAF/AAABp9gVOJ59
ydQtoisEgBgCAP4wAAABAQgKgPex/oD3sf0AAAAIBNIWLxJzOGcS1QkAQgAAAEIAAAAAAAAAAAAA
AAAAAAAIAEUAADTpnkAAQAZTI38AAAF/AAABFTin2C2iKwSefcncgBACAP4oAAABAQgKgPex/oD3
sf4Sczhn09cJAEMAAABDAAAAAAAAAAAAAAAAAAAACABFAAA16Z9AAEAGUyF/AAABfwAAARU4p9gt
oisEnn3J3IAYAgD+KQAAAQEICoD3sf6A97H+ThJzOGfi1wkAQgAAAEIAAAAAAAAAAAAAAAAAAAAI
AEUAADRTJEAAQAbpnX8AAAF/AAABp9gVOJ59ydwtoisFgBACAP4oAAABAQgKgPex/4D3sf4Sczhn
8dcJAGwAAABsAAAAAAAAAAAAAAAAAAAACABFAABeUyVAAEAG6XJ/AAABfwAAAafYFTiefcncLaIr
BYAYAgD+UgAAAQEICoD3sf+A97H+AAAAKgADAAB1c2VyAHBvc3RncmVzAGRhdGFiYXNlAGN0cF9s
b2NhbAAAEnM4Z4bcCQDcAQAA3AEAAAAAAAAAAAAAAAAAAAgARQABzumgQABABlGHfwAAAX8AAAEV
OKfYLaIrBZ59ygaAGAIA/8IAAAEBCAqA97IAgPex/1IAAAAIAAAAAFMAAAAWYXBwbGljYXRpb25f
bmFtZQAAUwAAABljbGllbnRfZW5jb2RpbmcAVVRGOABTAAAAF0RhdGVTdHlsZQBJU08sIE1EWQBT
AAAAJmRlZmF1bHRfdHJhbnNhY3Rpb25fcmVhZF9vbmx5AG9mZgBTAAAAF2luX2hvdF9zdGFuZGJ5
AG9mZgBTAAAAGWludGVnZXJfZGF0ZXRpbWVzAG9uAFMAAAAbSW50ZXJ2YWxTdHlsZQBwb3N0Z3Jl
cwBTAAAAFGlzX3N1cGVydXNlcgBvbgBTAAAAGXNlcnZlcl9lbmNvZGluZwBVVEY4AFMAAAAyc2Vy
dmVyX3ZlcnNpb24AMTQuNSAoRGViaWFuIDE0LjUtMS5wZ2RnMTEwKzEpAFMAAAAjc2Vzc2lvbl9h
dXRob3JpemF0aW9uAHBvc3RncmVzAFMAAAAjc3RhbmRhcmRfY29uZm9ybWluZ19zdHJpbmdzAG9u
AFMAAAAVVGltZVpvbmUARXRjL1VUQwBLAAAADAAAACqkE2zCWgAAAAVJEnM4Z8bcCQCIAAAAiAAA
AAAAAAAAAAAAAAAAAAgARQAAelMmQABABulVfwAAAX8AAAGn2BU4nn3KBi2iLJ+AGAIA/m4AAAEB
CAqA97IAgPeyAFEAAABFU0VUIGNsaWVudF9lbmNvZGluZyA9ICdVVEY4JztTRVQgY2xpZW50X21p
bl9tZXNzYWdlcyBUTyBXQVJOSU5HOwASczhnPd0JAFoAAABaAAAAAAAAAAAAAAAAAAAACABFAABM
6aFAAEAGUwh/AAABfwAAARU4p9gtoiyfnn3KTIAYAgD+QAAAAQEICoD3sgCA97IAQwAAAAhTRVQA
QwAAAAhTRVQAWgAAAAVJEnM4Z4/dCQCRAAAAkQAAAAAAAAAAAAAAAAAAAAgARQAAg1MnQABABulL
fwAAAX8AAAGn2BU4nn3KTC2iLLeAGAIA/ncAAAEBCAqA97IAgPeyAFAAAABJMABDUkVBVEUgVklF
VyBwdWJsaWMuZm9vYmFyIChhbGcsIGhhc2gpIEFTIFZBTFVFUyAoJ21kNScsICQxKTsAAAEAAAAZ
UwAAAAQSczhn+N0JAE0AAABNAAAAAAAAAAAAAAAAAAAACABFAAA/6aJAAEAGUxR/AAABfwAAARU4
p9gtoiy3nn3Km4AYAgD+MwAAAQEICoD3sgCA97IAMQAAAARaAAAABUkSczhnMN4JAH4AAAB+AAAA
AAAAAAAAAAAAAAAACABFAABwUyhAAEAG6V1/AAABfwAAAafYFTiefcqbLaIswoAYAgD+ZAAAAQEI
CoD3sgCA97IAQgAAACUAMAAAAQABAAEAAAAQdGVzdC1wYXJhbS12YWx1ZQABAAFEAAAABlAARQAA
AAkAAAAAAFMAAAAEEnM4Z5DeCQCrAAAAqwAAAAAAAAAAAAAAAAAAAAgARQAAnemjQABABlK1fwAA
AX8AAAEVOKfYLaIswp59yteAGAIA/pEAAAEBCAqA97IAgPeyADIAAAAEbgAAAARFAAAAXlNFUlJP
UgBWRVJST1IAQzQyUDAyAE10aGVyZSBpcyBubyBwYXJhbWV0ZXIgJDEAUDU3AEZwYXJzZV9leHBy
LmMATDg0MgBSdHJhbnNmb3JtUGFyYW1SZWYAABJzOGep3gkASAAAAEgAAAAAAAAAAAAAAAAAAAAI
AEUAADrppEAAQAZTF38AAAF/AAABFTin2C2iLSuefcrXgBgCAP4uAAABAQgKgPeyAID3sgBaAAAA
BUkSczhntd4JAEIAAABCAAAAAAAAAAAAAAAAAAAACABFAAA0UylAAEAG6Zh/AAABfwAAAafYFTie
fcrXLaItMYAQAgD+KAAAAQEICoD3sgCA97IAEnM4Z8LeCQBHAAAARwAAAAAAAAAAAAAAAAAAAAgA
RQAAOVMqQABABumSfwAAAX8AAAGn2BU4nn3K1y2iLTGAGAIA/i0AAAEBCAqA97IAgPeyAFgAAAAE
EnM4Z8zeCQBCAAAAQgAAAAAAAAAAAAAAAAAAAAgARQAANFMrQABABumWfwAAAX8AAAGn2BU4nn3K
3C2iLTGAEQIA/igAAAEBCAqA97IAgPeyABJzOGfa4wkAQgAAAEIAAAAAAAAAAAAAAAAAAAAIAEUA
ADTppUAAQAZTHH8AAAF/AAABFTin2C2iLTGefcrdgBECAP4oAAABAQgKgPeyAoD3sgASczhn6uMJ
AEIAAABCAAAAAAAAAAAAAAAAAAAACABFAAA0UyxAAEAG6ZV/AAABfwAAAafYFTiefcrdLaItMoAQ
AgD+KAAAAQEICoD3sgKA97IC

--=-pUFfiwPgwDtQSojGKx+1
Content-Type: application/vnd.tcpdump.pcap
Content-Disposition: attachment;
	filename=query42P02-with-prepstatement-off.pcap
Content-Transfer-Encoding: base64

1MOyoQIABAAAAAAAAAAAAAAABAABAAAAw3M4Z1sYDgBKAAAASgAAAAAAAAAAAAAAAAAAAAgARQAA
PIA9QABABrx8fwAAAX8AAAHFTBU47c+onQAAAACgAv/X/jAAAAIE/9cEAggKgPpmfQAAAAABAwMH
w3M4Z3AYDgBKAAAASgAAAAAAAAAAAAAAAAAAAAgARQAAPAAAQABABjy6fwAAAX8AAAEVOMVMog3m
DO3PqJ6gEv/L/jAAAAIE/9cEAggKgPpmfYD6Zn0BAwMHw3M4Z4AYDgBCAAAAQgAAAAAAAAAAAAAA
AAAAAAgARQAANIA+QABABryDfwAAAX8AAAHFTBU47c+onqIN5g2AEAIA/igAAAEBCAqA+mZ9gPpm
fcNzOGcvGg4ASgAAAEoAAAAAAAAAAAAAAAAAAAAIAEUAADyAP0AAQAa8en8AAAF/AAABxUwVOO3P
qJ6iDeYNgBgCAP4wAAABAQgKgPpmfoD6Zn0AAAAIBNIWL8NzOGc5Gg4AQgAAAEIAAAAAAAAAAAAA
AAAAAAAIAEUAADSAUUAAQAa8cH8AAAF/AAABFTjFTKIN5g3tz6imgBACAP4oAAABAQgKgPpmfoD6
Zn7DczhnTh4OAEMAAABDAAAAAAAAAAAAAAAAAAAACABFAAA1gFJAAEAGvG5/AAABfwAAARU4xUyi
DeYN7c+opoAYAgD+KQAAAQEICoD6Zn+A+mZ+TsNzOGdfHg4AQgAAAEIAAAAAAAAAAAAAAAAAAAAI
AEUAADSAQEAAQAa8gX8AAAF/AAABxUwVOO3PqKaiDeYOgBACAP4oAAABAQgKgPpmf4D6Zn/Dczhn
dh4OAGwAAABsAAAAAAAAAAAAAAAAAAAACABFAABegEFAAEAGvFZ/AAABfwAAAcVMFTjtz6imog3m
DoAYAgD+UgAAAQEICoD6Zn+A+mZ/AAAAKgADAAB1c2VyAHBvc3RncmVzAGRhdGFiYXNlAGN0cF9s
b2NhbAAAw3M4Z5MmDgDcAQAA3AEAAAAAAAAAAAAAAAAAAAgARQABzoBTQABABrrUfwAAAX8AAAEV
OMVMog3mDu3PqNCAGAIA/8IAAAEBCAqA+maBgPpmf1IAAAAIAAAAAFMAAAAWYXBwbGljYXRpb25f
bmFtZQAAUwAAABljbGllbnRfZW5jb2RpbmcAVVRGOABTAAAAF0RhdGVTdHlsZQBJU08sIE1EWQBT
AAAAJmRlZmF1bHRfdHJhbnNhY3Rpb25fcmVhZF9vbmx5AG9mZgBTAAAAF2luX2hvdF9zdGFuZGJ5
AG9mZgBTAAAAGWludGVnZXJfZGF0ZXRpbWVzAG9uAFMAAAAbSW50ZXJ2YWxTdHlsZQBwb3N0Z3Jl
cwBTAAAAFGlzX3N1cGVydXNlcgBvbgBTAAAAGXNlcnZlcl9lbmNvZGluZwBVVEY4AFMAAAAyc2Vy
dmVyX3ZlcnNpb24AMTQuNSAoRGViaWFuIDE0LjUtMS5wZ2RnMTEwKzEpAFMAAAAjc2Vzc2lvbl9h
dXRob3JpemF0aW9uAHBvc3RncmVzAFMAAAAjc3RhbmRhcmRfY29uZm9ybWluZ19zdHJpbmdzAG9u
AFMAAAAVVGltZVpvbmUARXRjL1VUQwBLAAAADAAAAC6CnJt8WgAAAAVJw3M4ZxonDgCIAAAAiAAA
AAAAAAAAAAAAAAAAAAgARQAAeoBCQABABrw5fwAAAX8AAAHFTBU47c+o0KIN56iAGAIA/m4AAAEB
CAqA+maBgPpmgVEAAABFU0VUIGNsaWVudF9lbmNvZGluZyA9ICdVVEY4JztTRVQgY2xpZW50X21p
bl9tZXNzYWdlcyBUTyBXQVJOSU5HOwDDczhn7ScOAFoAAABaAAAAAAAAAAAAAAAAAAAACABFAABM
gFRAAEAGvFV/AAABfwAAARU4xUyiDeeo7c+pFoAYAgD+QAAAAQEICoD6ZoGA+maBQwAAAAhTRVQA
QwAAAAhTRVQAWgAAAAVJw3M4Z5YoDgDGAAAAxgAAAAAAAAAAAAAAAAAAAAgARQAAuIBDQABABrv6
fwAAAX8AAAHFTBU47c+pFqIN58CAGAIA/qwAAAEBCAqA+maBgPpmgVAAAABIAENSRUFURSBWSUVX
IHB1YmxpYy5mb29iYXIgKGFsZywgaGFzaCkgQVMgVkFMVUVTICgnbWQ1JywgJDEpOwAAAQAAABlC
AAAAJAAAAAEAAQABAAAAEHRlc3QtcGFyYW0tdmFsdWUAAQABRAAAAAZQAEUAAAAJAAAAAABTAAAA
BMNzOGe1KQ4AsAAAALAAAAAAAAAAAAAAAAAAAAAIAEUAAKKAVUAAQAa7/n8AAAF/AAABFTjFTKIN
58Dtz6magBgCAP6WAAABAQgKgPpmgoD6ZoExAAAABDIAAAAEbgAAAARFAAAAXlNFUlJPUgBWRVJS
T1IAQzQyUDAyAE10aGVyZSBpcyBubyBwYXJhbWV0ZXIgJDEAUDU3AEZwYXJzZV9leHByLmMATDg0
MgBSdHJhbnNmb3JtUGFyYW1SZWYAAMNzOGfeKQ4ASAAAAEgAAAAAAAAAAAAAAAAAAAAIAEUAADqA
VkAAQAa8ZX8AAAF/AAABFTjFTKIN6C7tz6magBgCAP4uAAABAQgKgPpmgoD6ZoFaAAAABUnDczhn
+CkOAEIAAABCAAAAAAAAAAAAAAAAAAAACABFAAA0gERAAEAGvH1/AAABfwAAAcVMFTjtz6maog3o
NIAQAgD+KAAAAQEICoD6ZoKA+maCw3M4ZwoqDgBHAAAARwAAAAAAAAAAAAAAAAAAAAgARQAAOYBF
QABABrx3fwAAAX8AAAHFTBU47c+pmqIN6DSAGAIA/i0AAAEBCAqA+maCgPpmglgAAAAEw3M4Zxcq
DgBCAAAAQgAAAAAAAAAAAAAAAAAAAAgARQAANIBGQABABrx7fwAAAX8AAAHFTBU47c+pn6IN6DSA
EQIA/igAAAEBCAqA+maCgPpmgsNzOGcEMg4AQgAAAEIAAAAAAAAAAAAAAAAAAAAIAEUAADSAV0AA
QAa8an8AAAF/AAABFTjFTKIN6DTtz6mggBECAP4oAAABAQgKgPpmhID6ZoLDczhnIDIOAEIAAABC
AAAAAAAAAAAAAAAAAAAACABFAAA0gEdAAEAGvHp/AAABfwAAAcVMFTjtz6mgog3oNYAQAgD+KAAA
AQEICoD6ZoSA+maE

--=-pUFfiwPgwDtQSojGKx+1--

--=-YUNuVVNIzo64+NnZQaQr--