Thread: Dependencies of Matviews?

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.

Clearly this information is stored somehow, as a DROP MATERIALIZED VIEW complains about being referenced by other
matviews.

Am I missing something in pg_depend or do I need to look in a different system table?

Regards
Thomas

Thomas Kellerer <shammat@gmx.net> 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
thenamespace. 

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

Tom Lane schrieb am 23.06.2020 um 23:25:
>> 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, ...;
> 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;

Ah, great.
That's what I was looking for, thanks

Thomas