Re: Usability tweaks for extension commands

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Another thought is that it'd probably be useful for there to be a
>> "CREATE OR REPLACE EXTENSION" syntax, with the behavior of "install the
>> extension if it's not present, else make sure it's of the specified or
>> default version"; this behavior parallels CREATE OR REPLACE LANGUAGE
>> which is something we've been refining for awhile. I am not however
>> entirely sure what to do with the SCHEMA option if the extension already
>> exists --- we might be able to do SET SCHEMA, but perhaps that's too
>> aggressive.

> By all means, let's learn from our history. +1.

> I would expect CORE to be able to change things in the database, so I
> would vote for doing the SET SCHEMA here if needed. But if we do that,
> then certainly we should also automatically handle upgrades too when
> possible, right? That would be very useful, so still +1.

According to our prior discussions of C.O.R. commands, the general
principle that such a command ought to follow is that upon success,
the object exists with exactly the properties implied by the command's
arguments. So (1) if the extension isn't in the stated or default
schema, we must move it there, or report failure if we can't;
(2) if it's not of the stated or default version, we must update to that
version, or fail if we can't. That seems straightforward enough,
I'm just wondering whether applying that theory is leading to the
right choices here. In particular, the default behavior if you didn't
say "SCHEMA something" would be to automatically move the extension
into whatever random schema happens to be the front of your search_path,
which might well not be what you intended. Maybe it would be safer to
not do a move on the basis of a defaulted schema selection.

Anyway, I think this is all 9.2 material. I brought it up when I was
wondering if there were a chance of making CREATE LANGUAGE translate
into a CREATE EXTENSION operation for 9.1. I've since given that up,
after realizing that we are nowhere near the point where we'd be able
to allow non-superusers to execute CREATE EXTENSION. The permissions
and security implications are too complicated to rush through.

regards, tom lane