View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0004975||SymmetricDS||Bug||public||2021-05-05 00:35||2022-07-27 15:19|
|Target Version||3.13.6||Fixed in Version||3.13.6|
|Summary||0004975: insert with on conflict clause cannot be used with table that has insert or update rules|
|Description||If you have a table with an insert or update rule, the sync breaks because of the change that was made here: https://www.symmetricds.org/issues/view.php?id=3751|
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 Reproduce||Create 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"
|Tags||No tags attached.|
||I forgot to add the database version. It's postgres 9.6|
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.
||Yeah, I think a parameter for this option would be great.|
|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|