Обсуждение: 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

--
My projects:

Admin Boundaries of the World
Missing Boundaries
Emergency Map
Postal Code Map (Germany only)
Fools (QA for zipcodes in Germany)
Postcode Boundaries of Germany