sql: cannot store "AS OF SYSTEM TIME" clause in views

[robert-s-lee]

Describe the problem

would like to use AOST on reporting side without having to change the application.

To Reproduce

select ycsb_key from usertable as of system time '-1m' limit 1;

create view x as select ycsb_key from usertable as of system time '-1m' limit 1;
ERROR: AS OF SYSTEM TIME must be provided on a top-level statement
SQLSTATE: 42601

Expected behavior

support AOST inside view

Additional data / screenshots

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version 20.1.1
• Server OS: osx
• Client app cockroach sql

Additional context

changes are required on every SQL that require the use of AOST

Add any other context about the problem here.

Epic CRDB-9838
gz#5693

Jira issue: CRDB-4093

[knz]

Note: this is a variant of issue #35712.

[knz]

cc @ajwerner @RaduBerinde for triage

[RaduBerinde]

I have looked at this before, the way AOST currently is implemented in the execution layer precludes supporting this. We'll need to come up with a better design for the larger issue (#35712). Note that even this case is arguably a mixed-timestamp case: there is a timestamp inside the view, but there is also the (current) timestamp at which we retrieve the view descriptor itself.

[ajwerner]

This issue is additionally complicated by the fact that it's challenging to think about interactions with schema changes.

[bladefist]

@ajwerner when we use AOST what we are trying to tell the database is "give us the data but don't lock anything". Records that are current being written, new records during the query, or schema changes we don't care about. These are generally non-critical analytical queries where a bit of variance from the truth is acceptable.

[ajwerner]

Understood, my point is that it seems easier to support in generic queries and in CREATE TABLE AS SELECT AS OF SYSTEM TIME and in MATERIALIZED views because the structure of relevant tables needs to only be determined once whereas for views, today, we realized the structure of the view (its columns and references, etc) when we create it but then we need those to stay the same or get changed when something it references changes. In the case of materialized views, we can detect the problem we go to REFRESH. I suspect for your use case that using a materialized view would get the job done.

[bladefist]

@ajwerner actually I was sent here by Paul in support, I don't even use or care about views haha. My request was AOST to work across the aboard, not just top level queries. Sub queries, inserts/upserts that contain selects. Etc. Maybe I'm in the wrong ticket.

[jordanlewis]

#35712 is a general issue for this kind of thing.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!