Обсуждение: [NOVICE] array_agg cast issue

Please consider the environment before printing this email.

Peter Neave <Peter.Neave@jims.net> writes:
> We moved our development database from Windows (PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit to) to
Linux(PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit). When using pgAdmin 4
(v1.4)and DataGrip I get an occasional error message as I’m clicking on table names. 

> For example
>   ERROR: function array_agg(bigint) is not unique
>     Hint: Could not choose a best candidate function. You might need to add explicit type casts.

[ squint... ]  That should not be possible unless something's mucked up
the available set of functions.  In a standard 9.6 database, there are
two versions of array_agg:

regression=# \df array_agg
                           List of functions
   Schema   |   Name    | Result data type | Argument data types | Type
------------+-----------+------------------+---------------------+------
 pg_catalog | array_agg | anyarray         | anyarray            | agg
 pg_catalog | array_agg | anyarray         | anynonarray         | agg
(2 rows)

but only one of those could match any given call with a defined argument
type --- certainly a bigint argument couldn't match the first one.

9.0 was different:

regression=# \df array_agg
                           List of functions
   Schema   |   Name    | Result data type | Argument data types | Type
------------+-----------+------------------+---------------------+------
 pg_catalog | array_agg | anyarray         | anyelement          | agg
(1 row)

I'm suspicious that your DB contains some hacked-up definition for
array_agg() that worked with 9.0 but not so well for 9.6.  I don't
know what DataGrip is, maybe it needs an update?

            regards, tom lane

Please consider the environment before printing this email.

Peter Neave <Peter.Neave@jims.net> writes:
> For some reason when I restored a version of array_agg was brought with it.
> ...
> I simply ran
> DROP AGGREGATE public.array_agg(anyelement);
> and my problem has been fixed.

Ah, cool.  You might want to check and see if any other unwanted copies
of system functions are there too ...

            regards, tom lane

I would like to refresh the topic.

I have the same issue on PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

I took a query from  wiki
<https://wiki.postgresql.org/wiki/Index_Maintenance#Duplicate_indexes>  :

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,      (array_agg(idx))[1] AS idx1,
(array_agg(idx))[2]AS idx2,      (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
 
FROM (   SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'||
indclass::text ||E'\n'|| indkey::text ||E'\n'||
COALESCE(indexprs::text,'')||E'\n'
|| COALESCE(indpred::text,'')) AS KEY   FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

*and the result:*
ERROR:  function array_agg(regclass) is not unique
LINE 2:        (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS i...               ^
HINT:  Could not choose a best candidate function. You might need to add
explicit type casts.
Stan SQL: 42725
Znak: 76


What helps it's adding a schema before function:
public.array_agg

But it's probably not a desired behavior?








--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

pinker <pinker@onet.eu> writes:
> I took a query from  wiki
> <https://wiki.postgresql.org/wiki/Index_Maintenance#Duplicate_indexes>  :

> SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
>        (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
>        (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
> FROM (
>     SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'||
> indclass::text ||E'\n'|| indkey::text ||E'\n'||
>                                          COALESCE(indexprs::text,'')||E'\n'
> || COALESCE(indpred::text,'')) AS KEY
>     FROM pg_index) sub
> GROUP BY KEY HAVING COUNT(*)>1
> ORDER BY SUM(pg_relation_size(idx)) DESC;

> *and the result:*
> ERROR:  function array_agg(regclass) is not unique
> LINE 2:        (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS i...
>                 ^
> HINT:  Could not choose a best candidate function. You might need to add
> explicit type casts.

That example works fine for me.  Maybe you have an extra user-defined
function named "array_agg"?  Doing "\df array_agg" in psql might be
informative.
        regards, tom lane


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

It's a brand new, clean installation. yes, I've got 2 of them but I'm sure
it's not user-defined:

get=# \df+ array_agg                                                                                               
List of functions  Schema   |   Name    | Result data type | Argument data types | Type |
Volatility | Parallel |  Owner   | Security | Access privileges | Language |  
Source code   |                Description                

------------+-----------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-----------------+-------------------------------------------pg_catalog
|array_agg | anyarray         | anyarray            | agg  |
 
immutable  | safe     | postgres | invoker  |                   | internal |
aggregate_dummy | concatenate aggregate input into an arraypg_catalog | array_agg | anyarray         | anynonarray
  | agg  |
 
immutable  | safe     | postgres | invoker  |                   | internal |
aggregate_dummy | concatenate aggregate input into an array
(2 rows)




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

pinker <pinker@onet.eu> writes:
> It's a brand new, clean installation. yes, I've got 2 of them but I'm sure
> it's not user-defined:

Hm, yeah, those are the two I'd expect to see.  But how could "regclass",
which is surely a scalar type, be matching anyarray?  Have you installed
any user-defined casts?
        regards, tom lane


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

no it's really a fresh installation from edb package:
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#linux
I have only added two extensions: pg_buffercache and tablefunc and that's
all.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

pinker <pinker@onet.eu> writes:
> no it's really a fresh installation from edb package:
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#linux
> I have only added two extensions: pg_buffercache and tablefunc and that's
> all.

It still works for me in community sources with those extensions.
So this must be a consequence of something EDB has changed.  You'd need
to contact them for support.
        regards, tom lane


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Hmm, do you get any results from this query?
I'm doing some tests and probably the problem occurs if you have at least
one duplicated index in your database, i.e. when subquery returns anything.




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice