GoogleCloudPlatform / GoogleCloudPlatform/pgadapter

hibernate.hbm2ddl.auto=update isn't supported, but it really should be.

Open
#705 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.