metadata.drop_all() on cascading tables in PG doesn't work

[Former user]

Postgresql allows for cascading / inheriting tables. metadata.drop_all() doesn't delete these properly. See attached script.

(for followup... gregg.lind @ gmail.com

[Former user]

bug demonstration with link to url to suggested possible fix.

[Mike Bayer]

unfortuately its not as simple as just adding "CASCADE" to the "DROP TABLE" statement. It hits "summary" first, drops it, implicitly drops "summary_inherited", then attempts an explicit drop which fails. It would require a change to the mechanism of create/drop to search for each table as it is reached, which in itself means we can't figure out what needs to be created/dropped ahead of time. The ultimate issue is that we don't know what tables would be dropped implicitly.

So a solution that allows us to know about this connection is below. It should be "good enough" for now, and can be enhanced via reflection so that PG inherits can be added directly.

diff -r 25e7dc60dfa739692421a9576c83f2db29f73cb2 lib/sqlalchemy/schema.py
--- a/lib/sqlalchemy/schema.py  Mon May 10 11:37:48 2010 -0400
+++ b/lib/sqlalchemy/schema.py  Thu May 13 11:06:19 2010 -0400
@@ -224,6 +224,7 @@
         self._columns = expression.ColumnCollection()
         self._set_primary_key(PrimaryKeyConstraint())
         self._foreign_keys = util.OrderedSet()
+        self._extra_dependencies = set()
         self.ddl_listeners = util.defaultdict(list)
         self.kwargs = {}
         if self.schema is not None:
@@ -338,6 +339,20 @@

         return self.metadata and self.metadata.bind or None

+    def add_is_dependent_on(self, table):
+        """Add a 'dependency' for this Table.
+        
+        This is another Table object which must be created
+        first before this one can, or dropped after this one.
+        
+        Usually, dependencies between tables are determined via 
+        ForeignKey objects.   However, for other situations that 
+        create dependencies outside of foreign keys (rules, inheriting),
+        this method can manually establish such a link.
+        
+        """
+        self._extra_dependencies.add(table)
+        
     def append_column(self, column):
         """Append a ``Column`` to this ``Table``."""

diff -r 25e7dc60dfa739692421a9576c83f2db29f73cb2 lib/sqlalchemy/sql/util.py
--- a/lib/sqlalchemy/sql/util.py    Mon May 10 11:37:48 2010 -0400
+++ b/lib/sqlalchemy/sql/util.py    Thu May 13 11:06:19 2010 -0400
@@ -22,6 +22,10 @@
         visitors.traverse(table, 
                             {'schema_visitor':True}, 
                             {'foreign_key':visit_foreign_key})
+        tuples.extend(
+            [table](parent,) for parent in table._extra_dependencies
+        )
+        
     return list(topological.sort(tuples, tables))

 def find_join_source(clauses, join_to):

the test then passes with this modification:

metadata.reflect()
metadata.tables['summary_inherited']('summary_inherited').add_is_dependent_on(summary)

[Mike Bayer]

that fix is in 14d523265203d21152411b5f3c18843b09b23554. the job of PG reflecting the info is #1803

[Mike Bayer]

Removing milestone: 0.6.1 (automated comment)