fatmanverse opened a new pull request, #13082:
URL: https://github.com/apache/seatunnel/pull/13082
Fixes #12601
### Purpose of this pull request
With `decoding.plugin.name = "pgoutput"` and schema changes enabled,
Postgres-CDC builds the `ADD COLUMN` event from the RELATION message. pgoutput
identifies a column type only by OID, and Debezium keeps the bare
`pg_type.typname`, without quotes or schema. A PostgreSQL JDBC sink copies that
name into `ALTER TABLE ... ADD`, which fails for:
- a mixed-case type: `ADD "st2" JobStatus` -> `type "jobstatus" does not
exist`
- a reserved-word type: `ADD "o" order` -> syntax error
- a type whose schema is not on the `search_path`: `ADD "m" mood` for
`inv.mood` -> `type "mood" does not exist`
Changes:
- `PostgresRelationTypeNames` replaces the type name of user-defined enum,
range and multirange columns (`pg_type.typtype` `e`, `r`, `m`, OID >=
`FirstNormalObjectId`) with `format_type(oid, NULL)`, the spelling PostgreSQL
uses in DDL and that `PostgresCatalog` already uses for enum columns in the
initial `CREATE TABLE`. This gives `"JobStatus"`, `"order"`, `inv.mood`,
`"MyRange"`. Only the table passed to the schema change record is changed;
Debezium's own schema keeps the bare names it resolves types by.
- If the type of a column cannot be read from `pg_type`, the job fails with
the new error `POSTGRES-05`, naming the column and table, instead of emitting
an unqualified name.
- The lookup runs on the streaming data connection, on the streaming thread;
it is committed afterwards, or rolled back on failure, so no transaction is
left open.
- Out of scope, unchanged: composite types (Debezium reports them as
`STRUCT`, which the PostgreSQL type converter does not accept, before and after
this change), domains, and arrays of user-defined types.
Known limits, documented:
- By default Debezium does not emit values of a user-defined range column,
so the column is added but stays NULL.
- Writing enum values to a PostgreSQL sink needs `stringtype=unspecified` on
the sink `url`.
- A type name that `format_type` quotes for other reasons (spaces,
non-ASCII) still does not pass the existing `USER_DEFINED_TYPE_NAME` check in
`PostgresTypeConverter` and falls back to `text`, as before. I left that
converter unchanged here.
### Does this PR introduce _any_ user-facing change?
Yes. An added column of a user-defined enum, range or multirange type now
reaches the sink as `ALTER TABLE ... ADD c "JobStatus"` / `ADD c inv.mood`
instead of the bare name, so the sink needs the type in the same schema (or on
its `search_path` when the name is unqualified). A job whose sink resolved the
bare name to a same-named type in another schema would now fail in the sink;
this is recorded in `incompatible-changes.md` (en/zh). The PostgreSQL-CDC docs
(en/zh) describe the behaviour and the limits above.
### How was this patch tested?
- `PostgresRelationTypeNamesTest`: DDL spelling for mixed-case,
reserved-word, out-of-`search_path`, range and lowercase types; base types,
composites, domains and arrays keep their names; OIDs above 2^31; no query when
all types are built in; `POSTGRES-05` for an unknown OID; the qualified name
reaches `AlterTableAddColumnEvent` as the source type through
`PostgresRelationSchemaChangeResolver`.
- `PostgresSourceFetchTaskContextTest`: the lookup is committed after the
query; a failed lookup is rolled back and wrapped in `POSTGRES-05` with the
table id.
- `PostgresCDCIT#testAddColumnsOfUserDefinedTypes` (Zeta): Postgres-CDC ->
Jdbc PostgreSQL with the types pre-created in the sink database. One `ALTER
TABLE` adds a lowercase enum, `public."JobStatus"`, `public."order"`,
`public."MyRange"` and `udt_hidden.mood` (`udt_hidden` not on the
`search_path`). The test asserts `format_type` of every sink column against the
source, the written rows, the range column staying NULL, and that the job keeps
running.
- Run on JDK 17: `connector-cdc-postgres` unit tests (100 tests) pass;
`PostgresCDCIT#testAddColumnsOfUserDefinedTypes`,
`#testAddColumnSchemaEvolutionWithRestore` and
`#shouldResolveNewTypesUsingPostgresSearchPath` pass. With the connector module
reverted to dev, `testAddColumnsOfUserDefinedTypes` fails with `ERROR: type
"jobstatus" does not exist` from `ALTER TABLE ... ADD "st2" JobStatus NULL`.
### Check list
* [ ] If any new Jar binary package adding in your PR, please add License
Notice according
[New License
Guide](https://github.com/apache/seatunnel/blob/dev/docs/en/developer/new-license.md)
* [x] If necessary, please update the documentation to describe the new
feature. https://github.com/apache/seatunnel/tree/dev/docs
* [x] If necessary, please update `incompatible-changes.md` to describe the
incompatibility caused by this PR.
--
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]
