langchain Materialized Views not accessible

System Info

When specifying a materialized view in include_tables, the following error occurs:

include_tables {'table_name'} not found in database

Who can help?

No response

Information

[ ] The official example notebooks/scripts
[ ] My own modified scripts

Related Components

[ ] LLMs/Chat Models
[ ] Embedding Models
[ ] Prompts / Prompt Templates / Prompt Selectors
[ ] Output Parsers
[ ] Document Loaders
[ ] Vector Stores / Retrievers
[ ] Memory
[ ] Agents / Agent Executors
[ ] Tools / Toolkits
[X] Chains
[ ] Callbacks/Tracing
[ ] Async

Reproduction

include_tables=['table1', 'table2']

db = SQLDatabase.from_uri("postgresql://xxx:xxx@xxx:5432/xxx", include_tables=include_tables)

Expected behavior

Materialized views should be accessible.

May 08 '23 16:05 BenjiWilson

Hi!.

I noticed this as well. It looks like there is a change in the sql-alchemy

Changed in version 2.0: For those dialects that previously included the names of materialized views in this list (currently PostgreSQL), this method no longer returns the names of materialized views. the [Inspector.get_materialized_view_names()](https://docs.sqlalchemy.org/en/20/core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_materialized_view_names)method should be used instead.

So maybe near here, one could make a change like this:

diff --git a/langchain/sql_database.py b/langchain/sql_database.py
index fc0a5098..f193cf0c 100644
--- a/langchain/sql_database.py
+++ b/langchain/sql_database.py
@@ -53,6 +53,7 @@ class SQLDatabase:
         self._all_tables = set(
             self._inspector.get_table_names(schema=schema)
             + (self._inspector.get_view_names(schema=schema) if view_support else [])
+            + (self._inspector.get_materialized_view_names(schema=schema) if view_support else [])
         )

         self._include_tables = set(include_tables) if include_tables else set()

May 09 '23 22:05 kittenkill

Hi,

I faced the same issue initially, it seems you need to send a flag view_support as True (defaults to False) , if you want it to check for materialized views.

Try adding one more parameter (view_support = True) and see if this solves the issue in your case as it did in mine.

db = SQLDatabase.from_uri( "postgresql://xxx:xxx@xxx:5432/xxx", include_tables=include_tables, view_support = True)

Jul 17 '23 05:07 CadisERaizel

Hi,

I have a similar issue. 'one_all' is a materialized view. When I run: db = SQLDatabase(engine, schema='one', include_tables=['one_all'], view_support = True) I get no errors, but it actually brings all the tables and excludes the materialized view one_all - How to bring only the materialized view. Any suggestion¿?

Jul 24 '23 15:07 micaelarg

I was running into this issue and the view_support wasn't working for me in Postgres. I fixed it installing an older version of sqlalchemy (1.4.49).

Explanation

class SQLDatabase:
    """SQLAlchemy wrapper around a database."""

    def __init__(
......

self._all_tables = set(
            self._inspector.get_table_names(schema=schema)
            + (self._inspector.get_view_names(schema=schema) if view_support else [])
        )

langchain calls self._inspector.get_view_names(schema=schema) to get the name of views. As of version 2.0 of sqlalchemy no longer includes materialized views. There is a separate method for getting those.

The full fix would be to have langchain call that separate method get wanting to get materialized views as well.

Aug 13 '23 21:08 davvilla

Hi @micaelarg , in db = SQLDatabase(engine, schema='one', include_tables=['one_all'], view_support = True) what is the schema= 'one' ? and are we giving the view name in include_tables?

Nov 02 '23 14:11 jyoti194

Hi, @BenjiWilson! I'm helping the LangChain team manage their backlog and am marking this issue as stale.

It looks like you encountered an error when specifying a materialized view in include_tables, and there were some suggestions and potential solutions provided by other contributors. However, it seems that the issue is still unresolved.

Could you please confirm if this issue is still relevant to the latest version of the LangChain repository? If it is, please let the LangChain team know by commenting on the issue. Otherwise, feel free to close the issue yourself, or it will be automatically closed in 7 days. Thank you!