david.g.johnston@xxxxxxxxx wrote: 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:
and it says this about "internal":
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:
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'.
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. |
