QA: SELECT from view with circular reference crashes node

[msirek]

From star expressions in view definitions QA,

Creating a view with a circular reference to itself causes a stack overflow and crashes the node:

CREATE TABLE films (id int PRIMARY KEY, title text, kind text, classification CHAR(1), year int, language text, studio text, runtime float, country_code CHAR(2));
CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';
CREATE VIEW pg_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'PG';
CREATE OR REPLACE VIEW comedies AS
    SELECT *
    FROM pg_comedies;

EXPLAIN SELECT * FROM pg_comedies;
ERROR: unexpected EOF
warning: error retrieving the transaction status: connection closed unexpectedly: conn closed
warning: connection lost!
opening new connection: all session settings will be lost
warning: error retrieving the database name: failed to connect to `host=localhost user=root database=`: dial error (dial tcp 127.0.0.1:26257: connect: connection refused)

I230319 21:26:41.260793 1 util/log/flags.go:211  [-] 1  stderr capture started
runtime: goroutine stack exceeds 1000000000-byte limit
runtime: sp=0xc02e3ba388 stack=[0xc02e3ba000, 0xc04e3ba000]
fatal error: stack overflow

runtime stack:
runtime.throw({0x59e7120?, 0x9f342a0?})
        GOROOT/src/runtime/panic.go:1047 +0x5d fp=0x7f966a94c278 sp=0x7f966a94c248 pc=0x48f7dd
runtime.newstack()
        GOROOT/src/runtime/stack.go:1103 +0x5cc fp=0x7f966a94c430 sp=0x7f966a94c278 pc=0x4a9ccc
runtime.morestack()
        GOROOT/src/runtime/asm_amd64.s:570 +0x8b fp=0x7f966a94c438 sp=0x7f966a94c430 pc=0x4c204b

In Postgres, the above returns an error:

ERROR:  infinite recursion detected in rules for relation "pg_comedies"

Jira issue: CRDB-25645

[rharding6373]

Great find. This does not appear to be a * specific bug, as I've been able to repro Mark's example using column names instead of *. In UDFs we disallow UDFs to call other UDFs because of the circular dependency problem. I don't think we can disable the same for views though since it may break user workloads.

[chengxiong-ruan]

This would worth a gentle TA or at least let customers know. Maybe not worth a TA because I think it only crash the gateway node and a restart of the node would recover things.

[rharding6373]

Discussed the prospect of a TA with the SQl Queries team, and our conclusion is that we don't need one, since as you say it is recoverable, and since it only manifests with a bad schema design users with circular dependencies would have found them quickly (and can fix the issue by correcting the schema).