Calling Index() on a materialized view throws unexpected exception

Issue #3616 resolved
Jeff Widman
created an issue

Followup on this discussion on the mailing list: https://groups.google.com/forum/m/#!topic/sqlalchemy/2AoHW95Iqyw

My recipe for creating/managing materialized views using SQLAlchemy is at the bottom of this blog post: http://www.jeffwidman.com/blog/847/using-sqlalchemy-to-create-and-manage-postgresql-materialized-views/

There's also an example materialized view there, where I created an index using a custom DDL command.

Trying to use the normal Index(materialized_view.column, unique=True) syntax throws the following exception:

/db.Index(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/

Let me know if I can do anything else to help here.

Comments (14)

  1. Michael Bayer repo owner

    Let's work with only complete, self contained examples (see http://stackoverflow.com/help/mcve) so that the work of reproducing the error is done ahead of time, and let's take out the Flask dependencies so that it can be eliminated as a factor.

    Extracting segments of your blog post into something produces a script that does not reproduce this error (though I've no doubt there's a bug because that assertion, as it says, should be impossible to reach ;) ):

    # materialized_view_factory.py
    from sqlalchemy.ext import compiler
    from sqlalchemy.schema import DDLElement
    
    import sqlalchemy as db
    
    
    class CreateMaterializedView(DDLElement):
        def __init__(self, name, selectable):
            self.name = name
            self.selectable = selectable
    
    
    @compiler.compiles(CreateMaterializedView)
    def compile(element, compiler, **kw):
        # Could use "CREATE OR REPLACE MATERIALIZED VIEW..."
        # but I'd rather have noisy errors
        return "CREATE MATERIALIZED VIEW %s AS %s" % (element.name,
                                compiler.sql_compiler.process(element.selectable))
    
    
    def create_mat_view(metadata, name, selectable):
        t = db.table(name)
        for c in selectable.c:
            c._make_proxy(t)
        CreateMaterializedView(name, selectable).execute_at('after-create', metadata)
        db.DDL('DROP MATERIALIZED VIEW IF EXISTS ' + name
                                                ).execute_at('before_drop', metadata)
        return t
    
    from sqlalchemy.ext.declarative import declarative_base
    Base = declarative_base()
    
    
    class Gear(Base):
        __tablename__ = 'gear'
        id = db.Column(db.Integer, primary_key=True)
        rating = db.Column(db.Integer)
    
    view = create_mat_view(
        Base.metadata,
        "gear_item_mv",
        db.select(
            [Gear.id.label('id'),
             db.func.count(Gear.id).label('review_count'),
             db.func.avg(Gear.rating).label('review_rating'),]
        ).select_from(Gear)
        .group_by(Gear.id))
    
    
    e = db.create_engine("postgresql://scott:tiger@localhost/test", echo=True)
    Base.metadata.drop_all(e)
    Base.metadata.create_all(e)
    Base.metadata.drop_all(e)
    

    output:

    $ python test.py
    2015-12-22 10:11:20,310 INFO sqlalchemy.engine.base.Engine select version()
    2015-12-22 10:11:20,310 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,311 INFO sqlalchemy.engine.base.Engine select current_schema()
    2015-12-22 10:11:20,311 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,312 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
    2015-12-22 10:11:20,312 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,313 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
    2015-12-22 10:11:20,313 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,314 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
    2015-12-22 10:11:20,314 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,315 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
    2015-12-22 10:11:20,315 INFO sqlalchemy.engine.base.Engine {'name': u'gear'}
    2015-12-22 10:11:20,316 INFO sqlalchemy.engine.base.Engine DROP MATERIALIZED VIEW IF EXISTS gear_item_mv
    2015-12-22 10:11:20,316 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,317 INFO sqlalchemy.engine.base.Engine COMMIT
    2015-12-22 10:11:20,319 INFO sqlalchemy.engine.base.Engine 
    DROP TABLE gear
    2015-12-22 10:11:20,319 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,320 INFO sqlalchemy.engine.base.Engine COMMIT
    2015-12-22 10:11:20,323 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
    2015-12-22 10:11:20,323 INFO sqlalchemy.engine.base.Engine {'name': u'gear'}
    2015-12-22 10:11:20,325 INFO sqlalchemy.engine.base.Engine 
    CREATE TABLE gear (
        id SERIAL NOT NULL, 
        rating INTEGER, 
        PRIMARY KEY (id)
    )
    
    
    2015-12-22 10:11:20,326 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,331 INFO sqlalchemy.engine.base.Engine COMMIT
    2015-12-22 10:11:20,333 INFO sqlalchemy.engine.base.Engine CREATE MATERIALIZED VIEW gear_item_mv AS SELECT gear.id AS id, count(gear.id) AS review_count, avg(gear.rating) AS review_rating 
    FROM gear GROUP BY gear.id
    2015-12-22 10:11:20,333 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,341 INFO sqlalchemy.engine.base.Engine COMMIT
    2015-12-22 10:11:20,343 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
    2015-12-22 10:11:20,343 INFO sqlalchemy.engine.base.Engine {'name': u'gear'}
    2015-12-22 10:11:20,344 INFO sqlalchemy.engine.base.Engine DROP MATERIALIZED VIEW IF EXISTS gear_item_mv
    2015-12-22 10:11:20,344 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,346 INFO sqlalchemy.engine.base.Engine COMMIT
    2015-12-22 10:11:20,348 INFO sqlalchemy.engine.base.Engine 
    DROP TABLE gear
    2015-12-22 10:11:20,348 INFO sqlalchemy.engine.base.Engine {}
    2015-12-22 10:11:20,349 INFO sqlalchemy.engine.base.Engine COMMIT
    
  2. Jeff Widman reporter

    Sorry about that, I was in a rush when I filed this and forgot to do a decent replication script. Thanks for taking the time to extract it.

    Here's a modified version of the above script where I've included a number of ways that I called db.Index() with each command followed by comments on what happened. It appears the 'Should not reach here on event call' error only triggers when I include a name for the index.

    I also included a number of ways where I included a call to db.Index() where I expected that when I called db.create_all() these would either throw an exception or succeed. They didn't throw an exception, and when I manually inspected the PostgreSQL database the index wasn't actually created.

    Again, happy to do whatever is needed to help out here.


    # materialized_view_factory.py
    from sqlalchemy.ext import compiler
    from sqlalchemy.schema import DDLElement
    
    import sqlalchemy as db
    
    
    class CreateMaterializedView(DDLElement):
        def __init__(self, name, selectable):
            self.name = name
            self.selectable = selectable
    
    
    @compiler.compiles(CreateMaterializedView)
    def compile(element, compiler, **kw):
        # Could use "CREATE OR REPLACE MATERIALIZED VIEW..."
        # but I'd rather have noisy errors
        return "CREATE MATERIALIZED VIEW %s AS %s" % (element.name,
                                compiler.sql_compiler.process(element.selectable))
    
    
    def create_mat_view(metadata, name, selectable):
        t = db.table(name)
        for c in selectable.c:
            c._make_proxy(t)
        CreateMaterializedView(name, selectable).execute_at('after-create', metadata)
        db.DDL('DROP MATERIALIZED VIEW IF EXISTS ' + name
                                                ).execute_at('before_drop', metadata)
        return t
    
    from sqlalchemy.ext.declarative import declarative_base
    Base = declarative_base()
    
    
    class Gear(Base):
        __tablename__ = 'gear'
        id = db.Column(db.Integer, primary_key=True)
        rating = db.Column(db.Integer)
    
    
    class GearMV(Base):
        __table__ = create_mat_view(
                        Base.metadata,
                        "gear_mv",
                        db.select(
                            [Gear.id.label('id'),
                             db.func.count(Gear.id).label('review_count'),
                             db.func.avg(Gear.rating).label('review_rating'),]
                        ).select_from(Gear)
                        .group_by(Gear.id))
    
        # db.Index('id', unique=True)
        # doesn't complain, but also doesn't actually create an index
    
        # db.Index('test_index', 'id', unique=True)
        # doesn't complain, but also doesn't actually create an index
    
        # db.Index('test_index', GearMV.id, unique=True)
        # throws: AssertionError: Should not reach here on event call
    
    
    # db.Index(GearMV.id, unique=True)
    # doesn't complain, but also doesn't actually create an index
    
    
    e = db.create_engine("postgresql://scott:tiger@localhost/test", echo=True) # Mike's DB
    # e = db.create_engine("postgresql://rc_com@localhost/rc_test", echo=True) # Jeff's DB
    Base.metadata.drop_all(e)
    Base.metadata.create_all(e)
    
    
    # db.Index('test', GearMV.id, unique=True).create(e)
    # throws: AssertionError: Should not reach here on event call
    
    # db.Index(GearMV.id, unique=True).create(e)
    # throws: sqlalchemy.exc.CompileError: Index 'GearMV.id' is not associated with
    # any table. Not sure why, because the materialized view is created at this
    # point.
    
  3. Michael Bayer repo owner

    OK first two:

        # db.Index('id', unique=True)
        # doesn't complain, but also doesn't actually create an index
    
        # db.Index('test_index', 'id', unique=True)
        # doesn't complain, but also doesn't actually create an index
    

    these Index objects aren't associated with any MetaData or Table object - they are loaded up with string values and that's it. If you want an all-string-defined Index to be part of a Table you should call my_table.append_constraint(my_index).

    looking at the rest...

  4. Michael Bayer repo owner

    third one, not possible:

    class GearMV(Base):
        # ...
    
        db.Index('test_index', GearMV.id, unique=True)
        # throws: AssertionError: Should not reach here on event call
    

    GearMV is an undefined name inside the block. Python throws NameError as expected.

  5. Michael Bayer repo owner

    fourth one, I begin to notice where this is wrong:

    db.Index(GearMV.id, unique=True)
    # doesn't complain, but also doesn't actually create an index
    

    There's no Index because GearMV is not mapped to a Table. Only the Table construct supports piggy-backing indexes such that the CreateIndex/DropIndex DDL is associated with them. The best we have is a lower-case table() object in create_mat_view(). We can't use Table as-is there because the semi-private _make_proxy() API doesn't support a Table as a target. None of these patterns have ever been supported before.

  6. Michael Bayer repo owner

    Fifth one, now we see the assertion raised is not where I thought it was, e.g. inside the event system where there are some very deep assertions going on (no stack trace was added on this issue), it's a fairly boring one inside the schema package and is not very interesting, it's because again Index has no support for being associated with a lower-case-t table() object in this way.

  7. Michael Bayer repo owner

    the assertion here could use some cleanup and in fact we'd be better off trying to more explicitly check that the index is not associated with the expected kind of object, but for the case here, here is use of modern events as well as foregoing the use of _make_proxy(), just create a new Column and append:

    # materialized_view_factory.py
    from sqlalchemy.ext import compiler
    from sqlalchemy.schema import DDLElement
    from sqlalchemy import event
    import sqlalchemy as db
    
    
    class CreateMaterializedView(DDLElement):
        def __init__(self, name, selectable):
            self.name = name
            self.selectable = selectable
    
    
    @compiler.compiles(CreateMaterializedView)
    def compile(element, compiler, **kw):
        # Could use "CREATE OR REPLACE MATERIALIZED VIEW..."
        # but I'd rather have noisy errors
        return "CREATE MATERIALIZED VIEW %s AS %s" % (
            element.name,
            compiler.sql_compiler.process(element.selectable))
    
    
    def create_mat_view(metadata, name, selectable):
        mt = db.MetaData()
        t = db.Table(name, mt)
        for c in selectable.c:
            t.append_column(db.Column(c.name, c.type, primary_key=c.primary_key))
    
        event.listen(
            metadata, "after_create",
            CreateMaterializedView(name, selectable)
        )
    
        @event.listens_for(metadata, "after_create")
        def create_indexes(target, connection, **kw):
            for idx in t.indexes:
                idx.create(connection)
    
        event.listen(
            metadata, "before_drop",
            db.DDL('DROP MATERIALIZED VIEW IF EXISTS ' + name)
        )
        return t
    
    from sqlalchemy.ext.declarative import declarative_base
    Base = declarative_base()
    
    
    class Gear(Base):
        __tablename__ = 'gear'
        id = db.Column(db.Integer, primary_key=True)
        rating = db.Column(db.Integer)
    
    
    class GearMV(Base):
        __table__ = create_mat_view(
            Base.metadata,
            "gear_mv",
            db.select(
                [Gear.id.label('id'),
                 db.func.count(Gear.id).label('review_count'),
                 db.func.avg(Gear.rating).label('review_rating'),]
            ).select_from(Gear)
            .group_by(Gear.id))
    
    db.Index('test_index', GearMV.id, unique=True)
    
    
    e = db.create_engine("postgresql://scott:tiger@localhost/test", echo=True) # Mike's DB
    # e = db.create_engine("postgresql://rc_com@localhost/rc_test", echo=True) # Jeff's DB
    Base.metadata.drop_all(e)
    Base.metadata.create_all(e)
    
    Base.metadata.drop_all(e)
    
  8. Michael Bayer repo owner
    • Fixed an assertion that would raise somewhat inappropriately if a :class:.Index were associated with a :class:.Column that is associated with a lower-case-t :class:.TableClause; the association should be ignored for the purposes of associating the index with a :class:.Table. fixes #3616

    → <<cset c7d6c667b53d>>

  9. Michael Bayer repo owner

    with the above fix, your script will produce "this Index is not associated with any Table" instead of the assertion failure. For the time being, the Index really needs to be associated with a Table object and that's it.

  10. Jeff Widman reporter

    Thanks Mike for the detailed reply.

    I (obviously) didn't realize db.Index required a name--I just assumed if it wasn't passed a name that it would create an anonymous index. It's my fault, as it states this right in the docs. Be convenient if index realized it wasn't getting a name and threw an exception. Although since index takes a variable numbers of args, I suppose this is only possible if you change name arg to a keyword arg which is probably more hassle than its worth.

    It makes complete sense to me that Index should be associated with a Table object. I just didn't know how to do that, so I very much appreciate you demonstrating it. I'd spent about half-a-day originally trying to get a Table object to work couldn't... I kept running into issues with db.metadata--I see you bypassed that by creating a separate metadata to use for the materialized view creation, although I don't quite understand how it gets swapped back to using the same db.metadata as the normal tables.

    Let me play around with this a little and then I'll update my blog post with this fixed recipe for creating a materialized view as a Table object.

  11. Log in to comment