| Bug #22813 | replace: change the internal behaviour from delete+insert to update | ||
|---|---|---|---|
| Submitted: | 29 Sep 2006 9:05 | Modified: | 29 Sep 2006 18:37 |
| Reporter: | Maciej Pilichowski | ||
| Status: | Won't fix | ||
| Category: | Server | Severity: | S4 (Feature request) |
| Version: | 5.0.24a | OS: | Linux (opensuse 10.0) |
| Assigned to: | Target Version: | ||
| Triage: | D5 (Feature request) | ||
[29 Sep 2006 12:07]
Martin Friebe
please see also bug #13479 , the situation was discussed there.
[29 Sep 2006 12:39]
Maciej Pilichowski
Thanks for the link, the report you mentioned is started about something different however final conclusion is not valid for 5.0.24a. In this version server works as described in documentation and replace = delete + insert.
[29 Sep 2006 17:16]
Valeriy Kravchuk
I do not think this feature may be implemented in 5.0.x. We have a documented, intended and consistent behaviour (even if not the best possible one) there, and no real reasons to change it "back" in the next version again.
[29 Sep 2006 18:15]
Martin Friebe
on reflection, I think that the described feature is what "insert on duplicate key update" does. Therefore the current implementation covers for both scenarios. replace = insert OR delete and insert insert on dup... = insert OR update
[29 Sep 2006 18:32]
Maciej Pilichowski
Thank you for the answer, mmm, it is a pity -- changing/adding data should not involve deleting (triggers problem -- I am curious if the design of current replace behaviour is dated before or after trigger feature). However, I hope I'll see it someday, not necessarily in 5.0.x line, maybe in 5.1.x? Would be great.
[29 Sep 2006 18:37]
Maciej Pilichowski
Martin, thank you for the tip, in that case is possible to make a workaround and avoid deleting. Now I check the triggers to be sure :-)
Description: I only consider the situation when the replaced data already exist. When replace means just insert everything is ok. I think that it is the last call to redesign the behaviour of replace, when the triggers are relatively fresh. Why? Well currently replace physically means delete+insert but logically it is as a matter of fact update. Furthermore it is the solution to annoying and errorprone problem with "check is exists and then update or insert" (at least it was my everyday problem with MSSQL Server 2K), however it is not really equivalent to it. Now the problem may seem not so important but with triggers it is -- notice that replace in fact fires the on-delete trigger. With well designed architecture of user database it can be a problem because nobody actually deletes anything -- it is only a "_replace_ment". So, one advantage would be consistency ("do what you mean"), another -- smooth integration with triggers. On new data insert-trigger is fired, on replacement only _one_ trigger is fired -- on-update, not on-delete & on-insert. However there is also another advantage of such change -- richer capabilities of the syntax. Currently when you omit some columns for the rest the default values are set (there is no other way since it is equivalent to insert). But with change to update only the affected columns can be changed and other could be unaffected (or set to default -- depending how syntax is built and how you use it). For me personally the most important thing is trigger -- it is a serious flaw to see on adding data that deletion trigger kicks in. How to repeat: This a wish, not a bug. Suggested fix: This a wish, not a bug.