hibernate.hbm2ddl.auto value="update" on PostgresSQL 9.4(x86) DB issue with Hibenate 5.1 & Wildfly 10.1.0

[parag.goyal]

I have an application presently running (without issue) on Wildfly 8.2.1 Final & PostgresSQL 9.4(x86), now we are trying to upgrade our application to WildFly 10.1.0 (Final) with PostgresSQL 9.4(x86). Most of this upgrade is going well but the upgrade from Hibernate 4.3.7 (on Wildfly 8.2.1) to Hibernate 5.0.1 (on WildFly 10.1) is causing an issue.

Specifically, in persistence.xml we have below property and DB is PostgresSQL 9.4(x86).

<property name="hibernate.hbm2ddl.auto" value="update"/>

NOTE: We have not done any schema change or other DB changes with this upgrade and also pointing to same database instance on PostgresSQL 9.4(x86) which was successfully running with Wildfly 8.2.1 instance. Seems <property name="hibernate.hbm2ddl.auto" value="update"/> is behaving like <property name="hibernate.hbm2ddl.auto" value="create"/> and It is trying to create tables which are actually already exists.

i.e. 1) Erroneously it determines that it needs to create new relation(s).

2) Fail to do above step.

Following is stack trace:

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: cms_layoutmgt] Unable to build Hibernate SessionFactory

  Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Unable to execute schema management to JDBC target [create table CMS_LAYM_APPLICATIONS (ID serial not null, mCommandLineArg varchar(255), mDescription varchar(4000), mHandleByCaption boolean NOT NULL DEFAULT TRUE, mName varchar(255), mPath varchar(255), mTags varchar(2000), mWindowTitle varchar(255), mWorkingDir varchar(255), Display_Id int4, primary key (ID))]

  Caused by: org.postgresql.util.PSQLException: ERROR: relation \\\"cms_laym_applications\\\" already exists\",

Kindly provide solution for above issue, thanks in advance.

[mayerw01]

I'd suggest to use

<property name="javax.persistence.schema-generation.database.action" value="none"/>

[parag.goyal]

I can't use <property name="javax.persistence.schema-generation.database.action" value="none"/> because "none" does not do any thing.

Actually, our requirements is schema/table needs to created if any of them does not already exist, in case table/schema already exists then these should remain intact with data.

As per JPA documentation above should be achievable by <property name="javax.persistence.schema-generation.database.action" value="create"/> or by <property name="hibernate.hbm2ddl.auto" value="update"/>.  I tried both options but both are behaving same ways.                                                                                               We were achieving above functionality <property name="hibernate.hbm2ddl.auto" value="update"/> till Wildfly 8.2.1 (even in lower version of JBoss), I am not sure this functionality is broken from PostgreSQL's Driver side or Dialect side.

My problem is still unresolved. Please let me know if you have some solution.

[mayerw01]

I would recommend to use 'hibernate.hbm2ddl.auto' only for test purposes. For production I'd rather use the standard persistence approach.

The standard Hibernate behaviour when using 'create' is to create the tables  unconditionally which may produce errors if the table already exists.

I guess the reason is that although Postgres supports 'IfExistsAfterTableName' this feature is disabled in the Dialect (maybe you could try your own version with this feature enabled).

Therefore you should use your own create script which checks this.

When using Postgres this should be simple since you may use statements like

CREATE TABLE IF NOT EXISTS ...

The entries in your persistence.xml may then look like:

<property name="javax.persistence.schema-generation.database.action" value= "create"/>

<property name="javax.persistence.schema-generation.create-script-source" value= "{path to your create.sq}l"/>