Обсуждение: ERROR: failed to change schema dependency for type xxx.yyy

> On 17/01/2023 11:46 CET Kouber Saparev <kouber@gmail.com> wrote:
>
> Trying to change the schema of an enumerated type results in the error below:
>
> db=# ALTER TYPE xxx.yyy SET SCHEMA zzz;
> ERROR: failed to change schema dependency for type xxx.yyy

What Postgres version are you on?  I cannot reproduce the error on 14.6:

    CREATE SCHEMA xxx;
    CREATE TYPE xxx.yyy AS ENUM ();
    CREATE SCHEMA zzz;
    ALTER TYPE xxx.yyy SET SCHEMA zzz;

> I was able to track this down to changeDependencyFor() within pg_depend.c, but
> still am not able to understand the reason for this error.
>
> When looking into the pg_depend catalog, originally there were 700 entries.
> After a series of cascading drops of the tables, views and functions involved,
> only one was left:
>
> db=# SELECT deptype, objid::regclass FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_type WHERE typnamespace =
'xxx'::regnamespaceAND typname = 'yyy'); 
>  deptype | objid
> ---------+------------
>  i | 1202633909
> (1 row)
>
> Then I deleted manually from pg_depend this entry, and there were 0 entries
> left, but still the error was persisting. I am not able to change the schema,
> which is quite a blocker in our migration plan.
>
> Any ideas how we could proceed any further? And btw what is the entry above:
> objid = 1202633909 + deptype = i?

That entry is type xxx.yyy[]:

    test=# SELECT classid::regclass, objid::regtype, refclassid::regclass, refobjid::regtype FROM pg_depend WHERE
refobjid= (SELECT oid FROM pg_type WHERE typnamespace = 'xxx'::regnamespace AND typname = 'yyy'); 
     classid |   objid   | refclassid | refobjid
    ---------+-----------+------------+----------
     pg_type | xxx.yyy[] | pg_type    | xxx.yyy
    (1 row)

--
Erik

On Tue, 2023-01-17 at 12:46 +0200, Kouber Saparev wrote:
> Trying to change the schema of an enumerated type results in the error below:
>
> db=# ALTER TYPE xxx.yyy SET SCHEMA zzz;
> ERROR:  failed to change schema dependency for type xxx.yyy
>
> I was able to track this down to changeDependencyFor() within pg_depend.c,
> but still am not able to understand the reason for this error.
>
> When looking into the pg_depend catalog, originally there were 700 entries.
> After a series of cascading drops of the tables, views and functions involved,
> only one was left:
>
> db=# SELECT deptype, objid::regclass FROM pg_depend WHERE refobjid =
>      (SELECT oid FROM pg_type WHERE typnamespace = 'xxx'::regnamespace AND typname = 'yyy');
>  deptype |   objid
> ---------+------------
>  i       | 1202633909
> (1 row)
>
> Then I deleted manually from pg_depend this entry, and there were 0 entries left,
> but still the error was persisting. I am not able to change the schema,
> which is quite a blocker in our migration plan.
>
> Any ideas how we could proceed any further?
> And btw what is the entry above: objid = 1202633909 + deptype = i?

There was probably a dependency *missing*: you get this error message if
the database wants to delete the dependency on the old schema and add
one on the new schema, but it cannot find the former.

This is data corruption, but considering the way you delete catalog entries,
I am not surprised.

The missing entry should be like this:

  INSERT INTO pg_depend
     (classid, objid, objsubid,
      refclassid, refobjid, refobjsubid,
      deptype)
  VALUES
     ('pg_type'::regclass, 'xxx.yyy'::regtype, 0,
      'pg_namespace'::regclass, 'xxx'::regnamespace, 0,
      'n');

That dependency prevents users from dropping the schema while the type
is still in it.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Tue, 2023-01-17 at 12:46 +0200, Kouber Saparev wrote:
>> Any ideas how we could proceed any further?
>> And btw what is the entry above: objid = 1202633909 + deptype = i?

> There was probably a dependency *missing*: you get this error message if
> the database wants to delete the dependency on the old schema and add
> one on the new schema, but it cannot find the former.
> This is data corruption, but considering the way you delete catalog entries,
> I am not surprised.

Indeed.  Personally, when I want to look into pg_depend, I invariably
use pg_describe_object() to make sense of the entries.

regression=# create type foo as enum ('a','b');
CREATE TYPE
regression=# create schema bar;
CREATE SCHEMA
regression=# alter type public.foo set schema bar;
ALTER TYPE
regression=# create schema baz;
CREATE SCHEMA
regression=# alter type bar.foo set schema baz;
ALTER TYPE

regression=# select pg_describe_object(classid, objid, objsubid) as obj, pg_describe_object(refclassid, refobjid,
refobjsubid)as ref, deptype from pg_depend where refobjid = 'baz.foo'::regtype; 
      obj       |     ref      | deptype
----------------+--------------+---------
 type baz.foo[] | type baz.foo | i
(1 row)

regression=# select pg_describe_object(classid, objid, objsubid) as obj, pg_describe_object(refclassid, refobjid,
refobjsubid)as ref, deptype from pg_depend where objid = 'baz.foo'::regtype; 
     obj      |    ref     | deptype
--------------+------------+---------
 type baz.foo | schema baz | n
(1 row)

If you'd done it like that, you would probably have figured out fairly
quickly that you were looking at the wrong end of the dependency
relationships.  See also

https://www.postgresql.org/docs/current/catalog-pg-depend.html

            regards, tom lane