[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: update table with suppress_redundant_updates_trigger()
From: wambacher () posteo ! de
Date: 2019-02-26 9:10:30
Message-ID: 45ae5825-0f5c-c9d1-412b-1e4d89149ad3 () posteo ! de
[Download RAW message or body]
[Attachment #2 (text/plain)]
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>
> 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>
--
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>
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>Problem "solved". <br>
</p>
<p>"found" will be true of false depending on the trigger action.</p>
<p>update done -> found = true, update not done ->
found=false.</p>
<p>But: The trigger sometimes allows updates where no data has been
changed! That is another problem to be solved :(</p>
<p>Regards</p>
<p>walter<br>
</p>
<div class="moz-cite-prefix">Am 25.02.19 um 19:42 schrieb
<a class="moz-txt-link-abbreviated" \
href="mailto:wambacher@posteo.de">wambacher@posteo.de</a>:<br> </div>
<blockquote type="cite"
cite="mid:fa1d484b-2416-041f-9de3-1ff520307f96@posteo.de">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<p>Hi,</p>
<p>i'm doing a lot of updates in my database, but most of them are
not necessary at all (sorry, just detected it)<br>
</p>
<p>Therefore i installed a trigger to minimize real updates.<br>
</p>
<p><tt>create trigger suppress_redundant_updates_boundaries</tt><tt><br>
</tt><tt> before update on boundaries</tt><tt><br>
</tt><tt> for each row execute procedure
suppress_redundant_updates_trigger();</tt></p>
<p>Is there a way to get the count of the real table updates?</p>
<p>Using pl/pgsql i'm updating boundaries with <tt><br>
</tt></p>
<p><tt> update boundaries<br>
set id = bT2.id,<br>
country = bT2.country,<br>
type = 'admin', <br>
value = bT2.value,<br>
...<br>
,qm = bT2.qm<br>
,lwqm =
st_area(geography(coalesce(xlandarea,rT.way)))<br>
where id = bT2.id;<br>
<br>
if (found) then<br>
if (debug > 0) then raise notice
'real db update of % done 2', bT2.id; end if;<br>
updatedDB := updatedDB + 1;<br>
end if;</tt></p>
<p>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.<br>
</p>
<p>Question: will "found" be set when update has been blocked by
the trigger - or does that not matter?</p>
<p>if "found" is always true: what else can i do?<br>
</p>
<p>regards</p>
<p>walter<br>
</p>
<div class="moz-signature">-- <br>
My projects:<br>
<br>
<a href="https://wambachers-osm.website/boundaries"
moz-do-not-send="true">Admin Boundaries of the World</a><br>
<a
href="https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries"
moz-do-not-send="true">Missing Boundaries</a><br>
<a href="https://wambachers-osm.website/emergency"
moz-do-not-send="true">Emergency Map</a><br>
<a href="https://wambachers-osm.website/plz"
moz-do-not-send="true">Postal Code Map (Germany only)</a><br>
<a href="https://wambachers-osm.website/fools"
moz-do-not-send="true">Fools (QA for zipcodes in Germany)</a><br>
<a href="https://wambachers-osm.website/pcoundaries"
moz-do-not-send="true"> Postcode Boundaries of Germany</a></div>
</blockquote>
<div class="moz-signature">-- <br>
My projects:<br>
<br>
<a href="https://wambachers-osm.website/boundaries">Admin
Boundaries of the World</a><br>
<a
href="https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries">Missing
Boundaries</a><br>
<a href="https://wambachers-osm.website/emergency">Emergency Map</a><br>
<a href="https://wambachers-osm.website/plz">Postal Code Map
(Germany only)</a><br>
<a href="https://wambachers-osm.website/fools">Fools (QA for
zipcodes in Germany)</a><br>
<a href="https://wambachers-osm.website/pcoundaries"> Postcode
Boundaries of Germany</a></div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic