Issue 1:
Migrated issue, originally created by cieplak (@cieplak)

Table reflection works in Postgres for regular views, but when reflecting materialized views, a sqlalchemy.exc.NoSuchTableError exception is raised.

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>5))
engine.execute(createview)

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

for r in engine.execute(v.select()):
print r

Issue 2:
Migrated issue, originally created by Erich Seifert (@eseifert)

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)

`#!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).