Re: alter function/procedure depends on extension
| От | Bryn Llewellyn |
|---|---|
| Тема | Re: alter function/procedure depends on extension |
| Дата | |
| Msg-id | 269BD3F1-B652-40E7-BC12-84BAF8955F6F@yugabyte.com обсуждение исходный текст |
| Ответ на | Re: alter function/procedure depends on extension ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: alter function/procedure depends on extension
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления:
Следующее
От: "David G. Johnston"Дата:
Сообщение: Re: alter function/procedure depends on extension