Postgres Pro
Downloads
Home   >   mailing lists

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

From Shreeyansh Dba
Subject Re: Oracle to Postgres Migration - Duplicate Key Issue
Date
Msg-id CAGDYbUPUEOQz9AgeJEhfow2sYaNVu8W1meJ_kANi3vo7++o3hQ@mail.gmail.com
Whole thread Raw
In response to Oracle to Postgres Migration - Duplicate Key Issue  (Matt Hempleman <mhemple@gmail.com>)
List pgsql-admin
Tree view
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@gmail.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)


pgsql-admin by date:

Previous
From: "drum.lucas@gmail.com"
Date:
Subject: Table size - optimization
Next
From: Marius
Date:
Subject: Re: Table size - optimization