Re: [hibernate-team] PostgreSQLDialect - Mailing list pgsql-hackers

> Hi, I've never used Hibernate but it seems to be that table of
> functions could be generated automatically.

That's the obvious solution. It would be nice if the dialect could
query the database itself to get a list of functions, since there will
be different sets of functions for different server versions, and the
functions can change when people install contrib modules or their own
functions. However, it doesn't look like the constructor for the
dialect gets given a connection or anything, so we'll probably have to
settle for static lists. It wouldn't be very hard to write a little
bit of java to parse pg_proc.h, but you'd want to filter out the types
that hibernate doesn't understand. One problem is that hibernate users
can install their own "types" - so hibernate might understand e.g.
polygons or whatever, but we won't know that at dialect initialization
time.

As someone who has contributed patches to both hibernate and pgsql I'd
be happy to help out on this, whatever the best way forward happens to
be. Top notch postgresql support in hibernate is something that I'd
very much like to see (and that goes for other JPA implementations as
well). I wasn't aware that it was particularly lacking, but clearly if
a function must be registered in the dialect to be usable by HQL,
there are an awful lot of functions that won't be available. I wonder
what happens with custom operators like tsearch provides...

> - You map "text" to CLOB. Not exactly sure what CLOB refers to but text
> column are not generally used for large objects. I mean, you can store
> up to a GB in them, but most such columns are not going to be large.

Actually, it's clob being mapped to text. I don't see a huge problem
with that, really, it'll often be mapped to a String at the java end
anyway. Think about it from the perspective of someone writing a
database agnostic hibernate application - they want a field to store
character data which can potentially be quite big - big enough that
they don't want to set arbitrary limits on it. So text pretty much
fits the bill since toasting was introduced.

It would be nice if we could register string data with no explicit
length as belonging to text as well, but it's not obvious how to do
that. Hmm.

The BLOB mapping is the one that looks wrong to me - surely that
should be bytea as well as varbinary, unless hibernate is explicitly
invoking the large object api. Perhaps it is.Although:public boolean useInputStreamToInsertBlob() {    return false;}
and in particular:public boolean supportsExpectedLobUsagePattern() {    // seems to have spotty LOB suppport    return
false;}
I wonder what the fallback lob usage pattern is. Someone with better
knowledge of our jdbc driver might be able to point out whether the
above functions are saying the right things or not.

> - You have supportsRowValueConstructorSyntax commented out. It does, if
> you have a recent enough version, or do you mean something else?

The way to fix both that and the differing available functions would
probably be to have a subclass of the dialect for each server version.
MySQL seems to have about 5 :)
http://www.hibernate.org/hib_docs/v3/api/org/hibernate/dialect/package-summary.html.

As a side note to Diego, I'll say that it's great to see a hibernate
commiter being proactive about improving these things. Getting
attention to a bug or bugfix hasn't always been easy.

Cheers

Tom