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

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