|
Hello,
We are in the progress of migrating from JDBC to Hibernate/JPA, as such we are using hibernate.hbm2ddl.auto="validate" to ensure our entities match the pre-existing database schema.
In our database we generally create columns with the "serial" data type (i.e. int4 with a sequence), which generally creates sequences with a name in this format; [tablename]_[columnname]_seq.
E.g. :
{{COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE DATA_DEFAULT ----------------------------------- --------- ----------- -------------- ---------- -------- ----------------------------------- id int4 NO nextval('country_id_seq'::regclass) YES name varchar 128 }}
In our JPA entity class, we have annotated this column like so: {{@Id @SequenceGenerator(name = "country_id_seq", sequenceName = "country_id_seq", allocationSize = 1) @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="country_id_seq") @Column(name = "id", updatable = false) public int getId() { return id; }
}}
However, when deploying our application with hibernate.hbm2ddl.auto="validate", it fails to validate correctly:
{{ org.hibernate.HibernateException: Missing sequence or table: `country_id_seq` at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1352) at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:175) at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:525) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1859) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:857) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:850) at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.withTccl(ClassLoaderServiceImpl.java:425) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:849) at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:75) at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:54) at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55) }}
When debugging the postgres logs, it seems the following query gets run for the validation:
{{ SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, c.relkind, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c' THEN 'TYPE' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') WHERE c.relnamespace = n.oid AND c.relname LIKE 'country_id_seq' AND (false OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) OR ( c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ) ) ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME }}
(formatted)
This query looks completely incorrect to me, as sequences have a "pg_class.relkind" of "S", yet the validation query for sequences filters by the "r" and "v" relkinds?
|