Обсуждение: update table with suppress_redundant_updates_trigger()
Hi,
i'm doing a lot of updates in my database, but most of them are not necessary at all (sorry, just detected it)
Therefore i installed a trigger to minimize real updates.
create trigger suppress_redundant_updates_boundaries
before update on boundaries
for each row execute procedure suppress_redundant_updates_trigger();
Is there a way to get the count of the real table updates?
Using pl/pgsql i'm updating boundaries with
update boundaries
set id = bT2.id,
country = bT2.country,
type = 'admin',
value = bT2.value,
...
,qm = bT2.qm
,lwqm = st_area(geography(coalesce(xlandarea,rT.way)))
where id = bT2.id;
if (found) then
if (debug > 0) then raise notice 'real db update of % done 2', bT2.id; end if;
updatedDB := updatedDB + 1;
end if;
i get a "wrong" result, because "found" is always true, even when the records are identical (hope so) and an update should be suppressed by the trigger.
Question: will "found" be set when update has been blocked by the trigger - or does that not matter?
if "found" is always true: what else can i do?
regards
walter
On 2/25/19 10:42 AM, wambacher@posteo.de wrote: > Hi, > > i'm doing a lot of updates in my database, but most of them are not > necessary at all (sorry, just detected it) Would it not be easier to just not do the unnecessary updates? Or to put it another way what distinguishes necessary/unnecessary? > > Therefore i installed a trigger to minimize real updates. > > create trigger suppress_redundant_updates_boundaries > before update on boundaries > for each row execute procedure suppress_redundant_updates_trigger(); > > Is there a way to get the count of the real table updates? > > Using pl/pgsql i'm updating boundaries with > > update boundaries > set id = bT2.id, > country = bT2.country, > type = 'admin', > value = bT2.value, > ... > ,qm = bT2.qm > ,lwqm = > st_area(geography(coalesce(xlandarea,rT.way))) > where id = bT2.id; > > if (found) then > if (debug > 0) then raise notice 'real db update > of % done 2', bT2.id; end if; > updatedDB := updatedDB + 1; > end if; > > i get a "wrong" result, because "found" is always true, even when the > records are identical (hope so) and an update should be suppressed by > the trigger. > > Question: will "found" be set when update has been blocked by the > trigger - or does that not matter? > > if "found" is always true: what else can i do? Untested: IF NEW.* != OLD.* THEN RETURN NEW.* ELSE RETURN NULL END IF; > > regards > > walter > > -- > My projects: > > Admin Boundaries of the World <https://wambachers-osm.website/boundaries> > Missing Boundaries > <https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries> > Emergency Map <https://wambachers-osm.website/emergency> > Postal Code Map (Germany only) <https://wambachers-osm.website/plz> > Fools (QA for zipcodes in Germany) <https://wambachers-osm.website/fools> > Postcode Boundaries of Germany <https://wambachers-osm.website/pcoundaries> -- Adrian Klaver adrian.klaver@aklaver.com
Problem "solved".
"found" will be true of false depending on the trigger action.
update done -> found = true, update not done -> found=false.
But: The trigger sometimes allows updates where no data has been changed! That is another problem to be solved :(
Regards
walter
Hi,
i'm doing a lot of updates in my database, but most of them are not necessary at all (sorry, just detected it)
Therefore i installed a trigger to minimize real updates.
create trigger suppress_redundant_updates_boundaries
before update on boundaries
for each row execute procedure suppress_redundant_updates_trigger();Is there a way to get the count of the real table updates?
Using pl/pgsql i'm updating boundaries with
update boundaries
set id = bT2.id,
country = bT2.country,
type = 'admin',
value = bT2.value,
...
,qm = bT2.qm
,lwqm = st_area(geography(coalesce(xlandarea,rT.way)))
where id = bT2.id;
if (found) then
if (debug > 0) then raise notice 'real db update of % done 2', bT2.id; end if;
updatedDB := updatedDB + 1;
end if;i get a "wrong" result, because "found" is always true, even when the records are identical (hope so) and an update should be suppressed by the trigger.
Question: will "found" be set when update has been blocked by the trigger - or does that not matter?
if "found" is always true: what else can i do?
regards
walter