View Issue Details

IDProjectCategoryView StatusLast Update
0004975SymmetricDSBugpublic2022-07-27 15:19
Reporternikahk Assigned Toelong  
Status closedResolutionfixed 
Product Version3.12.9 
Target Version3.13.6Fixed in Version3.13.6 
Summary0004975: insert with on conflict clause cannot be used with table that has insert or update rules
DescriptionIf you have a table with an insert or update rule, the sync breaks because of the change that was made here:

This is an example of the new query that doesn't work:
 insert into "public"."locations_of_interest" ("id", "name", "type", "category") values (1,'test',null,9998) on conflict do nothing

This is an example of the old query that works:
insert into "public"."locations_of_interest" ("id", "name", "type", "category") (select 100,'test',null,9998) where (select distinct 1 from "public"."locations_of_interest" where id=100) is null)"

Currently, we have to convert to an older version to make this work with the insert and update rules that we have on this table.
Steps To ReproduceCreate a table with insert or update rules in both your parent and child dbs
insert a row into the parent db
You see that the sync fails with this error message: "insert with on conflict clause cannot be used with table that has insert or update rules"
TagsNo tags attached.


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



2021-05-05 00:39

reporter   ~0001930

I forgot to add the database version. It's postgres 9.6


2021-05-05 17:18

developer   ~0001931

There is a big caution documented by Postgres that indicates that it is safer to use triggers than update rules.

Here is a link to that page:

We could provide a parameter that would disable the use of "on conflict do nothing" when the database has update rules defined.


2021-05-05 23:17

reporter   ~0001932

Yeah, I think a parameter for this option would be great.

Issue History

Date Modified Username Field Change
2021-05-05 00:35 nikahk New Issue
2021-05-05 00:39 nikahk Note Added: 0001930
2021-05-05 17:18 pmarzullo Note Added: 0001931
2021-05-05 23:17 nikahk Note Added: 0001932
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