On Tue, Mar 23, 2021, at 21:39, Alvaro Herrera wrote:
>I wonder if these views should be defined on top of pg_shdepend instead
>of querying every single catalog. That would make for much shorter
>queries.
+1
pg_shdepend doesn't contain the aclitem info though,
so it won't work for pg_permissions if we want to expose
privilege_type, is_grantable and grantor.
pg_shdepend should work fine for pg_ownerships though.
The semantics will not be entirely the same,
since internal objects are not tracked in pg_shdepend,
but I think this is an improvement.
Example:
create role baz;
create type foobar as ( foo int, bar boolean );
alter type foobar owner to baz;
-- UNION ALL variant:
select * from pg_ownerships where owner = 'baz'::regrole;
classid | objid | objsubid | owner | type | schema | name | identity
----------+--------+----------+-------+----------------+--------+---------+-----------------
pg_class | 407858 | 0 | baz | composite type | public | foobar | public.foobar
pg_type | 407860 | 0 | baz | type | public | foobar | public.foobar
pg_type | 407859 | 0 | baz | type | public | _foobar | public.foobar[]
(3 rows)
-- pg_shdepend variant:
select * from pg_ownerships where owner = 'baz'::regrole;
classid | objid | objsubid | owner | type | schema | name | identity
---------+--------+----------+-------+------+--------+--------+---------------
1247 | 407860 | 0 | baz | type | public | foobar | public.foobar
(1 row)
I'll update the patch.
/Joel
