Re: JBoss w/int8 primary keys in postgres ...

On 11/09/2003 04:42 Tom Lane wrote:
> Paul Thomas <paul@tmsl.demon.co.uk> writes:
> > I think the first big hurdle is going to be making them realize that
> with
> > CMP there _is no_ SQL source to modify in the first place. Yes, I think
>
> > you need to petition hackers and maybe x-post to advocacy too - there
> are
> > also people there who need to be made aware that PostgreSQL has a
> serious
> > Achilles heel as an enterprise database!
>
> <rolls eyes>  Do you think we have not heard about the datatype-mismatch
> issue ten thousand times before?  Don't waste our time with "petitions".
> Give us a practical way to fix it (ie, one that doesn't create more
> problems than it solves).  See for example
> http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php
> as a recent discussion of the pitfalls involved.

Without meaningful knowledge of the back-end internals its difficult for
me to see where that discussion fits in...

>
> It occurred to me this afternoon that we might be able to improve
> matters for int8 without necessarily fixing the general problem.
> The problems cited in the above message mostly stem from trying to
> type small constants as int2 so that "int2col = 42" is indexable.
> Once upon a time it seemed that was where the hot buttons were, but
> if your hot button is mostly int8, maybe we could fix that by removing
> the int8-vs-int4 cross-type operators, and not touch the initial typing
> of integer literals just yet.  Does someone want to explore the
> consequences of trying that?

Maybe the answer lies in finding out why I can define define an index on a
double precision column 'quantity' and the the planner will use the index
:-

test_db=#\d type_test

         Table "public.type_test"
   Column  |       Type       | Modifiers
----------+------------------+-----------
  id       | integer          |  age      | smallint         |  quantity |
double precision | Indexes: type_test_age btree (age),
          type_test_qty btree (quantity)


test_db=# explain select * from type_test where quantity = 0;

                                    QUERY
PLAN
--------------------------------------------------------------------------------
  Index Scan using type_test_qty on type_test  (cost=0.00..3.35 rows=1
width=14)
    Index Cond: (quantity = 0::double precision)
(2 rows)



--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+