BUG #17206: the function array_cat(anyarray, anyarray) does not exist

On Thu, Sep 30, 2021 at 2:51 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17206
Logged by: Edouard HIBON
Email address: edouard.hibon@free.fr
PostgreSQL version: 14.0
Operating system: windows 10
Description:

Trying to create the following aggregate function :
CREATE AGGREGATE array_accum (anyarray)
(
sfunc = array_cat,
stype = anyarray,
initcond = '{}'
);

I get the ERROR : the function array_cat(anyarray, anyarray) does not
exist,
SQL state: 42883
whereas this works in PostGreSQL 13.4

You may find this commit to be illuminating. Work was done in this area
for v14.

https://github.com/postgres/postgres/commit/97f73a978fc1aca59c6ad765548ce0096d95a923

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Sep 30, 2021 at 2:51 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

I get the ERROR : the function array_cat(anyarray, anyarray) does not
exist,

You may find this commit to be illuminating. Work was done in this area
for v14.
https://github.com/postgres/postgres/commit/97f73a978fc1aca59c6ad765548ce0096d95a923

This one might be more so:

https://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=9e38c2bb50

Also see the very first item in the v14 compatibility notes:

User-defined objects that reference certain built-in array functions
along with their argument types must be recreated (Tom Lane)

Specifically, array_append(), array_prepend(), array_cat(),
array_position(), array_positions(), array_remove(), array_replace(),
and width_bucket() used to take anyarray arguments but now take
anycompatiblearray. Therefore, user-defined objects like aggregates
and operators that reference those array function signatures must be
dropped before upgrading, and recreated once the upgrade completes.

regards, tom lane

Thank you Tom and David for your clear answer, I'm going to read more
carefully the v14 compatibility notes !
BR
Edouard

Le 01/10/2021 à 01:52, Tom Lane a écrit :

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Sep 30, 2021 at 2:51 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

I get the ERROR : the function array_cat(anyarray, anyarray) does not
exist,

You may find this commit to be illuminating. Work was done in this area
for v14.
https://github.com/postgres/postgres/commit/97f73a978fc1aca59c6ad765548ce0096d95a923

This one might be more so:

https://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=9e38c2bb50

Also see the very first item in the v14 compatibility notes:

User-defined objects that reference certain built-in array functions
along with their argument types must be recreated (Tom Lane)

Specifically, array_append(), array_prepend(), array_cat(),
array_position(), array_positions(), array_remove(), array_replace(),
and width_bucket() used to take anyarray arguments but now take
anycompatiblearray. Therefore, user-defined objects like aggregates
and operators that reference those array function signatures must be
dropped before upgrading, and recreated once the upgrade completes.

regards, tom lane

--
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus

Hi,

anycompatible didn't accept anyarray. Example from system catalogs:

SELECT most_common_freqs[

    array_position(most_common_vals::text::text[],'range_ops'::text)

] * 100 AS occurrence_percent  

FROM pg_stats 

WHERE schemaname='pg_catalog' AND tablename ='pg_opfamily' AND attname=
'opfname';

 occurrence_percent 

--------------------

 2.7397260069847107

must be cast to text::text[] without cast:

ERROR:  function array_position(anyarray, text) does not exist

Is bug that function with anycompatiblearray didn't accept anyarray data
type? 

regards, David Turoň

---------- Původní e-mail ----------
Od: Tom Lane <tgl@sss.pgh.pa.us>
Komu: David G. Johnston <david.g.johnston@gmail.com>
Kopie: edouard.hibon@free.fr, PostgreSQL mailing lists <pgsql-bugs@lists.
postgresql.org>
Datum: 11. 8. 2022 12:02:17
Předmět: Re: BUG #17206: the function array_cat(anyarray, anyarray) does not
exist
""David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Sep 30, 2021 at 2:51 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

I get the ERROR : the function array_cat(anyarray, anyarray) does not
exist,

You may find this commit to be illuminating. Work was done in this area
for v14.
https://github.com/postgres/postgres/commit/97f73a978fc1aca59c6ad765548ce

0096d95a923

This one might be more so:

https://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=9e38c2bb
50

Also see the very first item in the v14 compatibility notes:

User-defined objects that reference certain built-in array functions
along with their argument types must be recreated (Tom Lane)

Specifically, array_append(), array_prepend(), array_cat(),
array_position(), array_positions(), array_remove(), array_replace(),
and width_bucket() used to take anyarray arguments but now take
anycompatiblearray. Therefore, user-defined objects like aggregates
and operators that reference those array function signatures must be
dropped before upgrading, and recreated once the upgrade completes.

regards, tom lane

"

Hi

čt 11. 8. 2022 v 12:17 odesílatel David Turoň <Turon.David@seznam.cz>
napsal:

Hi,

anycompatible didn't accept anyarray. Example from system catalogs:

SELECT most_common_freqs[
array_position(most_common_vals::text::text[],'range_ops'::text)
] * 100 AS occurrence_percent
FROM pg_stats
WHERE schemaname='pg_catalog' AND tablename ='pg_opfamily' AND
attname='opfname';
occurrence_percent
--------------------
2.7397260069847107

must be cast to text::text[] without cast:
ERROR: function array_position(anyarray, text) does not exist

Is bug that function with anycompatiblearray didn't accept anyarray data
type?

This is interesting issue, because usual tables cannot to use polymorphic
types

(2022-08-11 13:01:20) postgres=# create table fpp(a anyarray);
ERROR: column "a" has pseudo-type anyarray

and yes - anyarray type is distinct from anycompatiblearray

so you can write

create or replace function fx(anyarray, anyarray, anycompatiblearray,
anycompatiblearray) that means

fx(T1, T1, T2, T2) -- like SELECT fx(ARRAY[current_date],
ARRAY[current_date + 1], ARRAY[1], ARRAY[1.1])

Regards

Pavel

p.s.

please, don't use top-posting in this mailing list

Pavel Stehule <pavel.stehule@gmail.com> writes:

čt 11. 8. 2022 v 12:17 odesílatel David Turoň <Turon.David@seznam.cz>
napsal:

Is bug that function with anycompatiblearray didn't accept anyarray data
type?

and yes - anyarray type is distinct from anycompatiblearray
so you can write
create or replace function fx(anyarray, anyarray, anycompatiblearray,
anycompatiblearray) that means

Yeah. We could introduce a cast from anyarray to anycompatiblearray,
but I fear it'd play hob with the ability to use both polymorphic
families in one function signature.

More to the point, though, the casts are required here in any case.
Pre-v14, you got a different error, but it was still an error.
Neither in principle nor in practice would it be safe to pass
pg_stats.most_common_vals to array_position() without any cast.
array_position expects the array's element type to match the type
of its second argument, and will probably crash if it doesn't.
So forcing most_common_vals to flat text and then to text[] is
essential to unify the incompatible types found in pg_stat.

regards, tom lane