pg-materialize 0.4.0

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.

Analyzes dependencies without actually generating the output files.

The directory for the PostgreSQL scripts to analyze.

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

The directory for the output creation and refresh scripts.

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

Enables verbose logging.