Subject: Re: Wiki editor request
To: Joe Conway <mail@joeconway.com>, pgsql-www@postgresql.org
References: <9b106c7b-4d27-4211-401e-26c6a7b4d4fb@gmail.com>
 <a7d44fb8-ae88-841e-5de6-4f9b706a6746@joeconway.com>
From: "David M. Kaplan" <dmkaplan2000@gmail.com>
Message-ID: <73881ed7-0e4d-4818-0bde-44bd9fe477af@gmail.com>
Date: Mon, 16 Jul 2018 18:07:18 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.7.0
MIME-Version: 1.0
In-Reply-To: <a7d44fb8-ae88-841e-5de6-4f9b706a6746@joeconway.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk

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
>