Re: Types pollution with unknown oids and server-side parameters binding

On Tue, May 3, 2022 at 3:35 PM Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
wrote:

> On Tue, 3 May 2022 at 21:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
>

As Tom mentioned in reply to me - that WHERE is evaluated before SET isn't
arbitrary - nor is "first one wins".

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

Correct. Parsing of a plan is deterministic. Though I am curious to what
extent this example might change if there were some layers of subqueries
involved where the common parameter was used.

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

That horse has already left the barn. While the current behavior is at
least in part organically grown we tend to not introduce errors where none
previously existed and where doing so would require breaking a
long-established rule - in this case "first planned instance wins". I do
think that trying to clarify "first" better, since it's not the
in-your-face textual order that is being used.

The error popped up because (I presume) the two SET column references are
tied in terms of "coming first" - their effective order doesn't matter so
the rule could not be applied.

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

Extrapolating this particular suggested change from one example seems
dangerous. Particularly since much of this is limited to the case of
treating a timestamp as both a timestamp and date at the same time. Making
any change from what is seemingly a rare corner-case doesn't seem to
provide a sufficient benefit/cost ratio.

I have to put this into the "unfortunate foot-gun" category at this point.
Users should be testing their code. I'm sure there are other concerns
given the layer of indirection, but a general rule to "always cast your
parameters" goes a long way if one chooses to avoid the API where explicit
parameter types can be supplied. Or at least "cast once, cast always" for
any given parameter.

David J.