Why does Hibernate execute select currval in persist occasionally?

Question

Sometimes and seems randomly Hibernate executes query like that during persist operation:

select currval('MY_TABLE_NAME_id_seq');

Entity:

@Entity
@Table(name = "MY_TABLE_NAME")
public class MyEntity {

   @Id
   @Column(name = "ID", unique = true, nullable = false)
   @GeneratedValue(strategy=GenerationType.IDENTITY)
   private Long id;

}

Code:

@Transactional
public void persistMyEntity(String name) {
   MyEntity entity= new MyEntity (name);
   sessionFactory.getCurrentSession().persist(entity);
}

Generated sql:

insert into MY_TABLE_NAME(name) values ('xyz');

select currval('MY_TABLE_NAME_id_seq');

But usually select currval is not executed. Is there any explanation about that?

BTW my question is very similar to this but the solution in the question is not worked for me.

Note:

My_TABLE_NAME ddl sql:

CREATE TABLE my_table_name (
   id bigserial NOT NULL,
   name character varying(256) NOT NULL,
   CONSTRAINT my_table_name_id PRIMARY KEY (id)
);

Hibernate properties:

Properties hibernateProperties = new Properties();
hibernateProperties.put("hibernate.dialect", HIBERNATE_DIALECT);
hibernateProperties.put("hibernate.show_sql", HIBERNATE_SHOW_SQL);
hibernateProperties.put("hibernate.hbm2ddl.auto", "none");
hibernateProperties.put("hibernate.connection.release_mode", "auto");
hibernateProperties.put("hibernate.archive.autodetection", ARCHIVE_AUTODETECTION);
hibernateProperties.put("hibernate.format_sql", true);
hibernateProperties.put("hibernate.use_sql_comments", true);
hibernateProperties.put("hibernate.generate_statistics", false);
hibernateProperties.put("hibernate.jdbc.use_scrollable_resultset", true);
hibernateProperties.put("hibernate.jdbc.use_streams_for_binary", true);
hibernateProperties.put("hibernate.jdbc.batch_size", 20);
hibernateProperties.put("hibernate.order_inserts", true);
hibernateProperties.put("hibernate.order_updates", true);
hibernateProperties.put("hibernate.jdbc.batch_versioned_data ", true);
hibernateProperties.put("hibernate.cache.region_prefix", "hibernate.cache");
hibernateProperties.put("hibernate.cache.use_query_cache", false);
hibernateProperties.put("hibernate.cache.use_second_level_cache", false);
sessionFactoryBean.setHibernateProperties(hibernateProperties);

PostgreSQL version: 11.5.
hibernate.dialect:org.hibernate.dialect.PostgreSQL9Dialect
Hibernate version: 5.4.5.Final
PostgreSQL JDBC Driver: postgresql-9.4

Could you please provide the `hibernate.cfg.xml` settings? What hibernate version do you use? — SternK, Mar 02 '20 at 13:01
Could you please also show your table `MY_TABLE_NAME` ddl sql. And what version of PostgreSQL JDBC Driver do you use? — SternK, Mar 02 '20 at 20:42
What value do you use for the property `hibernate.hbm2ddl.auto`? — SternK, Mar 02 '20 at 21:03

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

According to the hibernate documentation:

2.6.10. Using IDENTITY columns

For implementing identifier value generation based on IDENTITY columns, Hibernate makes use of its org.hibernate.id.IdentityGenerator id generator which expects the identifier to be generated by INSERT into the table. IdentityGenerator understands 3 different ways that the INSERT-generated value might be retrieved:

If Hibernate believes the JDBC environment supports java.sql.Statement#getGeneratedKeys, then that approach will be used for extracting the IDENTITY generated keys.

Otherwise, if Dialect#supportsInsertSelectIdentity reports true, Hibernate will use the Dialect specific INSERT+SELECT statement syntax.

Otherwise, Hibernate will expect that the database supports some form of asking for the most recently inserted IDENTITY value via a separate SQL command as indicated by Dialect#getIdentitySelectString

You can specify java.sql.Statement#getGeneratedKeys explicitly in the following way:

<property name="hibernate.jdbc.use_get_generated_keys">true</property>

You do not do it, so, hibernate somehow treat its value as false. Then hibernate checks value of dialect.getIdentityColumnSupport().supportsInsertSelectIdentity(). It is false for your case. And then hibernate follows by the last way. See the implementation of the PostgreSQL81IdentityColumnSupport class. The method getIdentitySelectString generates exactly the same sql that you complained about.

Thanks for your answer. But as far as I understand from hibernate docs `hibernate.jdbc.use_get_generated_keys` is `true` by default. I also observe that usually hibernate gets id without generating `select currval` sql. But raerly `select currval` sql is generated. So can we say that it's a kind of bug of JDBC driver or Hibernate? Is it a known issue? Why is this happening do you have any idea? — H.Ç.T, Mar 06 '20 at 13:11
I do not see from hibernate docs that `hibernate.jdbc.use_get_generated_keys` is `true` by default. I see that `By default, it tries to detect the driver capabilities from connection metadata.`. I tried to reproduce your problem by using exactly your version of jdbc driver, but without success. Maybe it is somehow related to the environment your application running in. — SternK, Mar 06 '20 at 13:32

Why does Hibernate execute select currval in persist occasionally?

1 Answers1

Linked