terrymanu commented on issue #37485:
URL:
https://github.com/apache/shardingsphere/issues/37485#issuecomment-3688548530
## Problem Understanding
- In ShardingSphere-JDBC 5.5.2 with PostgreSQL, a SINGLE-table
(main.outbox_dm_tables.mytestentity) insert via Spring Data JPA and
@GeneratedValue(strategy = IDENTITY) fails: Hibernate throws
NumberFormatException when reading the generated key; the value returned as the
“generated key” is actually the business column {my-event-as-string}.
- The SINGLE rule config is standard per docs; the expected behavior is to
relay the database’s identity key.
## Root Cause
- In single-table scenarios without ShardingSphere key generation,
GeneratedKeyContext is empty, so ShardingSphereStatement#getGeneratedKeys /
ShardingSpherePreparedStatement#getGeneratedKeys fall back to iterating
underlying statements’ getGeneratedKeys() and unconditionally read column 1 via
resultSet.getObject(1).
- PostgreSQL returns the whole row for RETURN_GENERATED_KEYS (equivalent
to RETURNING *); column 1 is the first table column event, not the identity
sequencenumber. This wrong value is wrapped into GeneratedKeysResultSet and
later parsed as long, causing the NumberFormatException.
- The logic assumes “generated key is always the first column” and does
not locate the generated key by column name or metadata, so it breaks on
PostgreSQL’s result column ordering.
## Analysis
- Usage is correct; the failure stems from ShardingSphere’s generated-key
aggregation logic being incompatible with PostgreSQL’s multi-column
getGeneratedKeys() result.
- Proper behavior: when a generated-key column name is known (from
GeneratedKeyContext or dialect GeneratedKeyOption), resolve the column index
from ResultSetMetaData/labels and read that column; only fall back to index 1
if no match exists. This keeps compatibility with both “only key returned”
drivers and PostgreSQL’s “whole row” behavior.
- GeneratedKeysResultSet itself only exposes values; the bug is in how
values are collected. The fix should live in
ShardingSphereStatement/ShardingSpherePreparedStatement when assembling
generated key values, avoiding hardcoded column 1.
## Conclusion
- This is a ShardingSphere-JDBC defect for PostgreSQL SINGLE-table +
IDENTITY inserts, not a user configuration issue. We should fix
getGeneratedKeys fallback to fetch by generated-key column name using metadata
instead of always column 1, and add PostgreSQL SINGLE-table
RETURN_GENERATED_KEYS regression tests to verify correct key value/type.
- We warmly invite community contributors to submit a PR with the fix and
accompanying tests; your help will make the PostgreSQL path more robust for
everyone.
--
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]
