You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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.)
Submitted by: Philip Williams (unordained)
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.
The text was updated successfully, but these errors were encountered: