terrymanu commented on issue #37437:
URL:
https://github.com/apache/shardingsphere/issues/37437#issuecomment-3675747050
## Problem Understanding
- With shardingsphere-proxy 5.5.2 speaking PostgreSQL protocol to a
PostgreSQL 17.6 backend, a timestamp column without fractional seconds is
returned with a trailing “.0”, unlike native PostgreSQL which omits trailing
zero fractional seconds. This can affect clients/
tests that rely on stable textual formatting.
## Root Cause
- The frontend text protocol outputs via
PostgreSQLDataRowPacket#writeTextValue
(database/protocol/dialect/postgresql/packet/command/query/PostgreSQLDataRowPacket.java),
which calls each.toString() for non-binary columns.
java.sql.Timestamp#toString() always emits
a fractional part, yielding “.0” when there are no fractional seconds.
This differs from PostgreSQL’s documented text output rules:
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
(trailing zero fractional seconds should be
omitted).
## Analysis
- Simple queries default to text protocol; the timestamp value does not go
through binary encoding and falls into the generic toString branch in
writeTextValue.
- Native PostgreSQL serializes timestamp text according to the official
formatting rules; the proxy lacks a type-aware formatter and uses JDK
Timestamp#toString(), causing the “.0” suffix.
- This is a proxy-side protocol formatting mismatch, unrelated to user
configuration or usage.
## Conclusion (with code-design-level fix suggestion)
- This is a formatting defect in the proxy. Proposed design direction:
- In the PostgreSQL text protocol write path
(PostgreSQLDataRowPacket#writeTextValue), use type-aware formatting for
timestamp/timestamptz/date/time instead of generic toString().
- Add or reuse a PostgreSQL-consistent formatter under the
database.protocol.postgresql module (e.g., a dedicated text formatter akin to
PostgreSQLTimestampUtils) that enforces “omit trailing 0 fractional seconds”
and handles timezone/precision per the official
spec.
- Encapsulate type checks and formatting in a factory/strategy to
avoid scattered instanceof; let writeTextValue choose a formatter based on
column type and only fall back to toString for unhandled types.
- Add protocol/integration tests: a timestamp without fractional
seconds should be asserted to return without “.0”; also cover fractional
seconds, timestamptz, and date/time to ensure no regressions.
- We warmly welcome community contributors to submit a PR implementing
this formatting alignment and adding tests. Thank you for the report and
support!
--
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]
