A Contrived Example to Illustrate How to Track "Last Updated Timestamp" in CockroachDB

In today's "Cockroach Hour", Sean from DoorDash was the guest, and it was a really interesting session. At one point he was commenting on the absence of triggers and stored procedures and how that related to their practice of automating the update of their LAST_UPDATED column, which likely contained the timestamp of the last INSERT or UPDATE to the given row. They worked with Cockroach Labs to get a workaround integrated into the 20.2 release, one which exposes the existing MVCC timestamp, and that was good enough.

Here, I'm just going to synthesize a little example, mostly so I don't lose track of this idea. First, I'll create a table:

root@:26257/defaultdb> create table person (id uuid, name string, primary key(id));
CREATE TABLE

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

Next, I'll insert a few random rows:

root@:26257/defaultdb> insert into person select gen_random_uuid(), substring(sha256(random()::text) from 1 for 20) from generate_series(1, 20);
INSERT 20

Time: 70ms total (execution 69ms / network 0ms)

And then, I can verify the data is there (note the addition of the crdb_internal_mvcc_timestamp system column):

root@:26257/defaultdb> select *, crdb_internal_mvcc_timestamp from person order by 3 desc limit 5;
                   id                  |         name         |  crdb_internal_mvcc_timestamp
---------------------------------------+----------------------+---------------------------------
  08c6ab92-507c-4f43-91f7-e0f8547628a0 | d9ea3d4601d0d21406d7 | 1614815527857987000.0000000000
  18e511e9-20a8-464e-bb76-94415b76ee4f | fa6a8a20c8089b7f6e5b | 1614815527857987000.0000000000
  207843e3-d52e-4502-9b96-998e0bef6ff6 | e9299d79f668c336db8a | 1614815527857987000.0000000000
  2869a943-ca3f-4bd9-ab05-55a924aa7ec2 | 236a9fd4c590e72552c9 | 1614815527857987000.0000000000
  41985298-057e-4364-af19-99be48569fa9 | 813fe18e4fbd431fb0a5 | 1614815527857987000.0000000000
(5 rows)

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

Now, I'll update a row, changing its name value:

root@:26257/defaultdb> update person set name = 'Jasper' where id = '2869a943-ca3f-4bd9-ab05-55a924aa7ec2';
UPDATE 1

Time: 72ms total (execution 71ms / network 0ms)

Finally, I get to see what I was hoping for when I re-run my SELECT:

root@:26257/defaultdb> select *, crdb_internal_mvcc_timestamp from person order by 3 desc limit 5;
                   id                  |         name         |  crdb_internal_mvcc_timestamp
---------------------------------------+----------------------+---------------------------------
  2869a943-ca3f-4bd9-ab05-55a924aa7ec2 | Jasper               | 1614815903953745000.0000000000
  08c6ab92-507c-4f43-91f7-e0f8547628a0 | d9ea3d4601d0d21406d7 | 1614815527857987000.0000000000
  18e511e9-20a8-464e-bb76-94415b76ee4f | fa6a8a20c8089b7f6e5b | 1614815527857987000.0000000000
  207843e3-d52e-4502-9b96-998e0bef6ff6 | e9299d79f668c336db8a | 1614815527857987000.0000000000
  41985298-057e-4364-af19-99be48569fa9 | 813fe18e4fbd431fb0a5 | 1614815527857987000.0000000000
(5 rows)

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

If I'd rather see that timestamp value in a human-readable format, I can modify this SELECT slightly:

root@:26257/defaultdb> select *, crdb_internal.approximate_timestamp(crdb_internal_mvcc_timestamp) from person order by 3 desc limit 5;
                   id                  |         name         | crdb_internal.approximate_timestamp
---------------------------------------+----------------------+--------------------------------------
  2869a943-ca3f-4bd9-ab05-55a924aa7ec2 | Jasper               | 2021-03-03 23:58:23.953745+00:00
  08c6ab92-507c-4f43-91f7-e0f8547628a0 | d9ea3d4601d0d21406d7 | 2021-03-03 23:52:07.857987+00:00
  18e511e9-20a8-464e-bb76-94415b76ee4f | fa6a8a20c8089b7f6e5b | 2021-03-03 23:52:07.857987+00:00
  207843e3-d52e-4502-9b96-998e0bef6ff6 | e9299d79f668c336db8a | 2021-03-03 23:52:07.857987+00:00
  41985298-057e-4364-af19-99be48569fa9 | 813fe18e4fbd431fb0a5 | 2021-03-03 23:52:07.857987+00:00
(5 rows)

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

That's all I have for the moment. I'll ponder some applications of this and hopefully follow up on that.