alter function/procedure depends on extension

On 2/17/22 08:52, Bryn Llewellyn wrote:

**Briefly**

*qu. 2.* Why does "drop extension restrict" quietly succeed—having the
effect of cascade-dropping any subprogram that you've altered to depend
on the extension in question? This is at odds with the documented
meaning of "restrict".

But not the ALTER FUNCTION docs:

https://www.postgresql.org/docs/current/sql-alterfunction.html

"DEPENDS ON EXTENSION extension_name
NO DEPENDS ON EXTENSION extension_name

This form marks the function as dependent on the extension, or no
longer dependent on that extension if NO is specified. A function that's
marked as dependent on an extension is automatically dropped when the
extension is dropped.
"

And from DROP EXTENSION

https://www.postgresql.org/docs/current/sql-dropextension.html

"RESTRICT

Refuse to drop the extension if any objects depend on it (other
than its own member objects and other extensions listed in the same DROP
command). This is the default.
"

The DEPENDS ON EXTENSION turns a function into a member object. The
'...any objects depend on it....' is shown in the EXAMPLES:

"To remove the extension hstore from the current database:

DROP EXTENSION hstore;

This command will fail if any of hstore's objects are in use in the
database, for example if any tables have columns of the hstore type. Add
the CASCADE option to forcibly remove those dependent objects as well.
"

*qu. 3.* I established by experiment that you can repeat "alter

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2022-Feb-17, Bryn Llewellyn wrote:

qu. 1. What is the use-case for "alter function/procedure depends on extension"?

Suppose you have an extension that implements change auditing for
tables, which works by creating triggers on the audited tables. You
enable auditing for specific tables by calling one of the extension's
function; this function creates a trigger on the table.

The design idea was that if you drop the auditing extension, then those
triggers should be gone too.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"I'm always right, but sometimes I'm more right than other times."
(Linus Torvalds)

On Thu, Feb 17, 2022 at 10:04 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/17/22 08:52, Bryn Llewellyn wrote:

**Briefly**

*qu. 2.* Why does "drop extension restrict" quietly succeed—having the
effect of cascade-dropping any subprogram that you've altered to depend
on the extension in question? This is at odds with the documented
meaning of "restrict".

But not the ALTER FUNCTION docs:

https://www.postgresql.org/docs/current/sql-alterfunction.html

"DEPENDS ON EXTENSION extension_name
NO DEPENDS ON EXTENSION extension_name

This form marks the function as dependent on the extension, or no
longer dependent on that extension if NO is specified. A function that's
marked as dependent on an extension is automatically dropped when the
extension is dropped.
"

And from DROP EXTENSION

https://www.postgresql.org/docs/current/sql-dropextension.html

"RESTRICT

Refuse to drop the extension if any objects depend on it (other
than its own member objects and other extensions listed in the same DROP
command). This is the default.
"

The DEPENDS ON EXTENSION turns a function into a member object. The
'...any objects depend on it....' is shown in the EXAMPLES:

This is categorically wrong on its face. I can depend on an extension
without being a member thereof. We already have a command to add a
function to the extension - it's in ALTER EXTENSION. The ALTER FUNCTION
command does not simply duplicate this but rather does it's own thing -
establishes a dependency.

I find the described behavior buggy - the documentation being unclear at
minimum. A function that has added an extension as a dependency should
expect that it MAY be dropped if the extension is dropped. But if the
extension dropping command says "don't do this if any [functions] depend on
me" that safety constraint should apply. The function depends on the
extension and so the dropping of the extension should fail due to that
dependency. The function doesn't get a vote here but the person executing
a command with RESTRICT does.

So the behavior of DROP EXTENSION needs to be fixed and the documentation
for ALTER FUNCTION should read "...as dependent on an extension may be
dropped when the extension is dropped".

David J.

P.S. The absence of a clean multi-extension declaring version of the ALTER
FUNCTION syntax is just a missing feature that someone could very well
spend time adding. Its absence doesn't seem like a big deal though.

On Thu, Feb 17, 2022 at 11:15 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

and the documentation for ALTER FUNCTION should read "...as dependent on
an extension may be dropped when the extension is dropped".

Nevermind this - if the extension is dropped the function will go away.
But the extension can refuse to be dropped due to the existence of this
function. Doesn't seem like we need to spell that out on the ALTER
FUNCTION page.

David J.

david.g.johnston@gmail.com wrote:

david.g.johnston@gmail.com wrote:

…and the documentation for ALTER FUNCTION should read "…as dependent on an extension may be dropped when the extension is dropped".

Nevermind this - if the extension is dropped the function will go away. But the extension can refuse to be dropped due to the existence of this function. Doesn't seem like we need to spell that out on the ALTER FUNCTION page.

I can't follow the logic of the ideas that Adrian's reply to me, David's reply to Adrian, and then David's follow up to his reply jointly express. It seems that this notion about "drop extension", expressed by Adrian, is central:

«
RESTRICT — Refuse to drop the extension if any objects depend on it (other than its own member objects and other extensions listed in the same DROP command). This is the default.
»

(Yes, I see this exact wording in the Current doc.) Adrian goes on to say this:

«
The DEPENDS ON EXTENSION turns a function into a member object
»

I'd expect that this would be stated as part of the "depends on extension" subsection in the "alter function" doc. But I can't find wording to this effect in the Current doc for this statement. Moreover, David argues against this notion.

So I don't know what to believe.

However, the notion seems to me to be back to front. I'd expect that an extension would by definition, depend on its member objects rather than the other way round. (But by all means make this a reciprocal notion.) I tested this in a trashable database by creating the "fuzzystrmatch" extension and then attempting to delete the function "soundex(text)" from the schema where it was installed. The attempt causes the "2BP01" error "cannot drop function s.soundex(text) because extension fuzzystrmatch requires it. You can drop extension fuzzystrmatch instead."

So it seems to me that the wording for RESTRICT in the doc for "drop extension" is wrong because it's the extension that depends on its member object, and not the member object that depends on the extension within which its a member—just as the error text I quoted says.

I *think* that, despite his "Nevermind this", David continues to believe that "drop extension e restrict" should refuse to go ahead if there exists at least one subprogram that has been defined, using "alter function/procedure" to depend on "e". And that this notion has nothing at all to do with the status of a subprogram as a member object of an extension. Am I right, David, and if not, could you please clarify what you do believe?

Finally, in a separate thread, from Álvaro Herrera, he said this:

alvherre@alvh.no-ip.org <mailto:alvherre@alvh.no-ip.org> wrote:

Suppose you have an extension that implements change auditing for tables, which works by creating triggers on the audited tables. You enable auditing for specific tables by calling one of the extension's function; this function creates a trigger on the table. The design idea was that if you drop the auditing extension, then those triggers should be gone too.

Fair enough. But if you generalize this to a requirements statement across more scenarios, then you can't get the behavior that Álvaro says is desirable. I did this test:

1. created table t.
2. created procedure abort(text) that does "assert false" and uses the actual input as the message.
3. created a trigger function to be called from a trigger that fires "after update for each row". If it detects any change, then it calls "abort()".
4. created the trigger on "t".

I confirmed that it worked as intended.

Then I attempted dropping the trigger function without "cascade". This failed with the "2BP01"error "cannot drop function… because other objects depend on it. trigger ... on table s.t depends on function…". So far so good, according to Álvaro's implied requirements statement.

Then (and without dropping the tigger function "cascade") I attempted to drop the "abort()" procedure with no "cascade". This silently succeeded. Then I tried to update a row, This causer the "XX000" error "cache lookup failed for function 1499654". In other words, Álvaro's implied requirements statement cannot be met in this scenario. (But, at least, the insert attempt did fail.)

This makes sense from a microscopic perspective: there are two radically different dependency models at work. The trigger depends statically on its function, and this is recorded in metadata. This is feasible because the "create trigger" SQL expresses this in a way that is fully understood at "create" time. In contrast, the trigger function depends only dynamically (maybe "emergently" is a better word) because the dependency is deduced only when the source code statement in question is evaluated as a SQL expression at runtime. The source text is just a black box at "create" time.

This simply is what it is. PG users have no choice but to understand both kinds of dependency model. But given this, I still see little value in using the "alter function/procedure depends on extension" to change a dynamic dependency into a static dependency because it brings just one nugget of static dependency within a larger universe where the dynamic dependencies paradigm reigns.

On Thu, Feb 17, 2022 at 5:15 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

So I don't know what to believe.

Well, since the documentation supports what I was saying but the
implementation effectively supports what Adrian was saying this confusion
isn't surprising. The status quo is inconsistent and needs to be changed
somehow. I claim the documentation is correct and the RESTRICT clause
non-adherence is a bug to be fixed (arguable whether to back-patch).

However, the notion seems to me to be back to front. I'd expect that an
extension would by definition, depend on its member objects rather than the
other way round.

An extension neither depends on nor is dependent upon its members. The
extension IS the members. Objects external to the extension can use the
members of the extension. But since a function is a black box this doesn't
produce a dependency. Thus a function can be explicitly made to depend on
an extension to put a real relationship into the database for what was
already there in fact.

I *think* that, despite his "Nevermind this", David continues to believe

that "drop extension e restrict" should refuse to go ahead if there exists
at least one subprogram that has been defined, using "alter
function/procedure" to depend on "e".

Yes. The "nevermind this" was strictly for my original proposal to update
the ALTER FUNCTION documentation wording. Nothing else.

Fair enough. But if you generalize this to a requirements statement across
more scenarios, then you can't get the behavior that Álvaro says is
desirable. I did this test:

1. created table t.
2. created procedure abort(text) that does "assert false" and uses the
actual input as the message.
3. created a trigger function to be called from a trigger that fires
"after update for each row". If it detects any change, then it calls
"abort()".
4. created the trigger on "t".

Then (and without dropping the tigger function "cascade") I attempted to
drop the "abort()" procedure with no "cascade". This silently succeeded.
Then I tried to update a row, This causer the "XX000" error "cache lookup
failed for function 1499654". In other words, Álvaro's implied requirements
statement cannot be met in this scenario. (But, at least, the insert
attempt did fail.)

Right, the trigger function itself is a black box - the fact that you got a
"cache lookup" failure instead of a "function not found" failure is largely
immaterial (but may be a bug in its own right). Avoiding this situation
when dealing with extensions is exactly why a function can be declared to
depend on an extension. If the abort() function were part of an extension
and the dependency from the trigger function to the extension were
installed then the attempt to drop the extension would either fail
(restrict) or the trigger function you created would be deleted (cascade).
You would not be left with a trigger function referencing an extension
function that no longer exists.

This simply is what it is. PG users have no choice but to understand both
kinds of dependency model. But given this, I still see little value in
using the "alter function/procedure depends on extension" to change a
dynamic dependency into a static dependency because it brings just one
nugget of static dependency within a larger universe where the dynamic
dependencies paradigm reigns.

Maybe, but the behavior does exist but is bugged in its current state.
Whether it is advisable for users to use this feature is immaterial, the
bug needs to be fixed so that is works as documented for those that choose
to do so.
David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Maybe, but the behavior does exist but is bugged in its current state.
Whether it is advisable for users to use this feature is immaterial, the
bug needs to be fixed so that is works as documented for those that choose
to do so.

There is no bug here; it's operating as designed and documented.
Maybe the docs need some refinement.

The way this is intended to work is that if you have a function that
depends on an extension -- say by using an extension-defined data
type -- then the *default* behavior is that you'd have to say CASCADE
in order to drop the extension. The point of the ALTER DEPENDS ON
functionality is to declare that you don't want that behavior, but
would like the function to go away silently (ie, without CASCADE)
when the extension is dropped. Thus:

test=# create extension cube;
CREATE EXTENSION
test=# create function myfunc(cube) returns int as 'select cube_dim($1)' language sql;
CREATE FUNCTION
test=# drop extension cube;
ERROR: cannot drop extension cube because other objects depend on it
DETAIL: function myfunc(cube) depends on type cube
HINT: Use DROP ... CASCADE to drop the dependent objects too.
test=# drop extension cube cascade;
NOTICE: drop cascades to function myfunc(cube)
DROP EXTENSION

versus

test=# create extension cube;
CREATE EXTENSION
test=# create function myfunc(cube) returns int as 'select cube_dim($1)' language sql;
CREATE FUNCTION
test=# alter function myfunc(cube) depends on extension cube;
ALTER FUNCTION
test=# drop extension cube;
DROP EXTENSION
test=# \df myfunc
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

That's acting as designed and we're not going to change it
for fear of breaking existing use-cases. However, maybe
the use of "automatic" in the documentation isn't sufficiently
clear.

Now, if you have a function that has some internal,
not-visible-to-the-dependency-system extension dependency,
it's true that you don't get any protection against that:

test=# create extension cube;
CREATE EXTENSION
test=# create function myfunc(int) returns int as 'select cube_dim(cube($1,$1))' language sql;
CREATE FUNCTION
test=# drop extension cube;
DROP EXTENSION

However, the function wasn't dropped, so it's hard to argue that the
dependency system didn't do its job. The function is still there,
even though it will fail at execution:

test=# select myfunc(42);
ERROR: function cube(integer, integer) does not exist
LINE 1: select cube_dim(cube($1,$1))
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: select cube_dim(cube($1,$1))
CONTEXT: SQL function "myfunc" during inlining

You just need to re-install the extension, so it's not like you lost much:

test=# create extension cube;
CREATE EXTENSION
test=# select myfunc(42);
myfunc
--------
1
(1 row)

In any case, ALTER DEPENDS ON EXTENSION is not meant to provide
protection against that scenario. The best thing we have for
one-off internal dependencies right now is that new-style SQL
functions do expose them:

test=# drop function myfunc(int);
DROP FUNCTION
test=# create function myfunc(int) returns int begin atomic; select cube_dim(cube($1,$1)); end;
CREATE FUNCTION
test=# drop extension cube;
ERROR: cannot drop extension cube because other objects depend on it
DETAIL: function myfunc(integer) depends on function cube(double precision,double precision)
HINT: Use DROP ... CASCADE to drop the dependent objects too.

Another possible approach is to group such functions into
their own extension, which you could make dependent on cube.

regards, tom lane

On Thu, Feb 17, 2022 at 6:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Maybe, but the behavior does exist but is bugged in its current state.
Whether it is advisable for users to use this feature is immaterial, the
bug needs to be fixed so that is works as documented for those that

choose

to do so.

There is no bug here; it's operating as designed and documented.
Maybe the docs need some refinement.

We should just say that ALTER FUNCTION ALTER DEPENDS ON makes the target
function/procedure a (transient?) member of the extension and it will be
dropped when the extension be dropped.

"This form marks the function as a member of the named extension, or
removes such membership if NO is specified. As a consequence of membership
the restrict clause of the DROP EXTENSION (link) command will ignore this
function (if applicable) and it will always be dropped along with all other
members of the extension."

Different terminology may be better, but we'd want to update DROP EXTENSION
to match. Member is already used there.

(continued ranting below)

The way this is intended to work is that if you have a function that
depends on an extension -- say by using an extension-defined data
type -- then the *default* behavior is that you'd have to say CASCADE
in order to drop the extension. The point of the ALTER DEPENDS ON
functionality is to declare that you don't want that behavior, but
would like the function to go away silently (ie, without CASCADE)
when the extension is dropped.

I don't get why we bothered then. We have to now ignore any possibility of
using RESTRICT here in order to avoid having the DROP EXTENSION user add
CASCADE to their command? At least the "black box" reason added a
capability to the system that wasn't already available.

While writing the above, and considering the stated goal of "avoid having
to specify cascade (i.e., bypass restrict)", I figured I'd test:

test=# create extension cube;
CREATE EXTENSION
test=# create function myfunc(int) returns int as 'select
cube_dim(cube($1,$1))' language sql;
CREATE FUNCTION
test=# alter function myfunc(int) depends on extension cube;
test=# drop extension cube;
DROP EXTENSION
--myfunc(int) no longer exists even though absent the membership it would
have, regardless of whether cascade or restrict was specified. So it's not
just avoiding the specification of CASCADE that is happening here.

That only partially matches the declared use case, you get the free drop
without having to specify cascade but the behavior you were trying to avoid
- the restrict error - never happened anyway.

That's acting as designed and we're not going to change it

for fear of breaking existing use-cases.

Fair.

However, maybe
the use of "automatic" in the documentation isn't sufficiently
clear.

Now, if you have a function that has some internal,
not-visible-to-the-dependency-system extension dependency,
it's true that you don't get any protection against that:

In any case, ALTER DEPENDS ON EXTENSION is not meant to provide

protection against that scenario.

Well, the documentation's use of the word "depend" instead of "member" led
me and the OP to reasonably conclude that it was in fact designed to handle
just this situation. I'd argue the fact that it doesn't is a POLA
violation given that, as I said upthread, we already have a way to add
members to extensions. Though upon further thought that particular form
has permissions issues that this method is capable of bypassing.

The best thing we have for
one-off internal dependencies right now is that new-style SQL
functions do expose them:

I don't really mind that we lack this ability overall - and the fact that
the expected behavior would have only solved it for extensions did seem a
bit odd, but not unreasonable. Especially if, for instance, the extension
itself was creating these functions instead of some
external-to-the-extension code. Solving the black-box problem there made
perfect sense.

David J.

On Thu, Feb 17, 2022 at 7:30 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, Feb 17, 2022 at 6:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Maybe, but the behavior does exist but is bugged in its current state.
Whether it is advisable for users to use this feature is immaterial, the
bug needs to be fixed so that is works as documented for those that

choose

to do so.

There is no bug here; it's operating as designed and documented.
Maybe the docs need some refinement.

The way this is intended to work is that if you have a function that
depends on an extension -- say by using an extension-defined data
type -- then the *default* behavior is that you'd have to say CASCADE
in order to drop the extension. The point of the ALTER DEPENDS ON
functionality is to declare that you don't want that behavior, but
would like the function to go away silently (ie, without CASCADE)
when the extension is dropped.

I don't get why we bothered then. We have to now ignore any possibility of
using RESTRICT here in order to avoid having the DROP EXTENSION user add
CASCADE to their command? At least the "black box" reason added a
capability to the system that wasn't already available.

I think I finally understand this. I'm unclear on why ALTER EXTENSION ADD
couldn't be used here instead of ALTER FUNCTION but for now I'll assume it
boils down to permissions.

The primary goal here is for extensions that create "user space"
functions/procedures to include them as effective members of the
extension. So long as the only dependencies created on the extension are
by the extension itself, and all of them are altered in this manner, then
executing drop extension restrict will just work. If any other
dependencies exist then because we are in restrict mode the drop will
fail. Being forced to add cascade to the command would drop these
uncontrolled routines as well; that is what we are trying to avoid.

So I guess my main complaint with the design is the choice of the word
"depend" since dependencies already have well established behaviors
associated with them that this particular design deviates from. Calling
them "members" has a similar problem (otherwise alter extension would
simply work and solve this problem nicely) but it seems better than
"depends" - though having the description wording use "member" and the
clause say "depends" isn't great either...adding a third category is
out-of-scope at this point.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Feb 17, 2022 at 6:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

There is no bug here; it's operating as designed and documented.
Maybe the docs need some refinement.

We should just say that ALTER FUNCTION ALTER DEPENDS ON makes the target
function/procedure a (transient?) member of the extension and it will be
dropped when the extension be dropped.

It's not a member though; there's a different syntax for that
(ALTER EXTENSION name ADD member_object). The differences are
a bit subtle, but for example making the function an extension
member would change how pg_dump treats it.

I confess not remembering exactly why we thought the auto-drop
behavior was important enough to deserve special infrastructure.
Still, it's there now, and the question of dependencies that the
dependency system can't see is really quite a different thing.

regards, tom lane

On Thu, Feb 17, 2022 at 8:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Feb 17, 2022 at 6:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

There is no bug here; it's operating as designed and documented.
Maybe the docs need some refinement.

We should just say that ALTER FUNCTION ALTER DEPENDS ON makes the target
function/procedure a (transient?) member of the extension and it will be
dropped when the extension be dropped.

It's not a member though; there's a different syntax for that
(ALTER EXTENSION name ADD member_object). The differences are
a bit subtle, but for example making the function an extension
member would change how pg_dump treats it.

So I amend my suggestions to:

https://www.postgresql.org/docs/current/sql-alterfunction.html

"Refuse to drop the extension if any objects depend on it (other than its
own member objects and other extensions listed in the same DROP command).
This is the default."

...(other than its own member objects, other extensions listed in the same
DROP command, and functions and procedures that have been altered to
explicitly depend on those extensions). This is the default.

https://www.postgresql.org/docs/current/sql-alterfunction.html

"This form marks the function as dependent on the extension, or no longer
dependent on that extension if NO is specified. A function that's marked as
dependent on an extension is automatically dropped when the extension is
dropped."

...A function that's marked as dependent on an extension is ignored during
the dependency crawl of a DROP EXTENSION RESTRICT (xref). It can have
multiple extensions it depends on and will be dropped when any one of those
extensions is dropped.

https://www.postgresql.org/docs/current/sql-alterprocedure.html

"The name of the extension that the procedure is to depend on."

Should probably match the sql-alterfunction/html wording substituting in
procedure for function.

David J.

david.g.johnston@gmail.com wrote:

It's not a member though; there's a different syntax for that (ALTER EXTENSION name ADD member_object). The differences are a bit subtle, but for example making the function an extension member would change how pg_dump treats it.

I read, and re-read, the six replies from Tom and David that came in yesterday (17-Feb) after my most recent turn in this thread. Here’s what I’ve concluded:

(1) Tom has ruled that there are no implementation bugs in this space. This means that all the functionality that the tests that I have done (and presumably could do) reflect the intended behavior.

(2) The reasons for deciding on at least some of this behavior are lost in the mists of time. But anyway, no implementation changes will be made.

(3) I, for one, found it very hard to build an overall, predictive, mental model from the documentation. But, then, you’ve seen (somewhat to my embarrassment) that I often have such difficulties. I’m grateful therefore, especially in these work-from-home days, for the answers that my questions to this list have received.

(4) The clue to the whole thing is the semantics of the LoV for "pg_depend. deptype" (see below) — but you all kept this a secret from me!
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Here’s some detailed observations and responses to what Tom and David wrote:

David: « An extension neither depends on nor is dependent upon its members. »

This must be a typo: "depends on" and "is dependent upon" mean the same. I’m guessing that this was meant: « An extension neither depends on nor is *depended* upon by its members. » If so, then it’s wrong. Yes: an extension doesn’t depend on its members. This query (which runs without error) shows that an extension depends upon only the schema nominated by "create extension… with schema…".

select nspname from
pg_namespace
where oid = (
select refobjid
from pg_catalog.pg_depend
where objid = (
select oid
from pg_catalog.pg_extension
where extname::text = $1));

But no: the objects do depend upon the extension that creates them, as this query shows:

prepare dependents_on_extension(text) as
select
p.proname::text as dependent_name,
'subprogram'::text as dependent_type,
e.extname::text as parent_name,
'extension'::text as parent_type,
d.deptype::text as dependecy_type
from
pg_catalog.pg_proc p
inner join
pg_catalog.pg_depend d
on p.oid = d.objid
inner join
pg_catalog.pg_extension e
on d.refobjid = e.oid
where e.extname::text = $1;

I tried it for the "cube" extension and it listed out the same function names that "\df the_schema.*" lists (where "the_schema" is the provided argument for the "with schema" clause of "create extension".

Moreover, it showed that the "dependency_type" is 'e' for the objects that creating the extension brings. And the section "52.18. pg_depend" says this about that value:

«
DEPENDENCY_EXTENSION (e) — The dependent object is a member of the extension that is the referenced object (see pg_extension). The dependent object can be dropped only via DROP EXTENSION on the referenced object. Functionally this dependency type acts the same as an INTERNAL dependency, but it's kept separate for clarity and to simplify pg_dump.
»

and it says this about "internal":

«
DEPENDENCY_INTERNAL (i) — The dependent object was created as part of creation of the referenced object, and is really just a part of its internal implementation. A direct DROP of the dependent object will be disallowed outright (we'll tell the user to issue a DROP against the referenced object, instead). A DROP of the referenced object will result in automatically dropping the dependent object whether CASCADE is specified or not…
»

Seems to me that the account of the "create extension" DDL would benefit from words to this effect and an x-ref to the account of "pg_depend"

In my tests, I installed the "cube" extension in schema "cube". Then I did this:

alter function cube.cube_cmp(cube.cube, cube.cube)depends on extension cube;

And then I repeated the query that I showed above. Now the results included these two rows:

dependent_name | dependent_type | parent_name | parent_type | dependecy_type
--------------------+----------------+-------------+-------------+----------------
cube_cmp | subprogram | cube | extension | e
cube_cmp | subprogram | cube | extension | x

Here's what the doc says about the new result:

«
DEPENDENCY_AUTO_EXTENSION (x) — The dependent object is not a member of the extension that is the referenced object (and so it should not be ignored by pg_dump), but it cannot function without the extension and should be auto-dropped if the extension is. The dependent object may be dropped on its own as well. Functionally this dependency type acts the same as an AUTO dependency, but it's kept separate for clarity and to simplify pg_dump.
»

This is the clue to everything that's been confusing me. But nobody mentioned "pg_depend. deptype"!

After reading and re-reading the accounts for all values of "pg_depend. deptype" I saw, eventually, that this means that "drop extension" will always silently remove all of its dependents of type 'x' whatever you might say about "restrict" (or "cascade").

B.t.w., a query like I show above but for the "parent_type" (user-defined) type shows that a function with an argument whose data type is such a user-defined) type depends on that type with deptype = 'n'.

«
DEPENDENCY_NORMAL (n)
A normal relationship between separately-created objects. The dependent object can be dropped without affecting the referenced object. The referenced object can only be dropped by specifying CASCADE, in which case the dependent object is dropped, too. Example: a table column has a normal dependency on its data type.
»

A slow learner like me would always appreciate a commentary, with self-contained runnable examples along the lines of what I've sketched here, to accompany every maximally terse definitional account. But I appreciate that the PG Doc doesn't aim to do this.

On Fri, Feb 18, 2022 at 6:16 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

(4) The clue to the whole thing is the semantics of the LoV for
"pg_depend. deptype" (see below) — but you all kept this a secret from me!

I didn't even think to look at the system catalogs for guidance in this
kind of thing. The catalogs are not what I consider end-user facing
documentation; I'd want the core documentation (SQL Command Reference and
exposition chapters for different features) to be sufficient for someone to
understand how these things work. Now, I would probably have been a bit
less certain of myself had I gone and looked at the catalogs early on.
Seeing the specification for DEPENDENCY_AUTO (a) would have reset my
internal consistency trigger.

David wrote:

This must be a typo: "depends on" and "is dependent upon" mean the same.
I’m guessing that this was meant: « An extension neither depends on nor is
*depended* upon by its members. » If so, then it’s wrong. Yes: an extension
doesn’t depend on its members. This query (which runs without error) shows
that an extension depends upon only the schema nominated by
"create extension… with schema…".

But no: the objects do depend upon the extension that creates them, as
this query shows:

Yeah, I seem to have had a brain fade there.
David J.

On Thu, Feb 17, 2022 at 9:32 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, Feb 17, 2022 at 8:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Feb 17, 2022 at 6:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

There is no bug here; it's operating as designed and documented.
Maybe the docs need some refinement.

We should just say that ALTER FUNCTION ALTER DEPENDS ON makes the target
function/procedure a (transient?) member of the extension and it will be
dropped when the extension be dropped.

It's not a member though; there's a different syntax for that
(ALTER EXTENSION name ADD member_object). The differences are
a bit subtle, but for example making the function an extension
member would change how pg_dump treats it.

So I amend my suggestions to:

Concretely as attached (and inlined):

commit 18206a1b2c81ae280ad9927decc5a975011ffb80
Author: David G. Johnston <david.g.johnston@gmail.com>
Date: Tue Apr 12 15:30:45 2022 +0000

doc: Elaborate on the interplay between depends on and drop extension

Per discussion on -general the documentation for the
ALTER ROUTINE ... DEPENDS ON EXTENSION and DROP EXTENSION doesn't
clearly indicate that these dependent routines are treated in a
similar manner to the extension's owned objects when it comes to
using RESTRICT mode drop: namely their presence doesn't force
the drop command to abort. Clear that up.

diff --git a/doc/src/sgml/ref/alter_function.sgml
b/doc/src/sgml/ref/alter_function.sgml
index 0ee756a94d..4597b4a4bb 100644
--- a/doc/src/sgml/ref/alter_function.sgml
+++ b/doc/src/sgml/ref/alter_function.sgml
@@ -160,8 +160,10 @@ ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [
<replaceable class="param
      <para>
       This form marks the function as dependent on the extension, or no
longer
       dependent on that extension if <literal>NO</literal> is specified.
-      A function that's marked as dependent on an extension is
automatically
-      dropped when the extension is dropped.
+      A function that's marked as dependent on an extension is skipped
during
+      dependency checking in restrict mode <xref
linkend="sql-dropextension"/>.
+      A function can depend upon multiple extensions, and will be dropped
when
+      any one of those extensions is dropped.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/alter_procedure.sgml
b/doc/src/sgml/ref/alter_procedure.sgml
index 033fda92ee..fe835acccf 100644
--- a/doc/src/sgml/ref/alter_procedure.sgml
+++ b/doc/src/sgml/ref/alter_procedure.sgml
@@ -147,7 +147,12 @@ ALTER PROCEDURE <replaceable>name</replaceable> [ ( [
[ <replaceable class="para
     <term><replaceable
class="parameter">extension_name</replaceable></term>
     <listitem>
      <para>
-      The name of the extension that the procedure is to depend on.
+      This form marks the procedure as dependent on the extension, or no
longer
+      dependent on that extension if <literal>NO</literal> is specified.
+      A procedure that's marked as dependent on an extension is skipped
during
+      dependency checking in restrict mode <xref
linkend="sql-dropextension"/>.
+      A procedure can depend upon multiple extensions, and will be dropped
when
+      any one of those extensions is dropped.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/drop_extension.sgml
b/doc/src/sgml/ref/drop_extension.sgml
index 5e507dec92..ec5f7bc4f7 100644
--- a/doc/src/sgml/ref/drop_extension.sgml
+++ b/doc/src/sgml/ref/drop_extension.sgml
@@ -30,7 +30,9 @@ DROP EXTENSION [ IF EXISTS ] <replaceable
class="parameter">name</replaceable> [

   <para>
    <command>DROP EXTENSION</command> removes extensions from the database.
-   Dropping an extension causes its component objects to be dropped as
well.
+   Dropping an extension causes its component objects, and other explicitly
+   dependent routines (see <xref linkend="sql-alterroutine"/>,
+   the depends on extension action), to be dropped as well.
   </para>

   <para>
@@ -77,9 +79,10 @@ DROP EXTENSION [ IF EXISTS ] <replaceable
class="parameter">name</replaceable> [
     <term><literal>RESTRICT</literal></term>
     <listitem>
      <para>
-      Refuse to drop the extension if any objects depend on it (other than
-      its own member objects and other extensions listed in the same
-      <command>DROP</command> command).  This is the default.
+      For each extension, refuse to drop anything if any objects (other
than the
+      other extensions listed) depend on it.  However, its own member
objects, and routines
+      that are explicitly dependent on this extension, are skipped.
+      This is the default.
      </para>
     </listitem>
    </varlistentry>

"David G. Johnston" <david.g.johnston@gmail.com> writes:

-      A function that's marked as dependent on an extension is automatically
-      dropped when the extension is dropped.
+      A function that's marked as dependent on an extension is skipped during
+      dependency checking in restrict mode <xref linkend="sql-dropextension"/>.

That is absolutely not an improvement. The proposed replacement text
is about as clear as mud.

+      A function can depend upon multiple extensions, and will be dropped when
+      any one of those extensions is dropped.

This addition might be worth making, though.

(likewise for the other instances, of course)

regards, tom lane

On Tue, Apr 12, 2022 at 8:49 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

- A function that's marked as dependent on an extension is

automatically

-      dropped when the extension is dropped.
+      A function that's marked as dependent on an extension is skipped

during

+ dependency checking in restrict mode <xref

linkend="sql-dropextension"/>.

That is absolutely not an improvement. The proposed replacement text
is about as clear as mud.

+ A function that's marked as dependent on an extension is dropped when the
extension is dropped, even if cascade is not specified.

I suppose that is a wordier way to say "automatically" but referencing the
actual command keyword seems beneficial.

David J.

On Tue, Apr 12, 2022 at 8:55 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tue, Apr 12, 2022 at 8:49 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

- A function that's marked as dependent on an extension is

automatically

-      dropped when the extension is dropped.
+      A function that's marked as dependent on an extension is skipped

during

+ dependency checking in restrict mode <xref

linkend="sql-dropextension"/>.

That is absolutely not an improvement. The proposed replacement text
is about as clear as mud.

+ A function that's marked as dependent on an extension is dropped when
the extension is dropped, even if cascade is not specified.

I suppose that is a wordier way to say "automatically" but referencing the
actual command keyword seems beneficial.

I put this change into v0002 and posted it to -hackers as part of a
multi-patch thread.

David J.