Re: ERROR: failed to change schema dependency for type xxx.yyy

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> On 17/01/2023 11:46 CET Kouber Saparev <kouber@xxxxxxxxx> 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






[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux