Обсуждение: BUG #2310: "ALTER TYPE name OWNER TO new_owner" not working
The following bug has been logged online: Bug reference: 2310 Logged by: eKo1 Email address: bernd@tti.hn PostgreSQL version: 8.1.3 Operating system: Linux Fedora Core 3 Description: "ALTER TYPE name OWNER TO new_owner" not working Details: I have the following function that displays the relation-like objects owned by a user in a given schema: ------------------------------------------------------- create or replace function fn_show_owned(text, text) returns setof text as $$ declare v_user alias for $1; v_schema alias for $2; v_rec record; begin for v_rec in select relname from pg_class, pg_namespace, pg_user where pg_namespace.oid = relnamespace and nspname = v_schema and relowner = usesysid and usename = v_user loop return next v_rec.relname; end loop; return; end; $$ language plpgsql; ------------------------------------------------------- Here is what I did to confirm that "ALTER TYPE name OWNER TO new_owner" is not working: testdb=# select * from fn_show_owned('bernd','public'); fn_show_owned --------------- (0 rows) testdb=# create type MyType as (a int); CREATE TYPE testdb=# select * from fn_show_owned('bernd','public'); fn_show_owned --------------- mytype (1 row) testdb=# alter type MyType owner to wiki; ALTER TYPE testdb=# select * from fn_show_owned('bernd','public'); fn_show_owned --------------- mytype (1 row) testdb=# \c - wiki You are now connected as new user "wiki". testdb=> select * from fn_show_owned('wiki','public'); fn_show_owned --------------- (0 rows) As you can see, changing the owner does nothing. If I do the above for a table, it works fine, so I'm pretty sure this is a bug. I also tested this in 8.1.2 on the same FC3 machine and in 8.1.1 on CentOS 3. I get the same results.
"eKo1" <bernd@tti.hn> writes: > I have the following function that displays the relation-like objects owned > by a user in a given schema: The relevant owner field for a composite type is pg_type.typowner. We don't bother to maintain pg_class.relowner for the subsidiary pg_class entry. regards, tom lane
I wrote: > The relevant owner field for a composite type is pg_type.typowner. > We don't bother to maintain pg_class.relowner for the subsidiary > pg_class entry. Actually, now that I look at it, there is a non-cosmetic issue here: we seem to be creating a dependency link for the pg_class owner field. We have to either not do that, or be willing to fix it during ALTER TYPE OWNER. For instance regression=# create user foo; CREATE ROLE regression=# create user bar; CREATE ROLE regression=# \c - foo You are now connected as new user "foo". regression=> create type mytype as (f1 int); CREATE TYPE regression=> \c - postgres You are now connected as new user "postgres". regression=# drop user foo; ERROR: role "foo" cannot be dropped because some objects depend on it DETAIL: owner of composite type mytype owner of type mytype regression=# alter type mytype owner to bar; ALTER TYPE regression=# drop user foo; ERROR: role "foo" cannot be dropped because some objects depend on it DETAIL: owner of composite type mytype regression=# regards, tom lane
-----Original Message----- From: Tom Lane <tgl@sss.pgh.pa.us> To: eKo1 <bernd@tti.hn> Cc: pgsql-bugs@postgresql.org; Christopher Kings-Lynne <chriskl@familyhealth.com.au> Sent: Thu, 09 Mar 2006 20:10:54 -0500 Subject: Re: [BUGS] BUG #2310: "ALTER TYPE name OWNER TO new_owner" not working I wrote: > The relevant owner field for a composite type is pg_type.typowner. > We don't bother to maintain pg_class.relowner for the subsidiary > pg_class entry. Actually, now that I look at it, there is a non-cosmetic issue here: we seem to be creating a dependency link for the pg_class owner field. We have to either not do that, or be willing to fix it during ALTER TYPE OWNER. For instance regression=# create user foo; CREATE ROLE regression=# create user bar; CREATE ROLE regression=# \c - foo You are now connected as new user "foo". regression=> create type mytype as (f1 int); CREATE TYPE regression=> \c - postgres You are now connected as new user "postgres". regression=# drop user foo; ERROR: role "foo" cannot be dropped because some objects depend on it DETAIL: owner of composite type mytype owner of type mytype regression=# alter type mytype owner to bar; ALTER TYPE regression=# drop user foo; ERROR: role "foo" cannot be dropped because some objects depend on it DETAIL: owner of composite type mytype regression=# regards, tom lane This is exactly what I did that led me to discover this issue. I need to drop a user but I can't because some objects depend on it. The only way around it is to delete the type and everything else that depends on it and recreate it and its dependents as the new owner of the type. What a pain.... Bernd ___________________________________________________ Try the New Netscape Mail Today! Virtually Spam-Free | More Storage | Import Your Contact List http://mail.netscape.com