Technical Advisory 82079

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;

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;