"create function... depends on extension..." not supported. Why?

On Tue, Apr 26, 2022 at 5:22 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

Note: I’m asking because the answer to the question “Why isn’t X
supported?” is always useful. For example, if supporting it would imply a
logical impossibility that I’m too slow to spot, it helps me when someone
explains what I failed to realize. Equally, it helps me to know when the
answer is “It’s just a historical accident. It could have been supported.
But, now that it isn’t, it doesn’t seem worth the effort to bridge that
gap” because this shows me that my existing mental model is sound.

Here is the commit that brought the feature into existence (it includes a
link to the archives for discussion from which you can read or infer
things).

https://github.com/postgres/postgres/commit/f2fcad27d59c8e5c48f8fa0a96c8355e40f24273

David J.

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

Note: I’m asking because the answer to the question “Why isn’t X supported?” is always useful. For example, if supporting it would imply a logical impossibility that I’m too slow to spot, it helps me when someone explains what I failed to realize. Equally, it helps me to know when the answer is “It’s just a historical accident. It could have been supported. But, now that it isn’t, it doesn’t seem worth the effort to bridge that gap” because this shows me that my existing mental model is sound.

Here is the commit that brought the feature into existence (it includes a link to the archives for discussion from which you can read or infer things).

https://github.com/postgres/postgres/commit/f2fcad27d59c8e5c48f8fa0a96c8355e40f24273

The discussion has diverging threads and very many turns. I think that I managed to skim through the entire tree. As I read it, the discussion was entirely about the semantics of the proposed dependency of a function (or procedure) upon an extension. The idea to establish such a dependency using “alter function” came up quite early in the discussion. It seems that establishing it at “create function” time was never considered.

Unless anybody corrects me, I’ll conclude that it’s perfectly feasible to establish the dependency at “create function” time. This would meet an obvious niceness goal (symmetry and guessability). It would also increase the possibility for component rule re-use in the presentation of the syntax rules. But I don’t s’pose that the effort of changing anything here would be cost-effective.

Bryn Llewellyn <bryn@yugabyte.com> writes:

The discussion has diverging threads and very many turns. I think that I managed to skim through the entire tree. As I read it, the discussion was entirely about the semantics of the proposed dependency of a function (or procedure) upon an extension. The idea to establish such a dependency using “alter function” came up quite early in the discussion. It seems that establishing it at “create function” time was never considered.

Probably not. Just for the record, it's not true that CREATE and ALTER
always have the same set of options. An obvious counterexample is that
you can't set the owner to someone different from yourself during CREATE.
There may be others.

I suppose that "DEPENDS ON EXTENSION" was modeled after the commands
to control extension membership, which likewise exist only in ALTER
form because CREATE's behavior for that is hard-wired. If you wanted
to hand-wave a lot, you could maybe claim that ownership and extension
membership/dependency are similar kinds of relationships and so it
makes sense that the command structures for manipulating them are
similar. But TBH that would probably be reverse-engineering an
explanation. I think that "we didn't bother" is more nearly the
situation.

regards, tom lane

tgl@sss.pgh.pa.us wrote:

bryn@yugabyte.com wrote:

The discussion has diverging threads and very many turns. I think that I managed to skim through the entire tree. As I read it, the discussion was entirely about the semantics of the proposed dependency of a function (or procedure) upon an extension. The idea to establish such a dependency using “alter function” came up quite early in the discussion. It seems that establishing it at “create function” time was never considered.

Probably not. Just for the record, it's not true that CREATE and ALTER always have the same set of options. An obvious counterexample is that you can't set the owner to someone different from yourself during CREATE. There may be others.

I suppose that "DEPENDS ON EXTENSION" was modeled after the commands to control extension membership, which likewise exist only in ALTER form because CREATE's behavior for that is hard-wired. If you wanted to hand-wave a lot, you could maybe claim that ownership and extension membership/dependency are similar kinds of relationships and so it makes sense that the command structures for manipulating them are similar. But TBH that would probably be reverse-engineering an explanation. I think that "we didn't bother" is more nearly the situation.

Thanks, Tom. Just as I’d hoped, I found your “we didn’t bother” reply very helpful. I take your point about the inevitability of some differences between what “create function” and “alter function” can express.

B.t.w., in Oracle Database, you can create a schema object with any owner as long as you have the object-type-specific “ANY” privilege. (You need an ordinary object-type-specific privilege just to create objects that you own yourself.) The PG model is as different from the Oracle model as it could be in the general area of creating, altering, and dropping schema objects. I see that “alter” to change the owner only after the fact, requiring as it does a superuser, is [almost] a forced choice in PG.

It would seem, though, that syntax could be invented to allow a superuser to create an object of any type with any owner. But I s’pose that the usability benefit that this would bring would be marginal and it might even tempt bad practices.

On 2022-Apr-26, Tom Lane wrote:

I suppose that "DEPENDS ON EXTENSION" was modeled after the commands
to control extension membership, which likewise exist only in ALTER
form because CREATE's behavior for that is hard-wired. If you wanted
to hand-wave a lot, you could maybe claim that ownership and extension
membership/dependency are similar kinds of relationships and so it
makes sense that the command structures for manipulating them are
similar. But TBH that would probably be reverse-engineering an
explanation. I think that "we didn't bother" is more nearly the
situation.

IIRC Abhijit and I discussed this point offline, and our conclusion was
that DEPENDS ON EXTENSION was not of general enough usefulness to
warrant support for it in CREATE FUNCTION -- keeping in mind that we
would need support for it in CREATE INDEX, CREATE TRIGGER, CREATE
MATERIALIZED VIEW as well.

It's probably not that much code. The effort of writing the code wasn't
the barrier we were optimizing for, but rather for getting the whole
*idea* accepted.

If this feature is suddenly so useful as to *require* support in the
various CREATE commands, that is most satisfying to me and I volunteer
to reviewing patches that implement it.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

IIRC Abhijit and I discussed this point offline, and our conclusion was
that DEPENDS ON EXTENSION was not of general enough usefulness to
warrant support for it in CREATE FUNCTION -- keeping in mind that we
would need support for it in CREATE INDEX, CREATE TRIGGER, CREATE
MATERIALIZED VIEW as well.

Yeah. Quite aside from the code bloat and grammar bloat, my main
fear about that would be the assumption that we could wedge a consistent
syntax for this into all those different sorts of CREATE commands.
At the very least we'd probably find ourselves having to make those
keywords more reserved than they are now.

regards, tom lane