CHANHAE OH created FLINK-38850:
----------------------------------
Summary: [Connector/JDBC] 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
Fix For: jdbc-4.0.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)
