GoogleCloudPlatform / GoogleCloudPlatform/pgadapter
hibernate.hbm2ddl.auto=update isn't supported, but it really should be.
- Dominant language
- Java
- Stars
- 91
- Forks
- 33
- Avg merge
- 3d 57m
- Merged PRs (30d)
- 58
Description
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 for `hibernate.hbm2ddl.auto` are supported.
### Issue
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:
1. 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.)
2. 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.)
3. 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.
4. 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
1. 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;
}
```
2. Start your application
3. Observe crash.
(note: Even if you do not configure any sequence properties on your entities, the crash is still observable.)
Contributor guide
Assessment
This issue has not been assessed yet.