Re: int8 primary keys still not using index without manual - Mailing list pgsql-general

Tom Lane wrote:
> Tino Wildenhain <tino@wildenhain.de> writes:
> > I agree with you wholeheartly - it also bothers me why
> > postgresql can cast [0-9]+ to int4, but only
> > '[0-9]+' to int8 or int2, I really cannot see the
> > difference.
>
> > Any ideas where we have to look for the place to patch?
>
> Try reading the thousands of lines of discussion of this problem that
> exist in the last several years of the pgsql-hackers archives.
> Basically, we have found no solution that doesn't have side-effects
> worse than what it fixes.  Here's one recent example of a possible
> solution that crashed and burned on takeoff:
> http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php

I read that URL and it covered the main stuff.  This part was
particularly interesting:

    I am wondering about adding some notion of "conversion distance"
    associated with casts, and preferring choices that require a smaller
    conversion distance; perhaps this could replace the concept of
    "preferred type", too.  But again I don't have a specific proposal to
    make.  Any thoughts?

    The test case that was actually in the regression tests was

      select to_hex(256*256*256 - 1) AS "ffffff";
    ! ERROR:  Function to_hex(smallint) does not exist
    !     Unable to identify a function that satisfies the given argument types
    !     You may need to add explicit typecasts

    Even had the parser resolved the overloaded to_hex call, this test would
    have failed, because int2 multiplication overflows:

    regression=# select 256::int2*256::int2*256::int2;
     ?column?
    ----------
            0
    (1 row)

    I am thinking that it might be good to eliminate all the basic
    arithmetic operators on int2, so that you get int4 as the minimum
    width for arithmetic.  But this cannot work unless we have some concept
    like conversion distance, or the parser will just fail to make a choice
    between int4, int8, etc alternatives.

I think your idea of conversion distance is required for any working
solution --- without it, things seem impossible --- you have an int4
value and have function for int2 and int8 --- you need to lean in a
specific direction and can't just give up.

We can try removing most int2 functions and see if that makes such
conversions much easier, or try casting incoming constants to int2 to
see what happens.  I see float4/float8/numeric as similar, though that
has precision issues that I am not sure how to address --- when can you
tell if the user wants full precision?

Sorry I haven't focused on this issue but I am ready to do so if I can
be of help.  How can we set up some tests of these ideas?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073