sql: AS OF SYSTEM TIME doesn't support dropped databases at timestamps prior to the drop time

[thoszhang]

Currently, AS OF SYSTEM TIME can be used to read historical data in a dropped table prior to the drop time, provided that the database still exists. However, if the database containing the table was dropped, AOST no longer works because name resolution fails during planning:

root@:26257/defaultdb> create database test;
CREATE DATABASE

Time: 3.011ms

root@:26257/defaultdb> create table test.t();
CREATE TABLE

Time: 4.49ms

root@:26257/defaultdb> select * from current_timestamp();
         current_timestamp
------------------------------------
  2020-07-13 15:40:45.259421+00:00
(1 row)

Time: 401µs

root@:26257/defaultdb> drop database test cascade;
DROP DATABASE

Time: 26.796ms

root@:26257/defaultdb> select * from test.t as of system time '2020-07-13 15:40:45.259421+00:00';
ERROR: relation "test.t" does not exist
SQLSTATE: 42P01

As I understand it, we don't make any provisions for accessing older versions of database descriptors. The database cache is just a name to ID mapping that attempts to store the latest version, and we fall back to reading the database descriptor from KV otherwise. To support this functionality, we'll need database descriptor leasing.

Jira issue: CRDB-4058

[ajwerner]

This limitation has been lifted in 20.2 with the generalized leasing mechanism for databases. It does not seem to work for schemas unfortunately. We'll track down what causes this for schemas.

[postamar]

Closing as this does indeed seem to work for schemas these days.

demo@127.0.0.1:26257/defaultdb> CREATE SCHEMA barf;
CREATE SCHEMA


Time: 34ms total (execution 34ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> CREATE TABLE barf.foo (x INT PRIMARY KEY);
CREATE TABLE


Time: 7ms total (execution 7ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> select * from current_timestamp();
        current_timestamp
---------------------------------
  2022-03-11 16:09:45.722436+00
(1 row)


Time: 1ms total (execution 0ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> DROP SCHEMA barf CASCADE;
DROP SCHEMA


Time: 123ms total (execution 123ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> SELECT * FROM barf.foo AS OF SYSTEM TIME '2022-03-11 16:09:45.722436+00';
  x
-----
(0 rows)


Time: 14ms total (execution 14ms / network 0ms)