You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "susongyan (via GitHub)" <gi...@apache.org> on 2023/05/22 04:33:13 UTC
[GitHub] [shardingsphere] susongyan opened a new issue, #25838: proxy update pg json field failed
susongyan opened a new issue, #25838:
URL: https://github.com/apache/shardingsphere/issues/25838
## Bug Report
### Which version of ShardingSphere did you use?
5.3.0
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
proxy
### Expected behavior
update json field success
### Actual behavior
org.postgresql.util.PSQLException: ERROR: column "xx" is of type json but expression is of type character varying
proxy seems to treat json as text in backend
### Reason analyze (If you can)
in "bind" phase, actual parameter is not parsed correctly
PostgreSQLComBindPacket#getTextParameters doesn't deal with PostgreSQLColumnType.POSTGRESQL_TYPE_JSON
<img width="1364" alt="image" src="https://github.com/apache/shardingsphere/assets/17156143/16a17fd6-bb38-4a20-9b2c-d3cd08a63621">
expected parameter object(i tried to fix myself): update success
<img width="1396" alt="image" src="https://github.com/apache/shardingsphere/assets/17156143/9cd95229-ba80-4cda-b615-8db804feb1da">
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
--
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
[GitHub] [shardingsphere] sandynz commented on issue #25838: proxy update pg json field failed
Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #25838:
URL: https://github.com/apache/shardingsphere/issues/25838#issuecomment-1558501138
> ok ,i'll submit a pr; assign it to me plz @sandynz
Well, done
--
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
[GitHub] [shardingsphere] susongyan commented on issue #25838: proxy update pg json field failed
Posted by "susongyan (via GitHub)" <gi...@apache.org>.
susongyan commented on issue #25838:
URL: https://github.com/apache/shardingsphere/issues/25838#issuecomment-1558481553
ok ,i'll submit a pr; assign it to me plz
@sandynz
--
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
[GitHub] [shardingsphere] sandynz commented on issue #25838: proxy update pg json field failed
Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #25838:
URL: https://github.com/apache/shardingsphere/issues/25838#issuecomment-1556733246
Hi @susongyan , thanks for your feedback.
Some questions:
1, How you set parameter for PreparedStatement when exception occur, is there example code
2, What's the workaround that make it work
--
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
[GitHub] [shardingsphere] susongyan commented on issue #25838: proxy update pg json field failed
Posted by "susongyan (via GitHub)" <gi...@apache.org>.
susongyan commented on issue #25838:
URL: https://github.com/apache/shardingsphere/issues/25838#issuecomment-1557102838
what i do to fix this case is
add case json in org.apache.shardingsphere.db.protocol.postgresql.packet.command.query.extended.bind.PostgreSQLComBindPacket#getTextParameters(java.lang.String, org.apache.shardingsphere.db.protocol.postgresql.packet.command.query.extended.PostgreSQLColumnType)
```java
case POSTGRESQL_TYPE_JSON:
return PostgreSQLTextJsonUtils.parse(textValue);
default:
```
PostgreSQLTextJsonUtils.parse returns PGobject type=json
--
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
[GitHub] [shardingsphere] susongyan commented on issue #25838: proxy update pg json field failed
Posted by "susongyan (via GitHub)" <gi...@apache.org>.
susongyan commented on issue #25838:
URL: https://github.com/apache/shardingsphere/issues/25838#issuecomment-1557099930
code:
using jdbc to update json field 'alert_persons'
String sql = "UPDATE t_alert_rule SET alert_persons = ?, update_time = CURRENT_TIMESTAMP WHERE id IN (?) RETURNING update_time";
try (Connection conn = dataSource.getConnection()) {
PreparedStatement preparedStatement = conn.prepareStatement(sql);
PGobject jsonObject = new PGobject();
jsonObject.setType("json");
jsonObject.setValue(JSON.toJSONString(Collections.singletonList("test)")));
preparedStatement.setObject(1, jsonObject);
preparedStatement.setLong(2, 130);
preparedStatement.execute();
}
--
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
[GitHub] [shardingsphere] sandynz commented on issue #25838: proxy update pg json field failed
Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #25838:
URL: https://github.com/apache/shardingsphere/issues/25838#issuecomment-1558457035
@susongyan Nice. Could you submit a PR to improve it?
And a possible workaround:
- Add `&stringtype=unspecified` in JDBC url parameter
--
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
[GitHub] [shardingsphere] susongyan commented on issue #25838: proxy update pg json field failed
Posted by "susongyan (via GitHub)" <gi...@apache.org>.
susongyan commented on issue #25838:
URL: https://github.com/apache/shardingsphere/issues/25838#issuecomment-1558481194
>
ok ,i'll submit a pr; assign it to me plz
--
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
[GitHub] [shardingsphere] sandynz closed issue #25838: proxy update pg json field failed
Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz closed issue #25838: proxy update pg json field failed
URL: https://github.com/apache/shardingsphere/issues/25838
--
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