Re: Oracle to Postgres Migration - Duplicate Key Issue

Hi Matt ,

ERROR: duplicate key violates unique constraint
Error likely means the primary key sequence in the table you're working
with has somehow become out of sync

Example:

create table test_con(i int primary key);
postgres=# insert into test_con values(nextval('con'));
INSERT 0 1
postgres=# insert into test_con values(2);
INSERT 0 1
postgres=# insert into test_con values(nextval('con'));
ERROR: duplicate key value violates unique constraint "test_con_pkey"
DETAIL: Key (i)=(2) already exists.

In aboe example it is shown that 1st values is inserted with sequence and
2nd value inserted directly with out using sequence. Here position of the
sequence is 2 and if I try to insert 3rd value using sequence then it
will throw an ERROR as already value '2' is present in column.
If the value exists in cloumn which contains primarykey and sequence is out
of sync with column value then this kind of error are possible.

postgres=# SELECT MAX(i) FROM test_con;
max
-----
3
(1 row)

postgres=# SELECT nextval('con');
nextval
---------
3
(1 row)

Reards,
Rajesh Madiwale
Shreeyansh Technologies
www.shreeyansh.com

On Mon, Dec 14, 2015 at 5:57 AM, Matt Hempleman <mhemple(at)gmail(dot)com> wrote:

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