[Sqlalchemy-tickets] [sqlalchemy] #2891: Support materialized views in PostgreSQL dialect

[sqlalchemy <mi...@zz...>]

#2891: Support materialized views in PostgreSQL dialect
-------------------------+-----------------------------------------
 Reporter:  eseifert     |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  medium       |       Milestone:  0.8.xx
Component:  postgres     |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 At the moment, with SQLAlchemy 0.8.4, introspecting a materialized view in
 a PostgreSQL 9.3.2 database fails with:

 {{{
   File "/tmp/test/meterializedview.py", line 16, in __init__
     self.MaterializedView = Table('mytable', MetaData(), autoload=True,
 autoload_with=self.db)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line
 332, in __new__
     table._init(name, metadata, *args, **kw)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line
 396, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line
 413, in _autoload
     self, include_columns, exclude_columns
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1595, in run_callable
     return conn.run_callable(callable_, *args, **kwargs)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1118, in run_callable
     return callable_(self, *args, **kwargs)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py",
 line 262, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 414, in reflecttable
     for col_d in self.get_columns(table_name, schema, **tblkw):
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 254, in get_columns
     **kw)
   File "<string>", line 1, in <lambda>
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 49, in cache
     ret = fn(self, con, *args, **kw)
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 1678, in
 get_columns
     info_cache=kw.get('info_cache'))
   File "<string>", line 1, in <lambda>
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 49, in cache
     ret = fn(self, con, *args, **kw)
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 1593, in
 get_table_oid
     raise exc.NoSuchTableError(table_name)
 }}}

 {{{get_table_oid()}}} executes the following statement:

 {{{#!sql
 SELECT c.oid
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE (%s)
 AND c.relname = :table_name AND c.relkind in ('r','v')
 }}}

 However, a new {{{relkind}}} value {{{m}}} was introduced for materialized
 views, which isn't handled at the moment.

 To (partly) solve the problem for me, i just added {{{m}}} to the where
 clause:

 {{{
 AND c.relname = :table_name AND c.relkind in ('r','v','m')
 }}}

 There are several other cases where {{{relkind}}} is used
 ({{{get_table_names}}}, {{{get_view_names}}}, {{{get_indexes}}}, etc.)
 where it isn't clear how to deal with materialized view because they are a
 hybrid of tables and views and can have their own indexes and table
 spaces.

 As far as I can see, the same issue exists in SQLAlchemy 0.9
 ([browser:lib/sqlalchemy/dialects/postgresql/base.py], rev 164bff0).

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2891>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python