Thread: Possible bug with ALTER LANGUAGE ... OWNER TO ...
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
Erik Jones <ejones@engineyard.com> writes:
> 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.
Why? The underlying functions are independent objects, in the general
case.
What you really want for this case is REASSIGN OWNED, I think.
regards, tom lane
On Dec 8, 2008, at 1:42 PM, Tom Lane wrote: > Erik Jones <ejones@engineyard.com> writes: >> 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. > > Why? The underlying functions are independent objects, in the general > case. While I understand what you're saying, in the general case, in this specific case I have a hard time grokking it. I guess I was thinking in terms of a language owning it's handler and validator functions but I now see that dropping a language doesn't also drop the underlying functions, which I also find unintuitive. > What you really want for this case is REASSIGN OWNED, I think. Yeah, that covers my specific use case nicely but for "whoopsie" cases where a language is created with the wrong user by accident it wouldn't really help. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Erik Jones wrote: > > On Dec 8, 2008, at 1:42 PM, Tom Lane wrote: > >> Erik Jones <ejones@engineyard.com> writes: >>> 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. >> >> Why? The underlying functions are independent objects, in the general >> case. > > While I understand what you're saying, in the general case, in this > specific case I have a hard time grokking it. I guess I was thinking in > terms of a language owning it's handler and validator functions but I now > see that dropping a language doesn't also drop the underlying functions, > which I also find unintuitive. Well, since CREATE LANGUAGE creates the functions internally, it does make a certain amount of sense that the functions are also handled internally when you do stuff to the language. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Well, since CREATE LANGUAGE creates the functions internally, it does
> make a certain amount of sense that the functions are also handled
> internally when you do stuff to the language.
It *might* create the functions internally, or it might not. Admittedly
the present behavior is somewhat skewed by historical compatibility
considerations, but as long as the functions are independently creatable
objects I don't think it makes sense to have ALTER LANGUAGE messing with
them.
We'd be heading down a very slippery slope if we did that, too ---
should ALTER AGGREGATE touch the underlying functions? How about ALTER
CONVERSION propagating to the underlying function? Or ALTER TYPE to its
underlying I/O functions? Or ALTER DOMAIN to the underlying type? Etc.
If we did change this, how do we not break pg_dump's ability to
replicate a situation where tbe ownerships had been different?
regards, tom lane
On Dec 9, 2008, at 3:50 PM, Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> Well, since CREATE LANGUAGE creates the functions internally, it does >> make a certain amount of sense that the functions are also handled >> internally when you do stuff to the language. > > It *might* create the functions internally, or it might not. > Admittedly > the present behavior is somewhat skewed by historical compatibility > considerations, but as long as the functions are independently > creatable > objects I don't think it makes sense to have ALTER LANGUAGE messing > with > them. > > We'd be heading down a very slippery slope if we did that, too --- > should ALTER AGGREGATE touch the underlying functions? How about > ALTER > CONVERSION propagating to the underlying function? Or ALTER TYPE to > its > underlying I/O functions? Or ALTER DOMAIN to the underlying type? > Etc. > If we did change this, how do we not break pg_dump's ability to > replicate a situation where tbe ownerships had been different? Only if the commands to create the objects being altered also created the underlying functions. I think the distinction should be that if command actually *creates* other objects beyond the "top level" object created with established relationships via pg_depend then that relationship should be followed by appropriate ALTER statements on the originally created objects. Perhaps OWNED BY would be a good add to ALTER FUNCTION statements to couple them to other objects when appropriate and create statements for other objects that create functions will have that be implicit? After all, the only reason those secondary objects are there is for use by the primarily created object. One pre-existing example is sequence created with the SERIAL keyword in CREATE TABLE/ALTER TABLE statements, they're still independent objects that can be ALTERed directly but a subsequent ALTER on the owning table propagates to the sequence. That being said, I'm satisfied now that I know that it isn't something that's already supposed to work. Just saying that it'd be nice :) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k