StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
11752742
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
10
CommunityOwnedDate
CreationDate
2012-08-01T04:55:07.727
FavoriteCount
0
LastActivityDate
2012-08-01T23:55:13.693
LastEditDate
2012-08-01T23:55:13.693
LastEditorUserId
398670
OwnerUserId
398670
ParentId
2183932
PostTypeId
2
Score
15
ViewCount
0
LastEditorDisplayName
text
Body
Danger: Your question implies that you may be making a design mistake - you are trying to use a database sequence for a "business" value that is presented to users, in this case invoice numbers. Don't use a sequence if you need to anything more than test the value for equality. It has no order. It has no "distance" from another value. It's just equal, or not equal. Rollback: Sequences are not generally appropriate for such uses because changes to sequences are't rolled back with transaction <code>ROLLBACK</code>. See the footers on <a href="http://www.postgresql.org/docs/9.1/static/functions-sequence.html">functions-sequence</a> and <a href="http://www.postgresql.org/docs/8.1/static/sql-createsequence.html"><code>CREATE SEQUENCE</code></a>. Rollbacks are expected and normal. They occur due to: <ul> <li>deadlocks caused by conflicting update order or other locks between two transactions;</li> <li>optimistic locking rollbacks in Hibernate;</li> <li>transient client errors;</li> <li>server maintenance by the DBA;</li> <li>serialization conflicts in <code>SERIALIZABLE</code> or snapshot isolation transactions</li> </ul> ... and more. Your application will have "holes" in the invoice numbering where those rollbacks occur. Additionally, there is no ordering guarantee, so it's entirely possible that a transaction with a later sequence number will commit earlier (sometimes much earlier) than one with a later number. Chunking: It's also normal for some applications, including Hibernate, to grab more than one value from a sequence at a time and hand them out to transactions internally. That's permissible because you are not supposed to expect sequence-generated values to have any meaningful order or be comparable in any way except for equality. For invoice numbering, you want ordering too, so you won't be at all happy if Hibernate grabs values 5900-5999 and starts handing them out from 5999 counting down or alternately up-then-down, so your invoice numbers go: n, n+1, n+49, n+2, n+48, ... n+50, n+99, n+51, n+98, [n+52 lost to rollback], n+97, .... Yes, the <a href="http://anonsvn.jboss.org/repos/hibernate/core/tags/hibernate-3.3.1.GA/core/src/main/java/org/hibernate/id/SequenceHiLoGenerator.java">high-then-low allocator exists in Hibernate</a>. It doesn't help that unless you define individual <code>@SequenceGenerator</code>s in your mappings, Hibernate likes to share a single sequence for every generated ID, too. Ugly. Correct use: A sequence is only appropriate if you only require the numbering to be unique. If you also need it to be monotonic and ordinal, you should think about using an ordinary table with a counter field via <code>UPDATE ... RETURNING</code> or <code>SELECT ... FOR UPDATE</code> ("pessimistic locking" in Hibernate) or via Hibernate optimistic locking. That way you can guarantee gapless increments without holes or out-of-order entries. What to do instead: Create a table just for a counter. Have a single row in it, and update it as you read it. That'll lock it, preventing other transactions from getting an ID until yours commits. Because it forces all your transactions to operate serially, try to keep transactions that generate invoice IDs short and avoid doing more work in them than you need to. <pre><code>CREATE TABLE invoice_number ( last_invoice_number integer primary key ); -- PostgreSQL specific hack you can use to make -- really sure only one row ever exists CREATE UNIQUE INDEX there_can_be_only_one ON invoice_number( (1) ); -- Start the sequence so the first returned value is 1 INSERT INTO invoice_number(last_invoice_number) VALUES (0); -- To get a number; PostgreSQL specific but cleaner. -- Use as a native query from Hibernate. UPDATE invoice_number SET last_invoice_number = last_invoice_number + 1 RETURNING last_invoice_number; </code></pre> Alternately, you can: <ul> <li>Define an entity for invoice_number, add a <code>@Version</code> column, and let optimistic locking take care of conflicts;</li> <li>Define an entity for invoice_number and use explicit pessimistic locking in Hibernate to do a select ... for update then an update.</li> </ul> All these options will serialize your transactions - either by rolling back conflicts using @Version, or blocking them (locking) until the lock holder commits. Either way, gapless sequences will really slow that area of your application down, so only use gapless sequences when you have to. <code>@GenerationType.TABLE</code>: It's tempting to use <code>@GenerationType.TABLE</code> with a <code>@TableGenerator(initialValue=1, ...)</code>. Unfortunately, while GenerationType.TABLE lets you specify an allocation size via @TableGenerator, it doesn't provide any guarantees about ordering or rollback behaviour. See the JPA 2.0 spec, section 11.1.46, and 11.1.17. In particular "This specification does not define the exact behavior of these strategies. and footnote 102 "Portable applications should not use the GeneratedValue annotation on other persistent fields or properties [than <code>@Id</code> primary keys]". So it is unsafe to use <code>@GenerationType.TABLE</code> for numbering that you require to be gapless or numbering that isn't on a primary key property unless your JPA provider makes more guarantees than the standard. If you're stuck with a sequence: The poster notes that they have existing apps using the DB that use a sequence already, so they're stuck with it. The JPA standard doesn't guarantee that you can use generated columns except on @Id, you can (a) ignore that and go ahead so long as your provider does let you, or (b) do the insert with a default value and re-read from the database. The latter is safer: <pre><code> @Column(name = "inv_seq", insertable=false, updatable=false) public Integer getInvoiceSeq() { return invoiceSeq; } </code></pre> Because of <code>insertable=false</code> the provider won't try to specify a value for the column. You can now set a suitable <code>DEFAULT</code> in the database, like <code>nextval('some_sequence')</code> and it'll be honoured. You might have to re-read the entity from the database with <code>EntityManager.refresh()</code> after persisting it - I'm not sure if the persistence provider will do that for you and I haven't checked the spec or written a demo program. The only downside is that it seems the column can't be made @ NotNull or <code>nullable=false</code>, as the provider doesn't understand that the database has a default for the column. It can still be <code>NOT NULL</code> in the database. If you're lucky your other apps will also use the standard approach of either omitting the sequence column from the <code>INSERT</code>'s column list or explicitly specifying the keyword <code>DEFAULT</code> as the value, instead of calling <code>nextval</code>. It won't be hard to find that out by enabling <code>log_statement = 'all'</code> in <code>postgresql.conf</code> and searching the logs. If they do, then you can actually switch everything to gapless if you decide you need to by replacing your <code>DEFAULT</code> with a <code>BEFORE INSERT ... FOR EACH ROW</code> trigger function that sets <code>NEW.invoice_number</code> from the counter table.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHibernate annotation for PostgreSQL serial type
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USCraig Ringer
UserOwnerUserId
1. USCraig Ringer
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.