Repository: Mause/duckdb_engine (language: python)

Issue:
[Bug]: MetaData.reflect() raises exception with sqlalchemy v2.0.36
### What happened?

This [commit](https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/5519/3/lib/sqlalchemy/dialects/postgresql/base.py) in sqlalchemy v2.0.36 causes a regression when creating a table in DuckDB. A SQLAlchemy developer [says](https://github.com/sqlalchemy/sqlalchemy/discussions/12011#discussioncomment-10992396) that this package will need to override the new statement.

```python
from sqlalchemy import MetaData, create_engine, text

engine = create_engine("duckdb:///:memory:")
metadata = MetaData()
with engine.connect() as conn:
    conn.execute(text("CREATE TABLE tbl(col1 INTEGER)"))
    conn.commit()
metadata.reflect(engine)
```

```
/private/tmp/env/lib/python3.11/site-packages/duckdb_engine/__init__.py:174: DuckDBEngineWarning: duckdb-engine doesn't yet support reflection on indices
  warnings.warn(
Traceback (most recent call last):
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "/private/tmp/env/lib/python3.11/site-packages/duckdb_engine/__init__.py", line 140, in execute
    self.__c.execute(statement, parameters)
duckdb.duckdb.CatalogException: Catalog Error: Type with name REGCLASS does not exist!

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/dthom/repos/chronify/scripts/repro_alchemy.py", line 8, in <module>
    metadata.reflect(engine)
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 5828, in reflect
    _reflect_info = insp._get_reflection_info(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 2024, in _get_reflection_info
    table_comment=run(self.get_multi_table_comment, optional=True),
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 2001, in run
    res = meth(filter_names=_fn, **kw)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 1377, in get_multi_table_comment
    self.dialect.get_multi_table_comment(
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/base.py", line 4709, in get_multi_table_comment
    result = connection.execute(query, params)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/private/tmp/env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "/private/tmp/env/lib/python3.11/site-packages/duckdb_engine/__init__.py", line 140, in execute
    self.__c.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (duckdb.duckdb.CatalogException) Catalog Error: Type with name REGCLASS does not exist!
[SQL: SELECT pg_catalog.pg_class.relname, pg_catalog.pg_description.description 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_class.oid = pg_catalog.pg_description.objoid AND pg_catalog.pg_description.objsubid = $1 AND pg_catalog.pg_description.classoid = CAST($2 AS REGCLASS) JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[$3, $4, $5]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != $6 AND pg_catalog.pg_class.relname IN ($7)]
[parameters: (0, 'pg_catalog.pg_class', 'r', 'p', 'f', 'pg_catalog', 'tbl')]
(Background on this error at: https://sqlalche.me/e/20/f405)
```


### DuckDB Engine Version

0.13.2

### DuckDB Version

1.1.2

### SQLAlchemy Version

2.0.36

### Relevant log output

_No response_

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct


Discussion / hints:
Hello! I've taken a look at both this codebase and the sqlalchemy codebase and I think I have an idea for how to fix the problem. My group and I are students at the University of Toronto so we'd love to take a crack at this issue! 

Fix patch:
diff --git a/duckdb_engine/__init__.py b/duckdb_engine/__init__.py
index f300ed78..24377577 100644
--- a/duckdb_engine/__init__.py
+++ b/duckdb_engine/__init__.py
@@ -1,5 +1,6 @@
 import re
 import warnings
+from functools import lru_cache
 from typing import (
     TYPE_CHECKING,
     Any,
@@ -15,7 +16,7 @@
 
 import duckdb
 import sqlalchemy
-from sqlalchemy import pool, text, util
+from sqlalchemy import pool, select, sql, text, util
 from sqlalchemy import types as sqltypes
 from sqlalchemy.dialects.postgresql import UUID
 from sqlalchemy.dialects.postgresql.base import (
@@ -31,6 +32,7 @@
 from sqlalchemy.engine.url import URL
 from sqlalchemy.exc import NoSuchTableError
 from sqlalchemy.ext.compiler import compiles
+from sqlalchemy.sql import bindparam
 from sqlalchemy.sql.selectable import Select
 
 from ._supports import has_comment_support
@@ -599,6 +601,51 @@ def get_multi_columns(
 
         return columns.items()
 
+    # fix for https://github.com/Mause/duckdb_engine/issues/1128
+    # (Overrides sqlalchemy method)
+    @lru_cache()
+    def _comment_query(  # type: ignore[no-untyped-def]
+        self, schema: str, has_filter_names: bool, scope: Any, kind: Any
+    ):
+        if sqlalchemy.__version__ >= "2.0.36":
+            from sqlalchemy.dialects.postgresql import (  # type: ignore[attr-defined]
+                pg_catalog,
+            )
+
+            if (
+                hasattr(super(), "_kind_to_relkinds")
+                and hasattr(super(), "_pg_class_filter_scope_schema")
+                and hasattr(super(), "_pg_class_relkind_condition")
+            ):
+                relkinds = getattr(super(), "_kind_to_relkinds")(kind)
+                query = (
+                    select(
+                        pg_catalog.pg_class.c.relname,
+                        pg_catalog.pg_description.c.description,
+                    )
+                    .select_from(pg_catalog.pg_class)
+                    .outerjoin(
+                        pg_catalog.pg_description,
+                        sql.and_(
+                            pg_catalog.pg_class.c.oid
+                            == pg_catalog.pg_description.c.objoid,
+                            pg_catalog.pg_description.c.objsubid == 0,
+                        ),
+                    )
+                    .where(getattr(super(), "_pg_class_relkind_condition")(relkinds))
+                )
+                query = self._pg_class_filter_scope_schema(query, schema, scope)
+                if has_filter_names:
+                    query = query.where(
+                        pg_catalog.pg_class.c.relname.in_(bindparam("filter_names"))
+                    )
+                return query
+        else:
+            if hasattr(super(), "_comment_query"):
+                return getattr(super(), "_comment_query")(
+                    schema, has_filter_names, scope, kind
+                )
+
 
 if sqlalchemy.__version__ >= "2.0.14":
     from sqlalchemy import TryCast  # type: ignore[attr-defined]


Test patch:
diff --git a/duckdb_engine/tests/test_basic.py b/duckdb_engine/tests/test_basic.py
index 04542fae..8c3dbb80 100644
--- a/duckdb_engine/tests/test_basic.py
+++ b/duckdb_engine/tests/test_basic.py
@@ -644,3 +644,10 @@ def test_no_cache(tmp_path: Path) -> None:
             assert res1 != res2
             assert res1[0][0] == "1"
             assert res2[0][0] == "2"
+
+
+def test_reflection(engine: Engine) -> None:
+    metadata = MetaData()
+    with engine.connect() as conn:
+        conn.execute(text("CREATE TABLE tbl(col1 INTEGER)"))
+    metadata.reflect(engine)