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?
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
On Mon, Nov 9, 2015 at 1:58 PM, Mike Bayer <mike...@zzzcomputing.com> wrote:
>
>
> 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+unsubscr...@googlegroups.com
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> > To post to this group, send email to sqlalchemy@googlegroups.com
> > <mailto:sqlalchemy@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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@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)
<><
ᐧ
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
