-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 
Author Message
 Post subject: Generating id in PostgreSQL
PostPosted: Wed Jan 28, 2009 11:10 am 
Newbie

Joined: Wed Jan 28, 2009 9:46 am
Posts: 10
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hi All,

Hibernate version: 3.3.1
Hibernate annotation version: 3.4.0
Hibernate tools version: 3.2.4.beta1
Java Version: 1.5

Name and version of the database you are using: postgresql 8.3.5-2

I am writing an application that needs to support multiple databases. I am trying to add support for PostgreSQL, however I have run into a problem. I am also using Hibernate tools ant task hbm2dll to generate all my db schema's. I am using annotations to map my classes to tables (see below)

Code:
...
@Entity
@AccessType("field")
@Table(name=Country.TABLE_COUNTRY)
public class Country implements Comparable<Country>
{
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    @Column(unique=true,nullable=false,length=7)
    @Enumerated(EnumType.STRING)
    private CountryCode code;
    @Column(nullable=false)
    private String name;
   
    public static final String TABLE_COUNTRY = "country";
    ...
}


This results in the following schema definition:

Code:
...
    create table country (
        id int8 not null,
        code varchar(7) not null unique,
        name varchar(255) not null,
        primary key (id)
    );
...
    create sequence hibernate_sequence;
...


However what I need to be generated is the following. Note the type of the id field and the individual sequences:

Code:
...
    create table country (
        id bigserial not null,
        code varchar(7) not null unique,
        name varchar(255) not null,
        primary key (id)
    );
...
    create sequence country_id_seq;
...


I have solved the individual sequences per table using the idea's suggested here:
http://www.hibernate.org/296.html

But I notice that the default native Id generator class is SequenceGenerator. This works fine for me when I have manually defined my id column to be a serial or bigserial in PostgreSQL. However when using hbm2ddl, the only generator that creates a field as serial/bigserial is the IdentityGenerator. If I explicitly set that as my generation strategy then I get errors later on. Also I think the sequnce generator works well with PostgreSQL and I dont really want to deviate from that if possible.

So finally my questions.

Is the behaviour described above intentional?
Is there a way to use the native Id generator for Postgres (i.e. SequenceGenerator) and be able to generate my schema ddl where my id fields are of type serial/bigserial?
Is there a way I can specify the name of the sequence for my id fields?

Cheers,
Ben


Last edited by bjsion on Fri Mar 06, 2009 2:51 am, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 20, 2009 2:29 am 
Newbie

Joined: Wed Jan 28, 2009 9:46 am
Posts: 10
In case anyone is interested. After looking at the Postgres documentation http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL, I ended up resolving my problem by manually creating my schema definition for PostreSQL.

Code:
  create sequence country_id_seq;

  create table country (
        country_id int8 not null default nextval('country_id_seq'),
        code varchar(7) not null unique,
        name varchar(255) not null,
        primary key (id)
    );
...
...


N.B. I renamed my id column to "country_id" from "id" and used my own defined sequence above not the default name (which would be country_country_id_seq).

This will do for now, but I think the that hbm2ddl should be able to generate a serial/bigserial column type for Postgres DB when using the default (SEQUENCE) id generation or at least generate an id column definition like so:
Code:
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')

I would be keen to know why this is not the case. I could not figure it out when I quickly tried to trace through the code and if some one could explain to me I would appreciate it.


Last edited by bjsion on Fri Mar 06, 2009 3:14 am, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 05, 2009 4:19 pm 
Regular
Regular

Joined: Tue Feb 17, 2009 3:36 pm
Posts: 78
I have been using the following approach for PostgreSQL without any problem.

Quote:
<class name="User" table="bm_user">
<id name="id" type="int" unsaved-value="null" >
<generator class="sequence">
<param name="sequence">bm_user_id_seq</param>
</generator>
</id>
...

where the bm_user_id_seq is the sequence automatically generated by PostgreSQL when you have a table as
Quote:
CREATE TABLE bm_user (
id SERIAL UNIQUE primary key,
...);


I used to create a sequence separately and found the approach could lead to some problems later on.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 05, 2009 4:40 pm 
Newbie

Joined: Thu Mar 05, 2009 3:56 pm
Posts: 10
One question which I would like to ask is :
Code:
@Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

Does this generate random unique ID?

I need to generate random unique Ids but as till now what I saw, only SequenceGenerator is there in hibernate through which I can get sequential IDs. IS there any way to get random Ids? I can use
Code:
RandomUtils.getUUID()
sort of function to generate random ids my self, but that will be of 16 characters. Can I get random unique numbers of around 10 digits using some generator in hibernate.

Iam using Java, Hibernate, Sql Server 2k8.

Thanks


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 05, 2009 5:23 pm 
Regular
Regular

Joined: Tue Feb 17, 2009 3:36 pm
Posts: 78
satyendra_411 wrote:
One question which I would like to ask is :
Code:
@Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

Does this generate random unique ID?

I need to generate random unique Ids but as till now what I saw, only SequenceGenerator is there in hibernate through which I can get sequential IDs. IS there any way to get random Ids? I can use
Code:
RandomUtils.getUUID()
sort of function to generate random ids my self, but that will be of 16 characters. Can I get random unique numbers of around 10 digits using some generator in hibernate.

Iam using Java, Hibernate, Sql Server 2k8.

Thanks


As its name indicated, a sequence generate a sequence of numbers, which is not a random value. To my knowledge, any DB built-in mechanism doesn't generate a random value.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 06, 2009 12:08 am 
Newbie

Joined: Wed Jan 28, 2009 9:46 am
Posts: 10
Hi wei725,

What version of Hibernate and Hibernate tools are using? Are you using the hbm2ddl task to generate your schema? Which Dialect are you using or is it a custom Dialect?

I am using an id generation strategy of "auto", as I need to support multiple DB types, not just PostgreSQL. That being said, from my investigation into the Hibernate code, the default generation strategy for PostgreSQL (PostgreSQLDialect) is "sequence". From what I can tell, the only time an id will be generated with a type of "serial" is if the generation type is "identifier". A type of "sequence" will result in the following output:

Code:
id int8 not null


Please correct if I am wrong.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 06, 2009 2:05 am 
Regular
Regular

Joined: Tue Feb 17, 2009 3:36 pm
Posts: 78
I have been using Hibernate since the 3.0 and don't use any Hibernate tools at all. I use the Spring in conjugation with Hibernate.

I don't think that you can use a single set of Hibernate configuration for various DBs. Primary key generation is one of reasons. There are two schools of primary key (or the entity ID in the object world) generation approaches.

If you want to have a primary key in random value, you might not want to use the DB primary key generation since it would not give you a random set of values. To keep your id unique, you need to use the unique constraint.

BTW, the above can be learned by yourself from related documents.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 06, 2009 1:15 pm 
Regular
Regular

Joined: Tue Feb 17, 2009 3:36 pm
Posts: 78
wei725 wrote:
I have been using Hibernate since the 3.0 and don't use any Hibernate tools at all. I use the Spring in conjugation with Hibernate.

I don't think that you can use a single set of Hibernate configuration for various DBs. Primary key generation is one of reasons. There are two schools of primary key (or the entity ID in the object world) generation approaches.

If you want to have a primary key in random value, you might not want to use the DB primary key generation since it would not give you a random set of values. To keep your id unique, you need to use the unique constraint.

BTW, the above can be learned by yourself from related documents.


Please rate if it helps.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 09, 2009 2:48 am 
Regular
Regular

Joined: Tue Feb 17, 2009 3:36 pm
Posts: 78
It seems that I wasted my time here.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 09, 2009 4:47 am 
Newbie

Joined: Wed Jan 28, 2009 9:46 am
Posts: 10
Actually you have wasted everyone's time here.

I didn't want to get into this, and at the risk of being rude, but at best you didn't read or understand my question at worst I dont think you understand how hibernate works

Quote:
I don't think that you can use a single set of Hibernate configuration for various DBs. Primary key generation is one of reasons. There are two schools of primary key (or the entity ID in the object world) generation approaches.


Actually I think that is kinda the point of hibernate. To abstract away configuration and connection details used to interact with various databases. Such that if I wanted to support multiple database's I could, without having to recompile my code. Thus the point of delegating the id generation to the native type for that database would be that I don't need to care what that particular database uses for unique identifier generation.

(For anyone still following this thread) From what I have observed, if I use the native id generation for the PostgreSQL dialect (defaults to sequence) then i do not get a sequence associated with the id for that table when I try to generate my schema using hbm2dll, which I believe is not the correct behavior. But this is probably a question for the hibernate developers.

Note that I am generating my schema using hbm2dll (as stated in my original post), something you stated you weren't even using:

Quote:
I have been using Hibernate since the 3.0 and don't use any Hibernate tools at all. I use the Spring in conjugation with Hibernate.


So how you could claim in your original response to get auto schema generation to create a data type of "SERIAL" for an id field is a little mystifying.

Also I have never said I want to randomize the id. It goes against the idea of using a sequence. I think some other guy hijacked this thread to ask about randomizing the id.

Lastly, telling me go read the manual, i found a little a little ironic (especially since I actually included links to bits of the manual I researched and I mentioned that i have attempted to trace through the code). I am quite keen to discuss the technology, but to be honest it just seems like you are trolling.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 13, 2009 1:07 am 
Regular
Regular

Joined: Tue Feb 17, 2009 3:36 pm
Posts: 78
bjsion wrote:
...
So how you could claim in your original response to get auto schema generation to create a data type of "SERIAL" for an id field is a little mystifying.
...


How so?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 13, 2009 7:06 am 
Newbie

Joined: Wed Jan 28, 2009 9:46 am
Posts: 10
You mentioned that you were not using hibernate tools, of which the hbm2ddl task is a component. This task is used to auto generate a DB schema based off your Hibernate mappings (either xml or annotations). So I am curious as to how you are able to generate your schema from the hibernate mappings if, as you stated you were not using the hbm2dll task? In particular, I am curious as to how you were able to generate a schema definition where the id column was of type 'SERIAL'. Are you hand crafting your schema? or does your DB already exist? Or are you using some other tool to generate your schema?

Because based on what I have seen it is not possible to use a sequence id generator to get a definition of :

Code:
id bigserial not null

which in PostgreSQL is syntactically equivalent to (see the reference to the postgres docs i mentioned previously)
Code:
id int8 not null default nextval('table_id_seq')


when using the PostgreSQLDialect.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 13, 2009 12:52 pm 
Regular
Regular

Joined: Tue Feb 17, 2009 3:36 pm
Posts: 78
bjsion wrote:
You mentioned that you were not using hibernate tools, of which the hbm2ddl task is a component. This task is used to auto generate a DB schema based off your Hibernate mappings (either xml or annotations). So I am curious as to how you are able to generate your schema from the hibernate mappings if, as you stated you were not using the hbm2dll task? In particular, I am curious as to how you were able to generate a schema definition where the id column was of type 'SERIAL'. Are you hand crafting your schema? or does your DB already exist? Or are you using some other tool to generate your schema?

Because based on what I have seen it is not possible to use a sequence id generator to get a definition of :

Code:
id bigserial not null

which in PostgreSQL is syntactically equivalent to (see the reference to the postgres docs i mentioned previously)
Code:
id int8 not null default nextval('table_id_seq')


when using the PostgreSQLDialect.


I should not jump into this question at all in the first place since I don't use hbm2dll. In the environment of a Spring-Hibernate combination, DB schema need to be hand written for some reason at least from the demo samples. That might resolve the "mystery". Sorry for misread the hijacker's response as yours.
BTW, I have read that these are some issues with the big int data type in PostgreSQL. Please let me know whether it works for you or not.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.