# Technical Advisory 82079

Publication date: July 18, 2022

## Description

The problem can occur on any cluster running versions v21.2.0 to v21.2.12 or v22.1.0. If a `CREATE MATERIALIZED VIEW` statement fails, all the tables, views, sequences and types referenced in its `SELECT` query will become unusable. Any statement or query referencing these objects will trigger an error matching the following regular expression:

`invalid depended-on-by relation back reference: referenced table ID [0-9]*: referenced descriptor not found`

This problem can be diagnosed by running the following query:

    WITH
        tables
            AS (
                SELECT
                    id, tab
                FROM
                    (
                        SELECT
                            id,
                            crdb_internal.pb_to_json(
                                'cockroach.sql.sqlbase.Descriptor',
                                descriptor
                            )->'table'
                                AS tab
                        FROM
                            system.descriptor
                    )
                WHERE
                    tab IS NOT NULL
            ),
        old_depended_on_by_entries
            AS (
                SELECT
                    id, json_array_elements(tab->'dependedOnBy') AS entry
                FROM
                    tables
            ),
        ids_of_descriptors_that_need_upsert
            AS (
                SELECT
                    DISTINCT id
                FROM
                    old_depended_on_by_entries
                WHERE
                    (entry->>'id')::INT8 NOT IN (SELECT id FROM system.namespace)
            )
    SELECT
        count(*)
    FROM
        ids_of_descriptors_that_need_upsert;

If you get back a non-zero result, your cluster has the problem described in this technical advisory. If you get back zero, your cluster does not have this problem.

## Statement

This is resolved in CockroachDB by PR #82087 which fixes the missing removal of back-references to the materialized view.

The fix has been applied to maintenance releases of CockroachDB v21.2.13 and v22.1.1. This public issue is tracked by #82079.

## Mitigation

Users of CockroachDB versions v21.2.0 to v21.2.12 or v22.1.0 are encouraged to upgrade to v21.2.13 and v22.1.1 or a later version. Upgrading to a later maintenance release will prevent this problem from appearing, but it will not fix the problem if it is already present.

If the problem is present according to the diagnostic query above, users can attempt a repair by executing the following:

    WITH
        tables
            AS (
                SELECT
                    id, tab
                FROM
                    (
                        SELECT
                            id,
                            crdb_internal.pb_to_json(
                                'cockroach.sql.sqlbase.Descriptor',
                                descriptor
                            )->'table'
                                AS tab
                        FROM
                            system.descriptor
                    )
                WHERE
                    tab IS NOT NULL
            ),
        old_depended_on_by_entries
            AS (
                SELECT
                    id, json_array_elements(tab->'dependedOnBy') AS entry
                FROM
                    tables
            ),
        new_depended_on_by_entries
            AS (
                SELECT
                    *
                FROM
                    old_depended_on_by_entries
                WHERE
                    (entry->>'id')::INT8 IN (SELECT id FROM system.namespace)
            ),
        ids_of_descriptors_that_need_upsert
            AS (
                SELECT
                    DISTINCT id
                FROM
                    old_depended_on_by_entries
                WHERE
                    (entry->>'id')::INT8 NOT IN (SELECT id FROM system.namespace)
            ),
        new_depended_on_by_arrs
            AS (
                (
                    SELECT
                        id,
                        json_agg(entry ORDER BY (entry->>'id')::INT8 ASC)
                            AS new_depended_on_by_arr
                    FROM
                        new_depended_on_by_entries
                    WHERE
                        id IN (SELECT id FROM ids_of_descriptors_that_need_upsert)
                    GROUP BY
                        id
                    UNION
                        SELECT
                            id, '[]'::JSONB AS new_depended_on_by_arr
                        FROM
                            ids_of_descriptors_that_need_upsert
                        WHERE
                            id NOT IN (SELECT id FROM new_depended_on_by_entries)
                )
                ORDER BY
                    id
            )
    SELECT
        crdb_internal.unsafe_upsert_descriptor(
            tables.id,
            crdb_internal.json_to_pb(
                'cockroach.sql.sqlbase.Descriptor',
                json_build_object(
                    'table',
                    json_remove_path(
                        json_set(
                            json_set(
                                tab,
                                ARRAY['dependedOnBy'],
                                new_depended_on_by_arr
                            ),
                            ARRAY['version'],
                            ((tab->>'version')::INT8 + 1)::STRING::JSONB
                        ),
                        ARRAY['modificationTime']
                    )
                )
            ),
            true
        )
    FROM
        tables
        JOIN new_depended_on_by_arrs ON tables.id = new_depended_on_by_arrs.id;

If the query succeeds, that means the repair has succeeded. You can rerun the diagnostic query to confirm this. We encourage users to back up their clusters before undertaking such a repair.

## Impact

This problem affects any cluster in which a materialized view has been unsuccessfully created while running versions v21.2.0 to v21.2.12 or v22.1.0. All objects depended upon by that view would become unusable.

Questions about any technical alert can be directed to our support team.