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 |
| Reporter | nikahk | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.12.9 | ||||
| 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: https://www.postgresql.org/docs/13/rules-update.html 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. |
| 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 |