Materialized view can't be dropped due to missing dependent object

When trying to drop a materialized view, we receive an 'error resolving dependent view ID 7568: relation “[7568}” does not exist. Nowhere can we determine what id 7568 is referring to. Since this object does not exist, it should invalidate the use of the materialized view; however we are still able to select data from it. We would like a method for determining how to resolve the missing relation error so that the view and table the view is based on can be dropped.

Sounds like a bug in dependency tracking of materialized views, potentially related to dropping things. I can help you repair the situation Can you indicate the version? Can you see if the below query returns anything about a job that might help me piece this all back together? Also, was the view definition referring to another view that is now dropped?

SELECT *
  FROM crdb_internal.jobs
 WHERE job_id
       IN (
          SELECT DISTINCT job_id
            FROM (
                  SELECT job_id, unnest(descriptor_ids) AS descriptor_id
                    FROM crdb_internal.jobs
                 )
           WHERE descriptor_id = 7568
        );

For better or for worse, we’ll likely need to do “descriptor surgery” to fix the situation. In order to facilitate that surgery, I’ll need more information. We can exchange information out of band as needed.

Andrew,

We are using version 21.1.6

The jobs query did not return any data.

The materialized view create statement shows 5 different tables, all of which exist and return data. There are no views present in the create statement.

I’m hesitant to perform ‘descriptor surgery’ without a viable CRDB support engineer.

May I ask what experience you have with CRDB ? Are you part of the CRDB support staff?

I’m the tech lead on the sql schema team here at cockroach labs. I agree that descriptor surgery is scary and should be a last resort. Before we do anything, I’ll want to make sure I have a good understanding of what’s going on.

In the meantime, is this blocking anything for you? Can you rename the view to get it out of your way if it is?

I’ll ask the application development team if renaming both the materialized view and possibly a table is doable.

Andrew,

I tried the ALTER TABLE RENAME on the view, it errored out :

[Code: , SQL State: XXUUU] ERROR: referenced table ID 7568: descriptor not found.

Is it possible to schedule a working session to discover the underlying issues.

Apparently, there are other tables/views which have a similar problem in this database.

Yes, my email is ajwerner@cockroachlabs.com, we can coordinate something. In the meantime, can you collect a debug zip, if only for yourself and run cockroach debug doctor zipdir examine <path to extracted zip> on the zip. It’ll have useful information. Alternatively, you can run cockroach debug doctor examine cluster <pgurl> to run it against the cluster.

A debug.zip file was already sent to the lab under ticket 10128. Do you have access to that?

Are you available via Teams meeting at 2:00 pm Eastern ?

We’ll coordinate through the support ticket from here on out.

root@rn000019529:/cockroachdb_backup/DLM/debug

cockroach debug doctor examine zipdir /cockroachdb_backup/DLM/debug

Examining 1165 descriptors and 1185 namespace entries…

Examining 21026 jobs…

No problems found!

This debug was taken on 10/13, a day after the problem was identified.

even though we are trying to drop database, still the getting same error.
any help or suggestion is appreciate.

SQL Error [42P01]: ERROR: error resolving dependent view ID 7568: relation “[7568]” does not exist