pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17206: the function array_cat(anyarray, anyarray) does not exist
7+ messages / 6 participants
[nested] [flat]
* BUG #17206: the function array_cat(anyarray, anyarray) does not exist
@ 2021-09-30 20:54 PG Bug reporting form <noreply@postgresql.org>
2021-09-30 22:49 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2021-09-30 20:54 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: edouard.hibon@free.fr
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist
2021-09-30 20:54 BUG #17206: the function array_cat(anyarray, anyarray) does not exist PG Bug reporting form <noreply@postgresql.org>
@ 2021-09-30 22:49 ` David G. Johnston <david.g.johnston@gmail.com>
2021-09-30 23:52 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2021-09-30 22:49 UTC (permalink / raw)
To: edouard.hibon@free.fr, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist
2021-09-30 20:54 BUG #17206: the function array_cat(anyarray, anyarray) does not exist PG Bug reporting form <noreply@postgresql.org>
2021-09-30 22:49 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David G. Johnston <david.g.johnston@gmail.com>
@ 2021-09-30 23:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-10-01 19:36 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Edouard HIBON <edouard.hibon@free.fr>
2022-08-11 10:17 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David Turoň <Turon.David@seznam.cz>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2021-09-30 23:52 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: edouard.hibon@free.fr, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
"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&a=commitdiff&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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist
2021-09-30 20:54 BUG #17206: the function array_cat(anyarray, anyarray) does not exist PG Bug reporting form <noreply@postgresql.org>
2021-09-30 22:49 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David G. Johnston <david.g.johnston@gmail.com>
2021-09-30 23:52 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-10-01 19:36 ` Edouard HIBON <edouard.hibon@free.fr>
1 sibling, 0 replies; 7+ messages in thread
From: Edouard HIBON @ 2021-10-01 19:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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&a=commitdiff&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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist
2021-09-30 20:54 BUG #17206: the function array_cat(anyarray, anyarray) does not exist PG Bug reporting form <noreply@postgresql.org>
2021-09-30 22:49 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David G. Johnston <david.g.johnston@gmail.com>
2021-09-30 23:52 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-11 10:17 ` David Turoň <Turon.David@seznam.cz>
2022-08-11 11:08 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: David Turoň @ 2022-08-11 10:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: edouard.hibon@free.fr; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; David G. Johnston <david.g.johnston@gmail.com>
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&a=commitdiff&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
"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist
2021-09-30 20:54 BUG #17206: the function array_cat(anyarray, anyarray) does not exist PG Bug reporting form <noreply@postgresql.org>
2021-09-30 22:49 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David G. Johnston <david.g.johnston@gmail.com>
2021-09-30 23:52 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 10:17 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David Turoň <Turon.David@seznam.cz>
@ 2022-08-11 11:08 ` Pavel Stehule <pavel.stehule@gmail.com>
2022-08-11 13:53 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2022-08-11 11:08 UTC (permalink / raw)
To: David Turoň <Turon.David@seznam.cz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; edouard.hibon@free.fr, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; David G. Johnston <david.g.johnston@gmail.com>
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
> 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/97f73a978fc1aca59c6ad765548ce0096d95a923
>
> This one might be more so:
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&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
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist
2021-09-30 20:54 BUG #17206: the function array_cat(anyarray, anyarray) does not exist PG Bug reporting form <noreply@postgresql.org>
2021-09-30 22:49 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David G. Johnston <david.g.johnston@gmail.com>
2021-09-30 23:52 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 10:17 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist David Turoň <Turon.David@seznam.cz>
2022-08-11 11:08 ` Re: BUG #17206: the function array_cat(anyarray, anyarray) does not exist Pavel Stehule <pavel.stehule@gmail.com>
@ 2022-08-11 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2022-08-11 13:53 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: David Turoň <Turon.David@seznam.cz>; edouard.hibon@free.fr, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; David G. Johnston <david.g.johnston@gmail.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-08-11 13:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-30 20:54 BUG #17206: the function array_cat(anyarray, anyarray) does not exist PG Bug reporting form <noreply@postgresql.org>
2021-09-30 22:49 ` David G. Johnston <david.g.johnston@gmail.com>
2021-09-30 23:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-10-01 19:36 ` Edouard HIBON <edouard.hibon@free.fr>
2022-08-11 10:17 ` David Turoň <Turon.David@seznam.cz>
2022-08-11 11:08 ` Pavel Stehule <pavel.stehule@gmail.com>
2022-08-11 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox