Date: Tue, 17 Jan 2023 12:29:11 +0100 (CET)
From: Erik Wienhold <ewie@ewie.name>
To: Kouber Saparev <kouber@gmail.com>, pgsql-admin@lists.postgresql.org
Message-ID: <946179387.343291.1673954951660@office.mailbox.org>
In-Reply-To: 
 <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
References: 
 <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
Subject: Re: ERROR: failed to change schema dependency for type xxx.yyy
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/946179387.343291.1673954951660%40office.mailbox.org>
Precedence: bulk

> 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 be=
low:
>
> db=3D# 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:

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

> 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.
>
> 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:
>
> db=3D# SELECT deptype, objid::regclass FROM pg_depend WHERE refobjid =3D =
(SELECT oid FROM pg_type WHERE typnamespace =3D 'xxx'::regnamespace AND typ=
name =3D 'yyy');
> =C2=A0deptype | objid
> ---------+------------
> =C2=A0i | 1202633909
> (1 row)
>
> 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 sch=
ema,
> which is quite a blocker in our migration plan.
>
> Any ideas how we could proceed any further? And btw what is the entry abo=
ve:
> objid =3D 1202633909=C2=A0+ deptype =3D i?

That entry is type xxx.yyy[]:

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

--
Erik