Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 历史版本查询为何不支持写入到表中呢
[TiDB Usage Environment] Poc
[TiDB Version] v7.0.0
TiDB provides a method to read historical data using the AS OF TIMESTAMP syntax (recommended method), but this method can only export the entire table to an external file and then import it back into the database. Why not support directly using “insert into select … from … as of timestamp”? This way, combined with batch on non-transaction mode, can save import and export time and make operations more convenient.
At this time, the data being queried is based on past points in time, including all the data, even the data you insert…
When using this feature, the cluster’s snapshot has already switched to a historical version, and historical data can only provide read operations.
What I understand is that insert happens as a current read, which conflicts with snapshot read. But if it is a simple insert select (and a few specific recovery scenarios), is it possible to make insert compatible with snapshot read?
I remember ORACLE supports this, and I also suggest improving this feature.
Where conditions are supported, I tested it and you might have made a mistake.
select a, b, c from test as of timestamp '2023-05-25 09:55:00'
where a = 1;
Insert into select is not supported, specific error:
mysql> insert into test1
-> select a, b, c from test as of timestamp '2023-05-25 10:05:00'
-> where a = 2;
ERROR 8135 (HY000): can not set different time in the as of
Yes, thank you for the correction.
Starting from version 8.1, the import into statement supports importing historical version data using as of timestamp. For example:
import into t from select * from customer as of timestamp date_sub(now(), interval 1 minute);
Give it a thumbs up!
Finally supported after a year.
You even dug up a post from a year ago.
Still haven’t upgraded to version 8.
Oracle supports this feature, but TiDB can also implement it quickly in the new version. Thumbs up.
|
username: 人如其名
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: