Thread: alter function/procedure depends on extension
*Briefly*
qu. 1. What is the use-case for "alter function/procedure depends on extension"?
After all, subprograms depend, functionally, on all sorts of things. But (unlike in Oracle Database) there’s (almost) no semantic check at “create” time and therefore nothing like the semantics that ORCL’s "DBA_Dependencies" exposes. Rather, if some semantic dependency parent doesn’t exist, then you might get a run-time error—depending on whether the execution path tries to use it. Moreover, on such an error, the persisted source code remains in place so that if you create the missing dependency parent, then the dependant will then just work. It would seem that this same run-time error model that was designed for schema objects would be a good choice for extensions too. And you get this model, anyway, by default. What is the argument for overriding the default behavior by using "alter function/procedure"?
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".
qu. 3. I established by experiment that you can repeat "alter function/procedure depends on extension" time and again for the same subprogram to make it depend on lots of extensions. And I checked that dropping any one of the extensions nominated this way is enough to cascade-drop the dependent subprogram. Why, then, is there no list syntax like this:
alter function s.f() depends on extension tablefunc, fuzzystrmatch;
Trying it causes the generic 42601 syntax error at the comma.
*More detail*
"Chapter 52. System Catalogs" gave me what I needed to list out what functions depend on what extensions. But the approach that I used was straight Codd-and-Date: an intersection table between the procedures table and the extensions table.
qu. 4. Why the asymmetry of approach with the way that, for example, the many execute-grantees for a procedure are represented as an array, Stonebraker-object-relational-style, in the procedures table? (It's the same, too, for the list of session parameter settings that "alter function/procedure... set…" establishes. (Here, you _can_ set many different parameters with a single "alter".)
Just for completeness, this query (after my setup):
select
p.proname::text,
p.pronamespace::regnamespace::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.proname::text in ('a', 'b', 'c') and
p.pronamespace::regnamespace::text = 's'
order by 1, 2, 3;
p.proname::text,
p.pronamespace::regnamespace::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.proname::text in ('a', 'b', 'c') and
p.pronamespace::regnamespace::text = 's'
order by 1, 2, 3;
produced this result:
proname | pronamespace | extname
---------+--------------+---------------
a | s | tablefunc
b | s | fuzzystrmatch
c | s | fuzzystrmatch
c | s | tablefunc
---------+--------------+---------------
a | s | tablefunc
b | s | fuzzystrmatch
c | s | fuzzystrmatch
c | s | tablefunc
just as I expected.
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 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.
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
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:
"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.
"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.
"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:
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:
and it says this about "internal":
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
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;
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
--------------------+----------------+-------------+-------------+----------------
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>
Attachment
"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.