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

On Tue, May 3, 2022 at 12:55 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> writes:
> > A problem shown in https://github.com/psycopg/psycopg/discussions/289
>
> > In the query:
> > UPDATE test289 SET num = $1, name = $2, ts = $3 WHERE ts::date =
> $3::date
>
> > passing a string with unknown oid as param $3 results in the column ts
> > receiving only the date part. Looks like the cast needed on the param
> > in the WHERE gets propagated to the other occurrences of the same
> > parameter.
>
> 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".
>
> > 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?
>
>
Based upon:

"When a parameter's data type is not specified or is declared as unknown,
the type is inferred from the context in which the parameter is first
referenced (if possible)."
(PREPARE Command docs)

and:

"""
postgres=# prepare prepupd as update table1 set ts = $1, tsd = $1 returning
ts, tsd, pg_typeof($1);
ERROR: inconsistent types deduced for parameter $1
LINE 1: ...epare prepupd as update table1 set ts = $1, tsd = $1 returni...
^
DETAIL: timestamp without time zone versus date
"""
(the above confirming the set clause does provide information for the
deduction, ts is timestamp, tsd is date)

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

The parameter would seem to be first referenced as a timestamp (at least in
parsing order). As the explicit cast from timestamp to date is valid there
is no inconsistency if we don't use the explicit cast for deduction and
simply allow the cast to do its thing.

I'm not sure I actually believe this to be an improvement, but I also
wasn't expecting that specific error message given the documentation, which
at least lets the timestamp deduction stand to get past the assignment of a
type to the parameter - letting some other type problem pop up during the
rest of the planning or, as in this case, letting the explicit cast
actually produce the desired result.

David J.