update table with suppress_redundant_updates_trigger()

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&gt;
Missing Boundaries
<https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries&gt;
Emergency Map <https://wambachers-osm.website/emergency&gt;
Postal Code Map (Germany only) <https://wambachers-osm.website/plz&gt;
Fools (QA for zipcodes in Germany) <https://wambachers-osm.website/fools&gt;
Postcode Boundaries of Germany <https://wambachers-osm.website/pcoundaries&gt;

--
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

Am 25.02.19 um 19:42 schrieb wambacher@posteo.de:

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 <https://wambachers-osm.website/boundaries&gt;
Missing Boundaries
<https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries&gt;
Emergency Map <https://wambachers-osm.website/emergency&gt;
Postal Code Map (Germany only) <https://wambachers-osm.website/plz&gt;
Fools (QA for zipcodes in Germany) <https://wambachers-osm.website/fools&gt;
Postcode Boundaries of Germany
<https://wambachers-osm.website/pcoundaries&gt;

--
My projects:

Admin Boundaries of the World <https://wambachers-osm.website/boundaries&gt;
Missing Boundaries
<https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries&gt;
Emergency Map <https://wambachers-osm.website/emergency&gt;
Postal Code Map (Germany only) <https://wambachers-osm.website/plz&gt;
Fools (QA for zipcodes in Germany) <https://wambachers-osm.website/fools&gt;
Postcode Boundaries of Germany <https://wambachers-osm.website/pcoundaries&gt;