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

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Sunday, January 19, 2025, PG Bug reporting form <noreply(at)postgresql(dot)org>
> wrote:
>> 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.

Indeed. It's pretty discourteous to expect us to go read a discussion
somewhere else and try to infer what you're on about.

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

It will do that, to some extent. For example (using PREPARE as a
convenient proxy for what would happen in extended query mode):

regression=# create table foo (f1 timestamptz);
CREATE TABLE
regression=# prepare foo as insert into foo select $1;
PREPARE
regression=# table pg_prepared_statements;
name | statement | prepare_time | parameter_types | result_types | from_sql | generic_plans | custom_plans
------+-------------------------------------------+-------------------------------+------------------------------+--------------+----------+---------------+--------------
foo | prepare foo as insert into foo select $1; | 2025-01-20 10:21:30.777687-05 | {"timestamp with time zone"} | | t | 0 | 0
(1 row)

From the bug title I suspect that the complaint is that this doesn't
happen every time. But it doesn't, and we're unlikely to try to make
it do so, not least because doing so would change the behavior of a
lot of cases that people are depending on.

The case that works as the submitter desires is where we still haven't
resolved a type for the parameter symbol (or untyped literal string)
at completion of parsing of the SELECT part. But sometimes we have
to choose a type sooner. For instance consider

regression=# prepare foo2 as insert into foo select $1 group by 1;
ERROR: column "f1" is of type timestamp with time zone but expression is of type text
HINT: You will need to rewrite or cast the expression.

What's happened here is that in order to ascribe semantics to the
GROUP BY clause, we have to know the type of the column being
grouped by. In the information vacuum we have here, we default to
deciding that $1 has type "text". (IIRC, we used to just throw an
error, but that made even fewer people happy.) When we're done
parsing the SELECT, we try to coerce the output columns to foo's
column types, and now we fail because text->timestamptz isn't
permitted as an implicit cast.

You could imagine trying to pass foo's column types down into
parsing of the SELECT, but that feels very action-at-a-distance-y
and would probably break as many cases that work today as fix
cases that don't. I doubt we'd ever accept a patch for that.

> Searching the mailing lists for existing discussions may yield fruit too
> though I don;t know for certain.

Yeah, we've been around on this (many times) before.

regards, tom lane