Updateable Views

Hi,

I saw the view support RFC document (https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/views.md) and that the current version has support for views.

It would be great to know if there are any short term plans to make these views updateable?

I’m expecting that only ‘simple’ views would be updateable. By simple I mean in the similar way as described in the mysql docu (https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html).

Cheers.
Oliver

Hi Oliver,

There aren’t immediate plans to add this, but it’s something that comes up in conversations often, sometimes in conjunction with triggers and pub/sub streaming of updates.
One paper that made the rounds around here was the Naiad project from Microsoft, which got people all excited. It talks about a more general framework useful for materialized views.

Hi Andrei,

Thanks for the response. Will have a look at the article you sent.

We are interested in this to be able to implement a multi-tenant solution on Cockroach.
(along the lines of this article https://rjbtechnology.com/blog/2017-01/discriminator-multitenancy-with-spring-and-hibernate-no-filters/)

Cheers
Oliver

I think I may have misunderstood your question - I thought you were asking for “materialized views” - views that don’t have to query other tables when they’re queried themselves.

Instead, you’re asking about views that you can run UPDATEs and INSERTs on. This has not come up before, as far as I know. Can you please tell me more about what you have in mind and how this would help you with multi-tenancy?

Ah.

All the tenanted data is in tables that have an extra column to identify the Tenant.

CREATE TABLE T_PRODUCT  (
   id INT GENERATED BY DEFAULT AS IDENTITY,
   tenant_id INT,
   sku VARCHAR(255) NOT NULL
);

We then use a view something like the one shown below to allow our application to use in this example the PRODUCT table without any special knowledge about the tenanting because the underlying framework (datasources, connections, view) ensure a specific customer/tenant can only ever access their own data.

CREATE VIEW PRODUCT AS 
    SELECT * FROM T_PRODUCT
    WHERE t.tenant_id IS NULL          -- shared data
       OR t.tenant_id = CURRENT_USER   -- tenant data

In order to allow the application to also insert and update data (without being able to write to another Tenant’s data) the view must allow INSERTs and UPDATEs.

The advantage of this approach is that it also protects the tenant data when manual selects, inserts and updates are made.

Cheers
Oliver

Hi,

what you actually want is row-level access control. I saw that somewhere on the roadmap, but without any priority.

Petr

Hi Petr,

Yeah I guess you are right? I have done some searching around the roadmap etc. I’ve also seen some discussions about multi-tenanting being considered as a core feature by the cockroach team (i think). Is this potentially one of the features that will only be available in the ‘enterprise’ version?

Cheers
Oliver

Hi @oliver.henlich, apologies for taking a while to respond to this. I think we got the chance to speak over the phone though, so hopefully we were able to answer your questions. True multi-tenancy is on our roadmap, but still not formalized in terms of an actual timeline.