Issue Details (XML | Word | Printable)

Key: CORE-3684
Type: New Feature New Feature
Status: Open Open
Priority: Trivial Trivial
Assignee: Unassigned
Reporter: Philip Williams
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

update only if different

Created: 02/Dec/11 05:36 PM   Updated: 02/Dec/11 05:43 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None


 Description  « Hide
Allow update and insert-or-update statements to have a flag for "no trivial changes", if the update would have no effect except locking the row. Something like "update people set name = 'Bob' where id = 5 ONLY IF DIFFERENT". This is useful for migration / data-loading scenarios where you're running large import datasets, but you just want the data to end up a certain way, you don't want record locks / versions on rows that didn't really need to be touched. It'd be entirely optional, only for situations where you know you don't want do-nothing updates to trigger on-update triggers or place locks, and you're okay with it. To accomplish this currently, a simple:

update or insert into people (name, id) values ('Bob', 5);

becomes

if (not exists(select id from people where id = 5)) then insert into people (name, id) values ('Bob', 5)
else update people set name = 'Bob' where id = 5 and name is distinct from 'Bob';
-- not so bad for one field, but much more annoying for high-column-count tables

I know it's a very niche feature.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Philip Williams added a comment - 02/Dec/11 05:43 PM
Also equivalent to:

create exception e_trivial_change 'Nothing to be done';

create trigger trg_people_trivial on bob before update as
begin
 if (new.name is not distinct from old.name and new.id is not distinct from old.id) then
  exception e_trivial_change;
end

begin
 update or insert into people (name, id) values ('Bob', 5);
when exception e_trivial_change do begin end
end

... again, annoying, and you have to deal with the position of the trigger relative to any others. And this one would *always* happen, which is the opposite of the default I'd want. (I'd have to setup a rdb$set_context to indicate my desire to have the exception thrown, and my readiness to ignore it. GUI code would probably want to just forge ahead with the normal consequences of update-or-insert.)