fatmanverse commented on issue #12601: URL: https://github.com/apache/seatunnel/issues/12601#issuecomment-6517184276
I'd like to work on this, since #12606 was closed on 2026-10-15 and no other PR is open for it. **Still reproducible.** I ran the issue's steps on a build of dev e2957d2, which already contains #12588 and #12836 (the later dev changes to Postgres-CDC, #12561 and #12731, do not touch type names), with PostgreSQL 16.15, Postgres-CDC (pgoutput, `schema-changes.enabled = true`) -> Jdbc PostgreSQL, with the types present on both sides. The generated sink DDL: | Added column | 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` | **Where the name is lost.** On this path the type is not looked up by name. pgoutput sends the column's type OID in the RELATION message, and `PgOutputMessageDecoder` resolves it with `TypeRegistry.get(int oid)`, so the right `pg_type` row is already found. Only its bare `typname` is kept as the column type name, and it then flows through `PostgresTypeUtils.convertRelationColumnToTypeDefine` and `PostgresTypeConverter` (`sourceType`) into `PostgresDialect.buildAddColumnSQL`. **Proposed fix.** In the Postgres-CDC RELATION path (`PostgresRelationSchemaChangeResolver` / `PostgresTypeUtils`), before converting an added column whose type is user-defined, replace its type name with `format_type(oid, NULL)` for the column's `nativeType()` OID. PostgreSQL quotes the name when needed and qualifies it with the schema when it is not visible on the `search_path`; on the same server it returns `"JobStatus"`, `"order"`, `inv.mood` and `"MyRange"`. This is also how `PostgresCatalog` already builds the enum column types of the initial `CREATE TABLE` (#12588), so both DDLs use the same spelling. If the OID cannot be resolved, the job fails with an error that names the table, column and OID instead of emitting an unqualified name. This differs from the `TypeRegistry` cache-key / `get(String)` change discussed above. Those name-keyed lookups are not on this OID-based path, and `PostgresType#getName()` comes from the Debezium jar and is also used for Debezium's own name lookups, so I'd rather not change what it returns. The `SQL_NAME_LOOKUP` ordering was addressed separately by #12836. **Tests.** A unit test for the type-name resolution, and an extension of `PostgresCDCIT`'s ADD COLUMN coverage that pre-creates the types and adds a mixed-case enum, a reserved-word enum, an enum in a schema outside `search_path` and a mixed-case range type, then checks the sink column types (`format_type`) and the written rows. **Out of scope.** Arrays of user-defined types such as `"JobStatus"[]`, and domains, which #12588 left failing as before; and binding enum values on write, which still needs `stringtype=unspecified` (or #13056). Does this approach look right to you, @SEZ9 @DanielLeens? -- 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]
