BUG #17631: Prepare + Merge fails to identify parameter types

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: arthur(dot)mcgibbon(at)gmail(dot)com
Subject: BUG #17631: Prepare + Merge fails to identify parameter types
Date: 2022-10-10 17:14:34
Message-ID: 17631-7f892d95b3264da3@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17631
Logged by: Arthur McGibbon
Email address: arthur(dot)mcgibbon(at)gmail(dot)com
PostgreSQL version: Unsupported/Unknown
Operating system: Docker + Windows
Description:

PostgreSQL version: 15rc2.
The link on the 15c2 release page takes me here but I can't select 15rc2 as
the version.

I use the java JDBC driver to access Postgresql. To work out the parameters
of a prepared query it prepares the query without supplying the params...

e.g.
create table foo (id int, bar bit);

PREPARE foo_insert as
insert into foo
select $1, $2;

This works, and so do statements with "update" and
"insert-on-conflict-do-update" commands, but when I try it with the new
MERGE statement I get an error...

e.g.
create table foo (id int, bar bit);

PREPARE foo_merge as
merge into foo as target
using(values($1, $2))
as source (id, bar)
on target.id = source.id
when matched then
update set bar = source.bar
when not matched then
insert(id, bar)
values ($1, $2);

gives error...
ERROR: operator does not exist: integer = text
LINE 5: on target.id = source.id
^
HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
SQL state: 42883
Character: 121

I can get round this by casting and changing...
using(values($1, $2))
to...
using(values(cast($1 as int), cast($2 as bit)))

Is this a bug or is it currently too hard for Postgresql to work out the
parameter types? It does have all the information needed in just the first
2 lines.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2022-10-10 20:53:28 Re: BUG #17631: Prepare + Merge fails to identify parameter types
Previous Message Simon Riggs 2022-10-10 12:24:34 Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records