How do a I create Postgres Materialized View using SQLAlchemy?

[Jeff Widman]

A couple of quick questions about Postgres Materialized Views--searched, but found very little:

I've got a few values that are fairly expensive to calculate, so I'd like to pre-calculate the results. It's fine if the the data is stale for a few hours, so I'd like to use Postgres Materialized Views:

1) How do I create a Materialized View using SQLAlchemy? 

I checked the docs, and all I could find is how to reflect an already-created materialized view. All my other DDL is managed by SQLAlchemy in my app, so for simplicity I'd rather handle the materialized view definition using SQLAlchemy as well.

2) How do I query this materialized view?

3) Is there a special method for refreshing? 

Or should I just do db.engine.execute("REFRESH MATERIALIZED VIEW view_name CONCURRENTLY") ?

Cheers,

Jeff

[Mike Bayer]

On 11/09/2015 03:52 PM, Jeff Widman wrote:
> A couple of quick questions about Postgres Materialized Views--searched,
> but found very little:
>
> I've got a few values that are fairly expensive to calculate, so I'd
> like to pre-calculate the results. It's fine if the the data is stale
> for a few hours, so I'd like to use Postgres Materialized Views:
>
> 1) How do I create a Materialized View using SQLAlchemy?

connection.execute("CREATE MATERIALIZED VIEW ....")

alternatively you can adapt the recipe at
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views to
support the "MATERIALIZED VIEW" syntax supported by your database.

> I checked the docs, and all I could find is how to reflect an
> already-created materialized view. All my other DDL is managed by
> SQLAlchemy in my app, so for simplicity I'd rather handle the
> materialized view definition using SQLAlchemy as well.
>
> 2) How do I query this materialized view?

a view is like any other table-oriented structure in the database. a
Table metadata object or Table-mapped ORM class that uses the name and
columns of this view will suffice. I've added a short example of a
declarative mapping against the custom view object to the above example.

>
> 3) Is there a special method for refreshing?

> Or should I just do /db.engine.execute("REFRESH MATERIALIZED VIEW
> view_name CONCURRENTLY") /?
>
> Cheers,
> Jeff
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jeff Widman]

Thanks Mike. That helped tremendously.

A couple of followup questions:

(questions based on this code extending the view recipe: https://gist.github.com/jeffwidman/1656498de21dc0afcdab)

1) In the recipe for creating views, I see: 

CreateView(name, selectable).execute_at('after-create', metadata)

But in the docs it looks like this 'execute_at()' method is deprecated, should I be doing something different? 

Also, not clear to my why this method is deprecated, especially since the descriptions on that page for several other methods say 'see execute_at() for more information' so it doesn't exactly "feel" deprecated. 

2) What is the preferred way to create a unique index on the materialized view? PostgreSQL requires the index in order to refresh the view concurrently. 

I tried calling the normal Index function, but it threw an exception when I passed it a materialized view object (but worked perfectly when I passed a table object, so I know it's not incorrect params). So instead, I created a new subclass of DDLElement specifically for indexing materialized views... you can see it in the gist, and it works fine, just not sure if there's a better way. 

3) How do I set this index-creating-ddlelement to trigger (using sqlalchemy events) after the materialized view is created? 

Currently I first call CreateView_DDLElement.execute_at('after-create', db.metadata), and then immediately after call CreateViewIndex_DDLElement.execute_at('after-create', db.metadata). So far it's worked fine, but there's a potential for the 'create index' to be called before 'create view' since they both are listening for the same event. So how do I set this index to be triggered on completion of creating the view?

More than happy to submit a PR helping tidy up the docs on this, just not sure what I should be saying. 

Cheers,

Jeff

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Jeff Widman

jeffwidman.com | 740-WIDMAN-J (943-6265) 

<><

ᐧ

[Mike Bayer]

On 11/16/2015 02:23 AM, Jeff Widman wrote:
> Thanks Mike. That helped tremendously.
>
> A couple of followup questions:
> (questions based on this code extending the view recipe:
> https://gist.github.com/jeffwidman/1656498de21dc0afcdab)
>
> 1) In the recipe for creating views, I see:
>
> CreateView(name, selectable).execute_at('after-create', metadata)
>
>
> But in the docs it looks like this 'execute_at()' method is deprecated

> <http://docs.sqlalchemy.org/en/latest/core/ddl.html#sqlalchemy.schema.DDLElement.execute_at>,

> should I be doing something different?

execute_at is not really going to go away but the new way is
execute_if(), as detailed at
http://docs.sqlalchemy.org/en/latest/core/ddl.html#sqlalchemy.schema.DDLElement.execute_if

> Also, not clear to my why this method is deprecated,

because once the event system was added it became clear that the
execute_at() system was redundant, and it's always a good idea to
express as many behaviors as possible via the fewest number of internal
mechanisms.

especially since
> the descriptions on that page for several other methods say 'see
> execute_at() for more information' so it doesn't exactly "feel" deprecated.

I see it just at DDLElement.execute() where that doc should be updated.
Where else?

>
>
> 2) What is the preferred way to create a unique index on the
> materialized view? PostgreSQL requires the index in order to refresh the
> view concurrently.

Index(..., unique=True) should work?

>
> I tried calling the normal Index function, but it threw an exception
> when I passed it a materialized view object

what exception?

(but worked perfectly when I
> passed a table object, so I know it's not incorrect params).

OK this might be because that recipe uses a lower-case "table()" object
which is not as full featured as Table, i'd alter the recipe to use
Table perhaps

So instead,
> I created a new subclass of DDLElement specifically for indexing
> materialized views... you can see it in the gist, and it works fine,
> just not sure if there's a better way.

sounds like a good workaround

>
>
> 3) How do I set this index-creating-ddlelement to trigger (using
> sqlalchemy events) after the materialized view is created?
>
> Currently I first call CreateView_DDLElement.execute_at('after-create',
> db.metadata), and then immediately after call
> CreateViewIndex_DDLElement.execute_at('after-create', db.metadata). So
> far it's worked fine, but there's a potential for the 'create index' to
> be called before 'create view' since they both are listening for the
> same event. So how do I set this index to be triggered on completion of
> creating the view?

so there are event hooks for "after table create" and "after metadata
create" but not "after my custom DDLElement create", which might be a
problem, although, the events here are run in deterministic order based
on when they were assigned as listeners. Or you might want to
implement a custom "after_create" listener that just calls upon
CreateView create and CreateViewIndex directly.

>
> More than happy to submit a PR helping tidy up the docs on this, just
> not sure what I should be saying.

well we'd have to see what we get at the end and if it looks usable or
not. I guess this would all be part of that recipe for now. An
eventual feature would draw from all the lessons learned here.

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> > <mailto:sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> > To post to this group, send email to sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>

> > <mailto:sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>>.
> > Visit this group at http://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
>
> --

> *
> Jeff Widman*
> jeffwidman.com <http://www.jeffwidman.com/> | 740-WIDMAN-J (943-6265)
> <><
> ᐧ

[Jeff Widman]

> the descriptions on that page for several other methods say 'see
> execute_at() for more information' so it doesn't exactly "feel" deprecated.

I see it just at DDLElement.execute() where that doc should be updated.
 Where else?

I think here as well?

http://docs.sqlalchemy.org/en/latest/core/ddl.html#sqlalchemy.schema.DDL.params.statement

http://docs.sqlalchemy.org/en/latest/core/compiler.html?highlight=execute_at#subclassing-guidelines (search for execute_at)

 

> I tried calling the normal Index function, but it threw an exception
> when I passed it a materialized view object

what exception?

OK this might be because that recipe uses a lower-case "table()" object
which is not as full featured as Table, i'd alter the recipe to use
Table perhaps

 

It may be related to using __table__() rather than Table(). I tried altering the recipe to use Table(), but couldn't figure it out, since my materialized views need to be created after the other tables are created. Perhaps Flask-SQLAlchemy was doing some magic when I called db.create_all() that grabbed everything with Table().

Not a big deal. I'm planning to blog how I worked everything out later this week as reference for anyone else trying to create/manage a postgresql materialized view with sqlalchemy, and when I do I'll send out a link to the list.

For reference, here is the exception:

# materialized view already exists as a __table__() object called 'GearCategoryMV'

# Afterwards I try creating an index on it:

db.Index('myindex', GearCategoryMV.id, unique=False)

# fails with the following traceback

Traceback (most recent call last):

  File "manage.py", line 11, in <module>

    from app.models.gear_models import (GearCategory, GearCategoryMV, GearItem,

  File "/Users/jeffwidman/Code/rc/api_rc_flask/app/models/gear_models.py", line 428, in <module>

    db.Index('myindex', GearCategoryMV.id, unique=True)

  File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 3172, in __init__

    ColumnCollectionMixin.__init__(self, *columns)

  File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 2446, in __init__

    self._check_attach()

  File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 2491, in _check_attach

    col._on_table_attach(_col_attached)

  File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 1306, in _on_table_attach

    fn(self, self.table)

  File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 2488, in _col_attached

    self._check_attach(evt=True)

  File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 2479, in _check_attach

    assert not evt, "Should not reach here on event call"

AssertionError: Should not reach here on event call

ᐧ

[Mike Bayer]

> /# materialized view already exists as a __table__() object called
> 'GearCategoryMV'/
> /# Afterwards I try creating an index on it:/
>
> /db.Index('myindex', GearCategoryMV.id, unique=False)/
> /
> /
> /# fails with the following traceback/
> /
> /
> /Traceback (most recent call last):/
> / File "manage.py", line 11, in <module>/
> / from app.models.gear_models import (GearCategory, GearCategoryMV,
> GearItem,/
> / File
> "/Users/jeffwidman/Code/rc/api_rc_flask/app/models/gear_models.py", line
> 428, in <module>/
> / db.Index('myindex', GearCategoryMV.id, unique=True)/
> / File
> "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",
> line 3172, in __init__/
> / ColumnCollectionMixin.__init__(self, *columns)/
> / File
> "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",
> line 2446, in __init__/
> / self._check_attach()/
> / File
> "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",
> line 2491, in _check_attach/
> / col._on_table_attach(_col_attached)/
> / File
> "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",
> line 1306, in _on_table_attach/
> / fn(self, self.table)/
> / File
> "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",
> line 2488, in _col_attached/
> / self._check_attach(evt=True)/
> / File
> "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",
> line 2479, in _check_attach/
> / assert not evt, "Should not reach here on event call"/
> /AssertionError: Should not reach here on event call/

oh...if you can send me a short script that reproduces that there's
likely a bug.

> ᐧ
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.

> Visit this group at https://groups.google.com/group/sqlalchemy.