You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "zxs1633079383 (via GitHub)" <gi...@apache.org> on 2025/10/31 06:23:55 UTC
[I] PostgreSQL Custom Types Parsing Issue in ShardingSphere-Proxy [shardingsphere]
zxs1633079383 opened a new issue, #36978:
URL: https://github.com/apache/shardingsphere/issues/36978
# PostgreSQL Custom Types Parsing Issue in ShardingSphere-Proxy
## Description
I am using **ShardingSphere-Proxy version 5.5.2** with PostgreSQL.
In my database, I have custom data types, for example `varbit`.
When `TableMetaData` is loaded, these columns are identified as JDBC type `1111` (i.e., `Types.OTHER`).
As a result, the system defaults to the JSON parser for these columns. This behavior makes it impossible to process custom PostgreSQL types correctly, because JSON parsing is not applicable for types like `varbit`.
This raises a general concern: how should other PostgreSQL custom business types be handled properly through ShardingSphere-Proxy?
## Observed Problem
- Custom PostgreSQL types are treated as `Types.OTHER` (1111).
- The default JSON parser is applied, which fails for types like `varbit` or other user-defined types.
- Currently, multiple types would require multiple parsers unless a unified parser can be applied.
- Even in fast sequential operations, timestamp fields generated by `time.Now().UnixMilli()` can collide because of millisecond precision.
## Questions / Suggestions
1. How can we register or configure a parser for PostgreSQL custom types in ShardingSphere-Proxy 5.5.2?
2. Is there a recommended way to map PostgreSQL `udt_name` to a parser so that multiple custom types can share a single generic parser?
3. How can PostgreSQL UDTs flow correctly through ShardingSphere-Proxy without breaking inserts or selects?
## Environment
- ShardingSphere-Proxy version: 5.5.2
- PostgreSQL
- Custom types examples: `varbit`, `bit`, other user-defined 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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] PostgreSQL Custom Types Parsing Issue in ShardingSphere-Proxy [shardingsphere]
Posted by "zxs1633079383 (via GitHub)" <gi...@apache.org>.
zxs1633079383 commented on issue #36978:
URL: https://github.com/apache/shardingsphere/issues/36978#issuecomment-3479441270
@terrymanu
### 🙏 Thanks @terrymanu for the detailed and professional analysis!
Really appreciate your deep dive into this issue and the clear step-by-step breakdown.
Your explanation about the PostgreSQL custom type flow and the planned solution (from `varbit` fix → generic UDT parser framework → enhanced type resolution) is extremely insightful and well-structured.
I especially like the **Phase 2 + Phase 3** plan — introducing a `PostgreSQLUDTValueParser` and a `UDT_GENERIC` fallback feels like the right direction to make ShardingSphere more extensible and future-proof for PostgreSQL’s custom ecosystem.
I’ll follow your guidance:
- Prepare a PR branch (`fix/postgresql-custom-types`)
- Implement and test based on your design
- Provide real PostgreSQL validation (varbit + user-defined types)
Thanks again for your time and effort — this is an awesome reference for contributors like me to understand how to extend ShardingSphere’s type parsing mechanism. 🙌
_Respect from the trenches of debugging 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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] PostgreSQL Custom Types Parsing Issue in ShardingSphere-Proxy [shardingsphere]
Posted by "zxs1633079383 (via GitHub)" <gi...@apache.org>.
zxs1633079383 commented on issue #36978:
URL: https://github.com/apache/shardingsphere/issues/36978#issuecomment-3591023017
Hi @terrymanu ,
Thanks again for your guidance and for helping shape the final design direction.
I’m happy to share an update — the PostgreSQL-side implementation is now fully completed.
All code has been polished according to the Contributor Covenant Code of Conduct, Apache policies, and ShardingSphere development, coding, and testing standards.
The corresponding PR has also been submitted: #37216.
✨ Summary of Completed Work
Following your Phase 1 → Phase 3 design, I have implemented full and stable support for:
PostgreSQL custom UDTs
varbit and bit varying
Other Types.OTHER–based PostgreSQL extensions
Enhancements include the metadata, protocol, binding, execution, and testing layers.
🔧 1. Metadata Layer Improvements
✔ Added typeName to metadata classes
ShardingSphereColumn
ColumnMetaData
This ensures correct PostgreSQL UDT propagation throughout the metadata lifecycle.
✔ PostgreSQLMetaDataLoader loads udt_name correctly
✔ YAML swapper updated (YAML ↔ Column symmetrical round-trip)
🔄 2. Protocol Layer Improvements
✔ Extended PostgreSQLColumnType.valueOfJDBCType(int, String)
Supports UDT detection via JDBC type + PostgreSQL udt_name.
✔ Added UDT_GENERIC fallback type
Provides safe and extensible handling for PostgreSQL UDTs.
✔ Introduced PostgreSQLUDTValueParser
Generates proper PGobject
Injects correct PostgreSQL UDT typeName
Ensures INSERT/UPDATE/SELECT all work correctly through Proxy
🧪 3. Comprehensive Tests Added (per your request)
Implemented full coverage based on your test scenarios:
✔ Basic varbit / bit varying
INSERT / SELECT / UPDATE / DELETE
Correct typeName mapping
Correct parser behavior
Full Proxy round-trip validated
✔ Custom UDTs (ENUM, domain types)
Example:
CREATE TYPE post_status AS ENUM ('draft', 'publish');
Correctly transmitted through PGobject(typeName, value).
✔ Mixed schemas (standard + UDT)
(int4 + varchar + jsonb + enum)
Validated metadata + value parsing correctness.
✔ Through ShardingSphere-Proxy
Tests executed via:
jdbc:postgresql://localhost:3307/db
✔ PostgreSQL multi-version compatibility
Verified on:
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
Behavior is consistent across all versions.
🔙 4. Backward Compatibility Verification
As requested (“Verify backward compatibility”), I validated:
✔ No regressions for standard PostgreSQL types
(varchar, jsonb, int4, bytea, boolean)
✔ Non-UDT schemas behave exactly as before
typeName defaults gracefully or remains null.
✔ Other dialects unchanged
(MySQL, SQLServer, openGauss, etc.)
✔ PostgreSQLColumnType remains stable and non-breaking
Only metadata enrichment added.
✔ All existing ShardingSphere tests still pass
Binder, engine, protocol, and metadata loader tests.
🧹 5. Updated Existing Unit Tests
Since typeName became a required constructor parameter:
Updated all impacted tests
Adjusted mocks
Adopted actualXxx / expectedXxx naming
Fully compliant with AIR & BCDE testing principles
No logs or System.out
Coverage remains ≥ master
🚀 6. PR Submitted
PR: #37216
This PR:
Fixes UDT behavior
Adds metadata & protocol support
Updates YAML swapper
Adds comprehensive PostgreSQL custom-type tests
Leaves other dialects unchanged and backward compatible
📌 7. About Performance Testing (Not Included in This PR)
This PR focuses on correctness, compatibility, and metadata/protocol improvements,
so performance testing is intentionally not included, to avoid excessive scope and review complexity.
Once the maintainers confirm the official benchmarking procedure, I will follow the recommended methodology and provide performance results separately.
📌 8. Requesting Guidance for PostgreSQL UDT Documentation
You also mentioned:
“Performance testing with large datasets”
“Documentation updates for custom type configuration”
Before preparing documentation, I would like your guidance on where to add it.
Possible locations:
Proxy User Guide
docs/documentation/user-manual/shardingsphere-proxy/
PostgreSQL Dialect Reference
docs/documentation/reference/dialect/postgresql/
Type Mapping Documentation
docs/documentation/reference/type-mapping/
A new dedicated page
“PostgreSQL Custom Type (UDT) Support in ShardingSphere-Proxy"
Planned documentation content includes:
How ShardingSphere loads PostgreSQL UDT metadata (udt_name)
How Proxy maps UDTs to PGobject
Limitations
Examples for ENUM, varbit, domain types
Behavioral notes for Proxy vs native PostgreSQL
Please let me know the preferred documentation location so I can proceed accordingly.
🙏 Final Notes
Thanks again for your guidance.
Your deep explanation of PostgreSQL type handling was crucial for designing a correct, extensible, and maintainable solution.
I’m happy to continue contributing — including documentation updates, benchmark data, additional examples, or more E2E test cases.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] PostgreSQL Custom Types Parsing Issue in ShardingSphere-Proxy [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] PostgreSQL Custom Types Parsing Issue in ShardingSphere-Proxy [shardingsphere]
Posted by "zxs1633079383 (via GitHub)" <gi...@apache.org>.
zxs1633079383 commented on issue #36978:
URL: https://github.com/apache/shardingsphere/issues/36978#issuecomment-3580567164
Hi @terrymanu
Thank you again for your detailed guidance.
I have now completed the PostgreSQL part of the solution and would like to provide an update and ask for confirmation regarding the PR scope.
Current Status — PostgreSQL Part Fully Implemented
Following your Phase 1–3 design, I have completed the implementation for PostgreSQL.
Metadata Layer
- ColumnMetaData now includes typeName
- PostgreSQLMetaDataLoader correctly reads udt_name
- ShardingSphereColumn updated to store typeName
- YAML Swapper updated to support typeName in both directions (YAML to Column, Column to YAML)
Protocol Layer
- Extended PostgreSQLColumnType.valueOfJDBCType(int, String)
- Added UDT_GENERIC fallback logic
- Implemented PostgreSQLUDTValueParser
- BIND executor now correctly injects PGobject(typeName, value)
Validation Using Real PostgreSQL
I tested with tables that include:
- ENUM UDT (post_type)
- bit varying
- jsonb
- varchar[]
- bytea
- boolean
- complex update expressions such as bit operations and JSONB casting
All behaviors are now correct when passing through ShardingSphere-Proxy:
- Insert works correctly
- Update works correctly
- Select works correctly
- Complex expressions such as read_bits | CAST(...) work correctly
- PGobject consistently receives the correct PostgreSQL typeName
This fully resolves the original issue: custom PostgreSQL UDT types no longer break under ShardingSphere-Proxy.
Question About Multi-Dialect Implementation Scope
Your proposed solution is naturally PostgreSQL-first.
For other dialects (MySQL, SQLServer, Firebird, OpenGauss):
- They currently do not extract typeName
- They do not expose UDT information
- Their metadata models differ significantly
- Extracting correct typeName would require dialect maintainers to design their own logic
Therefore, I would like to confirm the following:
### Question 1
Is it acceptable for my PR to implement the feature for PostgreSQL only,
while keeping typeName as null (placeholder) for other dialects?
Dialect | typeName after PR
PostgreSQL | fully implemented
MySQL | null (unchanged)
SQLServer | null (unchanged)
Firebird | null (unchanged)
OpenGauss | null (unchanged)
### Question 2
For unit tests of other dialects:
Is it acceptable that tests simply verify typeName == null,
without requiring full typeName extraction for those databases?
This approach ensures:
- PostgreSQL improvements are merged
- No regressions for other dialects
- Dialect maintainers can independently extend typeName support later
-
This seems consistent with your earlier suggestion that PostgreSQL implementation can be contributed first.
### Summary
I have completed:
- Full PostgreSQL UDT, bit varying, and JSONB support
- Updated metadata, parser, binding, and execution paths
- Ensured no regressions for other dialects
- All PostgreSQL-related tests pass
I would like to proceed with submitting a PR that covers only the PostgreSQL part,
with placeholder support for other dialects.
Could you confirm that this PR scope is appropriate?
Thanks again for your time and guidance — it has been extremely helpful for contributing to ShardingSphere.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] PostgreSQL Custom Types Parsing Issue in ShardingSphere-Proxy [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #36978:
URL: https://github.com/apache/shardingsphere/issues/36978#issuecomment-3582341972
Thank you very much for your contribution.
I think implementing the PostgreSQL-related functionality first is a good approach.
It helps keep the scope of this PR more manageable, making it easier to test and review the code.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org