When hibernate.hbm2ddl.auto=update is specified, EntityManager creation fails when select * from information_schema.sequences is executed during the validation phase, which fails with ERROR: relation 'information_schema.sequences' does not exist. This behavior is expected of Cloud Spanner because Spanner does not support sequences. However the Postgres Hibernate dialect validates sequences prior to creating an EntityManager.
This is a problem because it causes the developer to face the following decision:
Use the Spanner Hibernate Dialect, which locks the developer in to a proprietary dialect, and prevents the corresponding entities from being used with Postgres. (This defeats the whole purpose of Cloud Spanner offering postgres-dialect databases in the first place.)
Manually maintain schema. (This defeats a large benefit of using Hibernate ORM in the first place. Maintaining both an entity hierarchy and a table hierarchy is redundant, duplicated effort. Having both a SQL schema and an entity class hierarchy is not DRY.)
Adopt only create and create-drop strategies, which blow away data, are risky, and can't be run every time the application starts, thus requiring different start-up modes for the application. Ultimately this is tantamount to 2.
Not using Spanner.
All of these choices should be considered suboptimal.
Proposed solution
The information_schema.sequences relation should return successfully and empty from either pgadapter or from postgres-dialect Spanner itself. Attempts to modify that relation should error out. This alone may not solve all issues related to hibernate.hbm2ddl.auto=update, but it is the observed immediate blocking issue.
Steps to reproduce
Configure JPA thusly...
@EnableTransactionManagement
public class ConfigJPA {
@Bean
@Primary
public DataSource getDataSource() {
return DataSourceBuilder.create()
.driverClassName("org.postgresql.Driver")
.url("jdbc:postgresql:" + System.getenv("PGADAPTER_ENDPOINT"))
.build();
}
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(getDataSource());
em.setPackagesToScan("com.mypackages");
JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
Properties properties = new Properties();
properties.setProperty("hibernate.hbm2ddl.auto", "update");
properties.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
em.setJpaProperties(properties);
return em;
}
Start your application
Observe crash.
(note: Even if you do not configure any sequence properties on your entities, the crash is still observable.)
This is less an "issue" than a "perf", and probably should be assigned to a Cloud Spanner ticket tracker, so if you've got any pointers to the correct place to submit it, they would be greatly appreciated! This is follow-up to https://stackoverflow.com/questions/75603286/does-cloud-spanner-actually-work-with-hibernate-ddl-using-postgresql-dialect
Background
Per https://github.com/GoogleCloudPlatform/pgadapter/tree/postgresql-dialect/samples/java/hibernate , hibernate ddl is generally not recommended for Postgres dialect databases, and apparently only "
create
" and "create-drop
" strategies forhibernate.hbm2ddl.auto
are supported.Issue
When
hibernate.hbm2ddl.auto=update
is specified, EntityManager creation fails whenselect * from information_schema.sequences
is executed during the validation phase, which fails withERROR: relation 'information_schema.sequences' does not exist
. This behavior is expected of Cloud Spanner because Spanner does not support sequences. However the Postgres Hibernate dialect validates sequences prior to creating an EntityManager.This is a problem because it causes the developer to face the following decision:
Use the Spanner Hibernate Dialect, which locks the developer in to a proprietary dialect, and prevents the corresponding entities from being used with Postgres. (This defeats the whole purpose of Cloud Spanner offering postgres-dialect databases in the first place.)
Manually maintain schema. (This defeats a large benefit of using Hibernate ORM in the first place. Maintaining both an entity hierarchy and a table hierarchy is redundant, duplicated effort. Having both a SQL schema and an entity class hierarchy is not DRY.)
Adopt only
create
andcreate-drop
strategies, which blow away data, are risky, and can't be run every time the application starts, thus requiring different start-up modes for the application. Ultimately this is tantamount to 2.Not using Spanner.
All of these choices should be considered suboptimal.
Proposed solution
The
information_schema.sequences
relation should return successfully and empty from either pgadapter or from postgres-dialect Spanner itself. Attempts to modify that relation should error out. This alone may not solve all issues related tohibernate.hbm2ddl.auto=update
, but it is the observed immediate blocking issue.Steps to reproduce