[postgis-users] Refresh materialized view only when it's necessary

Guillaume ARNAUD guillaume.arnaud at ledepartement82.fr
Wed Apr 1 08:11:12 PDT 2020

Hello, I need to refresh a materialized view in my database, but I only 
want to do it when necessary. I use the materialized view because the 
view costs more than 20 seconds. It's a big table. If I refresh with 
trigger on the table that is used, after each update, I wait 20s ... I 
have considered several solutions but I can't seem to put them in place. 
They may not be possible ... Is there one you know? I thought of : - Put 
a trigger on the tables used by the materialized view: Slow - delay 
updating the view materialized with notify / listen but I didn't 
understand how it works and send a request when the listen receives the 
notification. - Search for an asynchronous update of the materialized 
view: without success - Create a view of the materialized view to add a 
rule on select: without success - Create a view of the materialized view 
with in the call to the function that checks if the view is up to date 
(using a third-party table): error cannot execute REFRESH MATERIALIZED 
VIEW because this object is being use by active requests in this session 
Do you have any idea to do this ? Thanks

Logo CD82 <http://www.ledepartement.fr> 	Guillaume ARNAUD
Technicien SIG
Cellule SIGD
Direction des systèmes informatiques et de télécommunication
Pôle coordination et ressources
Tél : 05 63 91 82 37
guillaume.arnaud at ledepartement82.fr

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200401/e3959b35/attachment.html>

More information about the postgis-users mailing list