From: David M. Kaplan <dmkaplan2000@gmail.com>
To: Joe Conway <mail@joeconway.com>
To: pgsql-www@postgresql.org
Subject: Re: Wiki editor request
Date: Mon, 16 Jul 2018 18:07:18 +0200
Message-ID: <73881ed7-0e4d-4818-0bde-44bd9fe477af@gmail.com> (raw)
In-Reply-To: <a7d44fb8-ae88-841e-5de6-4f9b706a6746@joeconway.com>
References: <9b106c7b-4d27-4211-401e-26c6a7b4d4fb@gmail.com>
<a7d44fb8-ae88-841e-5de6-4f9b706a6746@joeconway.com>
Hi,
I have a new code snippet that I think would be a useful addition to the
website, but I can't see how to create a new page and I am not sure I
have the privileges to do so. Could you help me with that, perhaps
creating an empty page for me to fill?
The code deals with refreshing materialized views. I have a large set of
materialized views that depend on each other, but I could find no way to
refresh them all in the appropriate order so that you respect
dependencies. I googled the problem, but all the solutions I could find
just refresh all materialized views without considering dependencies
among them. Therefore, I created some code to get the list of
materialized views in the proper order so that they are refreshed in the
proper order. The code is below. I am using it to refresh all views in a
database, but it could also be used to find the dependencies of a single
mat view and refresh just that subset. I would put the code in the
"works for me" category as my understanding of pg_* tables is limited,
but I think it is useful as is.
Cheers,
David
--------------------------------------------------
--- A view giving the list of mat views that depend
--- on each mat view
--------------------------------------------------
CREATE OR REPLACE VIEW mat_view_dependencies AS
WITH RECURSIVE
s(start_schemaname,start_mvname,schemaname,mvname,mvoid,depth) AS (
-- List of mat views -- with no dependencies
SELECT n.nspname AS start_schemaname, c.relname AS start_mvname,
n.nspname AS schemaname, c.relname AS mvname, c.oid AS mvoid, 0 AS depth
FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid
WHERE c.relkind='m'
UNION
-- Recursively find all things depending on previous level
SELECT s.start_schemaname, s.start_mvname,
n.nspname AS schemaname, c.relname AS mvname,
c.oid AS mvoid, depth+1 AS depth
FROM s
JOIN pg_depend d ON s.mvoid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c ON r.ev_class=c.oid AND c.relkind='m'
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE s.mvoid <> c.oid -- exclude the current MV which always depends on
itself
)
SELECT * FROM s;
--------------------------------------------------
--- A view that returns the list of mat views in the
--- order they should be refreshed.
--------------------------------------------------
CREATE OR REPLACE VIEW mat_view_refresh_order AS
WITH b AS (
-- Select the highest depth of each mat view name
SELECT DISTINCT ON (schemaname,mvname) schemaname, mvname, depth
FROM mat_view_dependencies
ORDER BY schemaname, mvname, depth DESC
)
-- Reorder appropriately
SELECT schemaname, mvname, depth AS refresh_order
FROM b
ORDER BY depth, schemaname, mvname
;
--------------------------------------------------
--- Query to return the code to refresh all mat views in proper order
--- Should be a function, but haven't created that yet.
--- Instead, put code in a psql variable and then
--- execute that.
--------------------------------------------------
WITH a AS (
SELECT 'REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || mvname ||
'";' AS r
FROM mat_view_refresh_order WHERE schemaname='dmk'
ORDER BY refresh_order
)
SELECT string_agg(r,E'\n') AS script FROM a \gset
\echo :script
-- :script
On 04/29/2018 01:18 AM, Joe Conway wrote:
> On 04/27/2018 03:19 AM, David M. Kaplan wrote:
>> I noticed something that I do not think is correct on the PostgreSQL
>> wiki and would like to edit it, but don't have the privileges. Could you
>> either edit it for me or give me the privileges? My account is based on
>> my gmail dmkaplan2000@gmail.com
> Edit privilege granted.
>
> Joe
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-www@postgresql.org
Cc: dmkaplan2000@gmail.com, mail@joeconway.com
Subject: Re: Wiki editor request
In-Reply-To: <73881ed7-0e4d-4818-0bde-44bd9fe477af@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox