sql: expose MVCC timestamps to SQL

[ajwerner]

Is your feature request related to a problem? Please describe.

Cockroach associates each row in its KV store with an HLC timestamp for the purpose of MVCC. Currently this information is not accessible to client applications. Making this data accessible would be valuable to allow client applications to detect when data changed. We know for a fact that this has been a customer request on more than one occasion.

It also loosely relates to #39275 as using these timestamp might make detecting differences between rows easier. It seems like this task would be something of a requirement to implementing #19749.

Describe the solution you'd like

I'd like to see cockroach expose a system column that a sql query can ask for to retrieve the MVCC timestamp of a row. Postgres offers such system columns (see https://www.postgresql.org/docs/current/ddl-system-columns.html) that provide introspection into its concurrency control.

One wrinkle in all of this is that different column families in a row can have different MVCC timestamps. Perhaps the name of the column will need to somehow refer to the name of the column family. Perhaps then we'd need some way to refer to the upper bound of all of the column families.

Another related problem has to do with retrieving this column from indexes. Only in some cases will we know that the write timestamp on an index will be the same as the timestamp on a secondary index. Figuring out when the timestamp off of the secondary index can be used vs. needing to go back and consult the primary index may also be something of a complex problem. I suspect for many cases it's not a big deal -- the need to do an index join back on the primary index when using this feature would likely be acceptable for v0.

Describe alternatives you've considered

Not much. Perhaps some sort of auto-updating column that updates for every write to the row?
Triggers could relieve the need for this in some cases but it's not ideal.

Additional context

The changes required for this should be pretty isolated. The row-fetcher already always retrieves the MVCC timestamps in get and scan requests. It's a matter of telling the row-fetcher to decode it and use it and to plan that retrieval of these columns. This feels like a squarely SQL features project and might even make for a good intern project.

The remaining complication as far as I'm concerned is that today schema change backfills will update rows and their MVCC timestamps. The solution to this will be to construct new indexes and swapping over (#47989). This issue shouldn't necessarily precede the work to expose MVCC columns but it has major bearing on their semantics. I suspect the work should proceed in parallel.

[ajwerner]

Another proposal is to expose the MVCC timestamp as a builtin. It's not clear exactly what the signature of such a builtin would be but it seems conceivable that we could invent one. I have a harder time envisioning how such a builtin would get planned or implemented.

[awoods187]

Slack thread: https://cockroachlabs.slack.com/archives/CHVV403F0/p1591888449339100

[rohany]

One wrinkle in all of this is that different column families in a row can have different MVCC timestamps. Perhaps the name of the column will need to somehow refer to the name of the column family. Perhaps then we'd need some way to refer to the upper bound of all of the column families.

We already "collect" information about each kv read when buffering data for a row. It wouldn't be hard to keep a running max mvcc timestamp that is reset every time a row is emitted from the fetcher.

the need to do an index join back on the primary index when using this feature would likely be acceptable for v0.

This seems reasonable.

I might hack on this and see how possible it is. I'll update the thread with results if I get anywhere.

[rohany]

Turns out the fetcher level implementation of this is really easy -- it already tracks timestamps and buffers them across families for each row:

--- a/pkg/sql/row/kv_fetcher.go
+++ b/pkg/sql/row/kv_fetcher.go
@@ -17,6 +17,7 @@ import (
        "github.com/cockroachdb/cockroach/pkg/roachpb"
        "github.com/cockroachdb/cockroach/pkg/sql/sqlbase"
        "github.com/cockroachdb/cockroach/pkg/storage/enginepb"
+       "github.com/cockroachdb/cockroach/pkg/util/hlc"
        "github.com/cockroachdb/errors"
 )

@@ -73,14 +74,16 @@ func (f *KVFetcher) NextKV(
                        var key []byte
                        var rawBytes []byte
                        var err error
-                       key, rawBytes, f.batchResponse, err = enginepb.ScanDecodeKeyValueNoTS(f.batchResponse)
+                       var ts hlc.Timestamp
+                       key, ts, rawBytes, f.batchResponse, err = enginepb.ScanDecodeKeyValue(f.batchResponse)
                        if err != nil {
                                return false, kv, false, err
                        }
                        return true, roachpb.KeyValue{
                                Key: key,
                                Value: roachpb.Value{
-                                       RawBytes: rawBytes,
+                                       RawBytes:  rawBytes,
+                                       Timestamp: ts,
                                },
                        }, newSpan, nil
                }

The next step is just planning -- we need to advertise this timestamp column in the catalog, ensure that only the primary index contains it (which forces an index join), and then tell the fetcher in what column slot to place the timestamp. @RaduBerinde do you think you could give me a pointer about where to look to do this in the optimizer/is this something that would be easily done?

[ajwerner]

One question is whether decoding the timestamp has some cost here (I anticipate it does) and thus whether we want to only decode it if it we need it?

[rohany]

There is going to be some small amount of overhead, but it looks pretty easy to only decode the timestamp when we need it

[rohany]

cc @RaduBerinde, did you get a change to look at this? It seems like this is a pretty desired feature from customers, so we might look at changing the priority of it.

[RaduBerinde]

For the optimizer, it should be as easy as changing the catalog (sql/opt_catalog.go) to advertise an extra, hidden column and put it in the primary index. We'd want a similar change in the test catalog (sql/opt/testutils/testcat/test_catalog.go) to be able to have related opt tests

[rohany]

Hmm, it doesn't seem to be as easy as just changing the optTable. Query planning outside of the optimizer doesn't use the catalog interfaces and operate directly on descriptors, meaning that this extra column has to escape the optTable and live on the ImmutableTableDescriptor (and on the columns in the TableDescriptor inside). There's also a question about what ID to assign to this new column.

[RaduBerinde]

It's up to the ConstructScan opt_factory code to create whatever metadata is needed to scan this column. E.g. it could be converted to a special flag in the scanNode, or whatever..

[andreimatei]

For posterity, the hidden column that was added here is crdb_internal_mvcc_timestamp.