misc: remove SQL objects in order to use less locks (#43108)

wcs_olap/feeder.py

@@ -3,6 +3,7 @@
 from __future__ import unicode_literals
 
 from collections import OrderedDict
+import contextlib
 import datetime
 import copy
 import itertools
@@ -11,6 +12,7 @@ import json
 import hashlib
 from .utils import Whatever
 import psycopg2
+import psycopg2.errorcodes
 
 from cached_property import cached_property
 from wcs_olap.wcs_api import WcsApiError
@@ -30,6 +32,15 @@ def truncate_pg_identifier(identifier, hash_length=6, force_hash=False):
             + identifier[-(63 - hash_length) // 2:])
 
 
+@contextlib.contextmanager
+def ignore_undefined_object_or_table():
+    try:
+        yield
+    except psycopg2.ProgrammingError as e:
+        if e.pgcode not in [psycopg2.errorcodes.UNDEFINED_TABLE, psycopg2.errorcodes.UNDEFINED_OBJECT]:
+            raise
+
+
 def quote(name):
     return '"%s"' % name
 
@@ -335,10 +346,36 @@ class WcsOlapFeeder(object):
         """
         Drop tables one by one in order to avoid reaching max_locks_per_transaction
         """
-        self.ex("SELECT tablename FROM pg_tables WHERE schemaname = '%s'" % schema)
+        # drop foreign key constraints first
+        self.ex("SELECT table_name, constraint_name FROM "
+                "information_schema.key_column_usage "
+                "WHERE table_schema = %s AND constraint_name LIKE '%%_fkey'", vars=[schema])
+        for table_name, constraint_name in self.cur.fetchall():
+            # drop of PK constraints can have effects on FK constraint on other tables.
+            with ignore_undefined_object_or_table():
+                print('Dropping s', constraint_name)
+                self.ex('ALTER TABLE %s.%s DROP CONSTRAINT IF EXISTS %s CASCADE'
+                        % (quote(schema), quote(table_name), quote(constraint_name)))
+        # remove others
+        self.ex("SELECT table_name, constraint_name FROM "
+                "information_schema.key_column_usage "
+                "WHERE table_schema = %s", vars=[schema])
+        for table_name, constraint_name in self.cur.fetchall():
+            # drop of PK constraints can have effects on FK constraint on other tables.
+            with ignore_undefined_object_or_table():
+                self.ex('ALTER TABLE %s.%s DROP CONSTRAINT IF EXISTS %s CASCADE'
+                        % (quote(schema), quote(table_name), quote(constraint_name)))
+        # then drop indexes
+        self.ex("SELECT tablename, indexname FROM pg_indexes WHERE schemaname = %s", vars=[schema])
+        for table_name, index_name in self.cur.fetchall():
+            with ignore_undefined_object_or_table():
+                self.ex('DROP INDEX %s.%s CASCADE' % (quote(schema), quote(index_name)))
+
+        # finally drop tables, cascade will have no effect
+        self.ex("SELECT tablename FROM pg_tables WHERE schemaname = %s ORDER BY tablename DESC", vars=[schema])
         for table in self.cur.fetchall():
             tablename = '%s.%s' % (quote(schema), quote(table[0]))
-            self.ex('DROP TABLE IF EXISTS %s CASCADE;' % tablename)
+            self.ex('DROP TABLE IF EXISTS %s;' % tablename)
 
     def do_dates_table(self):
         self.ex('CREATE TABLE IF NOT EXISTS public.dates (date date, day text, month text)')