Re: Wiki editor request - Mailing list pgsql-www

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
>