table pg_catalog.pg_collation not found #9867
Description
Failed SQL
QUERY:
SELECT pg_catalog.pg_type.typname AS name,
pg_catalog.format_type(
pg_catalog.pg_type.typbasetype,
pg_catalog.pg_type.typtypmod
) AS attype,
NOT pg_catalog.pg_type.typnotnull AS nullable,
pg_catalog.pg_type.typdefault AS "default",
pg_catalog.pg_type_is_visible(pg_catalog.pg_type.oid) AS visible,
pg_catalog.pg_namespace.nspname AS schema,
domain_constraints.condefs,
domain_constraints.connames,
pg_catalog.pg_collation.collname
FROM pg_catalog.pg_type
JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_type.typnamespace
LEFT JOIN pg_catalog.pg_collation ON pg_catalog.pg_type.typcollation = pg_catalog.pg_collation.oid
LEFT JOIN (
SELECT pg_catalog.pg_constraint.contypid AS contypid,
ARRAY_AGG(
pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid, true)
) AS condefs,
ARRAY_AGG(CAST(pg_catalog.pg_constraint.conname AS TEXT)) AS connames
FROM pg_catalog.pg_constraint
WHERE pg_catalog.pg_constraint.contypid <> 0
GROUP BY pg_catalog.pg_constraint.contypid
) AS domain_constraints ON pg_catalog.pg_type.oid = domain_constraints.contypid
WHERE pg_catalog.pg_type.typtype = 'd'
ORDER BY pg_catalog.pg_namespace.nspname,
pg_catalog.pg_type.typnameLogical Plan
Fail SQL command, cannot show logical plan.
Tool
Langchain SQLDatabase + Langchain SQLDatabaseToolkit + SQLAlchemy + psycopg2
Version:
E.g., v1.13.46
Additional context
I tried to use Cube SQL API with Langchain SQLDatabaseToolkit to help LLM answer natural language questions using data in Cube server.
But it keep failing at calling tool InfoSQLDatabaseTool. I investigated further and found that this tool call reflect function of SQLAlchemy. The generated SQL query is failed due to table pg_catalog.pg_collation not found. Other tables in generated SQL is found:
pg_catalog.pg_typepg_catalog.pg_namespacepg_catalog.pg_constraint
Traceback:
Traceback (most recent call last):
File "src/main.py", line 30, in <module>
db._metadata.reflect(
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 5884, in reflect
_reflect_info = insp._get_reflection_info(
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 2019, in _get_reflection_info
columns=run(
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 2005, in run
res = meth(filter_names=_fn, **kw)
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 932, in get_multi_columns
self.dialect.get_multi_columns(
File ".venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3821, in get_multi_columns
for d in self._load_domains(
File "<string>", line 2, in _load_domains
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 106, in cache
ret = fn(self, con, *args, **kw)
File ".venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/base.py", line 5134, in _load_domains
result = connection.execute(self._domain_query(schema))
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1415, in execute
return meth(
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 523, in _execute_on_connection
return connection._execute_clauseelement(
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
ret = self._execute_context(
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
return self._exec_single_context(
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1982, in _exec_single_context
self._handle_dbapi_exception(
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2351, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1963, in _exec_single_context
self.dialect.do_execute(
File ".venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 943, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.errors.InternalError_) Initial planning error: Error during planning: Table or CTE with name 'pg_catalog.pg_collation' not found QUERY: SELECT pg_catalog.pg_type.typname AS name, pg_catalog.format_type(pg_catalog.pg_type.typbasetype, pg_catalog.pg_type.typtypmod) AS attype, NOT pg_catalog.pg_type.typnotnull AS nullable, pg_catalog.pg_type.typdefault AS "default", pg_catalog.pg_type_is_visible(pg_catalog.pg_type.oid) AS visible, pg_catalog.pg_namespace.nspname AS schema, domain_constraints.condefs, domain_constraints.connames, pg_catalog.pg_collation.collname FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_type.typnamespace LEFT JOIN pg_catalog.pg_collation ON pg_catalog.pg_type.typcollation = pg_catalog.pg_collation.oid LEFT JOIN (SELECT pg_catalog.pg_constraint.contypid AS contypid, ARRAY_AGG(pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid, true)) AS condefs, ARRAY_AGG(CAST(pg_catalog.pg_constraint.conname AS TEXT)) AS connames FROM pg_catalog.pg_constraint WHERE pg_catalog.pg_constraint.contypid <> 0 GROUP BY pg_catalog.pg_constraint.contypid) AS domain_constraints ON pg_catalog.pg_type.oid = domain_constraints.contypid WHERE pg_catalog.pg_type.typtype = 'd' ORDER BY pg_catalog.pg_namespace.nspname, pg_catalog.pg_type.typname
How can I fix this issue?
Thanks