Re: alter function/procedure depends on extension - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: alter function/procedure depends on extension |
| Date | |
| Msg-id | BEFD0F38-FBB4-45DB-94B4-84B7C948D1A0@yugabyte.com Whole thread Raw |
| In response to | Re: alter function/procedure depends on extension ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: alter function/procedure depends on extension
|
| List | pgsql-general |
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.
pgsql-general by date: