Re: Compatible defaults for LEAD/LAG - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Compatible defaults for LEAD/LAG
Date
Msg-id CAFj8pRBBz+SLMNrdPMmFTzfRPwh89KQ=j5QkWxoUMV6DvmJDMA@mail.gmail.com
Whole thread Raw
In response to Re: Compatible defaults for LEAD/LAG  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers


po 1. 6. 2020 v 17:36 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> po 1. 6. 2020 v 4:07 odesílatel Tom Lane <tgl@sss.pgh.pa.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.

anycompatible types are fully conssistent with Postgres buildin functions  supported by parser. It is main benefit and important benefit.
Without anycompatible types is pretty hard to write variadic functions with friendly behave like buildin functions has.
It can be perfect for LAG() example. It does almost same work what we did manually in parser.

Surely, it is not compatible with Oracle's polymorphism rules, because

a) Our and Postgres type system is different (sometimes very different).

b) Oracle's casting rules depends on argument positions and some specific exceptions - so it is not possible to map it on Postgres type system (or system of polymorphic types).

I wrote and I spent lot of time on this feature to be used - by core developers, by extension's developers. Like lot of other feature - it can carry  more comfort with some risk of performance issues.

On second hand if we use this feature for buildin functions, there is zero impact of current applications - there should not be any problem with compatibility or performance.

Maybe I am too old, and last year was too terrible so I have a problem to imagine a "intelligent" user now :)

Regards

Pavel

Although I can imagine other enhancing of polymorphic types, I don't propose any new, and I don't expect any enhancing in near feature. Is true, so there are not requests and I think so "anycompatible" and "any" are more than good enough for 99.99% developers.

I am little bit surprised so semi compatibility mode implemented in Orafce is good enough and full compatibility with Oracle a) isn't possible, b) isn't requested by visible group of users (or users who need it are satisfied by EDB).

 

                        regards, tom lane

pgsql-hackers by date:

Previous
From: Mark Dilger
Date:
Subject: Re: Small code cleanup
Next
From: Tom Lane
Date:
Subject: Re: Small code cleanup