From: Erik Wienhold <ewie@ewie.name>
To: Kouber Saparev <kouber@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: failed to change schema dependency for type xxx.yyy
Date: Tue, 17 Jan 2023 12:29:11 +0100 (CET)
Message-ID: <946179387.343291.1673954951660@office.mailbox.org> (raw)
In-Reply-To: <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
References: <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
> 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'::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?
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ewie@ewie.name, kouber@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: failed to change schema dependency for type xxx.yyy
In-Reply-To: <946179387.343291.1673954951660@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox