View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005045SymmetricDSTaskpublic2021-06-29 19:082022-07-27 15:19
Reporterrogeriosatler@nasajon.com.br Assigned Toelong  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.12.10 
Target Version3.13.6Fixed in Version3.13.6 
Summary0005045: Errors in "ON CONFLICT " - need a new parameter
DescriptionI'm getting this error on my log:
ERROR: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters.

This is caused by the "ON CONFLICT DO NOTHING" at the end of insert statements.

Our database have a lot of tables with unique constraints wich have the DEFERRED type.

Can we have a parameter to disabel the use of "ON CONFLICT DO NOTHING"?

Database version:
PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Steps To Reproducecreate a table with a constraint like:
CONSTRAINT "CK_name" UNIQUE (fieldA, fieldB) DEFERRABLE,

tries to activate the data sync and receives the error:

ManageIncomingBatchListener - Failed to load batch xxxxxxxx org.jumpmind.db.sql.SqlException: ERROR: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters
    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.addRow(JdbcSqlTransaction.java:466)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1060)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:257)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:196)
    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:1091)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1067)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters
    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.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
    at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:485)
    at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:463)
Additional Informationrelated to the task 4975
https://www.symmetricds.org/issues/view.php?id=4975
same solution
TagsNo tags attached.

Relationships

related to 0005341 closedelong Add system property to control use of "on conflict" on Postgres 

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2021-06-29 19:08 rogeriosatler@nasajon.com.br New Issue
2022-07-27 15:16 elong Relationship added related to 0005341
2022-07-27 15:19 elong Assigned To => elong
2022-07-27 15:19 elong Status new => closed
2022-07-27 15:19 elong Resolution open => fixed
2022-07-27 15:19 elong Fixed in Version => 3.13.6
2022-07-27 15:19 elong Target Version => 3.13.6