Open yuqi1129 opened 3 months ago
xloya
commented
3 months ago In H2, the UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) function does not seem to be able to retain data below seconds, and the results are all 0 after seconds, so conflicts may occur.
yuqi1129
commented
3 months ago In H2, the CURRENT_TIMESTAMP(3) function does not seem to be able to retain data below milliseconds, and the results are all 0 after mills, so conflicts may occur.
I recall that we discussed this several months ago. Do you have any suggestions on how to solve it?
xloya
commented
3 months ago In H2, the CURRENT_TIMESTAMP(3) function does not seem to be able to retain data below milliseconds, and the results are all 0 after mills, so conflicts may occur.
I recall that we discussed this several months ago. Do you have any suggestions on how to solve it?
The current method works well in our production environment, but I think the following research can be done:
yuqi1129
commented
3 months ago From my observation, most RDBMSs cannot support time precision less than one millisecond. Using the built-in function to get the current time is far from ideal in terms of precision.
Is there a way to get millisecond time in H2
We don't need to limit our system to H2. We have left the interface to support other embedded databases like derby, sqlite, though it's not so necessary now or in the near future.
Optimize the current function to support millisecond or even higher precision timestamps in a more general way. But this may affect some code logic.
Yeah, I'm focusing on this point and will provide a TSO that is strongly related to current time but provides higher precision time. In this way, it can be database independent. I'm not entirely sure if the complexity is acceptable.
xloya
commented
3 months ago From my observation, most RDBMSs cannot support time precision less than one millisecond. Using the built-in function to get the current time is far from ideal in terms of precision.
Is there a way to get millisecond time in H2
We don't need to limit our system to H2. We have left the interface to support other embedded databases like derby, sqlite, though it's not so necessary now or in the near future.
Optimize the current function to support millisecond or even higher precision timestamps in a more general way. But this may affect some code logic.
Yeah, I'm focusing on this point and will provide a TSO that is strongly related to current time but provides higher precision time. In this way, it can be database independent. I'm not entirely sure if the complexity is acceptable.
My initial consideration was to calculate the time externally and save it to the database, but this means that the time may be client-dependent, which may lead to inaccurate time if the clocks of multiple clients are not synchronized.
yuqi1129
commented
3 months ago My initial consideration was to calculate the time externally and save it to the database, but this means that the time may be client-dependent, which may lead to inaccurate time if the clocks of multiple clients are not synchronized.
I see, there could be a time-skew problem if we use the time generated by the Gravitino server. let me think if it can be addressed.
yuqi1129
commented
2 months ago Resolved by #3954
yuqi1129
commented
3 weeks ago The problem still exists, please see: https://github.com/apache/gravitino/actions/runs/10570943483/job/29286239153?pr=4694
yuqi1129
commented
3 weeks ago Test code
for (int i = 0; i < 10; i++) {
try {
ResultSet rs = statement.executeQuery("SELECT (UNIX_TIMESTAMP() * 1000.0), EXTRACT(MICROSECOND FROM CURRENT_TIMESTAMP(3)) / 1000");
rs.next();
String v1 = rs.getString(1);
String v2 = rs.getString(2);
LOG.info("Current timestamp-yq: {}, {}", v1, v2);
statement.execute("update schema_meta set deleted_at = 1000 where schema_id = 1");
} catch (Exception e) {
LOG.error("Failed to connect to H2 database. Retrying...", e);
Thread.sleep(1000);
}
}
What would you like to be improved?
When we switch default entity store to embedded JDBC database and try to drop a entity, the following errors occur:
As the query response time(less that 1ms) is very fast compared to MySQL, the value of new
deleteAtis the same as old one, then error occur.How should we improve?
I believe we need to improve the precision or use other ways to solve it, or we can't use embedded JDBC database well.