Re: How to check if a materialised view is being updated?

[Thiemo Kellner]

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


smime.p7s
Description: S/MIME Signature

Re: How to check if a materialised view is being updated?

[Jayadevan M]

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

Re: How to check if a materialised view is being updated?

[Philip Semanchuk]

> On Jan 19, 2021, at 6:33 AM, Jayadevan M  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

How to check if a materialised view is being updated?

[Myklebust , Bjørn Magnar]

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


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.

Ist there a chance to provide postGIS 3.x combined with postgreSQL12 or 13 as a zypper repository released for SLES15 SP1 or SP2

[OWL-IT]

Dear postgreSQL developer Team,

we are curently supporting a couple of SLES12 SP5 Server running different 
application
together with postgreSQL10 or postgreSQL11 database including postGIS30 
extension.
These server has to be updateted to SLES15 SP2 in the next time, so we are 
looking
for a valid zypper repository that can be used within our SUSE Manager 
environment.
As far as we have seen, there doesn't seem to be a postGIS version available 
for SLES15
at the download directoy   https://download.postgresql.org/pub/repos/zypp/
So we would like to ask, if there is a chance to provide a postGIS 3.x 
extension combined
with postgreSQL12 or postgreSQL13 released for SLES15 SP1 or SLES15 SP2 in the 
next time?
If not, can you recommend what kind of sourcecode of postGIS extension, 
available at
https://postgis.net/source/ can be used running postgreSQL12 together with 
SLES15.


Many thanks
Kind regards
Heiko Kämpf

Ostwestfalen-Lippe-IT Paderborn
Technologiepark 11
33100 Paderborn
Germany
E-Mail:h.kae...@owl-it.de

Re: How to check if a materialised view is being updated?

[Myklebust , Bjørn Magnar]

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 
Date: Tuesday, 19 January 2021 at 12:33
To: "Myklebust, Bjørn Magnar" 
Cc: "pgsql-gene...@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.

Re: Error messages on duplicate schema names

[Alvaro Herrera]

On 2021-Jan-15, Michael Paquier wrote:

> On Wed, Jan 06, 2021 at 07:15:24PM +0200, Andrus wrote:
> > Should duplicate schema names accepted or should their usage throw better
> > error messages.
> 
> This means that we are one call of CommandCounterIncrement() short for
> such queries, and similar safeguards already exist in this area for
> GRANT/REVOKE.  The spot where I would put this new CCI is at the end
> of SetDefaultACL(), like in the attached with a test added to
> privileges.sql.
> 
> Any thoughts from others?

Looks to match what we do in ExecGrant_Relation() et al, so +1.

I guess you could make the case that the CCI call should be in the
callers where we actually loop (SetDefaultACLsInSchemas,
RemoveRoleFromObjectACL), but it's hard to get excited about the added
clutter.

-- 
Álvaro Herrera   Valdivia, Chile

Re: Accounting for between table correlation

[Michael Lewis]

On Fri, Jan 15, 2021 at 3:27 PM Alexander Stoddard <
alexander.stodd...@gmail.com> wrote:

> The queries are complex, multiple joins to 10 plus tables, although most
> are to tiny enum type lookup tables. I believe it is the join between the
> two large tables that I have described that causes the issue, and that
> seems to be reflected in the different strategies in the plans. For my own
> learning and to clarify the problem I probably will have to try and
> reproduce the behavior in a test case.
>  ...
> I am doing nothing to specify the optimizer. Do I have configurable
> options in that regard? I was unaware of them.
>

Read up on join_collapse_limit and geqo.

https://www.postgresql.org/docs/current/runtime-config-query.html

Note that you can set these values within your session to check and even
just run explain [query] to check if the plan that would be used varies.

Re: Accounting for between table correlation

[Alexander Stoddard]

On Tue, Jan 19, 2021 at 2:47 PM Michael Lewis  wrote:

> On Fri, Jan 15, 2021 at 3:27 PM Alexander Stoddard <
> alexander.stodd...@gmail.com> wrote:
>
>> I am doing nothing to specify the optimizer. Do I have configurable
>> options in that regard? I was unaware of them.
>>
>
> Read up on join_collapse_limit and geqo.
>
> https://www.postgresql.org/docs/current/runtime-config-query.html
>
> Note that you can set these values within your session to check and even
> just run explain [query] to check if the plan that would be used varies.
>

Thank you for this. I can get different plans by changing the different
parameters, but if the output of EXPLAIN VERBOSE tells me geqo planning
specifically is being used I'm not seeing it.  Is there a way to see in the
explain plan if geqo was used?

Re: Accounting for between table correlation

[Tom Lane]

Alexander Stoddard  writes:
> Thank you for this. I can get different plans by changing the different
> parameters, but if the output of EXPLAIN VERBOSE tells me geqo planning
> specifically is being used I'm not seeing it.  Is there a way to see in the
> explain plan if geqo was used?

It's not reported directly, but you can assume that it was used if the
number of relations in the query (the number of table scans in the plan,
more or less) is >= geqo_threshold.  Or if you're unsure, check whether
turning the "geqo" boolean on/off changes the plan at all.  In theory you
might get exactly the same plan from the regular and geqo planners, but
for reasonable values of geqo_threshold that's pretty unlikely, I think.

(Another way to be sure might be to notice whether turning geqo on/off
changes the planning time significantly.  geqo off should be a great deal
more expensive for large plans.)

regards, tom lane

Re: Error messages on duplicate schema names

[Michael Paquier]

On Tue, Jan 19, 2021 at 05:37:51PM -0300, Alvaro Herrera wrote:
> I guess you could make the case that the CCI call should be in the
> callers where we actually loop (SetDefaultACLsInSchemas,
> RemoveRoleFromObjectACL), but it's hard to get excited about the added
> clutter.

Yeah, that matches my thoughts.  And I did not see any harm in putting
the CCI in SetDefaultACL() even for the case of pg_default_acl with
DROP OWNED BY.  So applied and backpatched.  Thanks, Alvaro and
Andrus.
--
Michael


signature.asc
Description: PGP signature