[Question] How to add a unique index to definition for PGMaterializedView

[MeganBeckett]

Hi there

I have a materialized view defined using PGMaterializedView in a views.py file. The views had already been created in the database.

I want to now add a unique index to my view - can I do this in the definition of a PGMaterializedView and manage the update with alembic?

As a minimal example:

my_view = PGMaterializedView (
    schema="public",
    signature="vw_period_totals",
    definition="""
        SELECT 
            id, 
            name,
            period,
            sum(count) AS total
        FROM table
        GROUP BY id, name, period
    """,
    with_data=True
)

I now want to add a unique index to the view and have this managed by alembic, namely:

CREATE unique index vw_period_totals_index ON vw_period_totals (name, period);

I can't add this to the definition argument. So, is there some way to manage this?

[olirice]

currently there is no way to have alembic_utils manage an index on a materialized view

there is some more discussion and a partial implementation over at https://github.com/olirice/alembic_utils/pull/46 but for now I'd recommend managing them manually

If you're trying to do something highly consistent, like add a unique index on id to support concurrent refreshes, you could consider adding a hook in env.py to raise an exception if it detects a materialized view with an id column that does not have an associated unique index. If that sounds useful but you're not sure how to do it, lmk and I'll pull a code sample together

[antonio-antuan]

@olirice I would appreciate if you provided a hook example, which can be used with revision --autogenerate. I found a way to make a comparison in env.py, but don't know how to raise an exception after new revision is generated.

[antonio-antuan]

ok found a hook named process_revision_directives, looks like I can customize autogenerated ops. thanks :)

[phenry2]

Found a way around this by creating a new PGMaterializedViewExtended class which took the indexes that should be included with the MV as a parameter. It then injects create scripts for the indexes into the MV definition to aid with detecting changes. from_database then just pulls the MV, and any indexes relating to the MV and re-creates the definition with both the MV and its indexes. Works nicely on upgrade/downgrade and detects any changes to indexes I've added, remove or changed.