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

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.

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.

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

On Wed, Sep 10, 2003 at 07:23:02PM +0100, Paul Thomas wrote:

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

I have to ask .. If your CMP implementation knows enough about Postgresql
types to use an int8 column, why can't it also know about the index
behaviour?

Or is it ending up with an int8 column via other means? (alias from a
standard type name? manual configuration?)

Our inhouse CMP implementation has to know about postgresql specifics for
other reasons anyway (e.g. "use bytea for storing complex serializable
objects"), so while we don't currently use int8 columns with indexes it
wouldn't be hard to add. We don't support mapping to arbitary schemas,
though, so we really do need to know more about the DB's guts to get table
creation etc. right.

For CMPs that do map to user-provided schemas, from memory the couple I've
looked at (Weblogics, the J2EE RI) both let you edit the SQL to be used
directly (via an extension DD), presumably since machine-generated code is
hard to do a correct mapping with in all cases. Does JBoss not let you do
this?

-O

>
> I have to ask .. If your CMP implementation knows enough about
> Postgresql
> types to use an int8 column, why can't it also know about the index
> behaviour?
>
> Or is it ending up with an int8 column via other means? (alias from a
> standard type name? manual configuration?)
>
> Our inhouse CMP implementation has to know about postgresql specifics
> for
> other reasons anyway (e.g. "use bytea for storing complex serializable
> objects"), so while we don't currently use int8 columns with indexes it
> wouldn't be hard to add. We don't support mapping to arbitary schemas,
> though, so we really do need to know more about the DB's guts to get
> table
> creation etc. right.
>
> For CMPs that do map to user-provided schemas, from memory the couple
> I've
> looked at (Weblogics, the J2EE RI) both let you edit the SQL to be used
> directly (via an extension DD), presumably since machine-generated
> code is
> hard to do a correct mapping with in all cases. Does JBoss not let you
> do
> this?
>
> -O

From the perspective of someone running the EJB server who has to
deploy the beans (possibly written by 3rd parties who have no clue what
particular DB (or, for that matter, which EJB server it is being
deployed on -- in theory, anyway)) on a given DB backend, say, PG, the
CMP component of JBoss lets you tell it two major things off the top:
which JDBC driver URL to bind into the datasource, plus the name of a
set of typemappings that map Java datatype (i.e. java.lang.Long) to
JDBC datatype to SQL datatype that JBoss would use when issuing the
create table command for persisting a component that uses that
datatype. It is this suite of XML mapping tags that lets you describe
to JBoss that, for example, byte arrays should correspond to 'bytea'
columns. This type info, AFAIK, is only used at table creation time.

Other things it lets you vary per SQL backend are things like how to
lock a row -- how exactly to do a 'select for update', as well as how
to issue foreign key constraints, etc.

Attached, for you personal enjoyment, is our current PG settings block.

JBoss does do full table creation, so it is pretty spiffy in that
respect. Enough voluminous code in there to make you both respect it,
yet fear it if you would like to just focus on business-level app logic
yet have to go pokin' around in there to really try to figure out what
it is thinking.

JBoss does let you provide hand-written SQL implementations of most any
finder (not quite sure offhand about the all-important findByPrimaryKey
case) using a custom DD, but I don't remember seeing anything that
would let you override the SQL that it issues when it needs to emit an
update, create, or delete statement, as well as the entire class of
selects that it will generate when navigating container-managed
relationships. So, through converting all of our SQL-container-neutral
EQL finder strings into JBoss+Postgres+hack to fix our int8 pk-fields
tags in a custom DD, we could work around ~50% or so of the selects to
get 'em to use index scans, but that has a serious wrong way feel for
it. I'm much more inclined to whack all of our EJBs to use Integer as
the PK type (a little sed/awk magic on all of the source), then a
second sed/awk pass on the 'CREATE TABLE' section of a pg_dump of the
production schema to massage the columns into int4, then dropdb +
restore the massaged dump. A fun 2AM site update. That is to say, we're
nowhere near consumed the lower 32 bits of our pk space yet. But that
would be giving up completely, wouldn't it?

On looking at this problem sideways, could I somehow build functional
indexes on the result of cooercing the int8 pk field to int4 and have
the 'select * from foo where id = 44' queries somehow find the
int4-oriented index?

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.

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?

regards, tom lane

On Wed, Sep 10, 2003 at 08:54:33PM -0400, James Robinson wrote:

> From the perspective of someone running the EJB server who has to
> deploy the beans (possibly written by 3rd parties who have no clue what
> particular DB (or, for that matter, which EJB server it is being
> deployed on -- in theory, anyway)) on a given DB backend, say, PG, the
> CMP component of JBoss lets you tell it two major things off the top:
> which JDBC driver URL to bind into the datasource, plus the name of a
> set of typemappings that map Java datatype (i.e. java.lang.Long) to
> JDBC datatype to SQL datatype that JBoss would use when issuing the
> create table command for persisting a component that uses that
> datatype. It is this suite of XML mapping tags that lets you describe
> to JBoss that, for example, byte arrays should correspond to 'bytea'
> columns. This type info, AFAIK, is only used at table creation time.

This sounds like a reasonable way to operate.

In the absence of a backend fix, it sounds like appending explicit types to
parameter values is the way to go. That is, have setLong(12345) produce a
parameter of "12345::int8", setShort(12345) produce "12345::int2", etc.

This moves the breakage from "setLong() vs. int8 column" to "setLong() vs.
int4 column". Is this a reasonable thing to do? ISTM that the case where the
Java and DB types match is the case that should work generally, and the case
where type conversion is needed is dependant on Postgresql's exact behaviour
anyway.

Something related to think about: with PREPARE, we already explicitly
specify the types of parameters based on the method (or sql typecode) used
to set them. Given:

CREATE TABLE foo(bar int4 not null primary key);

and this code fragment:

PreparedStatement ps = c.prepareStatement("SELECT * FROM foo WHERE bar = ?");
ps.setLong(1, 12345)

The current driver generates something like this normally:

SELECT * FROM foo WHERE bar = 12345

which should use the index. However with server-side prepared queries
enabled the query is transformed to something like this:

PREPARE jdbc_statement_N(int8) AS SELECT * FROM foo WHERE bar = $1;
EXECUTE jdbc_statement_N(12345)

Which I suspect won't use the index as $1 is typed to int8. (anyone know for
sure? I can't find an easy way to get at the query plan produced by
PREPARE).

If it doesn't use the index this breaks the transparency of converting to
PREPARE i.e. the "setInt() vs. int8 column" is already broken in some cases
with the current driver. So making setLong() vs. int8 work at the expense of
this case doesn't really break things further. Honest. :)

-O

On Thu, Sep 11, 2003 at 05:28:34PM +1200, Oliver Jowett wrote:
> If it doesn't use the index this breaks the transparency of converting to
> PREPARE i.e. the "setInt() vs. int8 column" is already broken in some cases

Sorry, this should read 'the "setLong() vs int4 column" case'.

-O

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> which should use the index. However with server-side prepared queries
> enabled the query is transformed to something like this:

> PREPARE jdbc_statement_N(int8) AS SELECT * FROM foo WHERE bar = $1;
> EXECUTE jdbc_statement_N(12345)

> Which I suspect won't use the index as $1 is typed to int8. (anyone know for
> sure? I can't find an easy way to get at the query plan produced by
> PREPARE).

In 7.4 you can do EXPLAIN EXECUTE. But no, this wouldn't use the index
:-(. What exactly is causing the driver to decide to type the parameter
as int8?

regards, tom lane

On Thu, Sep 11, 2003 at 01:40:21AM -0400, Tom Lane wrote:
> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> > which should use the index. However with server-side prepared queries
> > enabled the query is transformed to something like this:
>
> > PREPARE jdbc_statement_N(int8) AS SELECT * FROM foo WHERE bar = $1;
> > EXECUTE jdbc_statement_N(12345)
>
> > Which I suspect won't use the index as $1 is typed to int8. (anyone know for
> > sure? I can't find an easy way to get at the query plan produced by
> > PREPARE).
>
> In 7.4 you can do EXPLAIN EXECUTE. But no, this wouldn't use the index
> :-(. What exactly is causing the driver to decide to type the parameter
> as int8?

JDBC doesn't provide a separate parameter-typing step for parameterized
queries, so the driver has to infer the type from the method used to set the
actual value of a parameter. In this case, setLong() takes a Java long which
is a signed 64-bit integer, so the driver picks int8.

There's a more general interface (setObject()) that allows both a value and
a target SQL type to be specified, but that's not commonly used for
primitive types thanks to Java's primitive-vs-Object distinction.

See http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

-O

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

On Wednesday, September 10, 2003, at 11:42 PM, Tom Lane wrote:

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

I would be delighted, sitting on a 59 table production OLTP system,
all with int8 primary keys and foreign key references out the yin yang.
Things are running within within our needs currently, but just having
realized that things could potentially be much better, the desire for
'wow, we're doing all of these O(n) searches that we thought were
log2(n), so this could be so much better than we realized without
us touching our app logic at all.'

My personal need-to-be-scratched is int8 exclusively. What sort of
'fool'
would ever have a int2 index :-) ? Probably the same number of 'fools'
with int8.

James

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

As i told before if you have an app already in production state i
recomend you to do as we did:
patch the setLong and setBigDecimal methods and compile your own
version of postgres JDBC driver.

It's very easy to be done. Here is an example:

The orginal look like:

public void setBigDecimal(int parameterIndex, BigDecimal x) throws
SQLException
{
if (x == null)
setNull(parameterIndex, Types.OTHER);
else
set(parameterIndex, x.toString());
}

public void setLong(int parameterIndex, long x) throws SQLException {
set(parameterIndex, (new Long(x)).toString());
}

and we changed de set(...) to setString(..) and its look like:

public void setBigDecimal(int parameterIndex, BigDecimal x) throws
SQLException {
if (x == null)
setNull(parameterIndex, Types.OTHER);
else
setString(parameterIndex, x.toString());
}

public void setLong(int parameterIndex, long x) throws SQLException {
setString(parameterIndex, (new Long(x)).toString());

}

With this change when we use the setBigdecimal or the setLong in a
query the value passed will be evaluated and the will be correctly
used.

This has been working in a production database for more than a year.

Regards.
João Paulo Ribeiro

James Robinson wrote:

>
> On Wednesday, September 10, 2003, at 11:42 PM, Tom Lane wrote:
>
>> 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?
>>
>
> I would be delighted, sitting on a 59 table production OLTP system,
> all with int8 primary keys and foreign key references out the yin yang.
> Things are running within within our needs currently, but just having
> realized that things could potentially be much better, the desire for
> 'wow, we're doing all of these O(n) searches that we thought were
> log2(n), so this could be so much better than we realized without
> us touching our app logic at all.'
>
> My personal need-to-be-scratched is int8 exclusively. What sort of 'fool'
> would ever have a int2 index :-) ? Probably the same number of 'fools'
> with int8.
>
> James
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/YZzwbwM7R+C+9B0RAjZfAKCwHXfajcKndw8mDf31CPzpm0BinwCeLu2S
PvX0B9KY5zfwiVJFi29MRD8=
=3Jay
-----END PGP SIGNATURE-----