Re: Compatible defaults for LEAD/LAG - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: Compatible defaults for LEAD/LAG |
| Date | |
| Msg-id | CAFj8pRDtPwhTMz966hrd22aYFbWDOOwYbWroAdxT26qmexLPcA@mail.gmail.com Whole thread Raw |
| In response to | Re: Compatible defaults for LEAD/LAG (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Compatible defaults for LEAD/LAG
|
| List | pgsql-hackers |
po 1. 6. 2020 v 4:07 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Vik Fearing <vik@postgresfriends.org> writes:
> On 5/31/20 9:53 PM, Tom Lane wrote:
>> When the anycompatible patch went in, I thought for a little bit about
>> trying to use it with existing built-in functions, but didn't have the
>> time to investigate the issue in detail. I'm not in favor of hacking
>> things one-function-at-a-time here; we should look through the whole
>> library and see what we've got.
> array_replace, lead, and lag are the only functions we have that take
> more than one anyelement.
That's just the tip of the iceberg, though. If you consider all the
old-style polymorphic types, we have
proc
-----------------------------------------------
array_append(anyarray,anyelement)
array_cat(anyarray,anyarray)
array_eq(anyarray,anyarray)
array_ge(anyarray,anyarray)
array_gt(anyarray,anyarray)
array_larger(anyarray,anyarray)
array_le(anyarray,anyarray)
array_lt(anyarray,anyarray)
array_ne(anyarray,anyarray)
array_position(anyarray,anyelement)
array_position(anyarray,anyelement,integer)
array_positions(anyarray,anyelement)
array_prepend(anyelement,anyarray)
array_remove(anyarray,anyelement)
array_replace(anyarray,anyelement,anyelement)
array_smaller(anyarray,anyarray)
arraycontained(anyarray,anyarray)
arraycontains(anyarray,anyarray)
arrayoverlap(anyarray,anyarray)
btarraycmp(anyarray,anyarray)
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.
For example array_positions(int[], numeric). In this case conversion int[] to numeric[] can be bad idea in some cases. Reversely in this case we want to convert numeric to int. When it is not possible without loss, then we can return false, or maybe raise exception. I designed anycompatible* for usage when the parameters has "symmetric weight", and cast to most common type should not to have performance issue. It is not this case. 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):
a) force_cast .. it can be good for array's functions - array_position(anyarray, anyelement_force_cast), array_position(anyarray, anyelement(force_cast)) .. this is often behave in Oracle
b) force_safe_cast .. special kind of casting - safer variant of tolerant Oracle's casting - 1.0::int is valid, 1.1::int is not valid in this type of casting
anycompatible* family can helps with some cases, but it is not silver bullet for all unfriendly, or not compatible situation (mainly for buildin functionality).
Regards
Pavel
elem_contained_by_range(anyelement,anyrange)
lag(anyelement,integer,anyelement)
lead(anyelement,integer,anyelement)
range_adjacent(anyrange,anyrange)
range_after(anyrange,anyrange)
range_before(anyrange,anyrange)
range_cmp(anyrange,anyrange)
range_contained_by(anyrange,anyrange)
range_contains(anyrange,anyrange)
range_contains_elem(anyrange,anyelement)
range_eq(anyrange,anyrange)
range_ge(anyrange,anyrange)
range_gist_same(anyrange,anyrange,internal)
range_gt(anyrange,anyrange)
range_intersect(anyrange,anyrange)
range_le(anyrange,anyrange)
range_lt(anyrange,anyrange)
range_merge(anyrange,anyrange)
range_minus(anyrange,anyrange)
range_ne(anyrange,anyrange)
range_overlaps(anyrange,anyrange)
range_overleft(anyrange,anyrange)
range_overright(anyrange,anyrange)
range_union(anyrange,anyrange)
width_bucket(anyelement,anyarray)
(45 rows)
(I ignored anyenum here, since it has no mapping to the anycompatible
family.) Some of these are internal or can otherwise be discounted,
but surely there'd be a market for, say, "int8[] || int4". The big
question that raises is can we do it without creating impossible confusion
with text-style concatenation.
> Are you sure we don't want to give
> at least the anycompatible type a nice public workout with this?
Yes, I'm quite sure. If the idea crashes and burns for some reason,
we'll be glad we didn't buy into it full-speed-ahead right away.
regards, tom lane
pgsql-hackers by date: