Обсуждение: How to check if a materialised view is being updated?
Hi.
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.
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?
I’m using PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Thanks,
Bjørn
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.
> 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?
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
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 withouthaving 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 ifnecessary. pg_stat_activity also has some other useful columns that, like query_start which can tell you how long the queryhas been running. Cheers Philip