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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
