Re: [sqlalchemy] Best way to implement PostgreSQL materialized views?

[Jonathan Vanasco]

There also exists a 3rd party library that has been somewhat maintained: 
https://github.com/jklukas/sqlalchemy-views

IIRC, it is largely based on the Wiki recipe that Mike linked to.


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/8dc8616e-2362-45f9-8c34-aafc171875d6%40googlegroups.com.

Re: [sqlalchemy] Best way to implement PostgreSQL materialized views?

[Ethan Ralph]

Replies inline.

On Wednesday, April 29, 2020 at 1:55:49 PM UTC, Mike Bayer wrote:
>
> Addition of views to SQLAlchemy is an extremely long term issue that has 
> never been done, largely because we have a recipe that works well enough 
> for most cases, which you have probably seen, at 
> https://github.com/sqlalchemy/sqlalchemy/wiki/Views. 
> The issue itself 
> is https://github.com/sqlalchemy/sqlalchemy/issues/812, 
> 
>  
> keep in mind all those comments are 13 years old.
>
> Yes, I wasn't planning this as a generic ready-for-mainline 
implementation, I simply wanted to use it in my own project. I know that it 
has a lot of shortcomings, and just wanted to add whatever was necessary 
for me to use materialized views. But now that you mention it, you have 
gotten me interested on getting work underway to implement some sort of 
views. Perhaps with help from the SQLAlchemy maintainers/community I could 
implement such a feature in a fork.

> A real View object would feature the "materialized" part as just a boolean 
> flag since AFAICT the "materialized" aspect of it only presents itself as a 
> keyword to the user and possibly some other options, the rest of what goes 
> on has to do with the internals of the database.   The class hierarchy 
> would be the same as that of Table:  class View(DialectKWArgs, SchemaItem, 
> TableClause):and the object would need to act pretty much like Table in 
> just about every way, including the metadata support, autoload, etc.
>
I see, thanks for the info. I don't know too much about the SQLAlchemy 
internals so not sure about how to implement the things you mentioned.

>   Basically it would have an extra field indicating the SELECT source of 
> the data, which based on modern conventions would be presented either as a 
> select() object against other Table objects, or as a text() construct.
>
text() might work better for Alembic migrations... But maybe a method could 
be provided which turns the select() into a text() (if that's possible).

>
> on the DDL side, a command like metadata.create_all() / 
> metadata.drop_all() would need to be smart about when it creates / drops 
> these views.   views are first and foremost dependent on the Table objects 
> that they select against.   However, they can also be dependent on each 
> other, as you can make views from views.  so a full create/drop sequence 
> would likely need to assemble this list of dependencies in the same way 
> that Table objects are dependent on each other via foreign key 
> relationship, and then ensure creates/drops are done in the right order.   
> There is a lot of infrastructure in lib/sqlalchemy/sql/ddl.py that does 
> work like this which should be leveraged here.
>
I see. Wouldn't it be easy enough for the expression API to discover all 
the tables that are used in a select() construct (and in fact, it already 
has to do this anyway)?

>
> The good news for views is that reflection is actually already implemented 
> for views in that they come back as Table objects, and even the "view 
> definition" can be retrieved however that's not integrated into the schema 
> system.
>
> Can you give a little bit more detail about how to implement the retrieval 
of the view definition? As far as I can tell, SQLAlchemy is already able to 
fetch the table definitions (because Alembic can magically generate 
CreateTable operations in downgrades despite the code for the table not 
existing in the codebase anymore). Would it work in a similar fashion?

> So there's a lot to think about here and overall views haven't happened 
> because there is a lot to consider in terms of a fully complete feature 
> that requires no special user intervention, especially the DDL / dependency 
> issue, whereas the recipe plus the reflection support has been most of what 
> people need.If you have proposals for how things would look then you 
> can use issue 812 for notes.
>
It feels like not much effort has been put in because people are content 
with the recipe. I'm fine with it too, but I'm wondering if some work can 
be started towards a real mainlined View object in SQLAlchemy. 

>
>
>
> On Tue, Apr 28, 2020, at 12:05 PM, Ethan Ralph wrote:
>
> Hello. I am currently working on an implementation of PostgreSQL 
> materialized views in SQLAlchemy and Alembic. To give a basic explanation, 
> materialized views are regular views but all the rows are computed 
> statically, and you can refresh them with a REFRESH MATERIALIZED VIEW query.
> I've attached the code I have so far. It uses sqlalchemy.sql.table and 
> column proxies to simulate a table, and also implements Alembic operations 
> with autogenerate. I want to make MaterializedView into a proper class like 
> Table. What would be the best way to go about this? What 

Re: [sqlalchemy] Best way to implement PostgreSQL materialized views?

[Mike Bayer]

Addition of views to SQLAlchemy is an extremely long term issue that has never 
been done, largely because we have a recipe that works well enough for most 
cases, which you have probably seen, at 
https://github.com/sqlalchemy/sqlalchemy/wiki/Views. The issue itself is 
https://github.com/sqlalchemy/sqlalchemy/issues/812, keep in mind all those 
comments are 13 years old.

A real View object would feature the "materialized" part as just a boolean flag 
since AFAICT the "materialized" aspect of it only presents itself as a keyword 
to the user and possibly some other options, the rest of what goes on has to do 
with the internals of the database. The class hierarchy would be the same as 
that of Table: class View(DialectKWArgs, SchemaItem, TableClause): and the 
object would need to act pretty much like Table in just about every way, 
including the metadata support, autoload, etc. Basically it would have an extra 
field indicating the SELECT source of the data, which based on modern 
conventions would be presented either as a select() object against other Table 
objects, or as a text() construct.

on the DDL side, a command like metadata.create_all() / metadata.drop_all() 
would need to be smart about when it creates / drops these views. views are 
first and foremost dependent on the Table objects that they select against. 
However, they can also be dependent on each other, as you can make views from 
views. so a full create/drop sequence would likely need to assemble this list 
of dependencies in the same way that Table objects are dependent on each other 
via foreign key relationship, and then ensure creates/drops are done in the 
right order. There is a lot of infrastructure in lib/sqlalchemy/sql/ddl.py that 
does work like this which should be leveraged here.

The good news for views is that reflection is actually already implemented for 
views in that they come back as Table objects, and even the "view definition" 
can be retrieved however that's not integrated into the schema system.

So there's a lot to think about here and overall views haven't happened because 
there is a lot to consider in terms of a fully complete feature that requires 
no special user intervention, especially the DDL / dependency issue, whereas 
the recipe plus the reflection support has been most of what people need. If 
you have proposals for how things would look then you can use issue 812 for 
notes.




On Tue, Apr 28, 2020, at 12:05 PM, Ethan Ralph wrote:
> Hello. I am currently working on an implementation of PostgreSQL materialized 
> views in SQLAlchemy and Alembic. To give a basic explanation, materialized 
> views are regular views but all the rows are computed statically, and you can 
> refresh them with a REFRESH MATERIALIZED VIEW query.
> I've attached the code I have so far. It uses sqlalchemy.sql.table and column 
> proxies to simulate a table, and also implements Alembic operations with 
> autogenerate. I want to make MaterializedView into a proper class like Table. 
> What would be the best way to go about this? What should I inherit from? 
> Which methods should I override? Any input is appreciated.
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/ee389a85-cba5-4773-9862-a412aa0e67ca%40googlegroups.com
>  
> .
> 
> 
> *Attachments:*
>  * _materialized_view.py

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/dd32137d-4400-4c4f-b6cc-90d2a20c8cb6%40www.fastmail.com.