Обсуждение: 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
2009/1/19 Igor Katson <descentspb@gmail.com>
you could query the pg_depend system catalog:
http://www.postgresql.org/docs/8.3/static/catalog-pg-depend.html
but regarding recursive queries, you have to wait for 8.4 :)
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 could query the pg_depend system catalog:
http://www.postgresql.org/docs/8.3/static/catalog-pg-depend.html
--
Filip Rembiałkowski
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
In postgresql 8.2 ,how find it?
De: Richard Huxton <dev@archonet.com>
Para: Ivan Sergio Borgonovo <mail@webthatworks.it>
Cc: pgsql-general@postgresql.org
Enviadas: Segunda-feira, 19 de Janeiro de 2009 14:36:46
Assunto: Re: [GENERAL] How can I look at a recursive table dependency tree?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
De: Richard Huxton <dev@archonet.com>
Para: Ivan Sergio Borgonovo <mail@webthatworks.it>
Cc: pgsql-general@postgresql.org
Enviadas: Segunda-feira, 19 de Janeiro de 2009 14:36:46
Assunto: Re: [GENERAL] How can I look at a recursive table dependency tree?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 - Celebridades - Música - Esportes
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