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.
Was this helpful?
