sql: Support UPDATE, DELETE and INSERT on (simple) views

[lgo]

In PostgreSQL, there is a strict set of criteria which for views that categorize them as "simple views" or "updatable views". https://www.postgresql.org/docs/10/static/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS

An example of a use case would be to create a crude form of column-level permissions. If you make a view that selects a subset of columns and then grant permissions on that view instead of the underlying table.

Jira issue: CRDB-5907

[awoods187]

@RaduBerinde this came up today with Active Record and we realized its more of an optimizer issue. Curious if you have an idea of how involved it will be.

[RaduBerinde]

It's probably doable (albeit tedious).

[rafiss]

A suggestion also came up internally that supporting updatable views could make the experience of renaming a column/table much less of a hassle from the app perspective. Currently renaming is hard for an application to deal with because there is a period when it needs to support both the old name and the new name. The standard way of doing a rename is to (1) add a new temporary column, (2) change the app to write to both columns, and read from the original column, (3) run some job that copies all the old data into the new column if the new column is null, (4) change the app to only use the new column, then (5) drop the original column.

An updatable view would remove the need to do all that in the application. For example:

root@:26257/defaultdb> create table t (a int primary key, b interval[]);

root@:26257/defaultdb> begin;
BEGIN
root@:26257/defaultdb  OPEN> alter table t rename column b to c;
ALTER TABLE
root@:26257/defaultdb  OPEN> create or replace view v(a, b) as select a, c from t;
CREATE VIEW
root@:26257/defaultdb  OPEN> commit;
COMMIT
root@:26257/defaultdb> select * from v;
  a |      b
----+--------------
  1 | {-23:00:00}
root@:26257/defaultdb> select * from t;
  a |      c
----+--------------
  1 | {-23:00:00}

This requires fewer changes in the app, since it just needs to use the view. (If the view name could be changed to the table name in the same atomic transaction, then there would be even fewer app changes.)

@andy-kimball suggested that allowing updatable views for the very narrow case of SELECT <cols> FROM <table> might be a smaller work item, but would still allow for this improved experience.

[rafiss]

Related to the above comment, here’s a way to do a column rename without needing to stop the clients of the database.

ALTER TABLE tab ADD COLUMN newname string;
ALTER TABLE tab ADD COLUMN tempalias string AS (COALESCE(newname,oldname)) STORED;
-- Deploy code that writes to newname and reads from tempalias
UPDATE t SET newname=oldname where newname IS NULL;
ALTER TABLE tab ALTER COLUMN tempalias DROP STORED, RENAME COLUMN newname to newname_deleted, DROP COLUMN oldname, DROP column newname_deleted, RENAME tempalias TO newname, ADD COLUMN tempalias string as (newname) VIRTUAL;
-- Deploy code that reads from and writes to newname
ALTER TABLE tab DROP COLUMN tempalias;

The UPDATE step could be expensive for a large table, but it can be broken up into batches to perform better.

Of course, it’s not that easy to remember how to do this. But maybe we could come up with a docs guide for this.