From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Kouber Saparev <kouber@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: failed to change schema dependency for type xxx.yyy
Date: Tue, 17 Jan 2023 10:46:27 -0500
Message-ID: <2965714.1673970387@sss.pgh.pa.us> (raw)
In-Reply-To: <4f49be99291439cfd0b04ec5c2c6f3ece6ed731a.camel@cybertec.at>
References: <CAN4RuQv5PddHd9WQEewy5LAdVY5iTAs2WpuUB_TGPWFfAjdCdQ@mail.gmail.com>
<4f49be99291439cfd0b04ec5c2c6f3ece6ed731a.camel@cybertec.at>
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
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: tgl@sss.pgh.pa.us, 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: <2965714.1673970387@sss.pgh.pa.us>
* 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