Thread: How can I look at a recursive table dependency tree?

I want to DROP CASCADE a table, but I am afraid that amoung numerous
recursive dependencies there will be smth, that I don't want to drop.

Is there a way to watch all dependencies recursively without doing a drop?

Igor Katson wrote:
> I want to DROP CASCADE a table, but I am afraid that amoung numerous
> recursive dependencies there will be smth, that I don't want to drop.
>
> Is there a way to watch all dependencies recursively without doing a drop?

BEGIN;
DROP CASCADE...
-- check things
ROLLBACK;

--
  Richard Huxton
  Archonet Ltd

On Mon, 19 Jan 2009 14:19:51 +0000
Richard Huxton <dev@archonet.com> wrote:

> Igor Katson wrote:
> > I want to DROP CASCADE a table, but I am afraid that amoung
> > numerous recursive dependencies there will be smth, that I don't
> > want to drop.
> >
> > Is there a way to watch all dependencies recursively without
> > doing a drop?

> BEGIN;
> DROP CASCADE...
> -- check things
> ROLLBACK;

Isn't it going to be a pretty expensive way to see?

Is the default log level enough to take note of the things that will
be touched? (including eg. sequences, indexes etc...)?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

In response to Igor Katson :
> I want to DROP CASCADE a table, but I am afraid that amoung numerous
> recursive dependencies there will be smth, that I don't want to drop.
>
> Is there a way to watch all dependencies recursively without doing a drop?

You can walk through pg_depend.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Ivan Sergio Borgonovo wrote:
> On Mon, 19 Jan 2009 14:19:51 +0000
> Richard Huxton <dev@archonet.com> wrote:
>
>> Igor Katson wrote:
>>> Is there a way to watch all dependencies recursively without
>>> doing a drop?
>
>> BEGIN;
>> DROP CASCADE...
>> -- check things
>> ROLLBACK;
>
> Isn't it going to be a pretty expensive way to see?

Not necessarily - you're not likely to have a lot of concurrency on a
backup database. And we are all testing this sort of stuff on a backup
database, aren't we?

> Is the default log level enough to take note of the things that will
> be touched? (including eg. sequences, indexes etc...)?

I don't think so. You could pull the information from pg_depend
(http://www.postgresql.org/docs/8.3/static/catalog-pg-depend.html) which
is what the DROP will be doing.

--
  Richard Huxton
  Archonet Ltd

On Mon, 19 Jan 2009 14:41:12 +0000
Richard Huxton <dev@archonet.com> wrote:

> Ivan Sergio Borgonovo wrote:
> > On Mon, 19 Jan 2009 14:19:51 +0000
> > Richard Huxton <dev@archonet.com> wrote:
> >
> >> Igor Katson wrote:
> >>> Is there a way to watch all dependencies recursively without
> >>> doing a drop?
> >
> >> BEGIN;
> >> DROP CASCADE...
> >> -- check things
> >> ROLLBACK;
> >
> > Isn't it going to be a pretty expensive way to see?

> Not necessarily - you're not likely to have a lot of concurrency
> on a backup database. And we are all testing this sort of stuff on
> a backup database, aren't we?

Isn't it going to be expensive even if there is no concurrency?

mvcc should be pretty efficient to rollback transactions but... well
it should have a cost anyway... and you add deleting to rolling
back, not just traversing some schema somehow.

Surely recursively traversing a schema may be expensive in termos of
programming time if there is no pre-build function.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Ivan Sergio Borgonovo wrote:
> On Mon, 19 Jan 2009 14:41:12 +0000
> Richard Huxton <dev@archonet.com> wrote:
>
>> Ivan Sergio Borgonovo wrote:
>>> On Mon, 19 Jan 2009 14:19:51 +0000
>>> Richard Huxton <dev@archonet.com> wrote:
>>>
>>>> Igor Katson wrote:
>>>>> Is there a way to watch all dependencies recursively without
>>>>> doing a drop?
>>>> BEGIN;
>>>> DROP CASCADE...
>>>> -- check things
>>>> ROLLBACK;
>>> Isn't it going to be a pretty expensive way to see?
>
>> Not necessarily - you're not likely to have a lot of concurrency
>> on a backup database. And we are all testing this sort of stuff on
>> a backup database, aren't we?
>
> Isn't it going to be expensive even if there is no concurrency?

Not particularly. If you DELETE a lot of rows that can be expensive, but
dropping a table doesn't need to track each record individually.

> mvcc should be pretty efficient to rollback transactions but... well
> it should have a cost anyway... and you add deleting to rolling
> back, not just traversing some schema somehow.

No deletion, just removes the table (and its indexes) from catalogues
and deletes the relevant file(s) on commit.

> Surely recursively traversing a schema may be expensive in termos of
> programming time if there is no pre-build function.

Well, it shouldn't take more than an hour or so to write and test a
function. Never done so myself, since I tend to know what my schemas
look like.

--
  Richard Huxton
  Archonet Ltd

Richard Huxton wrote:
> Igor Katson wrote:
>
>> I want to DROP CASCADE a table, but I am afraid that amoung numerous
>> recursive dependencies there will be smth, that I don't want to drop.
>>
>> Is there a way to watch all dependencies recursively without doing a drop?
>>
>
> BEGIN;
> DROP CASCADE...
> -- check things
> ROLLBACK;
>
>
Thanks everyone, who responded. DROP TABLE while in transaction (if you
just want to look at the cascading drops) is really fast and safe.

A. Kretschmer wrote:
> In response to Igor Katson :
>> I want to DROP CASCADE a table, but I am afraid that amoung numerous
>> recursive dependencies there will be smth, that I don't want to drop.
>>
>> Is there a way to watch all dependencies recursively without doing a drop?
>
> You can walk through pg_depend.

Is it possible to find all view names depend on one table?

For example,

create table t1;
create view v1 as select * from t1;
create view v2 as select ... from v1;

To get all dependencies based on t1:

returns

t1   | v1
t1   | v2


--
Lu Ying

On Fri, May 29, 2009 at 5:10 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
> Is it possible to find all view names depend on one table?

With 8.4 you can use a query like this (change 'messages' to your table name)

This query could stand to be polished a bit though...

WITH RECURSIVE tree AS (
  SELECT 'messages'::regclass::text AS tree,
  0 AS level,
  'pg_class'::regclass AS classid,
  'messages'::regclass AS objid
 UNION ALL
  SELECT tree || ' <-- ' || get_obj_description(pg_depend.classid,
pg_depend.objid),
  level+1,
  pg_depend.classid,
  pg_depend.objid
 FROM tree
 JOIN pg_depend ON ( tree.classid = pg_depend.refclassid
                                  AND tree.objid = pg_depend.refobjid)
)
SELECT tree.tree
FROM tree
WHERE level < 10

--
greg

Greg Stark <stark@enterprisedb.com> writes:
> On Fri, May 29, 2009 at 5:10 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
>> Is it possible to find all view names depend on one table?

> With 8.4 you can use a query like this (change 'messages' to your table name)

In earlier versions the usual trick is

    BEGIN;
    DROP VIEW whatever RESTRICT;
    ... read the error message ...
    ROLLBACK;

            regards, tom lane