Calling Index() on a materialized view throws unexpected exception
Issue #3616
resolved
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)
-
repo owner
-
repo owner
please work with the above self-contained script to show me how to get your error, thanks!
-
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 calleddb.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. -
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 indexthese 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...
-
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 callGearMV is an undefined name inside the block. Python throws NameError as expected.
-
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 indexThere'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.
-
repo owner
oh nevermind, fourth one, wrong because the first argument to Index is always the index name. This index has no columns. Same with the first one.
-
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.
-
repo owner
Sixth one again the Index has no columns, the first argument is the name always.
-
repo owner
- changed status to resolved
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) -
repo owner
- Fixed an assertion that would raise somewhat inappropriately
if a :class:
.Indexwere associated with a :class:.Columnthat 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>>
- Fixed an assertion that would raise somewhat inappropriately
if a :class:
-
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.
-
reporter
Thanks Mike for the detailed reply.
I (obviously) didn't realize
db.Indexrequired 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 ifindexrealized it wasn't getting a name and threw an exception. Although sinceindextakes a variable numbers of args, I suppose this is only possible if you changenamearg to a keyword arg which is probably more hassle than its worth.It makes complete sense to me that
Indexshould be associated with aTableobject. 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 aTableobject 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.
-
reporter
Thanks again for the help on this Mike--I updated my blog post on sqlalchemy + postgresql materialized views using the new code posted above.
- Log in to comment
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 ;) ):
output: