Materialize: Python client via SqlAlchemy Table introspection `get_columns()` fails

[romainr]

What version of Materialize are you using?

$ materialized -v

unstable

How did you install Materialize?

• [x] Docker image
• [ ] Linux release tarball
• [ ] APT package
• [ ] macOS release tarball
• [ ] Homebrew tap
• [ ] Built from source

What was the issue?

We can't use the API to DESCRIBE a table and get its columns (which is very useful).
Follow-up of https://github.com/MaterializeInc/materialize/issues/4913

Is the issue reproducible? If so, please provide reproduction instructions.

from sqlalchemy import inspect
from sqlalchemy.engine import create_engine

engine = create_engine('postgresql://localhost:6875/materialize')

inspector = inspect(engine)
inspector.get_columns(table_name='mz_columns', database_name='mz_catalog')

Please attach any applicable log files.

>>> inspector.get_columns(table_name='mz_columns', database_name='mz_catalog')
Traceback (most recent call last):
  File "python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    self.dialect.do_execute(
  File "python3.8/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InternalError_: function "pg_catalog.format_type" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "python3.8/site-packages/sqlalchemy/engine/reflection.py", line 372, in get_columns
    col_defs = self.dialect.get_columns(
  File "<string>", line 2, in get_columns
  File "python3.8/site-packages/sqlalchemy/engine/reflection.py", line 56, in cache
    ret = fn(self, con, *args, **kw)
  File "python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2814, in get_columns
    c = connection.execute(s, table_oid=table_oid)
  File "python3.8/site-packages/sqlalchemy/engine/base.py", line 2179, in execute
    return connection.execute(statement, *multiparams, **params)
  File "python3.8/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "python3.8/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "python3.8/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
    ret = self._execute_context(
  File "python3.8/site-packages/sqlalchemy/engine/base.py", line 1252, in _execute_context
    self._handle_dbapi_exception(
  File "python3.8/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    self.dialect.do_execute(
  File "python3.8/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.errors.InternalError_) function "pg_catalog.format_type" does not exist

[SQL: 
            SELECT a.attname,
              pg_catalog.format_type(a.atttypid, a.atttypmod),
              (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM pg_catalog.pg_attrdef d
               WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
               AND a.atthasdef)
              AS DEFAULT,
              a.attnotnull, a.attnum, a.attrelid as table_oid,
              pgd.description as comment
            FROM pg_catalog.pg_attribute a
            LEFT JOIN pg_catalog.pg_description pgd ON (
                pgd.objoid = a.attrelid AND pgd.objsubid = a.attnum)
            WHERE a.attrelid = %(table_oid)s
            AND a.attnum > 0 AND NOT a.attisdropped
            ORDER BY a.attnum
        ]
[parameters: {'table_oid': 20045}]
(Background on this error at: http://sqlalche.me/e/2j85)

[benesch]

Thanks for another awesome report, @romainr! I'll see about adding support for the format_type function shortly.

[benesch]

@sploiselle are you up for taking this one? Ties in super nicely with the stuff you're working on this very moment.

[sploiselle]

sgtm

[sploiselle]

I have an implementation of format_type behind #5175

[sploiselle]

@romainr pinged you on #5225, but if there are more roadblocks, feel free to re-open this issue

[romainr]

Thanks!

Will check back when the image is there

docker run -p 6875:6875 materialize/materialized:unstable-9f8c40fb8b77519bb2722ad00d1f1888486b5d05 --workers 1 Unable to find image 'materialize/materialized:unstable-9f8c40fb8b77519bb2722ad00d1f1888486b5d05' locally docker: Error response from daemon: manifest for materialize/materialized:unstable-9f8c40fb8b77519bb2722ad00d1f1888486b5d05 not found: manifest unknown: manifest unknown. See 'docker run --help'.

[sploiselle]

@romainr SInce this got merged, should be available on our newest version of unstable (materialize/materialized:unstable); will want to make sure you do a docker pull to make sure it's the newest version (the function didn't exist before so should be pretty clear when you have the right one)

[romainr]

Thanks and right now I am seeing another error (with same code as above):

```raceback (most recent call last):
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 609, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InternalError_: unknown catalog item 'pg_catalog.pg_attrdef'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "", line 1, in
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 390, in get_columns
col_defs = self.dialect.get_columns(
File "", line 2, in get_columns
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 52, in cache
ret = fn(self, con, args, *kw)
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3038, in get_columns
c = connection.execute(s, table_oid=table_oid)
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2235, in execute
return connection.execute(statement, multiparams, *params)
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
return meth(self, multiparams, params)
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
ret = self._execute_context(
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
self._handle_dbapi_exception(
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
util.raise_(
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/home/romain/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 609, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.errors.InternalError_) unknown catalog item 'pg_catalog.pg_attrdef'

[SQL:
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
AND a.atthasdef)
AS DEFAULT,
a.attnotnull, a.attnum, a.attrelid as table_oid,
pgd.description as comment,
NULL as generated
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_description pgd ON (
pgd.objoid = a.attrelid AND pgd.objsubid = a.attnum)
WHERE a.attrelid = %(table_oid)s
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
]
[parameters: {'table_oid': 20045}]
(Background on this error at: http://sqlalche.me/e/13/2j85)
```

[sploiselle]

Makes sense! We don't have complete for pg_catalog queries, but this is something we can look into.

[benesch]

I've opened #5237 to track pg_attrdef, and marking this as closed, just to keep discussion focused! Thanks for trying it out, @romainr. I've also opened #5238 to track the wider problem of SQLAlchemy support.