Feedback-ID: i76984098:Fastmail
Message-ID: <574887e1-21a3-4847-9933-c05ac56edde2@aklaver.com>
Date: Sat, 16 Nov 2024 08:09:29 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Getting error 42P02, despite query parameter being sent
To: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>,
 pgsql-general@lists.postgresql.org
References: <NCI1NS.TUGY51AF5VWQ3@gmail.com>
 <40d8beef-ff67-4c6c-828c-2941ca30fdef@cloud.gatewaynet.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <40d8beef-ff67-4c6c-828c-2941ca30fdef@cloud.gatewaynet.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/574887e1-21a3-4847-9933-c05ac56edde2%40aklaver.com>
Precedence: bulk

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

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com