Re: update table with suppress_redundant_updates_trigger()

On 2/25/19 10:42 AM, wambacher(at)posteo(dot)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(dot)klaver(at)aklaver(dot)com