From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: "vik@notexi.st" <vik@notexi.st>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18780: Bindings types are lost for complex queries
In-reply-to: 
 <CAKFQuwauor0qYsPaB1CEu5atYu6H1y1ibcVA0VtgxkLKVyDXig@mail.gmail.com>
References: <18780-bae540b8dbfe4cdc@postgresql.org>
 <CAKFQuwauor0qYsPaB1CEu5atYu6H1y1ibcVA0VtgxkLKVyDXig@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Mon, 20 Jan 2025 08:18:08 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3001658.1737387466.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 20 Jan 2025 10:37:46 -0500
Message-ID: <3001659.1737387466@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3001659.1737387466%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, January 19, 2025, PG Bug reporting form <noreply@postgresql.o=
rg>
> 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 lo=
ng
> thread isn=E2=80=99t 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
> =E2=80=9Clet the server decide the type=E2=80=9D and the server reply wi=
th 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=3D# create table foo (f1 timestamptz);
CREATE TABLE
regression=3D# prepare foo as insert into foo select $1;
PREPARE
regression=3D# 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.77=
7687-05 | {"timestamp with time zone"} |              | t        |        =
     0 |            0
(1 row)

=46rom 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=3D# 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