On 2/25/19 10:42 AM, wambacher@xxxxxxxxx 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@xxxxxxxxxxx
