pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: failed to change schema dependency for type xxx.yyy
4+ messages / 4 participants
[nested] [flat]
* ERROR: failed to change schema dependency for type xxx.yyy
@ 2023-01-17 10:46 Kouber Saparev <kouber@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Kouber Saparev @ 2023-01-17 10:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
Regards,
--
Kouber Saparev
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: failed to change schema dependency for type xxx.yyy
@ 2023-01-17 11:29 Erik Wienhold <ewie@ewie.name>
parent: Kouber Saparev <kouber@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Erik Wienhold @ 2023-01-17 11:29 UTC (permalink / raw)
To: Kouber Saparev <kouber@gmail.com>; pgsql-admin@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: failed to change schema dependency for type xxx.yyy
@ 2023-01-17 14:58 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Kouber Saparev <kouber@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2023-01-17 14:58 UTC (permalink / raw)
To: Kouber Saparev <kouber@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: failed to change schema dependency for type xxx.yyy
@ 2023-01-17 15:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-01-17 15:46 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Kouber Saparev <kouber@gmail.com>; pgsql-admin@lists.postgresql.org
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 = 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.
Indeed. Personally, when I want to look into pg_depend, I invariably
use pg_describe_object() to make sense of the entries.
regression=# create type foo as enum ('a','b');
CREATE TYPE
regression=# create schema bar;
CREATE SCHEMA
regression=# alter type public.foo set schema bar;
ALTER TYPE
regression=# create schema baz;
CREATE SCHEMA
regression=# alter type bar.foo set schema baz;
ALTER TYPE
regression=# select pg_describe_object(classid, objid, objsubid) as obj, pg_describe_object(refclassid, refobjid, refobjsubid) as ref, deptype from pg_depend where refobjid = 'baz.foo'::regtype;
obj | ref | deptype
----------------+--------------+---------
type baz.foo[] | type baz.foo | i
(1 row)
regression=# select pg_describe_object(classid, objid, objsubid) as obj, pg_describe_object(refclassid, refobjid, refobjsubid) as ref, deptype from pg_depend where objid = '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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-01-17 15:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-17 10:46 ERROR: failed to change schema dependency for type xxx.yyy Kouber Saparev <kouber@gmail.com>
2023-01-17 11:29 ` Erik Wienhold <ewie@ewie.name>
2023-01-17 14:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-01-17 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
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