[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-611) ID generator using Oracle-style sequences with increment

[Chris Wilson (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-611?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=34442#action_34442 ] 

Chris Wilson commented on HHH-611:
----------------------------------

This seems to be a problem with Postgres as well. The docs say:

"sequence: uses a sequence in DB2, PostgreSQL, Oracle, SAP DB, McKoi or a generator in Interbase. The returned identifier is of type long, short or int"

which I feel is a bit misleading. Although it creates a sequence, it does not use the type SERIAL for the column definition, so it gets:

ritatest=# \d sequence_test
 Table "public.sequence_test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "sequence_test_pkey" PRIMARY KEY, btree (id)

and Hibernate has to hit the database a second time to retrieve the primary key. The docs imply (by omission) that the "identity" generator would not work on Postgres, but it does (http://opensource.atlassian.com/projects/hibernate/browse/HHH-1675).

The "native" strategy chooses "sequence" in postgres rather than "identity", which I think would be a much better choice given that "identity" automatically assigns IDs and "sequence" does not.

> ID generator using Oracle-style sequences with increment
> --------------------------------------------------------
>
>                 Key: HHH-611
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-611
>             Project: Hibernate Core
>          Issue Type: Patch
>    Affects Versions: 3.0.5
>         Environment: Hibernate 3, Oracle
>            Reporter: Binil Thomas
>            Assignee: Steve Ebersole
>            Priority: Minor
>         Attachments: caching-sequence-test.patch, caching-sequence.patch
>
>
> The org.hibernate.id.SequenceGenerator can be used to generate id's based on Oracle-style database sequences. But in its current form, this id generator does not use 'increment' option. It will hit the DB everytime it is used.
> A more efficient implementation is the org.hibernate.id.SequenceHiLoGenerator. This does not go to the DB often, but the id's generated by it can clash with those generated by other applications if they follow the DB sequence.
> In our applications, we have an Oracle sequence with increment 50 for each table - we would want all inserts into a table to pick the ID from the corresponding sequence.
> I am submitting a patch with an ID generator which accomplishes this.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira