View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004823 | SymmetricDS | Bug | public | 2021-02-10 19:14 | 2021-02-25 21:33 |
| Reporter | elong | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.12.0 | ||||
| Target Version | 3.12.7 | Fixed in Version | 3.12.7 | ||
| Summary | 0004823: Postgres unique constraint causes current transaction is aborted | ||||
| Description | On Postgres 9.4 and older when encountering a unique key violation on a table, it can get "transaction is aborted" without being able to resolve the conflict. The table has both a primary key on one column and a unique key constraint on another column. Postgres 9.5 and newer does not have the problem because it uses "on conflict do nothing" and handles the case differently. | ||||
| Steps To Reproduce | - Setup: create table mytest (id integer primary, name varchar(50) unique); insert into mytest values (1, 'One'); insert into mytest values (2, 'Two'); - Get both nodes in sync - Turn off push jobs - Update node1: insert into mytest values (3, 'Three') - Update node2: insert into mytest values (30, 'Three') - On both nodes, ignore the batches - Queue up load for mytest table from node1 to node2 - Turn on push jobs | ||||
| Additional Information | ERROR [node-1] [ManageIncomingBatchListener] [node-1-dataloader-4] Failed to load batch 2-832 org.jumpmind.db.sql.SqlException: ERROR: current transaction is aborted, commands ignored until end of transaction block at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:313) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:398) at org.jumpmind.db.sql.JdbcSqlTransaction.execute(JdbcSqlTransaction.java:300) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriterConflictResolver.doInTransaction(DefaultDatabaseWriterConflictResolver.java:628) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriterConflictResolver.queryForInt(DefaultDatabaseWriterConflictResolver.java:597) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriterConflictResolver.checkForUniqueKeyViolation(DefaultDatabaseWriterConflictResolver.java:363) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.performChainedFallbackForInsert(AbstractDatabaseWriterConflictResolver.java:187) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.needsResolved(AbstractDatabaseWriterConflictResolver.java:67) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:214) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:202) at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:212) at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:178) at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1086) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1062) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:307) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:300) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:396) ... 20 more Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "name_uq" Detail: Key (name)=(mytest) already exists. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:485) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:463) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1054) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:255) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:194) ... 13 more | ||||
| Tags | conflict manager | ||||
|
SymmetricDS: 3.12 3d76cf09 2021-02-10 19:16:03 Details Diff |
0004823: Postgres unique constraint causes current transaction is aborted |
Affected Issues 0004823 |
|
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/load/DefaultDataLoaderFactory.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/AcknowledgeService.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/ManageIncomingBatchListener.java | Diff File | ||
| mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriter.java | Diff File | ||
|
SymmetricDS: 3.12 a432c6ca 2021-02-17 17:30:19 Details Diff |
0004823: Postgres unique constraint causes current transaction is aborted |
Affected Issues 0004823 |
|
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDmlStatement.java | Diff File | ||
| mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriter.java | Diff File | ||
|
SymmetricDS: 3.12 45ce5316 2021-02-22 18:59:56 Details Diff |
0004823: Postgres unique constraint causes current transaction is aborted |
Affected Issues 0004823 |
|
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/load/DefaultDataLoaderFactory.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/IDataLoaderService.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataLoaderService.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/IncomingBatchService.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/ManageIncomingBatchListener.java | Diff File | ||
| mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/AbstractDatabaseWriter.java | Diff File | ||
| mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/AbstractDatabaseWriterConflictResolver.java | Diff File | ||
| mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriter.java | Diff File | ||
|
SymmetricDS: 3.12 fabcc865 2021-02-25 13:14:20 Details Diff |
0004823: Postgres unique constraint causes current transaction is aborted |
Affected Issues 0004823 |
|
| mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriterConflictResolver.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2021-02-10 19:14 | elong | New Issue | |
| 2021-02-10 19:14 | elong | Status | new => assigned |
| 2021-02-10 19:14 | elong | Assigned To | => elong |
| 2021-02-10 19:14 | elong | Tag Attached: conflict manager | |
| 2021-02-10 19:16 | elong | Status | assigned => resolved |
| 2021-02-10 19:16 | elong | Resolution | open => fixed |
| 2021-02-10 19:16 | elong | Fixed in Version | => 3.12.7 |
| 2021-02-10 20:00 | admin | Changeset attached | => SymmetricDS 3.12 3d76cf09 |
| 2021-02-17 18:00 | admin | Changeset attached | => SymmetricDS 3.12 a432c6ca |
| 2021-02-22 19:00 | admin | Changeset attached | => SymmetricDS 3.12 45ce5316 |
| 2021-02-25 14:00 | admin | Changeset attached | => SymmetricDS 3.12 fabcc865 |
| 2021-02-25 21:33 | admin | Status | resolved => closed |