> david.g.johns...@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.
