terrymanu commented on issue #36978:
URL:
https://github.com/apache/shardingsphere/issues/36978#issuecomment-3477382049
# PostgreSQL Custom Types Parsing Issue - Analysis & Solution
## Problem Analysis
After analyzing the ShardingSphere codebase, I've identified the root
cause of this issue:
### Current Flow Issues
1. Metadata Discovery: PostgreSQLMetaDataLoader:220 uses
DataTypeRegistry.getDataType() to resolve column types from udt_name
2. Type Resolution: For custom types like varbit, the registry returns
Types.OTHER (1111) when not found in PostgreSQLDataTypeOption.EXTRA_DATA_TYPES
3. Parser Assignment: PostgreSQLColumnType:195 maps Types.OTHER → JSON →
PostgreSQLJsonValueParser
4. Processing Failure: PostgreSQLJsonValueParser creates PGobject with
type "json", which fails for varbit and other non-JSON custom types
### Key Findings
- varbit is already defined in PostgreSQLColumnType:145 but uses
PostgreSQLVarcharValueParser
- The current system only handles 4 special cases: UUID, BIT, BOOL, JSON
- Missing extensible mechanism for PostgreSQL UDTs (User-Defined Types)
## Complete Solution Plan
### Phase 1: Immediate Fix for varbit
File:
database/protocol/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/protocol/postgresql/packet/command/query/extended/PostgreSQLColumnType.java
```java
// Add varbit detection method
private static boolean isVarbit(final int jdbcType, final String
columnTypeName) {
return Types.OTHER == jdbcType &&
("varbit".equalsIgnoreCase(columnTypeName) || "bit
varying".equalsIgnoreCase(columnTypeName));
}
// Modify valueOfJDBCType method
public static PostgreSQLColumnType valueOfJDBCType(final int jdbcType,
final String columnTypeName) {
if (isBit(jdbcType, columnTypeName)) {
return BIT;
}
if (isBool(jdbcType, columnTypeName)) {
return BOOL;
}
if (isUUID(jdbcType, columnTypeName)) {
return UUID;
}
if (isVarbit(jdbcType, columnTypeName)) {
return VARBIT;
}
return valueOfJDBCType(jdbcType);
}
```
File:
database/connector/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/connector/postgresql/metadata/database/option/PostgreSQLDataTypeOption.java
```java
private static Map<String, Integer> setUpExtraDataTypes() {
Map<String, Integer> result = new CaseInsensitiveMap<>();
// ... existing mappings ...
result.put("VARBIT", Types.OTHER); // Keep as OTHER but let column
type detection handle it
result.put("BIT VARYING", Types.OTHER);
return result;
}
```
### Phase 2: Generic UDT Parser Framework
New File: PostgreSQLUDTValueParser.java
```java
public final class PostgreSQLUDTValueParser implements
PostgreSQLTextValueParser<PGobject> {
@Override
public PGobject parse(final String value) {
try {
PGobject result = new PGobject();
// Set the actual type name based on the UDT name
// This will be set dynamically based on column metadata
result.setValue(value);
return result;
} catch (final SQLException ex) {
throw new SQLWrapperException(ex);
}
}
}
```
### Phase 3: Enhanced Type Resolution
Modify PostgreSQLColumnType.java to add:
```java
// New enum entry for generic UDT handling
UDT_GENERIC(0, new PostgreSQLUDTValueParser()),
// Enhanced type detection with fallback
public static PostgreSQLColumnType valueOfJDBCType(final int jdbcType,
final String columnTypeName) {
// ... existing special case handling ...
// For any OTHER type with known column name, use generic UDT parser
if (Types.OTHER == jdbcType && columnTypeName != null &&
!columnTypeName.isEmpty()) {
return UDT_GENERIC;
}
return valueOfJDBCType(jdbcType);
}
```
## Call for Contribution
I invite the community to help implement this solution:
### How to Contribute
1. Fork the Repository and create a feature branch:
fix/postgresql-custom-types
2. Implement the Changes following the solution above
3. Add Comprehensive Tests:
- Create tests for varbit type handling
- Test with other PostgreSQL custom types
- Verify backward compatibility
4. Test with Real Database:
CREATE TABLE test_custom_types (
id SERIAL PRIMARY KEY,
varbit_col VARBIT(10),
custom_udt_col YOUR_CUSTOM_TYPE
);
5. Submit Pull Request with detailed test results
### Testing Scenarios
Please test with:
- Basic varbit and bit varying columns
- Custom UDTs with insert/select operations
- Mixed tables with standard and custom types
- Connection through ShardingSphere-Proxy
- Different PostgreSQL versions (12, 13, 14, 15)
### Additional Requirements
- 100% test coverage for new code
- Backward compatibility verification
- Performance testing with large datasets
- Documentation updates for custom type configuration
Let's work together to make ShardingSphere fully compatible with
PostgreSQL custom types!
--
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]
