Re: Types pollution with iso-8859-1 oids and server-side parameters binding

On Tue, 3 May 2022 at 21:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I think it's operating exactly as designed.  The parameter can only
> have one type, and the cast is telling it to assume that that type
> is "date".

The same problem was actually reported without that cast. The OP was
using a query such as:

    UPDATE test289 SET num = $1, name = $2, ts = $3 WHERE ts::date = $3

$3 is used in two different contexts, a datetime and a date, and it
seems arbitrary that the date wins.

It seems that the date type is chosen arbitrarily, possibly depending
on the way the parsed query is traversed building the plan?

> > I see why it happens... I don't think it's the right behaviour though.
>
> What do you think ought to happen?  The same parameter somehow having
> different types in different places?

Looking at the above variation of the problem, maybe the right thing
to do would be to throw an error because the parameter can be
interpreted in different ways in different places? I seem to
understand, from David's example, that such an error is thrown, in
other contexts.

Another possibility is to fix the users. Many of them will see a
parametric query more like a C macro than like a C function, so
performing a literal replacement, because this is the behaviour they
get in psql typing:

    UPDATE ..., ts = '2022-03-03 11:00:00' WHERE ts::date =
'2022-03-03 11:00:00'::date