Greetings,
I've just run up against a problem with ALTER LANGUAGE ... OWNER
TO ... wherein the change of ownership does not propagate to a
language's handler and validator functions preventing you from
dropping the role if it created a language. I'm assuming a valid
workaround is manually change the owner of the handler and validator
functions but I'd think that changing a languages owning role should
propagate to any other objects created when the language was created.
Here's my test case. I haven't posted this as an official bug report
as I'm not sure if this should be called a bug or simply not
implemented convenience behavior (i.e. a feature request).
$ psql -U postgres
Password for user postgres:
Null display is "\N".
Timing is on.
Welcome to psql 8.2.7 (server 8.3.1), the PostgreSQL interactive
terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING: You are connected to a server with major version 8.3,
but your psql client is major version 8.2. Some backslash commands,
such as \d, might not work properly.
postgres=# create user foouser superuser;
CREATE ROLE
postgres=# create database foo;
CREATE DATABASE
postgres=# \c foo
You are now connected to database "foo".
foo=# set role foouser;
SET
foo=# create language plpgsql;
CREATE LANGUAGE
foo=# reset role;
RESET
foo=# drop user foouser;
ERROR: role "foouser" cannot be dropped because some objects depend
on it
DETAIL: owner of language plpgsql
owner of function plpgsql_validator(oid)
owner of function plpgsql_call_handler()
foo=# alter language plpgsql owner to postgres;
ALTER LANGUAGE
foo=# drop user foouser;
ERROR: role "foouser" cannot be dropped because some objects depend
on it
DETAIL: owner of function plpgsql_validator(oid)
owner of function plpgsql_call_handler()
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
