The semantics of the "depends on extension" subprogram attribute
Installing an extension typically brings several subprograms. For example, installing the tablefunc extension brings the normal_rand() function. If, later, you drop the extension, then all the subprograms that it brought are silently dropped as a consequence. You don't need to say cascade to bring this outcome. Occasionally, users enlarge an extension's functionality with their own subprograms. They consider that these, too, are part of the extension and they want, therefore, to have these be silently dropped if the extension is dropped. This is what "depends on extension" achieves.
You can test this easily, if you have a sandbox cluster, by creating a brand-new database and installing, say, the tablefunc extension into it, specifying, for example, "with schema extensions". (You need to do this as a superuser.) Then create a test function, say f(), and a test procedure, say p() in the same database and schema and use "alter... depends on extension..." to make these "part of" tablefunc. Use this query to observe this outcome:
select p.proname::text, e.extname 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 p.pronamespace::regnamespace::text = 'extensions' order by 1, 2, 3;
You'll see this:
proname | extname -------------+----------- connectby | tablefunc connectby | tablefunc connectby | tablefunc connectby | tablefunc crosstab | tablefunc crosstab | tablefunc crosstab | tablefunc crosstab2 | tablefunc crosstab3 | tablefunc crosstab4 | tablefunc f | tablefunc normal_rand | tablefunc p | tablefunc
Then do this:
drop extension tablefunc restrict;
the restrict keyword is used to emphasize the pedagogy. Usually, it prevents the attempt to drop an object when the object has dependent objects. But dropping an extension is a special case. And "alter ... depends on extension..." recruits your user-defined subprograms into the special-case regime. You can confirm the outcome by attempting to execute f() or p(). You'll get the 42883 (undefined_function) error.
'alter ... depends on extension ...' currently draws a warning.
If you use "alter ... depends on extension ...", then you'll get this warning:
0A000: This statement not supported yet Please report the issue on https://github.com/YugaByte/yugabyte-db/issues
But don't create a new issue. Rather, just look at this:
Issue #11523 The "alter function/procedure depends on extension" variant causes a spurious warning but produces the expected result