Message-ID: <4f49be99291439cfd0b04ec5c2c6f3ece6ed731a.camel@cybertec.at>
Subject: Re: ERROR: failed to change schema dependency for type xxx.yyy
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kouber Saparev <kouber@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Tue, 17 Jan 2023 15:58:21 +0100
In-Reply-To: 
 <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
References: 
	<CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/4f49be99291439cfd0b04ec5c2c6f3ece6ed731a.camel%40cybertec.at>
Precedence: bulk

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 be=
low:
>=20
> db=3D# ALTER TYPE xxx.yyy SET SCHEMA zzz;
> ERROR: =C2=A0failed to change schema dependency for type xxx.yyy
>=20
> I was able to track this down to=C2=A0changeDependencyFor() within pg_dep=
end.c,
> but still am not able to understand the reason for this error.
>=20
> When looking into the pg_depend catalog, originally there were 700 entrie=
s.
> After a series of cascading drops of the tables, views and functions invo=
lved,
> only one was left:
>=20
> db=3D# SELECT deptype, objid::regclass FROM pg_depend WHERE refobjid =3D
>      (SELECT oid FROM pg_type WHERE typnamespace =3D 'xxx'::regnamespace =
AND typname =3D 'yyy');
> =C2=A0deptype | =C2=A0 objid
> ---------+------------
> =C2=A0i =C2=A0 =C2=A0 =C2=A0 | 1202633909
> (1 row)
>=20
> Then I deleted manually from pg_depend this entry, and there were 0 entri=
es left,
> but still the error was persisting. I am not able to change the schema,
> which is quite a blocker in our migration plan.
>=20
> Any ideas how we could proceed any further?
> And btw what is the entry above: objid =3D 1202633909=C2=A0+ deptype =3D =
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