Re: Operator is not unique

[Tom Lane]

Adrian Klaver  writes:
> The list of extensions that you sent earlier are fairly common. I would 
> not expect them to be contributing to the below otherwise there would 
> have been more reports of what you are seeing.

A quick "grep" shows that there is nothing named "day_inc" in core
Postgres nor any of the contrib extensions.  So this is something
homegrown.  It looks to me like somebody wanted a plus operator
that would add numerics and dates/timestamps/timestamptzs, and
figured they could be lazy and make one operator using "anyelement".
But this will capture numeric plus *anything*, so it was not a
good idea.  I'd recommend dropping the use of anyelement and just
making three non-polymorphic operators.

regards, tom lane

Re: Operator is not unique

[Adrian Klaver]

On 9/30/19 7:28 AM, PegoraroF10 wrote:

your select returns no records but if I use
WHERE p.proname ~ 'day_inc'
instead of
WHERE p.proname OPERATOR(pg_catalog.~) '^(pg_catalog.day_inc)$'


Yeah I was not paying attention to what it was really looking for, the 
function name.


The list of extensions that you sent earlier are fairly common. I would 
not expect them to be contributing to the below otherwise there would 
have been more reports of what you are seeing.


From the name of the functions and function arguments they look like 
something that is working with dates.


Does that bring anything to mind?

Do you have code you can grep for use of the functions?




Schema
Name
Result data type
Argument data types
Type


pg_catalog
day_inc
anyelement
adate anyelement, ndays numeric
func


pg_catalog
day_inc
anyelement
ndays numeric, adate anyelement
func




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Operator is not unique

[PegoraroF10]

your select returns no records but if I use 
WHERE p.proname ~ 'day_inc'
instead of 
WHERE p.proname OPERATOR(pg_catalog.~) '^(pg_catalog.day_inc)$'


Schema
Name
Result data type
Argument data types
Type


pg_catalog
day_inc
anyelement
adate anyelement, ndays numeric
func


pg_catalog
day_inc
anyelement
ndays numeric, adate anyelement
func






--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

[Adrian Klaver]

On 9/30/19 5:48 AM, PegoraroF10 wrote:

This select gives me:

ERROR: more than one function named "pg_catalog.day_inc"


In psql:

\df pg_catalog.day_inc

Or if you cannot get to psql then the query behind the above:

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END as "Type"
FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname OPERATOR(pg_catalog.~) '^(pg_catalog.day_inc)$'
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;






--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Operator is not unique

[PegoraroF10]

This select gives me:

ERROR: more than one function named "pg_catalog.day_inc"



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

[PegoraroF10]

SELECT * FROM pg_extension;
extname, extversion
plpgsql,1.0
pg_stat_statements,1.6
pg_trgm,1.4
tablefunc,1.0
unaccent,1.1
pageinspect,1.7




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

[Adrian Klaver]

On 9/27/19 2:44 PM, PegoraroF10 wrote:

oprname oprkind oprleft oprrightoprresult   
oprcode



+   b   170022832283pg_catalog.day_inc
+   b   228317002283pg_catalog.day_inc


Not sure if it will provide any useful information but try running:

select oprcode, pg_get_functiondef(oprcode)  from pg_operator where 
oprname = '+' and oprcode = 'pg_catalog.day_inc'::regproc;


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Operator is not unique

[Adrian Klaver]

On 9/27/19 2:44 PM, PegoraroF10 wrote:

oprname oprkind oprleft oprrightoprresult   
oprcode



+   b   170022832283pg_catalog.day_inc
+   b   228317002283pg_catalog.day_inc


In addition to Tom's comment about above, have you installed any 
extensions lately?



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Operator is not unique

[Tom Lane]

PegoraroF10  writes:
>   oprname oprkind oprleft oprright
> oprresult   oprcode
>   +   b   170022832283pg_catalog.day_inc
>   +   b   228317002283pg_catalog.day_inc

Hm, I wonder what that is.  It'd evidently match to numeric + anything.

regards, tom lane

Re: Operator is not unique

[PegoraroF10]

oprname oprkind oprleft oprright
oprresult   oprcode
+   l   0   20  20  int8up
+   l   0   21  21  int2up
+   l   0   23  23  int4up
+   l   0   700 700 float4up
+   l   0   701 701 float8up
+   l   0   17001700numeric_uplus
+   b   20  20  20  int8pl
+   b   20  21  20  int82pl
+   b   20  23  20  int84pl
+   b   20  869 869 int8pl_inet
+   b   21  20  20  int28pl
+   b   21  21  21  int2pl
+   b   21  23  23  int24pl
+   b   23  20  20  int48pl
+   b   23  21  23  int42pl
+   b   23  23  23  int4pl
+   b   23  10821082integer_pl_date
+   b   600 600 600 point_add
+   b   602 600 602 path_add_pt
+   b   602 602 602 path_add
+   b   603 600 603 box_add
+   b   700 700 700 float4pl
+   b   700 701 701 float48pl
+   b   701 700 701 float84pl
+   b   701 701 701 float8pl
+   b   702 703 702 timepl
+   b   718 600 718 circle_add_pt
+   b   790 790 790 cash_pl
+   b   869 20  869 inetpl
+   b   103410331034aclinsert
+   b   108223  1082date_pli
+   b   108210831114datetime_pl
+   b   108211861114date_pl_interval
+   b   108212661184datetimetz_pl
+   b   108310821114timedate_pl
+   b   108311861083time_pl_interval
+   b   111411861114timestamp_pl_interval
+   b   118411861184timestamptz_pl_interval
+   b   118610821114interval_pl_date
+   b   118610831083interval_pl_time
+   b   118611141114interval_pl_timestamp
+   b   118611841184interval_pl_timestamptz
+   b   118611861186interval_pl
+   b   118612661266interval_pl_timetz
+   b   126610821184timetzdate_pl
+   b   126611861266timetz_pl_interval
+   b   170017001700numeric_add
+   b   170022832283pg_catalog.day_inc
+   b   228317002283pg_catalog.day_inc
+   b   383138313831range_union




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

[Adrian Klaver]

On 9/25/19 1:14 PM, PegoraroF10 wrote:

format_type format_type castfunccastcontext castmethod
bigint  smallint714 a   f
bigint  integer 480 a   f
bigint  real652 i   f
bigint  double precision482 i   f
bigint  numeric 1781i   f
bigint  regclass1287i   f
bigint  regtype 1287i   f
bigint  regconfig   1287i   f
bigint  regdictionary   1287i   f
bigint  regrole 1287i   f
bigint  regnamespace1287i   f
bigint  bit 2075e   f
bigint  money   3812a   f
bigint  oid 1287i   f
bigint  regproc 1287i   f
bigint  regprocedure1287i   f
bigint  regoper 1287i   f
bigint  regoperator 1287i   f
numeric bigint  1779a   f
numeric smallint1783a   f
numeric integer 1744a   f
numeric real1745i   f
numeric double precision1746i   f
numeric money   3824a   f
numeric numeric 1703i   f



Hmm, nothing strange here AFAICT.

What does:

select oprname, oprkind, oprleft, oprright, oprresult, oprcode  from 
pg_operator where oprname = '+';


show?


Have you made any changes/additions to CASTs and/or OPERATORs recently?




Sent from the PostgreSQL - general mailing list archive 
 at 
Nabble.com.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Operator is not unique

[PegoraroF10]

Doing a inner join with pg_proc I´ll get this result. So, none of this casts
I´ve created. Column prorettype is different, this is the problem ?select
format_type(castsource, NULL), format_type(casttarget, NULL),castfunc,
castcontext, castmethod, pr.* from pg_cast inner join pg_proc pr on castfunc
= pr.oid where (castsource = 'numeric'::regtype or castsource =
'bigint'::regtype) and format_type(casttarget, NULL) in ('numeric','bigint')
order by castsource;
format_type format_type castfunccastcontext castmethod  
proname pronamespace
proownerprolang procost prorows provariadic protransformprokind 
prosecdef
proleakproofproisstrict proretset   provolatile proparallel 
pronargs
pronargdefaults prorettype  proargtypes proallargtypes  proargmodes
proargnames proargdefaults  protrftypes prosrc  probin  proconfig   
proacl
bigint  numeric 1781i   f   numeric 11  10  12  1   
0   0   -   f   false   false   truefalse   i
s   1   0   170020  NULLNULLNULLNULLNULL
int8_numericNULLNULLNULL
numeric bigint  1779a   f   int811  10  12  1   
0   0   -   f   false   false   truefalse   i   s   
1
0   20  1700NULLNULLNULLNULLNULLnumeric_int8
NULLNULLNULL
numeric numeric 1703i   f   numeric 11  10  12  1   
0   0   numeric_transform   f   false
false   truefalse   i   s   2   0   17001700 23 NULL
NULLNULLNULLNULLnumeric NULL
NULLNULL




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

[PegoraroF10]

Nope, seems I didn´t create anything.

SELECT * FROM pg_operator WHERE oid = '+(numeric, bigint)'::regoperator;
ERROR: operator does not exist: +(numeric, bigint)

Ok, I can cast, it works. But why works without casting for you and not for
me ?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

[Fabrízio de Royes Mello]

Em ter, 24 de set de 2019 às 10:52, PegoraroF10 
escreveu:
>
> I don´t know if I did.
>

I think you did.

> PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled
> by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
>

Take a look:

postgres=# SELECT version();
 version

--
 PostgreSQL 11.5 (Debian 11.5-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

postgres=# SELECT 1::NUMERIC + 1::BIGINT;
 ?column?
--
2
(1 row)

And looking at the catalog:

postgres=# SELECT pg_typeof(1::NUMERIC + 1::BIGINT);
 pg_typeof
---
 numeric
(1 row)

postgres=# SELECT * FROM pg_operator WHERE oid = '+(numeric,
numeric)'::regoperator;
 oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash |
oprleft | oprright | oprresult | oprcom | oprnegate |   oprcode   | oprrest
| oprjoin
-+--+--+-+-++-+--+---++---+-+-+-
 +   |   11 |   10 | b   | f   | f  |
 1700 | 1700 |  1700 |   1758 | 0 | numeric_add | -   |
-
(1 row)


Please, try it in your environment and let us know.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Operator is not unique

[Tom Lane]

PegoraroF10  writes:
> If I do ...
> select 1::NUMERIC + 1::BIGINT;
> I get ...
> [42725] ERROR: operator is not unique: numeric + bigint Hint: Could not
> choose a best candidate operator. You might need to add explicit type casts.

This doesn't happen for me.

> This error means I have more than one way to calculate that formula ? 
> Did I create that operator erroneously ?

A more likely theory is that you made some ill-advised changes to casting
rules.  If, say, the bigint to numeric and numeric to bigint casts are
both marked implicit, the parser won't have any way to decide whether it
should use "numeric + numeric" or "bigint + bigint" here.

regards, tom lane

Re: Operator is not unique

[PegoraroF10]

I don´t know if I did.

PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

[Fabrízio de Royes Mello]

Em ter, 24 de set de 2019 às 10:35, PegoraroF10 
escreveu:
>
> If I do ...
> select 1::NUMERIC + 1::BIGINT;
>
> I get ...
> [42725] ERROR: operator is not unique: numeric + bigint Hint: Could not
> choose a best candidate operator. You might need to add explicit type
casts.
>

Witch version are you using? I tried it against current master and
everything is ok:

fabrizio=# SELECT 1::NUMERIC + 1::BIGINT;
 ?column?
--
2
(1 row)


> This error means I have more than one way to calculate that formula ?
> Did I create that operator erroneously ?
>

Did you created an operator for it? Why?

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento