Hi,

i use Long for all my object ids and therefore they are mapped as int8 in postgresql. It's a known issue that you have to explicitly cast into int8 when you pass a parameter if you wish to use the index with Postgresql.

Example:

- select * from foo where id=100; // will not use pk index
- select * from foo where id=100::int8 //will use pk index
- select * from foo where id='100' // will use pk index

I looked at the dialect class but it does not look like i can tell Hibernate about this Postgresql issue, have i missed something ?

Regards

Nobody from the postgres user base has an inpout about this issue please ? This is a rather annoying problem because at the moment all my object lookup are not very efficient (seq scan all the time....)

Thx in advance.

My understanding is that this was fixed in Postgres quite a while ago.

afaik not really,

there exists a patch to the jdbc-driver that fixes the problem. but i had some issues with 7.2.4 - the patch is for the jdbc-driver for 7.3.x - and the patched driver. last info from the patch-author was that somebody else took over to rewrite the patch and the jdbc-driver but i don't know if something happened yet. this info is about 8 weeks old. in official releases, the problem should still be there.

jan

Jan, do you still have the url where i can find this patch please ?

As for Hibernate, it would be pretty interesting to enhance the dialect class to allow transformation of parameter depending of their type (default = no transformation) so we could append ::int8 for Long type ..int2 for short type etc... I think it could even be usefull for other needs.

sorry for my late post.

there is no url. i received a patched jdbc-driver from the guy who built the patch. he was really busy with his work at that time so he did not gave much info on how to get the sources out of postgres' cvs. i found some issues with postgres 7.2.x that should not occur with 7.3.x (but i still need to use 7.2.4). so i can supply you with the jar but i can not help you, if you run into problems.

jan

You can fix it yourself, the most simple way is to implement "wrapper" for
for JDBC driver and implement it this way:

void setInt(int index, int value)throws SQLException{
original.setString( index, Integer.toString(value) );
}
...

Looks like it conflicts with custom type support implementation on postgres and as I understand nobody going to rewrite it, but you can try it too.