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. > > Cheers, > Jeff > > > On Mon, Nov 9, 2015 at 1:58 PM, Mike Bayer <mike...@zzzcomputing.com > <mailto: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%2bunsubscr...@googlegroups.com> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com > <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>. > > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com> > > <mailto: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 > <mailto:sqlalchemy%2bunsubscr...@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. > > > > > -- > * > 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 > <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.
