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 1tClBv-00CR4R-SF
	for pgsql-general@arkaria.postgresql.org; Sun, 17 Nov 2024 19:44: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 <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1tClBs-001P6a-QS
	for pgsql-general@arkaria.postgresql.org; Sun, 17 Nov 2024 19:44:13 +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 1tClBs-001P6O-Fe
	for pgsql-general@lists.postgresql.org; Sun, 17 Nov 2024 19:44:13 +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 1tClBo-002P5K-QR
	for pgsql-general@lists.postgresql.org; Sun, 17 Nov 2024 19:44:10 +0000
Message-ID: <67041789-6c66-4fde-acdf-0db3f6842b35@cloud.gatewaynet.com>
Date: Sun, 17 Nov 2024 21:44:01 +0200
MIME-Version: 1.0
Subject: Re: Getting error 42P02, despite query parameter being sent
To: Adrian Klaver <adrian.klaver@aklaver.com>,
 pgsql-general@lists.postgresql.org
References: <NCI1NS.TUGY51AF5VWQ3@gmail.com>
 <40d8beef-ff67-4c6c-828c-2941ca30fdef@cloud.gatewaynet.com>
 <574887e1-21a3-4847-9933-c05ac56edde2@aklaver.com>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <574887e1-21a3-4847-9933-c05ac56edde2@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
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/67041789-6c66-4fde-acdf-0db3f6842b35%40cloud.gatewaynet.com>
Precedence: bulk


Στις 16/11/24 18:09, ο/η Adrian Klaver έγραψε:
> On 11/16/24 03:15, Achilleas Mantzios wrote:
>>
>> Στις 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.
>
> It would seem so. Using psycopg:
>
> import psycopg
> from psycopg import sql
>
> con = 
> psycopg.connect("postgresql://postgres:postgres@127.0.0.1:5432/test")
> cur = con.cursor()
> cur.execute("CREATE VIEW public.foobar (alg, hash) AS VALUES ('md5', 
> %s)", ['test'])
>
> IndeterminateDatatype: could not determine data type of parameter $1
>
> cur.execute(sql.SQL("CREATE VIEW public.foobar (alg, hash) AS VALUES 
> ('md5', {})").format(sql.Literal('test')))
>
> con.commit()
>
> cur.execute("select * from foobar")
> cur.fetchone()
>
> ('md5', 'test')

I dont know python but this does not look like a solid prepared statement.

https://www.psycopg.org/psycopg3/docs/advanced/prepare.html

Does not seem to have used the prepared statement circuitry.

>
>> |
>>
>>> 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
>>>
>