pg_materialize

pg_materialize is a utility for generating PostgreSQL creation and refresh scripts from a directory containing Materialized View SQL definitions. It uses psqlparse to transform the SQL into parse trees, identifies which Materialized Views have a dependency on other views by generating a DAG, and produces the correct order for constructing and refreshing these views. The source directory is traversed recursively, cross-schema views are handled correctly, and extraneous transaction syntax blocks are filtered out from the final output. The files from each subsequent run are timestamped with the suffix YYYYMMDD-HHMMSS.sql.

Supported Python Versions

• Python 2.7
• Python 3.3+

Installation

If you have pip on your system, you can simply install or upgrade the Python library:

pip install -U pg_materialize

Alternately, you can download the source distribution from PyPI, unarchive it, and run:

python setup.py install

Note: both of the methods described above install pg_materialize as a system-wide package. You may consider using virtualenv to create isolated Python environments instead.

Usage

Example Command:

pg_materialize -v \
    -i ~/Projects/my_project/src \
    -o ~/Projects/my_project/output \
    -p _mv \
    -I invites

Example Output:

Found 97 Scripts in /Users/ali/Projects/my_project/src
Identified 169 Materialized Views, Containing 90 View Dependencies

Materialized View Dependencies:
'public.users_mv': ['public.user_addresses_mv', 'public.user_invites_mv'],
'public.orders_mv': ['public.payment_methods_mv']

Selecting 97 Materialized Views for Refresh
Successfully Saved Creation Script to ~/Projects/my_project/output/create-20170824-120626.sql
Successfully Saved Refresh Script to ~/Projects/my_project/output/refresh-20170824-120626.sql
Process Complete!

Example Creation Script:

BEGIN;

    -- ~/Projects/my_project/src/public/user_addresses.sql

    CREATE MATERIALIZED VIEW IF NOT EXISTS public.user_addresses_mv AS (
        SELECT *
        FROM public.user_addresses
        WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
    ) WITH DATA;

    -- ~/Projects/my_project/src/public/user_invites.sql

    CREATE MATERIALIZED VIEW IF NOT EXISTS public.user_invites_mv AS (
        SELECT *
        FROM public.user_invites
        WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
    ) WITH DATA;

    -- ~/Projects/my_project/src/public/payment_methods.sql

    CREATE MATERIALIZED VIEW IF NOT EXISTS public.payment_methods_mv AS (
        SELECT *
        FROM public.payment_methods
        WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
    ) WITH DATA;

    -- ~/Projects/my_project/src/public/users.sql

    CREATE MATERIALIZED VIEW IF NOT EXISTS public.users_mv AS (
        SELECT *
        FROM public.users
        WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
    ) WITH DATA;

    -- ~/Projects/my_project/src/public/orders.sql

    CREATE MATERIALIZED VIEW IF NOT EXISTS public.orders_mv AS (
        SELECT *
        FROM public.orders
        WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
    ) WITH DATA;

COMMIT;

Example Refresh Script:

BEGIN;

    REFRESH MATERIALIZED VIEW CONCURRENTLY public.user_addresses_mv;

    REFRESH MATERIALIZED VIEW CONCURRENTLY public.payment_methods_mv;

    REFRESH MATERIALIZED VIEW CONCURRENTLY public.users_mv;

    REFRESH MATERIALIZED VIEW CONCURRENTLY public.orders_mv;

COMMIT;

Options

pg_materialize accepts the following command line arguments.

-d

--dry-run

Analyzes dependencies without actually generating the output files.

-i

--input-dir

The directory for the PostgreSQL scripts to analyze.

-I

--ignore-refresh

Regex pattern to match when ignoring refresh on Materialized Views (i.e. hist for users_mv_hist).

-o

--output_dir

The directory for the output creation and refresh scripts.

-p

--pattern

Materialized View regex pattern to match (i.e. _mv for users_mv).

-v

--verbose

Enables verbose logging.