|
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?
|
