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

On 11/09/2003 04:42 Tom Lane wrote:
> Paul Thomas <paul(at)tmsl(dot)demon(dot)co(dot)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 |
+------------------------------+---------------------------------------------+