Lists: | pgsql-bugs |
---|
From: | "ykhuang" <hyk(at)ruc(dot)edu(dot)cn> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | why provide cross type arithmetic operators |
Date: | 2008-01-22 03:14:25 |
Message-ID: | fn3n3o$1g5r$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
there are many cross type arithmetic operators, like int2 + int4, int8 +
int4, I think these can be deleted. Here are the reasons, after deleted,
int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8 +
int8, Is that ok? Thanks.
From: | "ykhuang" <hyk(at)ruc(dot)edu(dot)cn> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: why provide cross type arithmetic operators |
Date: | 2008-01-22 05:55:34 |
Message-ID: | fn40l4$2vl4$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
for example,
postgres=# select int2'1' + int8'1';
ERROR: operator is not unique: smallint + bigint
LINE 1: select int2'1' + int8'1';
^
HINT: Could not choose a best candidate operator. You may need to add
explicit
type casts.
there are int4 + int8 and int8 + int8, but no int2 + int8, so two operators
available, they are int4 + int8 and int8 + int8,
system can't choose a best candidate operator.
"ykhuang" <hyk(at)ruc(dot)edu(dot)cn> :fn3n3o$1g5r$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
> there are many cross type arithmetic operators, like int2 + int4, int8 +
> int4, I think these can be deleted. Here are the reasons, after deleted,
> int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8
> + int8, Is that ok? Thanks.
>
From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "ykhuang" <hyk(at)ruc(dot)edu(dot)cn> |
Cc: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: why provide cross type arithmetic operators |
Date: | 2008-01-22 12:05:58 |
Message-ID: | 87y7aijc9l.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"ykhuang" <hyk(at)ruc(dot)edu(dot)cn> writes:
> there are many cross type arithmetic operators, like int2 + int4, int8 +
> int4, I think these can be deleted. Here are the reasons, after deleted,
> int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8 +
> int8, Is that ok? Thanks.
Then the system wouldn't be able to use indexes as flexibly. For example if
you have an index on an int2 column and perform a query with a restriction
like "int2col = 1" the system wouldn't find a matching =(int2,int4) operator
and would instead have to do a sequential scan casting the int2 column to an
int4 when checking each row.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Gregory Stark <stark(at)enterprisedb(dot)com> |
Cc: | "ykhuang" <hyk(at)ruc(dot)edu(dot)cn>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: why provide cross type arithmetic operators |
Date: | 2008-01-22 17:42:58 |
Message-ID: | 4006.1201023778@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "ykhuang" <hyk(at)ruc(dot)edu(dot)cn> writes:
>> there are many cross type arithmetic operators, like int2 + int4, int8 +
>> int4, I think these can be deleted. Here are the reasons, after deleted,
>> int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8 +
>> int8, Is that ok? Thanks.
> Then the system wouldn't be able to use indexes as flexibly. For example if
> you have an index on an int2 column and perform a query with a restriction
> like "int2col = 1" the system wouldn't find a matching =(int2,int4) operator
> and would instead have to do a sequential scan casting the int2 column to an
> int4 when checking each row.
This is a reason not to remove "redundant" indexable operators, but the
argument doesn't have a lot of force for non-indexable ones.
I looked into this, and the reason we fail to resolve int2 + int8 is
that after the "prefer more exact matches" test (the first heuristic in
func_select_candidate) we are down to int8 + int8 and int4 + int8,
and none of the remaining heuristics can prefer one over the other.
On the other hand, we resolve int2 < int8 just fine because there's an
exact match.
So it seems that the problem with cross-type operators is not so much
having them as having incomplete sets of them. We could fix this case
either by adding int2 + int8 or by removing int4 + int8, and simplicity
would seem to argue for the latter.
A different approach would be to add a heuristic preferring
same-input-type operators over others. (We currently apply that idea
only when one of the inputs is "unknown" type, which is why
'1' + int8'1' works.) It's a bit scary to wonder what cases that might
break, though.
regards, tom lane
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | ykhuang <hyk(at)ruc(dot)edu(dot)cn> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: why provide cross type arithmetic operators |
Date: | 2008-03-25 02:41:10 |
Message-ID: | 200803250241.m2P2fAq00740@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Added to TODO:
* Add more cross-data-type operators
http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php
---------------------------------------------------------------------------
ykhuang wrote:
> there are many cross type arithmetic operators, like int2 + int4, int8 +
> int4, I think these can be deleted. Here are the reasons, after deleted,
> int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8 +
> int8, Is that ok? Thanks.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | ykhuang <hyk(at)ruc(dot)edu(dot)cn>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: why provide cross type arithmetic operators |
Date: | 2008-03-25 04:59:05 |
Message-ID: | 8210.1206421145@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Added to TODO:
> * Add more cross-data-type operators
> http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php
Uh ... that is exactly 180 degrees away from the point of the thread.
regards, tom lane
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | ykhuang <hyk(at)ruc(dot)edu(dot)cn>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: why provide cross type arithmetic operators |
Date: | 2008-03-25 13:04:55 |
Message-ID: | 200803251304.m2PD4t911896@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Added to TODO:
> > * Add more cross-data-type operators
> > http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php
>
> Uh ... that is exactly 180 degrees away from the point of the thread.
OK, I see now, updated:
* Simplify integer cross-data-type operators
Email text is:
http://archives.postgresql.org/pgsql-bugs/2008-01/msg00199.php
So it seems that the problem with cross-type operators is not so much
having them as having incomplete sets of them. We could fix this case
either by adding int2 + int8 or by removing int4 + int8, and simplicity
would seem to argue for the latter.
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +