nextVal from PostgreSQL sequence in Hibernate fetch sequence multiple times

Question

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?

Josf · Accepted Answer · 2019-05-29 05:14:09Z

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

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

teknopaul · Accepted Answer · 2022-08-29 18:58:10Z

0

Hibernate caches query results.

Notice the "as num", looks like hibernate is altering the SQL as well.

You should be able to tell Hibernate not to cache results with

query.setCacheable(false);

answered Aug 29, 2022 at 18:58

teknopaul

6,8152 gold badges33 silver badges27 bronze badges

Collectives™ on Stack Overflow

nextVal from PostgreSQL sequence in Hibernate fetch sequence multiple times

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related