Thread: About dependency reports in DROP RESTRICT
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 NOTICE: y depends on x NOTICE: z depends on y that is, you can trace the chain of reasoning for each deletion. However, we don't do that in CASCADE mode; you'll just see NOTICE: drop cascades to x NOTICE: drop cascades to y NOTICE: drop cascades to z I'm working on revising the DROP dependency logic as sketched here: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00301.php and I'm realizing that it's going to be quite expensive to maintain the old NOTICE style for RESTRICT, because we aren't emitting the notices on-the-fly anymore, but only after we've finished recursing to find all the objects to delete; we'd have to save about twice as much state to remember which object was the immediate predecessor of each victim. And the old behavior was always a bit indeterminate anyway because there could be multiple dependency paths, and which one got reported as the deletion cause would be happenstance. So what I'd like to do about it is just use the CASCADE style all the time. Thoughts? 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? regards, tom lane
Tom Lane wrote: > So what I'd like to do about it is just use the CASCADE style all the > time. Thoughts? It is loss of functionality, but I very much doubt anyone is depending on it -- it's way too elaborate. +1 on doing the simpler report if it's too expensive to build the full report. > 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? I think it makes more sense to do it that way (considering that they're really part of the single error message, not independent reports), but there's the problem that the error report gets too long. So we would have to send a truncated report to the client and the full report to the log only. Would people be upset at that? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
"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
Gregory Stark <stark@enterprisedb.com> writes: > 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? Well, the problem is that it uglifies the code quite a lot. The patch as I've got it now adds a "flags" field to ObjectAddress, which is unused dead space for about half of the uses of ObjectAddress; to keep the old behavior we'd need to either add three more half-used fields, or persuade ObjectAddresses to manage two parallel arrays, neither of which seems very nice. I'll do it if people want it, but I thought first I should ask if anyone really cares. > 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. Actually, I think the patch as I've got it now will behave that way (though it's not done enough to test yet ...) >> 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? The arguments are all about the same as they were for shdepend messages, I think. The case to think about is where there are LOTS of dependencies. Do you want 10000 separate NOTICE messages, or a large, perhaps truncated DETAIL field? I don't recall for sure, but I think we made the shdepend code act the way it does because we thought that was better --- certainly it would've been easy to make it just spit individual NOTICES like the older pg_depend code does. regards, tom lane