Issue
For business logic we need to update a record with the next value from a sequence defined directly in database and not in Hibernate (because is not always applied on insert / updates)
For this purpose we have a sequence defined in PostgreSQL witch DDL is:
CREATE SEQUENCE public.facturaproveedor_numeracionperiodofiscal_seq
INCREMENT 1 MINVALUE 1
MAXVALUE 9223372036854775807 START 1
CACHE 1;
Then in DAO, when some conditions are true, we get the nextVal via:
Query query = sesion.createSQLQuery("SELECT nextval('facturaproveedor_numeracionperiodofiscal_seq')");
Long siguiente = ((BigInteger) query.uniqueResult()).longValue();
But the values asigned aren't consecutive. Looking the Hibernate output log we see four fetchs to the sequence in the same transaction:
Hibernate: SELECT nextval('facturaproveedor_numeracionperiodofiscal_seq') as num
Hibernate: SELECT nextval('facturaproveedor_numeracionperiodofiscal_seq') as num
Hibernate: SELECT nextval('facturaproveedor_numeracionperiodofiscal_seq') as num
Hibernate: SELECT nextval('facturaproveedor_numeracionperiodofiscal_seq') as num
Why is this happening? Is this for catching purposes? There is a way to disable this? Or this workaround is not correct?
Solution
Hibernate won't usually generate standalone nextval calls that look like that, so I won't be too surprised if it's your application doing the multiple fetches. You'll need to collect more tracing information to be sure.
I think you may have a bigger problem though. If you care about sequences skipping values or leaving holes then you're using the wrong tool for the job, you should be using a counter in a table that you UPDATE, probably UPDATE my_id_generator SET id = id + 1 RETURNING id. This locks out concurrent transactions and also ensures that if the transaction rolls back, the update is undone.
Sequences by contrast operate in parallel, which means that it's impossible to roll back a sequence increment when a transaction rolls back (see the PostgreSQL documentation). So they're generally not suitable for accounting purposes like invoice numbering and other things where you require a gapless sequence.
For other readers who don't have the specific requirement to only sometimes generate a value: don't generate the sequence values manually; use a @GeneratedValue annotation.
In Hibernate 3.6 and newer, you should set hibernate.id.new_generator_mappings in your Hibernate properties.
Assuming you're mapping a generated key from a PostgreSQL SERIAL column, use the mapping:
@Id
@SequenceGenerator(name="mytable_id_seq",sequenceName="mytable_id_seq", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="mytable_id_seq")
If you omit the allocationSize then Hibernate assumes it's bizarre default of 50 and fails to check that the sequence actually increments by 50 so it tries to insert already-used IDs and fails.
Hibernate/JPA isn't able to automatically create a value for your non-id-properties. The @GeneratedValue annotation is only used in conjunction with @Id to create auto-numbering
Answered By - Craig Ringer
Answer Checked By - Katrina (JavaFixing Volunteer)
