Re: BUG #18780: Bindings types are lost for complex queries

Sorry, I thought that it would be easier to read the thread on the pg-jdbc github than copypasting it in the report.

Well, I run a query through jdbc:

Table:

create table test
(
id bigint primary key,
ts timestamp with time zone,
amount integer
);

Query:

private static final String MERGE_QUERY = """
merge into test as dst
using (select ? as id,
? as ts,
? as amount) src
on dst.id=?
when matched then
update
set ts=src.ts,
amount=src.amount
when not matched then
insert ("id", "ts", "amount")
values (src.id, src.ts, src.amount)
""";

@Test
public void test()
{
try (Connection conn = dataSource.getConnection())
{
log.info("Driver version {}", conn.getMetaData().getDriverVersion());
PreparedStatement ps = conn.prepareStatement(MERGE_QUERY);

Integer id = 2;
Timestamp ts = Timestamp.valueOf(LocalDateTime.now());
Integer amount = 123;

ps.setObject(1, id);
ps.setObject(2, ts);
ps.setObject(3, amount);
ps.setObject(4, id);

ps.executeUpdate();
} catch (Exception e)
{
log.error(e.getMessage(), e);
}
}

And I am getting the error:

2025-01-17T17:58:32.798+02:00 ERROR 4696 --- [ main] st.notexi.springtest.DbTest : ERROR: column "ts" is of type timestamp with time zone but expression is of type text
Hint: You will need to rewrite or cast the expression.
Position: 169

I do not expect server to DECIDE what type. There IS the same type information as, for example, in the "insert into test(id, ts, amount) values(?, ?, ?)". So, I expect server just takes it and executes query.
I filed a bug to the jdbc driver and got a response that it is server's problem and that I have to report it here.
So, I'd like to understand, where the problem actually is. As for me it looks like a bug.

Thank you

Kind regards,
Viktor

On 20 Jan 2025, at 17:18, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

On Sunday, January 19, 2025, PG Bug reporting form <noreply(at)postgresql(dot)org<mailto:noreply(at)postgresql(dot)org>> wrote:
The following bug has been logged on the website:

Bug reference: 18780
Logged by: Viktr
Email address: vik(at)notexi(dot)st<mailto:vik(at)notexi(dot)st>
PostgreSQL version: 17.2
Operating system: Linux d2c635331de7 6.10.14-linuxkit #1 SMP PREEMPT
Description:

Hi there,

Actual discussion is here: https://github.com/pgjdbc/pgjdbc/issues/3482
In brief: I do understand that "select 1 as one, 2 as two, 3 as three" might
have lack of type info. But I suppose, in case these values are used in the
assignment, the type could be taken from the corresponding column type. Like
for "insert into sometable(one, two, three) select 1 as one, 2 as two, 3 as
three" it is obvious that types should match.

A bug report should be self-contained. Only pointing to a 40 message long thread isn’t helping get the bug (well, feature) fixed.

The crux of that thread is your driver is sending along a text data typed value because it cannot decide whether timestamp or timestamptz is needed. Implicitly casting text to something else isn’t going to happen.

This falls into a feature request, one that comes up from time-to-time, and doesn’t ever seem to meet anyone’s benefit/cost threshold for working on; or at least get pushed over the edge.

The underlying feature, I think, is you want the parse to be able to say “let the server decide the type” and the server reply with type info for unspecified parameters. Or maybe accept the pseudo-type “unknown” at the API level. In any case a thread to discuss a patch for such a change and how JDBC would leverage it to solve this problem would be the next step. Searching the mailing lists for existing discussions may yield fruit too though I don;t know for certain.

David J.