Re: Compatible defaults for LEAD/LAG

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> po 1. 6. 2020 v 4:07 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
>> That's just the tip of the iceberg, though. If you consider all the
>> old-style polymorphic types, we have [for example]
>> array_append(anyarray,anyelement)

> I am not sure, if using anycompatible for buildin's array functions can be
> good idea. Theoretically a users can do new performance errors due hidden
> cast of a longer array.

I don't buy that argument. If the query requires casting int4[] to
int8[], making the user do it by hand isn't going to improve performance
over having the parser insert the coercion automatically. Sure, there
will be some fraction of queries that could be rewritten to avoid the
need for any cast, but so what? Often the performance difference isn't
going to matter; and when it does, I don't see that this is any different
from hundreds of other cases in which there are more-efficient and
less-efficient ways to write a query. SQL is full of performance traps
and always will be. You're also assuming that when the user gets an
"operator does not exist" error from "int4[] || int8", that will magically
lead them to choosing an optimal substitute. I know of no reason to
believe that --- it's at least as likely that they'll conclude it just
can't be done, as in the LAG() example we started the thread with. So
I think most people would be much happier if the system just did something
reasonable, and they can optimize later if it's important.

> When I
> though about this cases, and about designing functions compatible with
> Oracle I though about another generic family (families) with different
> behave (specified by suffix or maybe with typemod or with some syntax):

So we're already deciding anycompatible can't get the job done? Maybe
it's a good thing we had this conversation now. It's not too late to
rip the feature out of v13 altogether, and try again later. But if
you think I'm going to commit yet another variant of polymorphism on
top of this one, you're mistaken.

regards, tom lane