merchant

commit f6f260361a74ee277f36280b41e0e700d3768d90
parent 7b5ced507545d37a8753cbe0f7917833048629f1
Author: Florian Dold <dold@taler.net>
Date:   Mon, 28 Sep 2026 21:34:22 +0200

merchantdb: release instance locks during database reset

Commit each instance schema drop separately so resetting many instances
does not exhaust PostgreSQL's shared lock table. Keep global metadata
until all instance schemas are gone so an interrupted reset can resume.
Restrict cleanup to numbered instance schemas.

Diffstat:
M
src/backenddb/sql-schema/drop.sql
 | 
40
++++++++++++++++++++++------------------
M
src/backenddb/test_order_sequence_migrations.py
 | 
30
++++++++++++++++++++++++++++++

2 files changed, 52 insertions(+), 18 deletions(-)
diff --git a/src/backenddb/sql-schema/drop.sql b/src/backenddb/sql-schema/drop.sql
@@ -14,11 +14,30 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
--- Everything in one big transaction
-BEGIN;
-
 -- This script DROPs all of the tables we create.
 
+-- Release the locks for each instance before dropping the next one. Keeping
+-- every instance's tables locked until the end exhausts PostgreSQL's shared
+-- lock table even with only a few dozen instances. An interrupted reset can
+-- be rerun; keep the global schema and version records until this loop ends.
+DO $$
+DECLARE
+  r RECORD;
+BEGIN
+  FOR r IN
+    SELECT nspname
+      FROM pg_namespace
+     WHERE nspname ~ '^merchant_instance_[0-9]+$'
+     ORDER BY nspname
+  LOOP
+    EXECUTE format('DROP SCHEMA %I CASCADE', r.nspname);
+    COMMIT;
+  END LOOP;
+END
+$$;
+
+BEGIN;
+
 -- On a database that was never initialized there is no versioning
 -- schema yet, and referencing _v.patches would abort the whole script
 -- before the DROPs below ever run.
@@ -36,21 +55,6 @@ END
 $$;
 
 
--- Drop all per-instance schemas created by the per-instance trigger.
-DO $$
-DECLARE
-  r RECORD;
-BEGIN
-  FOR r IN
-    SELECT nspname
-      FROM pg_namespace
-     WHERE nspname LIKE 'merchant_instance_%'
-  LOOP
-    EXECUTE format('DROP SCHEMA %I CASCADE', r.nspname);
-  END LOOP;
-END
-$$;
-
 DROP SCHEMA IF EXISTS merchant CASCADE;
 
 -- And we're out of here...
diff --git a/src/backenddb/test_order_sequence_migrations.py b/src/backenddb/test_order_sequence_migrations.py
@@ -192,6 +192,36 @@ class OrderSequenceMigrations(unittest.TestCase):
         self.assertEqual(orders.sequence_state(), (last_value, is_called),
                          f"Unexpected sequence state in {orders.schema}")
 
+    def test_reset_many_instance_schemas(self):
+        db = self.database_before(47)
+        for number in range(1, 25):
+            db.add_instance(number)
+        db.sql("CREATE SCHEMA merchant_instance_backup; "
+               "CREATE SCHEMA merchantxinstancey1; "
+               "SELECT _v.register_patch('other-0001', NULL, NULL)")
+        # Simulate resuming a reset interrupted after one instance was dropped.
+        db.sql("DROP SCHEMA merchant_instance_1 CASCADE")
+
+        # The cluster uses PostgreSQL's default lock budget. A transaction
+        # holding locks on every instance's tables cannot reset this database.
+        db.apply_file(self.sql_dir / 'drop.sql')
+        self.assertEqual(db.sql(
+            "SELECT count(*) FROM pg_namespace WHERE nspname='merchant' "
+            "OR nspname ~ '^merchant_instance_[0-9]+$'"), "0")
+        self.assertEqual(db.sql(
+            "SELECT count(*) FROM pg_namespace WHERE nspname IN "
+            "('merchant_instance_backup', 'merchantxinstancey1')"), "2")
+        self.assertEqual(db.sql(
+            "SELECT patch_name FROM _v.patches ORDER BY patch_name"), "other-0001")
+        db.apply_file(self.sql_dir / 'drop.sql')
+
+    def test_reset_empty_database(self):
+        self.admin.sql(f"CREATE DATABASE {self._testMethodName}")
+        db = Database(self._testMethodName, self.bindir, self.cluster_env,
+                      self.sql_dir)
+        db.apply_file(self.sql_dir / 'drop.sql')
+        db.apply_file(self.sql_dir / 'drop.sql')
+
     def test_0036_keeps_ids_from_both_tables(self):
         db = self.database_before(36)
         paid = db.add_instance(1, legacy=True)