sql: Cannot use AOST on the select query of an insert

[bladefist]

Cockroach 2.1 Sept 17th.

insert into Table(cols) select cols from table2 as of system time '-1m' where -something- fails with:

pq: AS OF SYSTEM TIME must be provided on a top-level statement.

It would be useful to do this so you could do a large table copy as a non-blocking/locking statement.

Thanks.

Epic: CRDB-9838

Jira issue: CRDB-4829

[maddyblue]

This is tricky due to the AOST implementation and can't be done right now. We've discussed this lots before but have no specific plans to implement it anytime soon. The best thing you can do now is SELECT ... AOST ... into a CSV file and then IMPORT that CSV.

[bladefist]

Alright. But w/ the snapshop isolation being deprecated, was told AOST was the way to do non-blocking queries. It's just so incredibly limiting in where you can use AOST.

[knz]

@awoods187 can you check where this would fit on the roadmap? I know other users have asked before.

[knz]

cc @rolandcrosby I think you may have an opinion too

[sploiselle]

@mjibson Can I get you to draft a short blurb describing this known limitation for the 2.1 known limitation page? Posting it on this issue and/or pinging me would be great.

[maddyblue]

AS OF SYSTEM TIME can only be used in a top-level SELECT statement. That is, we do not support statements like INSERT INTO t SELECT * FROM t2 AS OF SYSTEM TIME <time> or two subselects in the same statement with differing AS OF SYSTEM TIME arguments.

[rmloveland]

I just created cockroachdb/docs#6224 which seems to be related to this

[irfansharif]

Duplicate #35712.