Re: JBoss w/int8 primary keys in postgres ... - Mailing list pgsql-jdbc

On 10/09/2003 17:56 James Robinson wrote:
>
> On Wed, 10 Sep 2003 14:31:53, Paul Thomas wrote:
>
>> Is there any way we could find out for definite? Until this index/type
>> cast issue is sorted out I don't see any way that PostgreSQL can even be
>> considered ready for enterprise use. EJB/CMP is an important enterprise
>> technology and int8 index columns are not unknown in big company
>> databases.
>
> I ran 7.4B2 a few days ago, and it did not act any different than 7.3
> does -- that is, the problem still exists in the backend.

I didn't have a lot of hope that 7.4 would be the answer. This int8 index
column is a well known gotcha and the advice has always been either a type
cast or quotation. I can't recall anyone saying anything about the problem
going away in 7.4...

> Shall I petition hackers, stating that EJB/CMP is not a fly-by-night
> technology (rather, more like the COBOL of our generation), and that
> postgres would make a great backend for something like JBoss, or any
> DB-neutral relational persistence generation framework using any of the
> available interfaces, not just JDBC, if this issue was solved once and
> for all?
>
> Or will we be told 'show us the backend code that passes the regression
> tests', which is a valid response.

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!

This morning I discovered an inconsistency of behavior in 7.3.4. I have
one table which has 2 fields

     material_id             integer
     quantity_available      double precision

on which I need to do a search of the form:

     select * from mytable where material_id = 123 and
quantity_available > 0

I created the index on (material_id, quantity_available) and, in psql, did
an

     explain analyze select * from mytable where material_id = 123 and
quantity_available > 0;

to check that the index was being used - which it was. But it printed up a
most interesting line:

     Index Cond: ((material_id = 123) AND (quantity_available > 0::double
precision))

~~~~~~~~~~~~~~~~~~~

So it seems to know when a number should be treated as double precision
but not int8! Maybe the inconsistent behavior of the current production
release will strengthen your argument.

HTH

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