dybyte opened a new issue, #12601: URL: https://github.com/apache/seatunnel/issues/12601
### Search before asking - [x] I had searched in the [feature](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22Feature%22) and found no similar feature requirement. ### Description ### What happened With `schema-changes.enabled = true`, a Postgres-CDC → JDBC PostgreSQL job fails on `ALTER TABLE ... ADD COLUMN` when the new column has a user-defined type whose name needs quoting (upper case, reserved word) or is in a schema that is not on the `search_path`. The type name of the new column comes from Debezium (`Column.typeName()`, i.e. `pg_type.typname` from [`TypeRegistry`](https://github.com/apache/seatunnel/blob/4c4fd615d677695b619ca0e04fcfd82e104cd4a9/seatunnel-connectors-v2/connector-cdc/connector-cdc-postgres/src/main/java/io/debezium/connector/postgresql/TypeRegistry.java#L85)), so it is neither quoted nor schema-qualified. `PostgresTypeConverter` keeps it as `sourceType` for `Types.OTHER` ([L290](https://github.com/apache/seatunnel/blob/4c4fd615d677695b619ca0e04fcfd82e104cd4a9/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresTypeConverter.java#L290), added in #11232), and `PostgresDialect.buildAddColumnSQL` copies `sourceType` into the DDL as-is when the source is also Postgres ([L381](https://github.com/apache/seatunnel/blob/4c4fd615d677695b619ca0e04fcfd82e104cd4a9/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connecto rs/seatunnel/jdbc/internal/dialect/psql/PostgresDialect.java#L381)). PostgreSQL folds `MyRange` to `myrange`, so the type is not found. **Steps** ```sql CREATE TYPE my_range_lc AS RANGE (subtype = int4); CREATE TYPE "MyRange" AS RANGE (subtype = int4); CREATE TABLE public.t5 (id int PRIMARY KEY, name text); INSERT INTO public.t5 VALUES (1, 'a'); -- start the job below and wait for the snapshot ALTER TABLE public.t5 ADD COLUMN r my_range_lc; -- sink: ADD "r" my_range_lc NULL, OK INSERT INTO public.t5 VALUES (2, 'b', '[1,5)'); ALTER TABLE public.t5 ADD COLUMN r2 "MyRange"; -- sink: ADD "r2" MyRange NULL, fails INSERT INTO public.t5 VALUES (3, 'c', '[1,5)', '[2,6)'); ``` **Enums** On dev, adding an enum column fails earlier with COMMON-17 (#12586). With #12588 (b32259051), enums take the same path as above: | Added column type | Sink DDL | Result | |---|---|---| | `jobstatus_lc` | `ADD "c" jobstatus_lc NULL` | OK | | `"JobStatus"` | `ADD "st2" JobStatus NULL` | `type "jobstatus" does not exist` | | `"order"` | `ADD "o" order NULL` | `syntax error at or near "order"` | | `inv.mood` (`inv` not on `search_path`) | `ADD "m" mood NULL` | `type "mood" does not exist` | Mixed-case enum names are common, e.g. Prisma creates enums as `CREATE TYPE "Role" AS ENUM (...)`. The initial `CREATE TABLE` of the sink is not affected. Its type names come from `PostgresCatalog`, and with #12588 enum columns use `format_type()` (`"JobStatus"`, `inv."JobStatus"`). **Possible fix** Build the type name the way `format_type()` does: quote it with `quote_ident` rules and qualify it with the schema when needed. Quoting alone fixes `"MyRange"` and `"order"` but not `inv.mood`, because `typname` has no schema. The RELATION column also carries the type OID (`nativeType()`), which could be used for the lookup. ### Usage Scenario _No response_ ### Related issues _No response_ ### Are you willing to submit a PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
