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>
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
but regarding recursive queries, you have to wait for 8.4 :)
--
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
Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com
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