sql: Support renaming objects depended on by views

[a-robinson]

In our initial implementation of views, we store a mostly-unmodified text form of the query. This prevents us from allowing renames on the objects used in the query without doing query rewriting, which is quite difficult.

We plan to switch to a semantic encoding that would only rely on the IDs of objects in the query when possible (initial discussion of such an encoding is in #10055). Once that time has come, we should remove the restrictions on renaming dependent objects.

Opening this primarily for tracking.

Jira issue: CRDB-6143

[petermattis]

@knz Is this feasible in the 1.0 timeframe? It doesn't seem necessary, but I know you're working towards this.

[knz]

yes, this is feasible if we prioritize #12715 - that will give us a solution to this issue immediately.

[knz]

I got #13781 submitted as groundwork for this.
The remaining step is to implement a transform function that rewrites the table expressions in the view definition to become numeric table/index references prior to storing the definition in the view descriptor.

[dianasaur323]

Bringing into 1.0 milestone given the history of conversation, but if not necessary at this point, please move out into 1.1 milestone. Assigning @knz. Thanks!

[knz]

I can look into it. It is certainly easier now than it was three months ago, but I need to check relative to other work items.

Unless we have a very strong need I would simply push it to 1.1 straight away. Our list of strong 1.0 issues is already large.

[dianasaur323]

@knz let's move out.

[a-robinson]

Agreed - features should already be frozen and this isn't of critical major importance.

[cuongdo]

@knz is this going to be resolved before 1.1 feature freeze?

[knz]

Iv'e scheduled a meeting to discuss this next week.

[knz]

It depends on #15388 too

[jordanlewis]

Reopening this, as we'd like to make progress on it before doing a larger migration to store ids instead of names in stored descriptors.

[RichardJCai]

I've been thinking about doing this without referring to ColumnIDs and it seems pretty tricky. With the way we store view queries right now, we could potentially have something like

CREATE TABLE t(x int, y int);
CREATE VIEW vx AS SELECT x, y AS z FROM T;
ALTER TABLE t RENAME COLUMN x TO z;

Our resulting view would be
SELECT z, y AS z FROM T

Postgres avoids this by renaming one of the z's to z_1 but if this happens inside a subquery, we'd have to track which to rename as z_1. It would be tricky to do especially since we don't force subqueries to use aliases unlike Postgres' views.

To support ids for #22212. If we want to update all views to use ids, we would have to perform a rename but we wouldn't have to deal with this variable capture issue.

[shermanCRL]

We’ve got a customer request on this too, specifically that a table rename should not require dropping a view dependent on that table. (Let me know if this is the right issue.)

[ajwerner]

This is the right issue. We went through a bunch of discussions on this in the spring. The right want to do it will require machinery we don't currently have. See this long internal thread on the topic: https://cockroachlabs.slack.com/archives/C04U1BTF8/p1614712126092900.

The crux of the problem is that scoping in SQL is complex. We have one component in the whole system which understands it: the optbuilder. The problem is that what the optbuilder produces as it digests the statement throws away some information we'd need to rewrite the AST appropriately. The proposal in that thread is to have the optbuilder populate a mapping that as it builds the statement and then use that mapping to then rewrite the whole statement in a way that is name agnostic. It's not a small project but it does seem like something we need to do to unblock this issue. It'd be a generally very good thing.

[postamar]

relates to #83233

[shermanCRL]

Experienced by a customer as part of a table restore process, see internal thread.

[rimadeodhar]

Two issues related to this are being planned for the 23.2 release: #98606 and #98607