Thread

1. Prefered Types

   Zotov <zotov@oe-it.ru> — 2011-03-10T13:12:29Z

   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
   
   
   

2. Re: Prefered Types

   Robert Haas <robertmhaas@gmail.com> — 2011-03-11T14:01:00Z

   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
   
   

3. Re: Prefered Types

   Zotov <zotov@oe-it.ru> — 2011-03-14T15:15:59Z

   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
   
   

4. Re: Prefered Types

   Zotov <zotov@oe-it.ru> — 2011-04-11T20:39:32Z

   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
   
   

5. Re: Prefered Types

   Robert Haas <robertmhaas@gmail.com> — 2011-04-14T02:12:56Z

   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
   
   

6. Re: Prefered Types

   Bruce Momjian <bruce@momjian.us> — 2011-04-26T17:41:27Z

   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. +
   
   

7. Re: Prefered Types

   Alvaro Herrera <alvherre@commandprompt.com> — 2011-05-03T17:17:30Z

   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
   

8. Re: Prefered Types

   Tom Lane <tgl@sss.pgh.pa.us> — 2011-05-03T18:41:27Z

   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
   
   

9. Re: Prefered Types

   Alvaro Herrera <alvherre@commandprompt.com> — 2011-05-03T18:55:47Z

   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
   
   

10. Re: Prefered Types

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-05-03T19:06:06Z

    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
    
    

11. Re: Prefered Types

    Zotov <zotov@oe-it.ru> — 2011-05-03T19:31:31Z

    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
    
    
    

12. Re: Prefered Types

    Alvaro Herrera <alvherre@commandprompt.com> — 2011-05-03T20:00:12Z

    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
    
    

13. Re: Prefered Types

    Zotov <zotov@oe-it.ru> — 2011-05-04T05:00:42Z

    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
    
    
    

14. Re: Prefered Types

    Jim Nasby <jim@nasby.net> — 2011-05-04T12:48:49Z

    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 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
    
    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 explicit only...
    
    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 call an 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
    
    
    
    

15. Re: Prefered Types

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-05-04T13:49:38Z

    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
    
    

16. Re: Prefered Types

    Robert Haas <robertmhaas@gmail.com> — 2011-05-04T19:41:46Z

    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
    
    

17. Re: Prefered Types

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-05-04T20:14:25Z

    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
    
    

18. Re: Prefered Types

    Alvaro Herrera <alvherre@commandprompt.com> — 2011-05-06T21:38:12Z

    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
    
    

19. Re: Prefered Types

    Robert Haas <robertmhaas@gmail.com> — 2011-05-06T21:54:09Z

    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
    
    

20. Re: Prefered Types

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-05-06T22:04:22Z

    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
    
    

21. Re: Prefered Types

    Robert Haas <robertmhaas@gmail.com> — 2011-05-06T22:26:33Z

    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
    
    

22. Re: Prefered Types

    Peter Eisentraut <peter_e@gmx.net> — 2011-05-07T21:24:52Z

    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.
    
    
    

23. Re: Prefered Types

    Greg Sabino Mullane <greg@turnstep.com> — 2011-05-09T02:37:12Z

    -----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-----
    
    
    
    

24. Re: Prefered Types

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-05-09T03:00:27Z

    "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
    
    

25. Re: Prefered Types

    Greg Sabino Mullane <greg@turnstep.com> — 2011-05-09T03:41:38Z

    -----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-----
    
    
    
    

26. Re: Prefered Types

    Alvaro Herrera <alvherre@commandprompt.com> — 2011-05-11T22:05:27Z

    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