Why does historical version query not support writing to the table?

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 历史版本查询为何不支持写入到表中呢

| username: 人如其名

[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.

| username: tidb菜鸟一只 | Original post link

At this time, the data being queried is based on past points in time, including all the data, even the data you insert…

| username: Jellybean | Original post link

When using this feature, the cluster’s snapshot has already switched to a historical version, and historical data can only provide read operations.

| username: 人如其名 | Original post link

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?

| username: 爱学习的TiDBer | Original post link

I remember ORACLE supports this, and I also suggest improving this feature.

| username: zhanggame1 | Original post link

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
| username: 人如其名 | Original post link

Yes, thank you for the correction.

| username: 人如其名 | Original post link

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!

| username: Kongdom | Original post link

:call_me_hand: Finally supported after a year.

| username: zhaokede | Original post link

You even dug up a post from a year ago.
Still haven’t upgraded to version 8.

| username: lemonade010 | Original post link

Oracle supports this feature, but TiDB can also implement it quickly in the new version. Thumbs up.