View Issue Details

IDProjectCategoryView StatusLast Update
0003751SymmetricDSNew Featurepublic2018-11-28 11:00
ReporterelongAssigned Toelong 
Prioritynormal 
Status resolvedResolutionfixed 
Product Version3.10.0 
Target Version3.10.0Fixed in Version3.10.0 
Summary0003751: PostgreSQL 9.5 and newer can use "insert on conflict do nothing" for better performance
DescriptionOn PostgreSQL 9.5 and newer, take advantage of the "insert on conflict do nothing" syntax to get better performance on inserts. It currently uses "insert into table (...) select ... from table where ..." to make sure the row doesn't exist before inserting. This makes it possible to get 0 rows affected, so it knows to fall back to an update statement. But the extra "select" is slower than an ordinary "insert". The reason an ordinary "insert" statement can't be used is because it will get a unique key violation, which rolls back the transaction. The "insert on conflict do nothing" solves the problem by return 0 rows affected without a violation that rolls back the transaction.

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.10 f6c842a4

2018-10-10 15:22:56

admin

Details Diff
0003751: PostgreSQL 9.5 can use "insert on conflict do nothing" for
better performance

0003751
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/ext/BulkDataLoaderFactory.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/ext/PostgresBulkDataLoaderFactory.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/DatabaseNamesConstants.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/DdlBuilderFactory.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/DmlStatementFactory.java Diff File
add - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDmlStatement95.java Diff File
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/DbExport.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/JdbcDatabasePlatformFactory.java Diff File
add - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSql95DatabasePlatform.java Diff File

Issue History

Date Modified Username Field Change
2018-10-10 15:18 elong New Issue
2018-10-10 15:18 elong Status new => assigned
2018-10-10 15:18 elong Assigned To => elong
2018-10-10 15:23 elong Status assigned => resolved
2018-10-10 15:23 elong Resolution open => fixed
2018-10-10 15:23 elong Fixed in Version => 3.10.0
2018-11-28 11:00 admin Changeset attached => SymmetricDS 3.10 f6c842a4