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
