Oracle to Postgres Migration - Duplicate Key Issue - Mailing list pgsql-admin

Hi all,

I’m in the process of migrating a web application from Oracle to PostgreSQL.  After a few headaches, I’m almost there but one pesky issue is blocking me.  When the application first deploys to JBoss, it builds its environment in the Postgres db via hibernate.  I am getting intermittent duplicate key errors (see example trace below) for auto-generated hibernate keys during this process.  About half the time, the environment loads without issue and, when there is an error, it is always at a different point in the process.  Can anyone think of any differences between the two dbs (Postgres and Oracle) that could lead to this behavior?  Thanks.

Additional information;

Wildfly 8.2

Spring 4.1

PostgreSQL 9.4

Hibernate 3.64

11:12:39.121 [Admin #3] WARN  o.h.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 23505
11:12:39.121 [Local Derivation #0] INFO  c.e.d.LocalDerivationExecutor - Queueing execution for derivation [Phasor [12/31-12/30] (12/10 11:12) 1072]
2015-12-10 11:12:39,121 INFO  [stdout] (Admin #3) 11:12:39.121 [Admin #3]        WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 23505

11:12:39.121 [Admin #3] ERROR o.h.util.JDBCExceptionReporter - Batch entry 0 insert into MessageVariable (elementType, name, type, message_id, parameterPosition, serializedValue, id) values (NULL, 'Parameter0', 'java.lang.String', 752, 0, 'DerivedModeCentricPDX1', 753) was aborted.  Call getNextException to see the cause.
2015-12-10 11:12:39,121 INFO  [stdout] (Local Derivation #0) 11:12:39.121 [Local Derivation #0] INFO  com.etse.derivation.LocalDerivationExecutor - {"key":"persistence.info.1277132700","arguments":["Phasor [12/31-12/30] (12/10 11:12) 1072"]}

2015-12-10 11:12:39,121 INFO  [stdout] (Admin #3) 11:12:39.121 [Admin #3]        ERROR org.hibernate.util.JDBCExceptionReporter - Batch entry 0 insert into MessageVariable (elementType, name, type, message_id, parameterPosition, serializedValue, id) values (NULL, 'Parameter0', 'java.lang.String', 752, 0, 'DerivedModeCentricPDX1', 753) was aborted.  Call getNextException to see the cause.

11:12:39.121 [Local Derivation #0] INFO  c.e.j.service.BpmDerivationService - Setting status to [DEPENDENCY_WAIT] for derivation [Phasor [12/31-12/30] (12/10 11:12) 1072]
11:12:39.121 [Admin #3] WARN  o.h.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 23505
2015-12-10 11:12:39,121 INFO  [stdout] (Local Derivation #0) 11:12:39.121 [Local Derivation #0] INFO  com.etse.jbpm.service.BpmDerivationService - Setting status to [DEPENDENCY_WAIT] for derivation [Phasor [12/31-12/30] (12/10 11:12) 1072]

2015-12-10 11:12:39,121 INFO  [stdout] (Admin #3) 11:12:39.121 [Admin #3]        WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 23505

11:12:39.121 [Local Derivation #7] INFO  c.e.d.LocalDerivationExecutor - Executing local derivation [Phasor [12/31-12/30] (12/10 11:12) 1072]
11:12:39.121 [Admin #3] ERROR o.h.util.JDBCExceptionReporter - ERROR: duplicate key value violates unique constraint "messagevariable_pkey"
  Detail: Key (id)=(753) already exists.
2015-12-10 11:12:39,121 INFO  [stdout] (Local Derivation #7) 11:12:39.121 [Local Derivation #7] INFO  com.etse.derivation.LocalDerivationExecutor - {"key":"persistence.info.882617819","arguments":["Phasor [12/31-12/30] (12/10 11:12) 1072"]}

2015-12-10 11:12:39,121 INFO  [stdout] (Admin #3) 11:12:39.121 [Admin #3]        ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: duplicate key value violates unique constraint "messagevariable_pkey"
2015-12-10 11:12:39,121 INFO  [stdout] (Admin #3)   Detail: Key (id)=(753) already exists. 11:12:39.131 [Admin #3] ERROR c.e.c.l.p.i.LogPersistenceServiceImpl - Error persisting log message : [BaseLogPersistenceRecord [message=Loading process definition for [DeriveDeterminant] from [processes/DeriveDeterminant/DeriveDeterminant.jpdl.xml], mdcProperties=null, className=com.etp.bpm.persistence.validation.AuditableProcessDefinitionPreprocessor, methodName=process, lineNumber=36, hostName=SWE-222331-A, componentName=null, threadName=MSC service thread 1-4, timeLogged=2015-12-10T11:12:34.258-08:00, exception=null, parentLogId=null, severity=INFO, marker=null, hierarchyNodeId=null, username=Anonymous]]
java.sql.BatchUpdateException: Batch entry 0 insert into MessageVariable (elementType, name, type, message_id, parameterPosition, serializedValue, id) values (NULL, 'Parameter0', 'java.lang.String', 752, 0, 'DerivedModeCentricPDX1', 753) was aborted.  Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2743)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1928)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
    at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeBatch(CachedPreparedStatement.java:712)
    at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1077)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
    at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114)
    at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
    at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2412)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2875)
    at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:64)
    at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:1185)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1709)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:369)
    at com.etp.commons.logging.persistence.impl.LogPersistenceServiceImpl.getMessageDefinition(LogPersistenceServiceImpl.java:490)
    at com.etp.commons.logging.persistence.impl.LogPersistenceServiceImpl.getMessage(LogPersistenceServiceImpl.java:347)
    at com.etp.commons.logging.persistence.impl.LogPersistenceServiceImpl.saveMessages(LogPersistenceServiceImpl.java:238)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201)
    at com.sun.proxy.$Proxy116.saveMessages(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:68)
    at com.etse.security.SystemUserPreAuthenticatingInterceptor.invoke(SystemUserPreAuthenticatingInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy117.saveMessages(Unknown Source)
    at com.etp.commons.logging.persistence.impl.LogPersistenceFlushProcessorImpl.processLogs(LogPersistenceFlushProcessorImpl.java:30)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)