alter function/procedure depends on extension - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | alter function/procedure depends on extension |
| Date | |
| Msg-id | 7C2D01B4-AD32-48DB-B5FA-6FFBC312ECB2@yugabyte.com Whole thread Raw |
| Responses |
Re: alter function/procedure depends on extension
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: alter function/procedure depends on extension (Alvaro Herrera <alvherre@alvh.no-ip.org>) |
| List | pgsql-general |
*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.
pgsql-general by date: