From 555a18ac02b167db8aaace4c82f8f1076af166b5 Mon Sep 17 00:00:00 2001 From: KapilDagur Date: Sat, 13 Sep 2025 14:37:31 -0400 Subject: [PATCH] raise for ENUM/DOMAIN with reserved type name and no schema A :class:`.CompileError` is raised if attempting to create a PostgreSQL :class:`_postgresql.ENUM` or :class:`_postgresql.DOMAIN` datatype using a name that matches a known pg_catalog datatype name, and a default schema is not specified. These types must be explicit within a schema in order to be differentiated from the built-in pg_catalog type. The "public" or otherwise default schema is not chosen by default here since the type can only be reflected back using the explicit schema name as well (it is otherwise not visible due to the pg_catalog name). Pull request courtesy Kapil Dagur. We originally thought we were going to do some default logic for the default / "public" schema however this produces a type that is not symmetric to its reflection, since the schema name must be explicit for our current reflection queries. So since it's an extremely bad idea to make an ENUM/DOMAIN with a reserved type name anyway, we raise a compileerror if the type has a known name. this is not robust against other names that might exist in pg_catalog or other schemas that are in the search path with these names. People just have to know what they're doing here, the error here only covers a small subset of real world cases. Fixes: #12761 Closes: #12822 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12822 Pull-request-sha: 793cf27be28a37e8882d89ba6b79570c79f936ac Change-Id: I9df9c216ab4102f06685a64ae7e55db1b3a9535e --- doc/build/changelog/unreleased_21/12761.rst | 15 ++++++ lib/sqlalchemy/dialects/postgresql/base.py | 16 ++++++ test/dialect/postgresql/test_types.py | 59 +++++++++++++++++++++ 3 files changed, 90 insertions(+) create mode 100644 doc/build/changelog/unreleased_21/12761.rst diff --git a/doc/build/changelog/unreleased_21/12761.rst b/doc/build/changelog/unreleased_21/12761.rst new file mode 100644 index 0000000000..1ec54d5dc0 --- /dev/null +++ b/doc/build/changelog/unreleased_21/12761.rst @@ -0,0 +1,15 @@ +.. change:: + :tags: bug, postgresql + :tickets: 12761 + + A :class:`.CompileError` is raised if attempting to create a PostgreSQL + :class:`_postgresql.ENUM` or :class:`_postgresql.DOMAIN` datatype using a + name that matches a known pg_catalog datatype name, and a default schema is + not specified. These types must be explicit within a schema in order to + be differentiated from the built-in pg_catalog type. The "public" or + otherwise default schema is not chosen by default here since the type can + only be reflected back using the explicit schema name as well (it is + otherwise not visible due to the pg_catalog name). Pull request courtesy + Kapil Dagur. + + diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index d06b131a62..c51cd2b36b 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2875,6 +2875,22 @@ class PGIdentifierPreparer(compiler.IdentifierPreparer): name = self.quote(type_.name) effective_schema = self.schema_for_object(type_) + # a built-in type with the same name will obscure this type, so raise + # for that case. this applies really to any visible type with the same + # name in any other visible schema that would not be appropriate for + # us to check against, so this is not a robust check, but + # at least do something for an obvious built-in name conflict + if ( + effective_schema is None + and type_.name in self.dialect.ischema_names + ): + raise exc.CompileError( + f"{type_!r} has name " + f"'{type_.name}' that matches an existing type, and " + "requires an explicit schema name in order to be rendered " + "in DDL." + ) + if ( not self.omit_schema and use_schema diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index c45b4bc9b2..2e23136856 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -1241,6 +1241,65 @@ class NamedTypeTest( e["name"] for e in inspect(connection).get_enums() ] + @testing.variation("type_type", ["enum", "domain"]) + @testing.variation("use_schema", ["none", "default", "explicit"]) + def test_builtin_name_conflict( + self, + connection, + metadata, + type_type: testing.Variation, + use_schema: testing.Variation, + ): + """test #12761""" + + if use_schema.none: + kw = {} + elif use_schema.default: + kw = {"schema": testing.db.dialect.default_schema_name} + elif use_schema.explicit: + kw = {"schema": testing.config.test_schema} + else: + use_schema.fail() + + if type_type.enum: + type_ = ENUM("a", "b", "c", name="text", **kw) + elif type_type.domain: + type_ = DOMAIN(name="text", data_type=Integer, **kw) + else: + type_type.fail() + + Table("t", metadata, Column("c", type_)) + + if use_schema.none: + with expect_raises_message( + exc.CompileError, + r"(ENUM.*|DOMAIN.*) has name 'text' that " + r"matches an existing type,", + ): + metadata.create_all(connection) + return + + metadata.create_all(connection) + + type_names = ( + {elem["name"] for elem in inspect(connection).get_enums(**kw)} + if type_type.enum + else { + elem["name"] for elem in inspect(connection).get_domains(**kw) + } + ) + + assert "text" in type_names + + cols = inspect(connection).get_columns("t") + + if type_type.enum: + assert isinstance(cols[0]["type"], ENUM) + elif type_type.domain: + assert isinstance(cols[0]["type"], DOMAIN) + else: + type_type.fail() + class DomainTest( AssertsCompiledSQL, fixtures.TestBase, AssertsExecutionResults -- 2.47.3