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

[dcaudell]

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.)

[olavloite]

Reopened, as the addition of information_schema.sequences not necessarily is all that is needed for this.