How to check if a materialised view is being updated?

I’ve got a materialized view as a source for my ETL-process, and the
materialized view takes several hours to refresh. During which it
is locked for queries.

Would it be an option to split the process into a cascade of
materialized views to minimize the actual time of lock?

So I’m looking for a way to identify if the refresh process is
finished or if it’s still running - preferably without having to
wait for timeout by querying a locked materialized view. But by
e.g. using the system tables or otherwise.

I cannot answer this, however.

--
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Threema (Safer than WhatsApp): A76MKH3J
Handys: +41 78 947 36 21 | +49 1578 772 37 37

So I’m looking for a way to identify if the refresh process is finished or
if it’s still running - preferably without having to wait for timeout by
querying a locked materialized view. But by e.g. using the system tables
or otherwise.

Can anybody suggest some pointers on how to do this?

Maybe pg_stat_activity will have the refresh query?

Regards,
Jayadevan

Thanks for the tip, Jayadevan!
Looks like your suggestion can help med with this.
I’m getting all the info I need from this view.

Cheers,
Bjørn

From: Jayadevan M <maymala.jayadevan@gmail.com>
Date: Tuesday, 19 January 2021 at 12:33
To: "Myklebust, Bjørn Magnar" <Bjorn.Myklebust@skatteetaten.no>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: How to check if a materialised view is being updated?

So I’m looking for a way to identify if the refresh process is finished or if it’s still running - preferably without having to wait for timeout by querying a locked materialized view. But by e.g. using the system tables or otherwise.

Can anybody suggest some pointers on how to do this?

Maybe pg_stat_activity will have the refresh query?

Regards,
Jayadevan

________________________________
Denne e-posten og eventuelle vedlegg er beregnet utelukkende for den institusjon eller person den er rettet til og kan være belagt med lovbestemt taushetsplikt. Dersom e-posten er feilsendt, vennligst slett den og kontakt Skatteetaten.
The contents of this email message and any attachments are intended solely for the addressee(s) and may contain confidential information and may be legally protected from disclosure. If you are not the intended recipient of this message, please immediately delete the message and alert the Norwegian Tax Administration.

On Jan 19, 2021, at 6:33 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:

So I’m looking for a way to identify if the refresh process is finished or if it’s still running - preferably without having to wait for timeout by querying a locked materialized view. But by e.g. using the system tables or otherwise.

Can anybody suggest some pointers on how to do this?

Maybe pg_stat_activity will have the refresh query?

Yes, pg_stat_activity has a query column that could be searched with the regular string matching tools, including regex if necessary. pg_stat_activity also has some other useful columns that, like query_start which can tell you how long the query has been running.

Cheers
Philip