add reflection for pg DOMAIN types similarly to ENUM reflection

[ovangle]

Describe the use case

Motivated by an issue which I initially filed as sqlalchemy/alembic#1357, I did a little more digging into what was actually causing the issue today. It turns out that the column type information for DOMAIN columns is being replaced with a column of the domain's underlying type during reflection (see lib/sqlalchemy/dialects/postgresql/base.py:3760-3773). Note that the coltype isn't being set in that block, instead it replaces the attype and loops until it finds a suitable type to use instead of the domain.

Since it seems that the erasure is intentional (probably to work around deeper issues with wrapped types), I'm hesitant to pursue my investigation without seeking further guidance from someone more familiar with sqlalchemy internals.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.1.0b1

DBAPI (i.e. the database driver)

psycopg2

Database Vendor and Major Version

PostgreSQL 15.4

Python Version

3.11

Operating system

Ubuntu

To Reproduce

def test_domain_type_reflection(self, metadata, connection):
        positive_int = DOMAIN("positive_int", Integer(), check='value > 0', not_null=True)
        Table(
            "table",
            metadata,
            Column("value", positive_int)
        )

        metadata.create_all(connection)
        m2 = MetaData()
        t2 = Table("table", m2, autoload_with=connection)

        eq_(t2.c.value.type.name, 'positive_int')
        eq_(t2.c.value.check, 'value > 0')

Error

./test/dialect/postgresql/test_types.py::NamedTypeTest::test_domain_type_reflection Failed: [undefined]AttributeError: 'INTEGER' object has no attribute 'name'
Traceback (most recent call last):
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/_pytest/runner.py", line 341, in from_call
    result: Optional[TResult] = func()
                                ^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/_pytest/runner.py", line 262, in <lambda>
    lambda: ihook(item=item, **kwds), when=when, reraise=reraise
            ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_hooks.py", line 493, in __call__
    return self._hookexec(self.name, self._hookimpls, kwargs, firstresult)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_manager.py", line 115, in _hookexec
    return self._inner_hookexec(hook_name, methods, kwargs, firstresult)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_callers.py", line 152, in _multicall
    return outcome.get_result()
           ^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_result.py", line 114, in get_result
    raise exc.with_traceback(exc.__traceback__)
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_callers.py", line 77, in _multicall
    res = hook_impl.function(*args)
          ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/_pytest/runner.py", line 177, in pytest_runtest_call
    raise e
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/_pytest/runner.py", line 169, in pytest_runtest_call
    item.runtest()
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/_pytest/python.py", line 1792, in runtest
    self.ihook.pytest_pyfunc_call(pyfuncitem=self)
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_hooks.py", line 493, in __call__
    return self._hookexec(self.name, self._hookimpls, kwargs, firstresult)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_manager.py", line 115, in _hookexec
    return self._inner_hookexec(hook_name, methods, kwargs, firstresult)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_callers.py", line 113, in _multicall
    raise exception.with_traceback(exception.__traceback__)
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/pluggy/_callers.py", line 77, in _multicall
    res = hook_impl.function(*args)
          ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/.venv/lib/python3.11/site-packages/_pytest/python.py", line 194, in pytest_pyfunc_call
    result = testfunction(**testargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ovangle/dev/sqlalchemy/test/dialect/postgresql/test_types.py", line 1109, in test_domain_type_reflection
    eq_(t2.c.value.type.name, 'positive_int')
        ^^^^^^^^^^^^^^^^^^^^
AttributeError: 'INTEGER' object has no attribute 'name'

Additional context

No response

[CaselIT]

Apparently at some point a DomanType was added:

sqlalchemy/lib/sqlalchemy/dialects/postgresql/named_types.py

Line 385 in 44771ab

class DOMAIN(NamedType, sqltypes.SchemaType):

I would have to look at it better to see if that can be used when reflecting things or not

[zzzeek]

yes it was a big to-do that took months. cc @singingwolfboy

[CaselIT]

maybe 2.1 could add reflection for them? Assuming it round trips ok?

[zzzeek]

please disregard my comments regarding reflection

[ovangle]

@zzzeek
Reflecting the DOMAIN back into the DOMAIN is pretty simple, the issue is that in order to behave correctly, the DOMAIN has to function as it's data type in order to process binds and results, correct?

Essentially, what is required is that the DOMAIN be a dynamic TypeDecorator[T] with an impl_instance bound to data_type?

Would it be possible, at least for the moment, to add restrictions to what types could be used as the data_type of a DOMAIN, to insist that the underlying type is always recoverable via reflection? This essentially limits choices of data type to the simple types declared in PGDialect.ischema_names? Personally I don't mind if all my domain values are strs and ints, I'm just trying to push some validation into the db...

The restriction could be lifted later if there was a standard way to update the type registries...

I understand that this is probably an egregious suggestion for a number of reasons, but would something like the following be in the ballpark of the right direction to be taking.

_T = TypeVar('_T', bound=Any)

def is_reflectable(dialect, data_type: TypeEngine[Any]):
    """
    Determines whether the type is safe to be used as the basis of a DOMAIN. 
    """
    return data_type.__class__ in dialect.ischema_names.values()
    
class DOMAIN(NamedType, type_api.TypeDecorator[_T], sqltypes.SchemaType):
   impl = sqltypes.NULLTYPE
   
   def __init__(self, name: str, data_type: TypeEngineArgument[_T], *, data_type_args: Optional[tuple], data_type_kwargs: Optional[dict], **kwargs):
          self.name = name
          self.data_type = type_api.to_instance(data_type, *(data_type_args or ()), **(data_type_kwargs or {}))
          ...
          super().__init__(**kwargs)

   def load_dialect_impl(self, dialect):
       if not is_reflectable(dialect, self.data_type):
            raise NotSupportedError('The underlying data type of a DOMAIN must be reflectable`)
       return self.data_type

[ovangle]

The above comment was written before I actually tried to roundtrip values to the database, but from all the tests I've written today, everything seems to behave fine without any changes to the type itself.