From: Laurenz Albe <laurenz.albe@cybertec.at>
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 15:58:21 +0100
Message-ID: <4f49be99291439cfd0b04ec5c2c6f3ece6ed731a.camel@cybertec.at> (raw)
In-Reply-To: <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
References: <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
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
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: laurenz.albe@cybertec.at, 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>
* 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