Re: 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(at)tti(dot)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(at)tti(dot)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(at)sss(dot)pgh(dot)pa(dot)us>
To: eKo1 <bernd(at)tti(dot)hn>
Cc: pgsql-bugs(at)postgresql(dot)org; Christopher Kings-Lynne
<chriskl(at)familyhealth(dot)com(dot)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