Re: Compatible defaults for LEAD/LAG

po 1. 6. 2020 v 4:07 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Vik Fearing <vik(at)postgresfriends(dot)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
>
>
>