Re: About dependency reports in DROP RESTRICT - Mailing list pgsql-hackers

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Currently, if you do DROP something RESTRICT where there are multiple
> levels of dependencies on the "something", you get reports that might
> look about like this:
>
> NOTICE:  x depends on something
...
> So what I'd like to do about it is just use the CASCADE style all the
> time.  Thoughts?

Well personally I always react to the notices by adding the CASCADE token but
that's because I'm just testing stuff. If I was working with a real database I
would probably be quite likely to be looking for the minimal fix to break the
dependency chain.

So for example in a situation like this:

postgres=# create function a(text) returns text as 'select $1' language sql;

CREATE FUNCTION
postgres=# select a('foo'); a  
-----foo
(1 row)

postgres=# create view b as select a('foo');
CREATE VIEW

postgres=# create view c as select * from b;
CREATE VIEW

postgres=# drop function a(text);
NOTICE:  00000: rule _RETURN on view b depends on function a(text)
NOTICE:  00000: view b depends on rule _RETURN on view b
NOTICE:  00000: rule _RETURN on view c depends on view b
NOTICE:  00000: view c depends on rule _RETURN on view c
ERROR:  2BP01: cannot drop function a(text) because other objects depend on it

postgres=# create or replace view b  as select 'foo'::text as a;
CREATE VIEW

postgres=# drop function a(text);
DROP FUNCTION

postgres=# select * from c; a  
-----foo
(1 row)

It seems like it's quite relevant to provide the dependency chain to help the
DBA find the point in the chain he wants to intervene.

On the other hand the fact that we don't actually provide an exhaustive set of
data for that purpose and a) nobody's complained and b) it's for basically the
same reason that you're suggesting this change, ie, that it isn't convenient
and isn't important enough to go out of our way to build just for that purpose
could mean it's a reasonable compromise. Are you just worried about the memory
and cpu cycles or is it actually a lot of code?

Incidentally, if it happens to be straightforward (I suspect not :( ) in the
above example it would be nice to compress out the internal dependencies and
show just the "view b depends on function a(text)" which would actually make
sense to a DBA. The intermediate rules going via internal objects (rules)
they've never heard of make it a lot harder to read.

> BTW, it would now be possible to do something like what the shdepend
> code does, and stuff all these reports into the DETAIL field of a
> single message, instead of emitting them as separate notices.
> Any feelings pro or con about that?

Seems fine either way -- I wonder if one way is more convenient for pgadmin or
applications? I suspect if so it would be the DETAIL field?

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production
Tuning