Thomas Kellerer <shammat@xxxxxxx> writes:
> I would like to extract the dependency between materialized views.
> e.g. in the following situation:
> create materialized view mv1 as select ....;
> create materialized view mv2 as select ... from mv1, ...;
> I would like to know that mv2 depends on mv1.
> I assumed this could be done through pg_depend, but the only dependency I see there for the matviews is the one for the namespace.
Most of the interesting dependencies for a view or matview are actually
held by its ON SELECT rule. For example:
regression=# create materialized view mv1 as select * from int8_tbl;
SELECT 5
regression=# create materialized view mv2 as select * from mv1;
SELECT 5
regression=# select 'mv1'::regclass::oid;
oid
-------
58550
(1 row)
regression=# select * from pg_depend where objid >= 58550 or refobjid >= 58550;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
1247 | 58552 | 0 | 1259 | 58550 | 0 | i
1247 | 58551 | 0 | 1247 | 58552 | 0 | i
1259 | 58550 | 0 | 2615 | 2200 | 0 | n
2618 | 58553 | 0 | 1259 | 58550 | 0 | i
2618 | 58553 | 0 | 1259 | 58550 | 0 | n
2618 | 58553 | 0 | 1259 | 37540 | 1 | n
2618 | 58553 | 0 | 1259 | 37540 | 2 | n
1247 | 58556 | 0 | 1259 | 58554 | 0 | i
1247 | 58555 | 0 | 1247 | 58556 | 0 | i
1259 | 58554 | 0 | 2615 | 2200 | 0 | n
2618 | 58557 | 0 | 1259 | 58554 | 0 | i
2618 | 58557 | 0 | 1259 | 58554 | 0 | n
2618 | 58557 | 0 | 1259 | 58550 | 1 | n
2618 | 58557 | 0 | 1259 | 58550 | 2 | n
(14 rows)
or more readably,
regression=# select pg_describe_object(classid,objid,objsubid) as obj, pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from pg_depend where objid >= 58550 or refobjid >= 58550;
obj | ref | deptype
---------------------------------------+------------------------------------+---------
type mv1 | materialized view mv1 | i
type mv1[] | type mv1 | i
materialized view mv1 | schema public | n
rule _RETURN on materialized view mv1 | materialized view mv1 | i
rule _RETURN on materialized view mv1 | materialized view mv1 | n
rule _RETURN on materialized view mv1 | column q1 of table int8_tbl | n <<<<<
rule _RETURN on materialized view mv1 | column q2 of table int8_tbl | n <<<<<
type mv2 | materialized view mv2 | i
type mv2[] | type mv2 | i
materialized view mv2 | schema public | n
rule _RETURN on materialized view mv2 | materialized view mv2 | i
rule _RETURN on materialized view mv2 | materialized view mv2 | n
rule _RETURN on materialized view mv2 | column q1 of materialized view mv1 | n <<<<<
rule _RETURN on materialized view mv2 | column q2 of materialized view mv1 | n <<<<<
(14 rows)
where I marked the actually-interesting dependencies with <<<<<.
regards, tom lane
