From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Kouber Saparev <kouber@gmail.com>, pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: failed to change schema dependency for type xxx.yyy
In-reply-to: <4f49be99291439cfd0b04ec5c2c6f3ece6ed731a.camel@cybertec.at>
References: 
 <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
 <4f49be99291439cfd0b04ec5c2c6f3ece6ed731a.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Tue, 17 Jan 2023 15:58:21 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2965713.1673970387.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 17 Jan 2023 10:46:27 -0500
Message-ID: <2965714.1673970387@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2965714.1673970387%40sss.pgh.pa.us>
Precedence: bulk

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 =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 entr=
ies,
> 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=3D# create type foo as enum ('a','b');
CREATE TYPE
regression=3D# create schema bar;
CREATE SCHEMA
regression=3D# alter type public.foo set schema bar;
ALTER TYPE
regression=3D# create schema baz;
CREATE SCHEMA
regression=3D# alter type bar.foo set schema baz;
ALTER TYPE

regression=3D# select pg_describe_object(classid, objid, objsubid) as obj,=
 pg_describe_object(refclassid, refobjid, refobjsubid) as ref, deptype fro=
m pg_depend where refobjid =3D 'baz.foo'::regtype;
      obj       |     ref      | deptype =

----------------+--------------+---------
 type baz.foo[] | type baz.foo | i
(1 row)

regression=3D# select pg_describe_object(classid, objid, objsubid) as obj,=
 pg_describe_object(refclassid, refobjid, refobjsubid) as ref, deptype fro=
m pg_depend where objid =3D '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