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 <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1tCGmV-00FIyl-CI
	for pgsql-general@arkaria.postgresql.org; Sat, 16 Nov 2024 11:15:58 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1tCGmS-00DyMV-Fb
	for pgsql-general@arkaria.postgresql.org; Sat, 16 Nov 2024 11:15:57 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <a.mantzios@cloud.gatewaynet.com>)
	id 1tCGmS-00DyMN-47
	for pgsql-general@lists.postgresql.org; Sat, 16 Nov 2024 11:15:56 +0000
Received: from cloud.gatewaynet.com ([185.90.37.94])
	by makus.postgresql.org with esmtps  (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <a.mantzios@cloud.gatewaynet.com>)
	id 1tCGmP-002BnM-Db
	for pgsql-general@lists.postgresql.org; Sat, 16 Nov 2024 11:15:55 +0000
Content-Type: multipart/alternative;
 boundary="------------8vYJAP7cQCR82J4Vxb0HY1Jx"
Message-ID: <40d8beef-ff67-4c6c-828c-2941ca30fdef@cloud.gatewaynet.com>
Date: Sat, 16 Nov 2024 13:15:45 +0200
MIME-Version: 1.0
Subject: Re: Getting error 42P02, despite query parameter being sent
To: pgsql-general@lists.postgresql.org
References: <NCI1NS.TUGY51AF5VWQ3@gmail.com>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <NCI1NS.TUGY51AF5VWQ3@gmail.com>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/40d8beef-ff67-4c6c-828c-2941ca30fdef%40cloud.gatewaynet.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------8vYJAP7cQCR82J4Vxb0HY1Jx
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


Στις 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
>
--------------8vYJAP7cQCR82J4Vxb0HY1Jx
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">Στις 16/11/24 12:55, ο/η Max Ulidtko
      έγραψε:<br>
    </div>
    <blockquote type="cite" cite="mid:NCI1NS.TUGY51AF5VWQ3@gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div id="geary-body" dir="auto">Greetings, group!
        <div><br>
        </div>
        <div>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.</div>
        <div><br>
        </div>
        <div>The issue I'm hitting with it is exemplified by server logs
          like this:</div>
        <div><br>
        </div>
        <div><font face="monospace">2024-11-16 10:28:19.927 UTC [46]
            LOG: statement: SET client_encoding = 'UTF8';SET
            client_min_messages TO WARNING;</font></div>
        <div><font face="monospace">2024-11-16 10:28:19.928 UTC [46]
            LOG: execute &lt;unnamed&gt;: CREATE VIEW public.foobar
            (alg, hash) AS VALUES ('md5', $1);</font></div>
      </div>
    </blockquote>
    <p><font face="monospace">At least for SQL level prepared statements
        the statement has to be one of : <br>
      </font></p>
    <p><code class="command">SELECT</code>, <code class="command">INSERT</code>,
      <code class="command">UPDATE</code>, <code class="command">DELETE</code>,
      <code class="command">MERGE</code>, or <code class="command">VALUES</code></p>
    <p><code class="command">so CREATE is not valid, and I guess the
        extended protocol prepared statements aint no different in this
        regard.<br>
      </code></p>
    <blockquote type="cite" cite="mid:NCI1NS.TUGY51AF5VWQ3@gmail.com">
      <div id="geary-body" dir="auto">
        <div><font face="monospace">2024-11-16 10:28:19.928 UTC [46]
            DETAIL: parameters: $1 = 'test-param-value'</font></div>
        <div><font face="monospace">2024-11-16 10:28:19.928 UTC [46]
            ERROR: there is no parameter $1 at character 57</font></div>
        <div><br>
        </div>
        <div>Of course, I <i>am</i> passing a value for parameter <font
            face="monospace">$1</font>; and I can trace that the client
          lib sends it out on the wire as expected. (Attaching packet
          captures.)</div>
        <div><br>
        </div>
        <div>Heck, even the PG server itself says, <span
            style="font-family: monospace;">DETAIL: parameters: $1 =
            'test-param-value'</span> — 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 issue?</div>
        <div><br>
        </div>
        <div>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.</div>
        <div><br>
        </div>
        <div>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?</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
          delving into PG source, I'd greatly appreciate. Thanks in
          advance.</div>
        <div><br>
        </div>
        <div>Max</div>
        <div><br>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------8vYJAP7cQCR82J4Vxb0HY1Jx--