<p dir="auto"><strong>Migrated issue, originally created by cieplak (<a href="https://github.com/cieplak">@cieplak</a>)</strong></p>
<p dir="auto">Table reflection works in Postgres for regular views, but when reflecting materialized views, a sqlalchemy.exc.NoSuchTableError exception is raised.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import Table
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement


class CreateView(Executable, ClauseElement):
    def __init__(self, name, select):
        self.name = name
        self.select = select


@compiles(CreateView)
def visit_create_view(element, compiler, **kw):
    return &quot;CREATE MATERIALIZED VIEW %s AS %s&quot; % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )


from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.engine import create_engine


uri = 'postgresql://postgres@localhost/postgres'
engine = create_engine(uri)
metadata = MetaData(engine)
t = Table('t',
          metadata,
          Column('id', Integer, primary_key=True),
          Column('number', Integer))
t.create()
engine.execute(t.insert().values(id=1, number=3))
engine.execute(t.insert().values(id=9, number=-3))

createview = CreateView('viewname', t.select().where(t.c.id&gt;5))
engine.execute(createview)

v = Table('viewname', metadata, autoload=True)

for r in engine.execute(v.select()):
    print r"><pre class="notranslate"><code class="notranslate">from sqlalchemy import Table
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement


class CreateView(Executable, ClauseElement):
    def __init__(self, name, select):
        self.name = name
        self.select = select


@compiles(CreateView)
def visit_create_view(element, compiler, **kw):
    return "CREATE MATERIALIZED VIEW %s AS %s" % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )


from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.engine import create_engine


uri = 'postgresql://postgres@localhost/postgres'
engine = create_engine(uri)
metadata = MetaData(engine)
t = Table('t',
          metadata,
          Column('id', Integer, primary_key=True),
          Column('number', Integer))
t.create()
engine.execute(t.insert().values(id=1, number=3))
engine.execute(t.insert().values(id=9, number=-3))

createview = CreateView('viewname', t.select().where(t.c.id&gt;5))
engine.execute(createview)

v = Table('viewname', metadata, autoload=True)

for r in engine.execute(v.select()):
    print r
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Erich Seifert (<a href="https://github.com/eseifert">@eseifert</a>)</strong></p>
<p dir="auto">At the moment, with SQLAlchemy 0.8.4, introspecting a materialized view in a PostgreSQL 9.3.2 database fails with:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="  File &quot;/tmp/test/meterializedview.py&quot;, line 16, in __init__
    self.MaterializedView = Table('mytable', MetaData(), autoload=True, autoload_with=self.db)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py&quot;, line 332, in __new__
    table._init(name, metadata, *args, **kw)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py&quot;, line 396, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py&quot;, line 413, in _autoload
    self, include_columns, exclude_columns
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1595, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1118, in run_callable
    return callable_(self, *args, **kwargs)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py&quot;, line 262, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py&quot;, line 414, in reflecttable
    for col_d in self.get_columns(table_name, schema, **tblkw):
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py&quot;, line 254, in get_columns
    **kw)
  File &quot;&lt;string&gt;&quot;, line 1, in &lt;lambda&gt;
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py&quot;, line 49, in cache
    ret = fn(self, con, *args, **kw)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py&quot;, line 1678, in get_columns
    info_cache=kw.get('info_cache'))
  File &quot;&lt;string&gt;&quot;, line 1, in &lt;lambda&gt;
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py&quot;, line 49, in cache
    ret = fn(self, con, *args, **kw)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py&quot;, line 1593, in get_table_oid
    raise exc.NoSuchTableError(table_name)




`#!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')"><pre class="notranslate"><code class="notranslate">  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 "&lt;string&gt;", line 1, in &lt;lambda&gt;
  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 "&lt;string&gt;", line 1, in &lt;lambda&gt;
  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)




`#!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')
</code></pre></div>
<p dir="auto">However, a new <code class="notranslate">relkind</code> value <code class="notranslate">m</code> was introduced for materialized views, which isn't handled at the moment.</p>
<p dir="auto">To (partly) solve the problem for me, i just added <code class="notranslate">m</code> to the where clause:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="AND c.relname = :table_name AND c.relkind in ('r','v','m')"><pre class="notranslate"><code class="notranslate">AND c.relname = :table_name AND c.relkind in ('r','v','m')
</code></pre></div>
<p dir="auto">There are several other cases where <code class="notranslate">relkind</code> is used (<code class="notranslate">get_table_names</code>, <code class="notranslate">get_view_names</code>, <code class="notranslate">get_indexes</code>, 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.</p>
<p dir="auto">As far as I can see, the same issue exists in SQLAlchemy 0.9 (browser:lib/sqlalchemy/dialects/postgresql/base.py, rev <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/164bff07496c345c3c57a8b26439aa6a0fbce3b8/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/164bff07496c345c3c57a8b26439aa6a0fbce3b8"><tt>164bff0</tt></a>).</p>