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

Wow, quick responses :-)


On Nov 11, 2007 3:11 PM, Tom Dunstan <pgsql@tomd.cc> wrote:
> > 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.

Right Tom. The main problem is that hibernate propose is to be
database independent, so, it isn't all databases that has a table with
the list of all functions(and parameters/types to each function).


> 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).

Sure. But don't expect so much help from Oracle Toplink Team :-)


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

It is my motivation to ask for some help :-)


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

Right again Tom,

Clob = character large object
http://java.sun.com/javase/6/docs/api/java/sql/Clob.html
http://en.wikipedia.org/wiki/Character_large_object


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

Waiting for a response in that, too.


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

I'm thinking the same thing. We could let PostgreSQLDialect to do full
support to Postgre 7.x and extend it to support the new
features/functions in Postgre 8.x. Btw, to do that, one thing that we
must do is identify what functions are new/still avaiable in 8.x. That
approach is good too, because we can get different behaviors in each
version of the database.


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

Thanks. We're trying to improve this :-)


Cheers,
Diego Pires Plentz

-- 
http://plentz.org/
"Provide options, don't make lame excuses."