Обсуждение: Prefered Types

Hello, i  have an old system where used implicit casting
float<->integer
numeric<->float
numeric<->integer

I want define implicit casts, but postgresql don`t know cast priority
now postgresql have PREFERRED flag, but only flag
I can`t define prefer level like
Integer=0
Numeric=1
Float=2
Maybe
text = 2 or 3
and other to define My prefer cast more detail than just flag
i understand what it more dificult tuning, but more flexible
now i can only create duplicate operators like
numeric+integer, integer+numeric, integer>numeric.... and many other
What can i do? Can i wait for prefer flag changed to prefer level?

-- 
С уважением,
Зотов Роман Владимирович
руководитель Отдела разработки
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: zotov@oe-it.ru

On Thu, Mar 10, 2011 at 8:12 AM, Zotov <zotov@oe-it.ru> wrote:
> Hello, i  have an old system where used implicit casting
> float<->integer
> numeric<->float
> numeric<->integer
>
> I want define implicit casts, but postgresql don`t know cast priority
> now postgresql have PREFERRED flag, but only flag
> I can`t define prefer level like
> Integer=0
> Numeric=1
> Float=2
> Maybe
> text = 2 or 3
> and other to define My prefer cast more detail than just flag
> i understand what it more dificult tuning, but more flexible
> now i can only create duplicate operators like
> numeric+integer, integer+numeric, integer>numeric.... and many other
> What can i do? Can i wait for prefer flag changed to prefer level?

Interestingly, I've also had the thought that it might make sense to
change typispreferred to an integer typpreference.  But I'm not sure
we actually have any consensus on that point, and it probably wouldn't
happen until 9.2 at the earliest, so you're probably best off finding
some other way to attack the problem.  It's not going to help with
text vs. integer/float/numeric anyway, I think, because they have
different typcategory values.  The whole typcategory system seems a
little wonky to me, actually...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

11.03.2011 17:01, Robert Haas пишет:
> Interestingly, I've also had the thought that it might make sense to
> change typispreferred to an integer typpreference.  But I'm not sure
> we actually have any consensus on that point, and it probably wouldn't
> happen until 9.2 at the earliest, so you're probably best off finding
> some other way to attack the problem.  It's not going to help with
> text vs. integer/float/numeric anyway, I think, because they have
> different typcategory values.  The whole typcategory system seems a
> little wonky to me, actually...
>
I try to do this feature myself, and now it work, as i can test (not so)
i attach patch for "master" to do this, maybe you see it?


--
С уважением,
Зотов Роман Владимирович
руководитель Отдела разработки
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: zotov@oe-it.ru

11.04.2011 5:19, Robert Haas пишет:
> You only sent this to me, I think; I assume you meant to copy the list.
>
> ...Robert
>
> On Mar 16, 2011, at 12:46 AM, Zotov<zotov@oe-it.ru>  wrote:
I send full patch, prev patch could be not so full, because i didn`t
knew how does it build. (too many generated files)
now i think it work. now it doesn`t change defaults and not change
syntax to use prefered.
I just change it by "update pg_type ..."
this feature can be modified now.
And i saw two new ways to select functions
1. If function only, one we can choose it without current hard way
2. If we can`t choose function we can think, what more likely that
function, what have first match operand

Thank you, if you get this patch (with some changes) into your product.

--
С уважением,
Зотов Роман Владимирович
руководитель Отдела разработки
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: zotov@oe-it.ru

On Mon, Apr 11, 2011 at 1:39 PM, Зотов Роман <zotov@oe-it.ru> wrote:
> 11.04.2011 5:19, Robert Haas пишет:
>>
>> You only sent this to me, I think; I assume you meant to copy the list.
>>
>> ...Robert
>>
>> On Mar 16, 2011, at 12:46 AM, Zotov<zotov@oe-it.ru>  wrote:
>
> I send full patch, prev patch could be not so full, because i didn`t knew
> how does it build. (too many generated files)
> now i think it work. now it doesn`t change defaults and not change syntax to
> use prefered.
> I just change it by "update pg_type ..."
> this feature can be modified now.
> And i saw two new ways to select functions
> 1. If function only, one we can choose it without current hard way
> 2. If we can`t choose function we can think, what more likely that function,
> what have first match operand
>
> Thank you, if you get this patch (with some changes) into your product.

You should probably add this patch here:

https://commitfest.postgresql.org/action/commitfest_view/open

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:
> On Mon, Apr 11, 2011 at 1:39 PM, ????? ????? <zotov@oe-it.ru> wrote:
> > 11.04.2011 5:19, Robert Haas ?????:
> >>
> >> You only sent this to me, I think; I assume you meant to copy the list.
> >>
> >> ...Robert
> >>
> >> On Mar 16, 2011, at 12:46 AM, Zotov<zotov@oe-it.ru> ?wrote:
> >
> > I send full patch, prev patch could be not so full, because i didn`t knew
> > how does it build. (too many generated files)
> > now i think it work. now it doesn`t change defaults and not change syntax to
> > use prefered.
> > I just change it by "update pg_type ..."
> > this feature can be modified now.
> > And i saw two new ways to select functions
> > 1. If function only, one we can choose it without current hard way
> > 2. If we can`t choose function we can think, what more likely that function,
> > what have first match operand
> >
> > Thank you, if you get this patch (with some changes) into your product.
> 
> You should probably add this patch here:
> 
> https://commitfest.postgresql.org/action/commitfest_view/open

I have added it.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

Excerpts from Зотов Роман's message of lun abr 11 17:39:32 -0300 2011:

> I send full patch, prev patch could be not so full, because i didn`t
> knew how does it build. (too many generated files)
> now i think it work. now it doesn`t change defaults and not change
> syntax to use prefered.
> I just change it by "update pg_type ..."
> this feature can be modified now.

I had a brief look at this patch, updating it to current HEAD past some
pgindent conflicts.  It seems sane, but as Zotov says, it doesn't do
anything yet: it only changes typispreferred from bool to int.  I'm
attaching it for the benefit of those interested.

The interesting discussion is what happens next.  To me, this is all
related to this previous discussion:
http://archives.postgresql.org/pgsql-hackers/2010-09/msg00232.php

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Зотов Роман's message of lun abr 11 17:39:32 -0300 2011:
> I had a brief look at this patch, updating it to current HEAD past some
> pgindent conflicts.  It seems sane, but as Zotov says, it doesn't do
> anything yet: it only changes typispreferred from bool to int.  I'm
> attaching it for the benefit of those interested.

> The interesting discussion is what happens next.  To me, this is all
> related to this previous discussion:
> http://archives.postgresql.org/pgsql-hackers/2010-09/msg00232.php

Yeah, there doesn't seem like much point unless we have a clear idea
what we're going to do with the change.  In particular, I'm quite
unimpressed with the (undocumented) changes in func_select_candidate;
there's no justification given for making it work like this, and frankly
I see no reason to assume that this behavior will be useful.

I would also note that this patch is seriously incomplete, in that it
fails to touch the behavior of CREATE TYPE, which presumably ought to
now take something other than a boolean (but with backwards compatibility
handled in some reasonable fashion).  Documentation changes seem lacking
as well.  But there's not good reason to spend time cleaning up those
issues unless we're convinced this is a good way to proceed forward,
and so far I'm not convinced.
        regards, tom lane

Excerpts from Tom Lane's message of mar may 03 15:41:27 -0300 2011:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Excerpts from Зотов Роман's message of lun abr 11 17:39:32 -0300 2011:
> > I had a brief look at this patch, updating it to current HEAD past some
> > pgindent conflicts.  It seems sane, but as Zotov says, it doesn't do
> > anything yet: it only changes typispreferred from bool to int.  I'm
> > attaching it for the benefit of those interested.
> 
> > The interesting discussion is what happens next.  To me, this is all
> > related to this previous discussion:
> > http://archives.postgresql.org/pgsql-hackers/2010-09/msg00232.php
> 
> Yeah, there doesn't seem like much point unless we have a clear idea
> what we're going to do with the change.  In particular, I'm quite
> unimpressed with the (undocumented) changes in func_select_candidate;
> there's no justification given for making it work like this, and frankly
> I see no reason to assume that this behavior will be useful.

That strikes me as pretty random, yeah.  Whatever we come up with should
consider the boolean issue of one type having a greater preference than
some other type, not the integer difference of preference values.
(Keeping the original boolean names in local variables there was clearly
a mistake; sorry about that.)

I don't have a clear idea about this at the moment though.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

I wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> The interesting discussion is what happens next.  To me, this is all
>> related to this previous discussion:
>> http://archives.postgresql.org/pgsql-hackers/2010-09/msg00232.php

> Yeah, there doesn't seem like much point unless we have a clear idea
> what we're going to do with the change.

BTW, it occurs to me to wonder whether, instead of making types be more
or less preferred, we should attack the issue from a different direction
and assign preferred-ness ratings to casts.  That seems to be more or
less the direction that Robert was considering in the above-linked
thread.  I'm not sure it's better than putting the ratings on types ---
in particular, neither viewpoint seems to offer a really clean answer
about what to do when trying to resolve a multiple-argument function
in which one possible resolution offers a more-preferred conversion for
one argument but a less-preferred conversion for another one.  But it's
an alternative we ought to think about before betting all the chips on
generalizing typispreferred.

Personally I've always felt that the typispreferred mechanism was a bit
of a wart; changing it from a bool to an int won't improve that, it'll
just make it a more complicated wart.  Casts have already got a
standards-blessed notion that some are more equal than others, so
maybe attaching preferredness ratings to them will be less of a wart.
Not sure about it though.
        regards, tom lane

03.05.2011 23:06, Tom Lane пишет:
> I wrote:
>> Alvaro Herrera<alvherre@commandprompt.com>  writes:
>>> The interesting discussion is what happens next.  To me, this is all
>>> related to this previous discussion:
>>> http://archives.postgresql.org/pgsql-hackers/2010-09/msg00232.php
>> Yeah, there doesn't seem like much point unless we have a clear idea
>> what we're going to do with the change.
> BTW, it occurs to me to wonder whether, instead of making types be more
> or less preferred, we should attack the issue from a different direction
> and assign preferred-ness ratings to casts.  That seems to be more or
> less the direction that Robert was considering in the above-linked
> thread.  I'm not sure it's better than putting the ratings on types ---
> in particular, neither viewpoint seems to offer a really clean answer
> about what to do when trying to resolve a multiple-argument function
> in which one possible resolution offers a more-preferred conversion for
> one argument but a less-preferred conversion for another one.  But it's
> an alternative we ought to think about before betting all the chips on
> generalizing typispreferred.
>
> Personally I've always felt that the typispreferred mechanism was a bit
> of a wart; changing it from a bool to an int won't improve that, it'll
> just make it a more complicated wart.  Casts have already got a
> standards-blessed notion that some are more equal than others, so
> maybe attaching preferredness ratings to them will be less of a wart.
> Not sure about it though.
>
>             regards, tom lane
>
Now I use this change i manual change preferring of some types (in 
system tables) and it give me possibility not add some functions clones.
I don`t know how (and i think i have no right) to change syntax to use 
this feature.
After many times thinking i find another way to resolve my problem:
if function only one we must use Assignment cast rules.
But it can help only for me... thats why i think we can change rules to 
calc prefer like
assignment rules have lesser priority, but available.
but here we can see problem like
F(smallint)
F(integer)
but call like F(float)
i wouldn`t like to fail it.

PS This patch needet, because in any case we must calc prefer more 
smartly, yes this patch is 1/10 of full solution, but it`s first step!!!


-- 
С уважением,
Зотов Роман Владимирович
руководитель Отдела разработки
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: zotov@oe-it.ru

Excerpts from Зотов Роман's message of mar may 03 16:31:31 -0300 2011:

> but here we can see problem like
> F(smallint)
> F(integer)
> but call like F(float)
> i wouldn`t like to fail it.

I think this particular example would be a mistake, because that cast
would be lossy, so you want to invoke it only when the user explicitely
selects it.  The other way around would be fine, I think, that is,
F(float)
F(float8)
and the call is F(int)


> PS This patch needet, because in any case we must calc prefer more 
> smartly, yes this patch is 1/10 of full solution, but it`s first step!!!

Well, if the other 9/10 were clear, there would be no discussion.  The
problem is that the missing bits have not been designed and thus we
don't know if this 1/10 will be useful to them.  We need to find a
complete design before committing to any initial portion which may turn
out to be bogus down the road.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

04.05.2011 0:00, Alvaro Herrera пишет:
> Excerpts from Зотов Роман's message of mar may 03 16:31:31 -0300 2011:
>
>> but here we can see problem like
>> F(smallint)
>> F(integer)
>> but call like F(float)
>> i wouldn`t like to fail it.
> I think this particular example would be a mistake, because that cast
> would be lossy, so you want to invoke it only when the user explicitely
> selects it.  The other way around would be fine, I think, that is,
> F(float)
> F(float8)
> and the call is F(int)
As i think i not must write function with Float, String, and many other 
arg when my function have INT arg... and if caller wouln`t think about 
types he cant use your strong types
why it not must work like as assignment??? why implicit and assignment 
is different???
I know only implicit and explicit casts and i think imlicit=asssign

>
>> PS This patch needet, because in any case we must calc prefer more
>> smartly, yes this patch is 1/10 of full solution, but it`s first step!!!
> Well, if the other 9/10 were clear, there would be no discussion.  The
> problem is that the missing bits have not been designed and thus we
> don't know if this 1/10 will be useful to them.  We need to find a
> complete design before committing to any initial portion which may turn
> out to be bogus down the road.
>
Yes, but while you think what update table1 set IntField = FloatField is 
valid
but Select FuncWithIntArg(FloatArg) is not valid
you have no problems in current solution, because it works same :)

-- 
С уважением,
Зотов Роман Владимирович
руководитель Отдела разработки
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: zotov@oe-it.ru

On May 4, 2011, at 12:00 AM, Зотов Роман wrote:
>>> F(smallint)
>>> F(integer)
>>> but call like F(float)
>>> i wouldn`t like to fail it.
>> I think this particular example would be a mistake, because that cast
>> would be lossy, so you want to invoke it only when the user explicitely
>> selects it.  The other way around would be fine, I think, that is,
>> F(float)
>> F(float8)
>> and the call is F(int)
> As i think i not must write function with Float, String, and many other arg when my function have INT arg... and if
callerwouln`t think about types he cant use your strong types 
> why it not must work like as assignment??? why implicit and assignment is different???
> I know only implicit and explicit casts and i think imlicit=asssign

I was ready to educate Зотов on why that was a bad idea until I read...

> Yes, but while you think what update table1 set IntField = FloatField is valid
> but Select FuncWithIntArg(FloatArg) is not valid
> you have no problems in current solution, because it works same :)

Sure enough, this works: UPDATE ... SET int_field = ( SELECT float_field FROM ... );

Which begs the question... why do we allow on assignment casting of a float to an int? I would think that should be
explicitonly... 

If we are going to allow assignment casting of float to int, then I'm hard-pressed to see why we wouldn't allow you to
callan int function with a float value, assuming there wasn't a more suitable cast available. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

Jim Nasby <jim@nasby.net> writes:
> Which begs the question... why do we allow on assignment casting of a float to an int?

Because the SQL standard requires it.

In any case, the user's intent in such a case is perfectly clear.
The reasons for not allowing assignment casts to happen in expression
contexts are (1) it would often provide us with too many ways to resolve
an operator or function call, leading to "ambiguous operator" failures;
(2) it would be surprising for the parser to silently choose a cast that
causes information loss.  Neither of these arguments applies to "assign
this value to an integer column".

It's true that if you have exactly one function named f, and it takes an
int, then f(float) could be considered clear and unambiguous.  But
designing this behavior around only the easy cases is not going to work.
        regards, tom lane

On Tue, May 3, 2011 at 3:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>> The interesting discussion is what happens next.  To me, this is all
>>> related to this previous discussion:
>>> http://archives.postgresql.org/pgsql-hackers/2010-09/msg00232.php
>
>> Yeah, there doesn't seem like much point unless we have a clear idea
>> what we're going to do with the change.
>
> BTW, it occurs to me to wonder whether, instead of making types be more
> or less preferred, we should attack the issue from a different direction
> and assign preferred-ness ratings to casts.  That seems to be more or
> less the direction that Robert was considering in the above-linked
> thread.  I'm not sure it's better than putting the ratings on types ---
> in particular, neither viewpoint seems to offer a really clean answer
> about what to do when trying to resolve a multiple-argument function
> in which one possible resolution offers a more-preferred conversion for
> one argument but a less-preferred conversion for another one.  But it's
> an alternative we ought to think about before betting all the chips on
> generalizing typispreferred.
>
> Personally I've always felt that the typispreferred mechanism was a bit
> of a wart; changing it from a bool to an int won't improve that, it'll
> just make it a more complicated wart.  Casts have already got a
> standards-blessed notion that some are more equal than others, so
> maybe attaching preferredness ratings to them will be less of a wart.
> Not sure about it though.

I think this is a pretty good analysis.   One of the big, fat problems
with typispreferred is that it totally falls apart when more than two
types are involved.  For example, given a call f(int2), we can't
decide between f(int4) and f(int8), but it seems pretty clear (to me,
at least) that we should prefer to promote as little as possible and
should therefore pick f(int4).  The problem is less acute with
string-like data types because there are only two typcategory-S data
types that get much use: text and varchar.  But add a third type to
the mix (varchar2...) or start playing around with functions that are
defined for name and bpchar but not text or some such thing, and
things get sticky.

Generalizing typispreferred to an integer definitely helps with these
cases, assuming anyway that you are dealing mostly with built-in
types, or that the extensions you are using can somehow agree among
themselves on reasonable weighting values.  But it is not a perfect
solution either, because it can really only handle pretty linear
topologies.  It's reasonable to suppose that the integer types are
ordered int2 - int4 - int8 - numeric and that the floating point types
are ordered float4 - float8 (- numeric?), but I think the two
hierarchies are pretty much incomparable, and an integer
typispreferred won't handle that very well, unless we make the two
groups separate categories, but arguably numeric belongs in both
groups so that doesn't really seem to work very well either.
Certainly from a theoretical perspective there's no reason why you
couldn't have A - B - X and C - D - X, with A-C, A-D, B-C, and B-D
incomparable.  It almost feels like you need a graph to model it
properly, which perhaps argues for your idea of attaching weights to
the casts.

But there are some problems with that, too.  In particular, it would
be nice to be able to "hook in" new types with a minimum of fuss.  For
example, say we add a new string type, like citext, via an extension.
Right now, we need to add casts not only from citext to text, but also
from citext to all the things to which text has casts, if we really
want citext to behave like text.  That solution works OK for the first
extension type we load in, but as soon as you add any nonstandard
casts from text to other things (perhaps yet another extension type of
some kind), it starts to get a bit leaky.  In some sense it feels like
it'd be nice to be able to "walk the graph" - if an implicit cast from
A to B is OK, and an implicit cast from B to C is OK, perhaps an
implicit cast from A to C is also OK.  But that seems awfully
expensive to do at runtime, and it'd introduce some strange behavior
particularly with the way we have the reg* -> oid and oid -> reg*
casts set up.

select a.castsource::regtype, a.casttarget::regtype,
b.casttarget::regtype from pg_cast a, pg_cast b where a.casttarget =
b.castsource and a.castcontext = 'i' and b.castcontext = 'i' and not
exists (select 1 from pg_cast x where x.castsource = a.castsource and
x.casttarget = b.casttarget and x.castcontext = 'i') and a.castsource
<> b.casttarget;

It's not clear to me whether in any of this there is a solution to the
problem of int2 being a second-class citizen.  Perhaps we could add
casts from int4 and int8 back to int2, and make it less-preferred than
all of the other integer types, but I'm not sure what else that would
break.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> ... One of the big, fat problems
> with typispreferred is that it totally falls apart when more than two
> types are involved.  For example, given a call f(int2), we can't
> decide between f(int4) and f(int8), but it seems pretty clear (to me,
> at least) that we should prefer to promote as little as possible and
> should therefore pick f(int4).

Yeah.  If your mental model is one of "least promotion", you really
cannot express that at all with a "preferred type" concept, even if the
ratings are integers and not bools.  On the other hand, it does seem
possible to attach a "cost" or "distance" metric to casts and get some
reasonably intuitive behavior that way.  If you check the archives I
believe we've discussed this before using the "distance" terminology.
It still falls down though on the question of what to prefer when there
are several combinations of multiple casts to choose between.  And as
you say it's not entirely clear how well either approach generalizes to
after-the-fact insertion of new types/casts in the hierarchy.

Perhaps it would be adequate to allow automatic resolution of an
overloading conflict only when one of the available alternatives
dominates all others, ie, none of the argument positions requires a
"longer distance" cast than is used in that position by any other
available alternative.  I'm just throwing that out as a possibility,
I haven't tried it.

> It's not clear to me whether in any of this there is a solution to the
> problem of int2 being a second-class citizen.

I've always felt that the basic problem int2 has got is that the parser
initially types integer-looking constants as int4 or larger, even if
they'd fit in int2.  If it typed them as int2 then the unintuitive
behaviors would largely go away, without any need for allowing implicit
down-casting from int4 to int2.  I actually tried that once, probably
close to ten years ago, and it blew up real good because many cases that
formerly were considered an exact match no longer were, and the parser
started making some pretty surprising (or at least not backwards
compatible) resolution choices.  Maybe with a more controllable
type-promotion mechanism we could get better results there.

BTW, not to rain on the parade or anything, but I'll bet that
rejiggering anything at all here will result in whining that puts the
8.3-era removal of a few implicit casts to shame.  If the new behavior
is really significantly better *for users* then we can probably
withstand the complaints; but if it's just marginal improvements or just
improves life for hypothetical future extensions, it's not going to fly.
        regards, tom lane

Excerpts from Tom Lane's message of mié may 04 17:14:25 -0300 2011:
> Robert Haas <robertmhaas@gmail.com> writes:

> > It's not clear to me whether in any of this there is a solution to the
> > problem of int2 being a second-class citizen.
> 
> I've always felt that the basic problem int2 has got is that the parser
> initially types integer-looking constants as int4 or larger, even if
> they'd fit in int2.  If it typed them as int2 then the unintuitive
> behaviors would largely go away, without any need for allowing implicit
> down-casting from int4 to int2.  I actually tried that once, probably
> close to ten years ago, and it blew up real good because many cases that
> formerly were considered an exact match no longer were, and the parser
> started making some pretty surprising (or at least not backwards
> compatible) resolution choices.  Maybe with a more controllable
> type-promotion mechanism we could get better results there.
> 
> BTW, not to rain on the parade or anything, but I'll bet that
> rejiggering anything at all here will result in whining that puts the
> 8.3-era removal of a few implicit casts to shame.  If the new behavior
> is really significantly better *for users* then we can probably
> withstand the complaints; but if it's just marginal improvements or just
> improves life for hypothetical future extensions, it's not going to fly.

I remember that one of the problems put forth against this idea was that
stuff like int2+int2 which currently returns int2 would have to be
changed to return int4, otherwise it risks overflow which it currently
doesn't (not because the operator would change, but rather because some
expressions would be lexed differently).  And so on with other
operators.  I am not sure how severe this problem is for users in
practice -- my uneducated guess is that mostly they will not care about
such changes.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

2011/5/4 Tom Lane <tgl@sss.pgh.pa.us>:
> Perhaps it would be adequate to allow automatic resolution of an
> overloading conflict only when one of the available alternatives
> dominates all others, ie, none of the argument positions requires a
> "longer distance" cast than is used in that position by any other
> available alternative.  I'm just throwing that out as a possibility,
> I haven't tried it.

That works OK for most things, but there's one case where I think we
might need a better solution - suppose A is a subtype of B.  It's
fairly common to define a function or operator f(A,A) and f(B,B), and
to want f(A,B) or f(B,A) to be interpreted as a the latter rather than
the former.  For example, let A=int2, B=int4, f=+.  Now, we can (and
currently do) handle that by just defining all the combinations
explicitly, but people don't always want to do that.

>> It's not clear to me whether in any of this there is a solution to the
>> problem of int2 being a second-class citizen.
>
> I've always felt that the basic problem int2 has got is that the parser
> initially types integer-looking constants as int4 or larger, even if
> they'd fit in int2.  If it typed them as int2 then the unintuitive
> behaviors would largely go away, without any need for allowing implicit
> down-casting from int4 to int2.  I actually tried that once, probably
> close to ten years ago, and it blew up real good because many cases that
> formerly were considered an exact match no longer were, and the parser
> started making some pretty surprising (or at least not backwards
> compatible) resolution choices.  Maybe with a more controllable
> type-promotion mechanism we could get better results there.

Maybe, but I'm not convinced.  I think that's using the lexer to do
the type system's job.  Suppose we add a type uint4, for example: what
then?

> BTW, not to rain on the parade or anything, but I'll bet that
> rejiggering anything at all here will result in whining that puts the
> 8.3-era removal of a few implicit casts to shame.  If the new behavior
> is really significantly better *for users* then we can probably
> withstand the complaints; but if it's just marginal improvements or just
> improves life for hypothetical future extensions, it's not going to fly.

Yeah, I share that fear, which is why I think the idea of generalizing
typispreferred to an integer has more than no merit: it's less likely
to break in ways we can't anticipate.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> 2011/5/4 Tom Lane <tgl@sss.pgh.pa.us>:
>> Perhaps it would be adequate to allow automatic resolution of an
>> overloading conflict only when one of the available alternatives
>> dominates all others, ie, none of the argument positions requires a
>> "longer distance" cast than is used in that position by any other
>> available alternative. �I'm just throwing that out as a possibility,
>> I haven't tried it.

> That works OK for most things, but there's one case where I think we
> might need a better solution - suppose A is a subtype of B.  It's
> fairly common to define a function or operator f(A,A) and f(B,B), and
> to want f(A,B) or f(B,A) to be interpreted as a the latter rather than
> the former.  For example, let A=int2, B=int4, f=+.  Now, we can (and
> currently do) handle that by just defining all the combinations
> explicitly, but people don't always want to do that.

That case still works as long as downcasts (int4 -> int2) are either not
allowed to be invoked implicitly at all, or heavily penalized in the
distance assignments.

>> BTW, not to rain on the parade or anything, but I'll bet that
>> rejiggering anything at all here will result in whining that puts the
>> 8.3-era removal of a few implicit casts to shame.

> Yeah, I share that fear, which is why I think the idea of generalizing
> typispreferred to an integer has more than no merit: it's less likely
> to break in ways we can't anticipate.

Well, if you change it to an int and then don't change any of the values
from what they were before, I agree.  But then there's no point.
Presumably, the reason we are doing this is so that we can assign some
other preferredness values besides 0/1, and that will change the
behavior.  We'd better be damn sure that the new behavior is really
better.  Which is why it seems a bit premature to be working on an
implementation when we don't have even a suggestion as to what the
behavioral changes ought to be.
        regards, tom lane

2011/5/6 Tom Lane <tgl@sss.pgh.pa.us>:
> Robert Haas <robertmhaas@gmail.com> writes:
>> 2011/5/4 Tom Lane <tgl@sss.pgh.pa.us>:
>>> Perhaps it would be adequate to allow automatic resolution of an
>>> overloading conflict only when one of the available alternatives
>>> dominates all others, ie, none of the argument positions requires a
>>> "longer distance" cast than is used in that position by any other
>>> available alternative.  I'm just throwing that out as a possibility,
>>> I haven't tried it.
>
>> That works OK for most things, but there's one case where I think we
>> might need a better solution - suppose A is a subtype of B.  It's
>> fairly common to define a function or operator f(A,A) and f(B,B), and
>> to want f(A,B) or f(B,A) to be interpreted as a the latter rather than
>> the former.  For example, let A=int2, B=int4, f=+.  Now, we can (and
>> currently do) handle that by just defining all the combinations
>> explicitly, but people don't always want to do that.
>
> That case still works as long as downcasts (int4 -> int2) are either not
> allowed to be invoked implicitly at all, or heavily penalized in the
> distance assignments.

Not at all works, but heavily penalized doesn't.  Suppose A->B has
distance 1 and B->A has distance 1000.  Then f(A,B) can match f(A,A)
with distances (0,1000) or f(B,B) with distances (1,0).  If you add up
the *total* distance it's easy to say that the latter wins, but if you
compare position-by-position as you proposed (and, generally, I agree
that's the better route, BTW) then each candidate is superior to the
other in one of the two available positions.

>>> BTW, not to rain on the parade or anything, but I'll bet that
>>> rejiggering anything at all here will result in whining that puts the
>>> 8.3-era removal of a few implicit casts to shame.
>
>> Yeah, I share that fear, which is why I think the idea of generalizing
>> typispreferred to an integer has more than no merit: it's less likely
>> to break in ways we can't anticipate.
>
> Well, if you change it to an int and then don't change any of the values
> from what they were before, I agree.  But then there's no point.
> Presumably, the reason we are doing this is so that we can assign some
> other preferredness values besides 0/1, and that will change the
> behavior.  We'd better be damn sure that the new behavior is really
> better.  Which is why it seems a bit premature to be working on an
> implementation when we don't have even a suggestion as to what the
> behavioral changes ought to be.

Well, sure, to some degree.  But if you keep the currently preferred
types as having the highest level of preferred-ness in their same
categories, then the only effect (I think) will be to make some cases
work that don't now; and that's unlikely to break anything too badly.
Going to some whole new system will almost inevitably involve more
breakage.

> Which is why it seems a bit premature to be working on an
> implementation when we don't have even a suggestion as to what the
> behavioral changes ought to be.

I'm in complete agreement on this point.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On fre, 2011-05-06 at 18:38 -0300, Alvaro Herrera wrote:
> I remember that one of the problems put forth against this idea was
> that stuff like int2+int2 which currently returns int2 would have to
> be changed to return int4, otherwise it risks overflow which it
> currently doesn't (not because the operator would change, but rather
> because some expressions would be lexed differently).  And so on with
> other operators.  I am not sure how severe this problem is for users
> in practice -- my uneducated guess is that mostly they will not care
> about such changes.

Modulo backward compatibility concerns, I don't think it would
necessarily be wrong if int2+int2 returned int4.  sum(int2) returns
int8, and no one seems bothered by that.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Tom Lane wrote:
> BTW, not to rain on the parade or anything, but I'll bet that
> rejiggering anything at all here will result in whining that puts the
> 8.3-era removal of a few implicit casts to shame.

I'll take that bet, as it's really hard to imagine anything being worse 
than the pain caused by 8.3 to many people using Postgres. But if 
this is anything at all like that (e.g. requiring rewriting tons of 
SQL queries or modifying system catalogs), then a big fat -1.

I know, probably a moot point by now, but 8.3 is a sore spot 
for me. :)

- -- 
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 201105082230
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk3HU0IACgkQvJuQZxSWSshp2gCeLzjdXPQ0NkwDzby0f8DzUErz
FUEAoLNkIzJ5jWxVP2Ck3BZgxhd6HUhq
=yALY
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> Tom Lane wrote:
>> BTW, not to rain on the parade or anything, but I'll bet that
>> rejiggering anything at all here will result in whining that puts the
>> 8.3-era removal of a few implicit casts to shame.

> I'll take that bet, as it's really hard to imagine anything being worse 
> than the pain caused by 8.3 to many people using Postgres.

You think?  At least the 8.3 changes resulted in easily-diagnosed parser
errors.  The folks who complained about it were complaining because they
couldn't be bothered to fix anything about their applications, not
because it was difficult to understand or to fix.  It seems likely to me
that any changes in function resolution behavior will result in failures
that are *much* harder to diagnose.  The actual fix might be the same
(ie, insert an explicit cast or two) but back-tracking from the observed
problem to that fix could be an order of magnitude more difficult.  For
example, if you start noticing an occasional integer overflow that
didn't happen before, it might be pretty darn difficult to figure out
that the problem is that an operation that was formerly resolved as int4
+ int4 is now resolved as int2 + int2.
        regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Tom and I:
>>> BTW, not to rain on the parade or anything, but I'll bet that
>>> rejiggering anything at all here will result in whining that puts the
>>> 8.3-era removal of a few implicit casts to shame.

>> I'll take that bet, as it's really hard to imagine anything being worse 
>> than the pain caused by 8.3 to many people using Postgres.

> You think?  At least the 8.3 changes resulted in easily-diagnosed parser
> errors.  The folks who complained about it were complaining because they
> couldn't be bothered to fix anything about their applications, not
> because it was difficult to understand or to fix.

Those of us in the trenches saw things a little differently. There's a 
difference between "couldn't be bothered" and the sometimes herculean 
task of changing an existing complicated code base, including finding 
all the problems, fixing, writing tests, doing QA, etc. It was also 
difficult to explain all this to clients: why their code worked just 
fine on all previous versions, what the exact theoretical dangers 
involved are (and agreeing that, yes, it doesn't really apply to 
their particular code), and the sheer man-hours it was going to take 
to get their application over the 8.3 hump. (Granted, there's the 
system catalog hacks, but a) they introduce other problems and b) 
it's dangerous to reapply constantly when pg_dumping or moving 
across versions)

> It seems likely to me that any changes in function resolution behavior 
> will result in failures that are *much* harder to diagnose.  The 
> actual fix might be the same (ie, insert an explicit cast or two) 
> but back-tracking from the observed problem to that fix could be an 
> order of magnitude more difficult.  For example, if you start noticing 
> an occasional integer overflow that didn't happen before, it might be 
> pretty darn difficult to figure out that the problem is that an operation 
> that was formerly resolved as int4 + int4 is now resolved as int2 + int2.

Have I mentioned I'm already a big -1 on the whole idea? :) Yes, this 
will be a more subtle problem to diagnose, but I also think it will 
affect less code and thus not elicit as much whining. Besides, 
I never recommend clients use SMALLINT anyway. ("That type you are 
using: I do not think it's as efficient as you think it is")

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201105082312
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk3HYk4ACgkQvJuQZxSWSshQ+ACePUFS++9q4lhsdWSolIqDuI+r
LY4AoOBsEszt1goBe73GBuSW+dt0DfWF
=gycE
-----END PGP SIGNATURE-----

Excerpts from Tom Lane's message of dom may 08 23:00:27 -0400 2011:

> For
> example, if you start noticing an occasional integer overflow that
> didn't happen before, it might be pretty darn difficult to figure out
> that the problem is that an operation that was formerly resolved as int4
> + int4 is now resolved as int2 + int2.

About this particular example, I mentioned earlier that I preliminarly
consider the idea of changing the +(int2,int2) operator to return int4
instead of the current int2.  It's not impossible that we will miss
changing some operators, but my hope is that it won't be that horrible.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support