Thread: Problems with Hibernate Discriminators and 9.0-801.jdbc4

Hi,

I have a problem with Hibernate's "single tables"  and the last postgres
jdbc driver. It seems that now it includes a returning * when there is an
Insert, instead of a returning [identifier] that seems more cleaner and
doesn't return all the fields. There is a reason for that? and there
expectatives to change that?

Because of the "returning *", crash when there is an Insert in a table with
a String type discriminator, returning this :

 Caused by: org.postgresql.util.PSQLException: Bad value for type long :
NACIONAL
    at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2796)
    at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2019)
    at
org.hibernate.id.IdentifierGeneratorHelper.get(IdentifierGeneratorHelper.java:114)
    at
org.hibernate.id.IdentifierGeneratorHelper.getGeneratedIdentity(IdentifierGeneratorHelper.java:86)
    at
org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:98)
    at
org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57)

Thanks for any feedback!

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Problems-with-Hibernate-Discriminators-and-9-0-801-jdbc4-tp4259788p4259788.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On Wed, Mar 23, 2011 at 7:35 PM, sdavidr <david.ricoma@gmail.com> wrote:
> Hi,
>
> I have a problem with Hibernate's "single tables"  and the last postgres
> jdbc driver. It seems that now it includes a returning * when there is an
> Insert, instead of a returning [identifier] that seems more cleaner and
> doesn't return all the fields. There is a reason for that? and there
> expectatives to change that?
>
> Because of the "returning *", crash when there is an Insert in a table with
> a String type discriminator, returning this :
>
>  Caused by: org.postgresql.util.PSQLException: Bad value for type long :
> NACIONAL
>        at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2796)
>        at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2019)
>        at
> org.hibernate.id.IdentifierGeneratorHelper.get(IdentifierGeneratorHelper.java:114)
>        at
> org.hibernate.id.IdentifierGeneratorHelper.getGeneratedIdentity(IdentifierGeneratorHelper.java:86)
>        at
> org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:98)
>        at
> org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57)
>
> Thanks for any feedback!

I just had a look at the 8.4 driver and it returns * as well. Can you
give us a concrete example to see what is going on ?

Dave

www.credativ.ca

Yes, the problem is not only with version 9. The problem started with the
"returning *", that from my perspective is a very error prone solution and
of course it shows a poorly designed driver.

The question is: is it very difficult to change that to a more correct
returning [identifier] ?

Because of this error, third parties like hibernate expects that the first
returned element is the identifier, but that doesn't always happens.
Sometimes it returns a discriminator, sometimes it could be something else
that could be a long, and that could be disastrous if third party uses this
number as a identifier in an application.


Thanks.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Problems-with-Hibernate-Discriminators-and-9-0-801-jdbc4-tp4259788p4267176.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 28 March 2011 21:02, sdavidr <david.ricoma@gmail.com> wrote:
> Yes, the problem is not only with version 9. The problem started with the
> "returning *", that from my perspective is a very error prone solution and
> of course it shows a poorly designed driver.
>
> The question is: is it very difficult to change that to a more correct
> returning [identifier] ?

We need an example of what is going wrong.
What are the calls that Hibernate is making to the driver, and what is
the driver returning that is incorrect?

Oliver

On Mon, Mar 28, 2011 at 5:05 AM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 28 March 2011 21:02, sdavidr <david.ricoma@gmail.com> wrote:
>> Yes, the problem is not only with version 9. The problem started with the
>> "returning *", that from my perspective is a very error prone solution and
>> of course it shows a poorly designed driver.
>>
>> The question is: is it very difficult to change that to a more correct
>> returning [identifier] ?
>
> We need an example of what is going wrong.
> What are the calls that Hibernate is making to the driver, and what is
> the driver returning that is incorrect?
>
> Oliver
>

If I understand this correctly he is using a column other than the
first one as the id and hibernate is assuming that the first column is
the key. Seems to me the most robust way to fix this is in hibernate.
Hibernate should be getting the key out of the column by name instead
of by number.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 28 March 2011 21:02, sdavidr <david.ricoma@gmail.com> wrote:

> The question is: is it very difficult to change that to a more correct
> returning [identifier] ?

BTW, the driver *does* do exactly this if you call the query execution
methods that take an array of column names to return as generated
keys.
You only get RETURNING * added to the query if you asked for
RETURN_GENERATED_KEYS without specifying any column names.
So it sounds like Hibernate is doing something wrong, but it's very
hard to tell without seeing a concrete testcase.

Oliver

On Mon, Mar 28, 2011 at 5:43 AM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 28 March 2011 21:02, sdavidr <david.ricoma@gmail.com> wrote:
>
>> The question is: is it very difficult to change that to a more correct
>> returning [identifier] ?
>
> BTW, the driver *does* do exactly this if you call the query execution
> methods that take an array of column names to return as generated
> keys.
> You only get RETURNING * added to the query if you asked for
> RETURN_GENERATED_KEYS without specifying any column names.
> So it sounds like Hibernate is doing something wrong, but it's very
> hard to tell without seeing a concrete testcase.
>

Ok, looking at the hibernate source this is what is in the PostgreSQLDialect

public String getIdentitySelectString(String table, String column, int type) {
        return new StringBuffer().append("select currval('")
            .append(table)
            .append('_')
            .append(column)
            .append("_seq')")
            .toString();
    }

which will work assuming you are using sequences for identities. There
are some other issues in the dialect, but for the most part it is
sane.

So we will need to see exactly what you are doing that does not work.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 03/28/11 10:54, Dave Cramer wrote:
> Ok, looking at the hibernate source this is what is in the PostgreSQLDialect
>
> public String getIdentitySelectString(String table, String column, int type) {
>               return new StringBuffer().append("select currval('")
>                       .append(table)
>                       .append('_')
>                       .append(column)
>                       .append("_seq')")
>                       .toString();
>       }
>

Just as an aside, should that not be using a StringBuilder rather than
a StringBuffer? Or does Hibernate still explicitly support Java 1.4?

> which will work assuming you are using sequences for identities. There
> are some other issues in the dialect, but for the most part it is
> sane.
>
> So we will need to see exactly what you are doing that does not work.
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>

Chris
--




Chris Wareham
Senior Software Engineer
Visit London Ltd
6th floor,
2 More London Riverside
London SE1 2RR

Tel: +44 (0)20 7234 5848
Fax: +44 (0)20 7234 5753

http://www.visitlondon.com/


'Visit London Limited' is registered in England under No.761149;
Registered Office: Visit London, 2 More London Riverside, London SE1 2RR.

Visit London is the official visitor organisation for London. Visit London is partly funded by Partnership, the Mayor's
LondonDevelopment Agency and London Councils. 
The information contained in this e-mail is confidential and intended for the named recipient(s) only. If you have
receivedit in error, please notify the sender immediately and then delete the message. If you are not the intended
recipient,you must not use, disclose, copy or distribute this email. The views expressed in this e-mail are those of
theindividual and not of Visit London. We reserve the right to read and monitor any email or attachment entering or
leavingour systems without prior notice. 

 Please don't print this e-mail unless you really need to.

Ok, that's the hibernate's code that is failing. As it says :Extract the
value from the result set (which is assumed to already have been positioned
to the apopriate row).

Hibernate could receive a lot of numbers from a "returning *" but doesn't
know what is the correct value. It expects to be the first element, but if
is not, it fails.  Normally, the id is the first returned element by
postgres, but in some tables that is not true -> when there is a string
discriminator in the first column.

In another way, method from postgres driver getGeneratedKeys cannot return
the full table, isn't it?


/**
     * Extract the value from the result set (which is assumed to already have
been positioned to the apopriate row)
     * and wrp it in the appropriate Java numeric type.
     *
     * @param rs The result set from which to extract the value.
     * @param type The expected type of the value.
     * @return The extracted value.
     * @throws SQLException Indicates problems access the result set
     * @throws IdentifierGenerationException Indicates an unknown type.
     */
    public static Serializable get(ResultSet rs, Type type) throws
SQLException, IdentifierGenerationException {
        if ( ResultSetIdentifierConsumer.class.isInstance( type ) ) {
            return ( ( ResultSetIdentifierConsumer ) type ).consumeIdentifier( rs );
        }
        if ( CustomType.class.isInstance( type ) ) {
            final CustomType customType = (CustomType) type;
            if ( ResultSetIdentifierConsumer.class.isInstance(
customType.getUserType() ) ) {
                return ( (ResultSetIdentifierConsumer) customType.getUserType()
).consumeIdentifier( rs );
            }
        }

        Class clazz = type.getReturnedClass();
        if ( clazz == Long.class ) {
            return new Long( rs.getLong( 1 ) );
        }
        else if ( clazz == Integer.class ) {
            return new Integer( rs.getInt( 1 ) );
        }
        else if ( clazz == Short.class ) {
            return new Short( rs.getShort( 1 ) );
        }
        else if ( clazz == String.class ) {
            return rs.getString( 1 );
        }
        else if ( clazz == BigInteger.class ) {
            return rs.getBigDecimal( 1 ).setScale( 0, BigDecimal.ROUND_UNNECESSARY
).toBigInteger();
        }
        else if ( clazz == BigDecimal.class ) {
            return rs.getBigDecimal( 1 ).setScale( 0, BigDecimal.ROUND_UNNECESSARY );
        }
        else {
            throw new IdentifierGenerationException(
                    "unrecognized id type : " + type.getName() + " -> " + clazz.getName()
            );
        }
    }

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Problems-with-Hibernate-Discriminators-and-9-0-801-jdbc4-tp4259788p4267556.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On Mon, Mar 28, 2011 at 9:05 AM, sdavidr <david.ricoma@gmail.com> wrote:
> Ok, that's the hibernate's code that is failing. As it says :Extract the
> value from the result set (which is assumed to already have been positioned
> to the apopriate row).
>
> Hibernate could receive a lot of numbers from a "returning *" but doesn't
> know what is the correct value. It expects to be the first element, but if
> is not, it fails.  Normally, the id is the first returned element by
> postgres, but in some tables that is not true -> when there is a string
> discriminator in the first column.
>
> In another way, method from postgres driver getGeneratedKeys cannot return
> the full table, isn't it?
>

What code is this ? Is this a hibernate class ? If so which one?
>
> /**
>         * Extract the value from the result set (which is assumed to already have
> been positioned to the apopriate row)
>         * and wrp it in the appropriate Java numeric type.
>         *
>         * @param rs The result set from which to extract the value.
>         * @param type The expected type of the value.
>         * @return The extracted value.
>         * @throws SQLException Indicates problems access the result set
>         * @throws IdentifierGenerationException Indicates an unknown type.
>         */
>        public static Serializable get(ResultSet rs, Type type) throws
> SQLException, IdentifierGenerationException {
>                if ( ResultSetIdentifierConsumer.class.isInstance( type ) ) {
>                        return ( ( ResultSetIdentifierConsumer ) type ).consumeIdentifier( rs );
>                }
>                if ( CustomType.class.isInstance( type ) ) {
>                        final CustomType customType = (CustomType) type;
>                        if ( ResultSetIdentifierConsumer.class.isInstance(
> customType.getUserType() ) ) {
>                                return ( (ResultSetIdentifierConsumer) customType.getUserType()
> ).consumeIdentifier( rs );
>                        }
>                }
>
>                Class clazz = type.getReturnedClass();
>                if ( clazz == Long.class ) {
>                        return new Long( rs.getLong( 1 ) );
>                }
>                else if ( clazz == Integer.class ) {
>                        return new Integer( rs.getInt( 1 ) );
>                }
>                else if ( clazz == Short.class ) {
>                        return new Short( rs.getShort( 1 ) );
>                }
>                else if ( clazz == String.class ) {
>                        return rs.getString( 1 );
>                }
>                else if ( clazz == BigInteger.class ) {
>                        return rs.getBigDecimal( 1 ).setScale( 0, BigDecimal.ROUND_UNNECESSARY
> ).toBigInteger();
>                }
>                else if ( clazz == BigDecimal.class ) {
>                        return rs.getBigDecimal( 1 ).setScale( 0, BigDecimal.ROUND_UNNECESSARY );
>                }
>                else {
>                        throw new IdentifierGenerationException(
>                                        "unrecognized id type : " + type.getName() + " -> " + clazz.getName()
>                        );
>                }
>        }
>

The problem of course is that the driver is trying to solve the
general problem which is to return all generated values, whether they
be the result of sequences, triggers or rules. The simplest solution
is to return all of the columns since it is impossible for the driver
to determine what is generated by the backend and what is not. The
code above is assuming (incorrectly) that the first column is the
identity column.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Chris Wareham wrote:
> Dave Cramer wrote:
>> Ok, looking at the hibernate source this is what is in the PostgreSQLDialect
>>
>> public String getIdentitySelectString(String table, String column, int type) {
>>   return new StringBuffer().append("select currval('")
>>     .append(table)
>>     .append('_')
>>     .append(column)
>>     .append("_seq')")
>>     .toString();
>>   }
>> }

> Just as an aside, should that not be using a StringBuilder rather than
> a StringBuffer? Or does Hibernate still explicitly support Java 1.4?

My Red-Flag-O-Meter triggered on that, too.  What Hibernate ought to do is:

  return "select currval('" + table + '_' + column + "_seq')";

for Pete's sake.

I'm off to check if EclipseLink or OpenJPA is better written.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

On Mon, Mar 28, 2011 at 9:27 AM, Lew <noone@lewscanon.com> wrote:
> Chris Wareham wrote:
>>
>> Dave Cramer wrote:
>>>
>>> Ok, looking at the hibernate source this is what is in the
>>> PostgreSQLDialect
>>>
>>> public String getIdentitySelectString(String table, String column, int
>>> type) {
>>>  return new StringBuffer().append("select currval('")
>>>    .append(table)
>>>    .append('_')
>>>    .append(column)
>>>    .append("_seq')")
>>>    .toString();
>>>  }
>>> }
>
>> Just as an aside, should that not be using a StringBuilder rather than
>> a StringBuffer? Or does Hibernate still explicitly support Java 1.4?
>
> My Red-Flag-O-Meter triggered on that, too.  What Hibernate ought to do is:
>
>  return "select currval('" + table + '_' + column + "_seq')";
>
> for Pete's sake.


Really ??? the last time I checked the above will generate approx 5
objects StringBuffer.append is the recommended use for java 1.4,
StringBuilder is only marginally faster as it not synchronized.

+ or concat is much slower as it creates a new object each time and
copies the old into the new.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 03/28/11 14:27, Lew wrote:
> Chris Wareham wrote:
>> Dave Cramer wrote:
>>> Ok, looking at the hibernate source this is what is in the PostgreSQLDialect
>>>
>>> public String getIdentitySelectString(String table, String column, int type) {
>>>    return new StringBuffer().append("select currval('")
>>>      .append(table)
>>>      .append('_')
>>>      .append(column)
>>>      .append("_seq')")
>>>      .toString();
>>>    }
>>> }
>
>> Just as an aside, should that not be using a StringBuilder rather than
>> a StringBuffer? Or does Hibernate still explicitly support Java 1.4?
>
> My Red-Flag-O-Meter triggered on that, too.  What Hibernate ought to do is:
>
>    return "select currval('" + table + '_' + column + "_seq')";
>
> for Pete's sake.
>
> I'm off to check if EclipseLink or OpenJPA is better written.
>

I was flagging this up due to StringBuffer's locking, despite it being
used in a local scope - in other words, it doesn't need to be thread
safe, so the locking overhead can be avoided by using a StringBuilder
instead. As I suggested, perhaps Hibernate still supports Java 1.4, or
wasn't rototilled for this kind of thing when 1.4 support was dropped.
If it's still a common idiom across the Hibernate codebase then it
would be nice to see it changed.

You're right that in this case concatenation will achieve the same
thing as using an explicit StringBuilder, at least in Java 1.6[1].

Chris

[1] http://kaioa.com/node/59
--




Chris Wareham
Senior Software Engineer
Visit London Ltd
6th floor,
2 More London Riverside
London SE1 2RR

Tel: +44 (0)20 7234 5848
Fax: +44 (0)20 7234 5753

http://www.visitlondon.com/


'Visit London Limited' is registered in England under No.761149;
Registered Office: Visit London, 2 More London Riverside, London SE1 2RR.

Visit London is the official visitor organisation for London. Visit London is partly funded by Partnership, the Mayor's
LondonDevelopment Agency and London Councils. 
The information contained in this e-mail is confidential and intended for the named recipient(s) only. If you have
receivedit in error, please notify the sender immediately and then delete the message. If you are not the intended
recipient,you must not use, disclose, copy or distribute this email. The views expressed in this e-mail are those of
theindividual and not of Visit London. We reserve the right to read and monitor any email or attachment entering or
leavingour systems without prior notice. 

 Please don't print this e-mail unless you really need to.

2011/3/28 Віталій Тимчишин <tivv00@gmail.com>:
>
>
> 2011/3/28 Dave Cramer <pg@fastcrypt.com>
>>
>> On Mon, Mar 28, 2011 at 9:27 AM, Lew <noone@lewscanon.com> wrote:
>> > Chris Wareham wrote:
>> >>
>> >> Dave Cramer wrote:
>> >>>
>> >>> Ok, looking at the hibernate source this is what is in the
>> >>> PostgreSQLDialect
>> >>>
>> >>> public String getIdentitySelectString(String table, String column, int
>> >>> type) {
>> >>>  return new StringBuffer().append("select currval('")
>> >>>    .append(table)
>> >>>    .append('_')
>> >>>    .append(column)
>> >>>    .append("_seq')")
>> >>>    .toString();
>> >>>  }
>> >>> }
>> >
>> >> Just as an aside, should that not be using a StringBuilder rather than
>> >> a StringBuffer? Or does Hibernate still explicitly support Java 1.4?
>> >
>> > My Red-Flag-O-Meter triggered on that, too.  What Hibernate ought to do
>> > is:
>> >
>> >  return "select currval('" + table + '_' + column + "_seq')";
>> >
>> > for Pete's sake.
>>
>>
>> Really ??? the last time I checked the above will generate approx 5
>> objects StringBuffer.append is the recommended use for java 1.4,
>> StringBuilder is only marginally faster as it not synchronized.
>>
>> + or concat is much slower as it creates a new object each time and
>> copies the old into the new.
>
> Have just checked with decompiler - It is exactly same if 1 expression with
> multiple pluses is used, one StringBuilder (StringBuffer for J1.4) is
> created, then multiple .append calls, then toString. Explicit StringBuilder
> should be used only in multiple expressions (e.g. in famount str += value in
> for circle). For single expression "+" gives same result, but is more
> readable and will choose StringBuilder/StringBuffer automatically depending
> on java version.
>

Apparently my memory is failing because I used to know that modern
compilers used StringBuffer, apparently they are even better and use
StringBuilder when given the chance. Either way it's moot since it is
hibernate's code.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 29 March 2011 02:05, sdavidr <david.ricoma@gmail.com> wrote:

> Hibernate could receive a lot of numbers from a "returning *" but doesn't
> know what is the correct value. It expects to be the first element, but if
> is not, it fails.

Ok, so this is really a Hibernate bug. Hibernate needs to be fixed to
either request the correct generated column, or to choose the correct
column from the resultset. Of course, working out what the "correct
column" is could be tricky, but at least Hibernate has access to the
application's description of its data model so it's in a much better
position than the driver to do this.

> In another way, method from postgres driver getGeneratedKeys cannot return
> the full table, isn't it?

As Dave said, unless you explicitly ask for certain columns, it gives
you the entire inserted row because trying to determine a smaller set
of "generated" columns requires information that the driver doesn't
have. It's fine from a spec point of view to do this. Also, even if we
could find a smaller set of columns to return, there still might be
more than one and Hibernate would still fail ..

Oliver

Hi,

Thx all for your diagnostic, I just found a solution.

You have to create your own PostgreSQLDialect as

public class PostgreSQLDialect extends
org.hibernate.dialect.PostgreSQLDialect {
    public PostgreSQLDialect() {
        super();
        // tells to hibernate to not use result returned by INSERT .... RETURNING
*

this.getDefaultProperties().setProperty(AvailableSettings.USE_GET_GENERATED_KEYS,
"false");
    }
}

From now you have to solution :
1) let hibernate to do a "select currval ..." query, see
#getIdentitySelectString in org.hibernate.dialect.PostgreSQLDialect

2) if all of your bean have same pk name (ex: id), you can optimize insert
process by adding two methods to your custom Dialect :
    @Override
    public String appendIdentitySelectToInsert(String insertString) {
        return insertString + " RETURNING id";
    }
    @Override
    public boolean supportsInsertSelectIdentity() {
        return true;
    }

PHaroZ

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Problems-with-Hibernate-Discriminators-and-9-0-801-jdbc4-tp4259788p5055944.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.