[jira] [Updated] (FLINK-38850) Support PostgreSQL uuid type

["Yuepeng Pan (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/FLINK-38850?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Yuepeng Pan updated FLINK-38850:
--------------------------------
    Fix Version/s: jdbc-4.1.0
                   jdbc-3.4.0
                       (was: jdbc-3.3.0)

> Support PostgreSQL uuid type
> ----------------------------
>
>                 Key: FLINK-38850
>                 URL: https://issues.apache.org/jira/browse/FLINK-38850
>             Project: Flink
>          Issue Type: Improvement
>          Components: Connectors / JDBC
>    Affects Versions: jdbc-4.0.0
>            Reporter: CHANHAE OH
>            Assignee: CHANHAE OH
>            Priority: Minor
>              Labels: pull-request-available
>             Fix For: jdbc-4.1.0, jdbc-3.4.0
>
>
> h2. 1. The Reason for This Change (Motivation)
> Currently, the Flink JDBC Connector for PostgreSQL does not recognize the native uuid data type. When a user attempts to read a table containing a uuid column through the PostgresCatalog, it fails with an UnsupportedOperationException: Doesn't support Postgres type 'uuid' yet.
> The uuid type is a widely used and important data type in PostgreSQL for storing unique identifiers. Adding support for it significantly improves the usability and completeness of the PostgreSQL connector.
> h2. 2. The Solution (Changes)
> This PR introduces support for the PostgreSQL uuid type by making the following changes:
>  # Schema-level Mapping (PostgresTypeMapper.java):
> Mapped the PostgreSQL {{uuid}} type to Flink's {{{}DataTypes.VARCHAR(36){}}}. This is the most appropriate mapping as the standard string representation of a UUID is 36 characters long.
>  # Runtime Data Conversion (AbstractDialectConverter.java):
> Resolved a fundamental {{ClassCastException}} that occurred during runtime. The PostgreSQL JDBC driver returns a {{java.util.UUID}} object for {{uuid}} columns, but the existing converter logic tried to cast it directly to a {{{}java.lang.String{}}}.
> The deserialization logic for {{CHAR}} and {{VARCHAR}} types has been updated from {{(String) val}} to the more robust {{{}val.toString(){}}}. This not only fixes the issue for UUID but also improves the connector's resilience by correctly handling any object that has a valid string representation.
>  # Test Environment Setup (PostgresMetadata.java):
> Added the {{stringtype=unspecified}} option to the test-only JDBC URL in {{{}PostgresMetadata.java{}}}. This change is {*}critical for the verification process{*}. When populating test data, the test framework attempts to insert a Java {{String}} into a {{uuid}} database column. Without this option, the JDBC driver specifies the type as {{{}varchar{}}}, causing a {{PSQLException}} from the PostgreSQL server. With {{{}stringtype=unspecified{}}}, the driver does not specify the type, allowing the server to correctly perform an implicit cast from the string literal to the uuid type. *This change is confined to the test scope and does not affect the main production code.*
> h2. 3. Test Case Description (Verification)
> Comprehensive tests have been added to verify the correctness of this feature and ensure no regressions were introduced:
>  # PostgresCatalogTest.java:
> A new test testUuidDataTypes() was added. It verifies that the PostgresCatalog correctly infers the schema of a table with a uuid column as VARCHAR(36).
> The existing testListTables() test was updated to correctly expect the newly added uuid_table.
>  # PostgresCatalogITCase.java:
> A new end-to-end test testUuidTypes() was added. This test executes a Flink SQL SELECT query on the uuid_table and asserts that the data is read correctly, confirming that the runtime data conversion works as expected.
>  # PostgresDynamicTableSourceITCase.java:
> The existing integration test was modified to include a uuid column. This verifies that the changes work correctly within the dynamic table source framework.
> related PR : https://github.com/apache/flink-connector-jdbc/pull/182



--
This message was sent by Atlassian Jira
(v8.20.10#820010)