commit bf9ac326541908d55880759984b5ba1a42e7a62b
parent 58eb6db9ef9030edd1b2b7cac641798fa174ea9d
Author: Florian Dold <dold@taler.net>
Date: Mon, 7 Sep 2026 21:01:45 +0200
merchantdb: prevent order serial reuse after schema migration
Preserve retained contract serials and the old order sequence position
when migration 0036 creates per-instance sequences.
Add migration 0047 to advance affected order sequences without moving
already-safe sequences backward or renumbering existing orders.
Diffstat:
4 files changed, 486 insertions(+), 3 deletions(-)
diff --git a/src/backenddb/sql-schema/merchant-0036-setval.sql.fragment b/src/backenddb/sql-schema/merchant-0036-setval.sql.fragment
@@ -8,7 +8,39 @@
PERFORM setval(pg_get_serial_sequence(s || '.merchant_inventory', 'product_serial'), COALESCE((SELECT MAX(product_serial) FROM merchant.merchant_inventory WHERE merchant_serial = rec.merchant_serial), 0) + 1, false);
PERFORM setval(pg_get_serial_sequence(s || '.merchant_login_tokens', 'serial'), COALESCE((SELECT MAX(serial) FROM merchant.merchant_login_tokens WHERE merchant_serial = rec.merchant_serial), 0) + 1, false);
PERFORM setval(pg_get_serial_sequence(s || '.merchant_money_pots', 'money_pot_serial'), COALESCE((SELECT MAX(money_pot_serial) FROM merchant.merchant_money_pots WHERE merchant_serial = rec.merchant_serial), 0) + 1, false);
- PERFORM setval(pg_get_serial_sequence(s || '.merchant_orders', 'order_serial'), COALESCE((SELECT MAX(order_serial) FROM merchant.merchant_orders WHERE merchant_serial = rec.merchant_serial), 0) + 1, false);
+ -- Expired orders are removed from merchant_orders even when their paid
+ -- contracts remain. Preserve both those serials and the shared sequence's
+ -- allocation high-water mark when splitting it into per-instance sequences.
+ DECLARE
+ old_sequence REGCLASS;
+ new_sequence REGCLASS;
+ next_serial NUMERIC;
+ max_serial INT8;
+ BEGIN
+ old_sequence := pg_get_serial_sequence(
+ 'merchant.merchant_orders', 'order_serial')::REGCLASS;
+ new_sequence := pg_get_serial_sequence(
+ format('%I.merchant_orders', s), 'order_serial')::REGCLASS;
+ EXECUTE format(
+ 'SELECT last_value::NUMERIC + CASE WHEN is_called THEN 1 ELSE 0 END FROM %s',
+ old_sequence)
+ INTO next_serial;
+ SELECT GREATEST(
+ next_serial,
+ COALESCE((SELECT MAX(order_serial)::NUMERIC
+ FROM merchant.merchant_orders
+ WHERE merchant_serial = rec.merchant_serial), 0) + 1,
+ COALESCE((SELECT MAX(order_serial)::NUMERIC
+ FROM merchant.merchant_contract_terms
+ WHERE merchant_serial = rec.merchant_serial), 0) + 1)
+ INTO next_serial;
+ SELECT seqmax INTO max_serial FROM pg_sequence WHERE seqrelid = new_sequence;
+ IF next_serial > max_serial THEN
+ RAISE EXCEPTION 'Order serial sequence exhausted for instance %', s;
+ END IF;
+ EXECUTE format('ALTER SEQUENCE %s RESTART WITH %s',
+ new_sequence, next_serial);
+ END;
PERFORM setval(pg_get_serial_sequence(s || '.merchant_otp_devices', 'otp_serial'), COALESCE((SELECT MAX(otp_serial) FROM merchant.merchant_otp_devices WHERE merchant_serial = rec.merchant_serial), 0) + 1, false);
PERFORM setval(pg_get_serial_sequence(s || '.merchant_product_groups', 'product_group_serial'), COALESCE((SELECT MAX(product_group_serial) FROM merchant.merchant_product_groups WHERE merchant_serial = rec.merchant_serial), 0) + 1, false);
PERFORM setval(pg_get_serial_sequence(s || '.merchant_reports', 'report_serial'), COALESCE((SELECT MAX(report_serial) FROM merchant.merchant_reports WHERE merchant_serial = rec.merchant_serial), 0) + 1, false);
diff --git a/src/backenddb/sql-schema/merchant-0047.sql b/src/backenddb/sql-schema/merchant-0047.sql
@@ -0,0 +1,73 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2026 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+-- @file merchant-0047.sql
+-- @brief Advance order sequences past retained contracts after migration 0036
+--
+-- Run database upgrades with merchant writers stopped. This prevents further
+-- serial reuse; it does not renumber orders already assigned low serials or
+-- repair existing collisions. Historical listing order therefore stays as is.
+
+BEGIN;
+
+SELECT _v.register_patch('merchant-0047', NULL, NULL);
+
+SET search_path TO merchant;
+
+CREATE PROCEDURE merchant.merchant_0047_init(s TEXT)
+ LANGUAGE plpgsql
+ AS $OUTER$
+DECLARE
+ order_sequence REGCLASS;
+ current_next NUMERIC;
+ required_next NUMERIC;
+ max_serial INT8;
+BEGIN
+ EXECUTE format('SET LOCAL search_path TO %I', s);
+ LOCK TABLE merchant_orders, merchant_contract_terms IN ACCESS EXCLUSIVE MODE;
+
+ order_sequence := pg_get_serial_sequence(
+ format('%I.merchant_orders', s), 'order_serial')::REGCLASS;
+ EXECUTE format(
+ 'SELECT last_value::NUMERIC + CASE WHEN is_called THEN 1 ELSE 0 END FROM %s',
+ order_sequence)
+ INTO current_next;
+ SELECT GREATEST(
+ COALESCE((SELECT MAX(order_serial)::NUMERIC FROM merchant_orders), 0),
+ COALESCE((SELECT MAX(order_serial)::NUMERIC FROM merchant_contract_terms), 0)) + 1
+ INTO required_next;
+ SELECT seqmax INTO max_serial FROM pg_sequence WHERE seqrelid = order_sequence;
+ IF GREATEST(current_next, required_next) > max_serial THEN
+ RAISE EXCEPTION 'Order serial sequence exhausted for instance %', s;
+ END IF;
+
+ -- Unlike setval(), RESTART is rolled back if a later instance fixup fails.
+ -- Leave already-safe sequences alone, including their is_called state.
+ IF current_next < required_next THEN
+ EXECUTE format('ALTER SEQUENCE %s RESTART WITH %s',
+ order_sequence, required_next);
+ END IF;
+
+ SET LOCAL search_path TO merchant;
+END
+$OUTER$;
+
+INSERT INTO merchant.instance_fixups
+ (migration_name, version)
+ VALUES ('merchant_0047_init', 47);
+CALL merchant.fixup_instance_schema (47::INT8);
+
+COMMIT;
diff --git a/src/backenddb/sql-schema/meson.build b/src/backenddb/sql-schema/meson.build
@@ -141,10 +141,12 @@ generated_sql = [
['merchant-0044.sql'],
['merchant-0045.sql'],
['merchant-0046.sql'],
+ ['merchant-0047.sql'],
]
+migration_sql = []
foreach g : generated_sql
- custom_target(
+ migration_sql += custom_target(
'gen-merchantdb-' + g[0],
input: g[0],
output: g[0],
@@ -156,7 +158,7 @@ foreach g : generated_sql
endforeach
-custom_target(
+migration_sql += custom_target(
'gen-merchantdb-merchant_0036.sql',
input: [
'merchant-0036.sql.in',
@@ -174,3 +176,16 @@ custom_target(
install: true,
install_dir: sqldir,
)
+
+test(
+ 'order-sequence-migrations',
+ find_program('python3'),
+ args: [
+ files('../test_order_sequence_migrations.py'),
+ meson.current_source_dir(),
+ meson.current_build_dir(),
+ ],
+ depends: migration_sql,
+ suite: ['backenddb'],
+ timeout: 120,
+)
diff --git a/src/backenddb/test_order_sequence_migrations.py b/src/backenddb/test_order_sequence_migrations.py
@@ -0,0 +1,363 @@
+#!/usr/bin/env python3
+
+# This file is part of TALER
+# Copyright (C) 2026 Taler Systems SA
+#
+# TALER is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 3, or (at your option) any later version.
+#
+# TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+
+"""Regression tests for the order ID reset introduced by migration 0036.
+
+Unclaimed orders live in merchant_orders. Paid contracts can remain in
+merchant_contract_terms after the corresponding order rows have expired.
+The next order ID must therefore exceed IDs in both tables.
+
+Each test clones an empty database at version 35 or 46, seeds the relevant
+history, and runs the real migration SQL. These are database migration tests;
+the contract insertion check does not exercise the HTTP claim endpoint.
+"""
+
+from contextlib import contextmanager
+import os
+from pathlib import Path
+import shutil
+import subprocess
+import sys
+import tempfile
+import unittest
+
+
+MAX_SERIAL = 9223372036854775807
+
+
+def run(command, **kwargs):
+ result = subprocess.run(command, text=True, capture_output=True, **kwargs)
+ if result.returncode:
+ raise RuntimeError(f"{command[0]} failed:\n{result.stdout}\n{result.stderr}")
+ return result.stdout.strip()
+
+
+@contextmanager
+def postgres_cluster(bindir):
+ """Keep all test data in a disposable server, accessible only by Unix socket."""
+ with tempfile.TemporaryDirectory(prefix="merchant-seq-", dir="/tmp") as tmp:
+ data = Path(tmp) / "data"
+ env = {key: value for key, value in os.environ.items()
+ if not key.startswith("PG")}
+ env.update(PGHOST=tmp, PGPORT="5432", PGUSER="postgres",
+ PGOPTIONS="-c client_min_messages=warning")
+ run([str(bindir / "initdb"), "-D", str(data), "-A", "trust",
+ "-U", "postgres", "--no-locale"], env=env)
+ try:
+ run([str(bindir / "pg_ctl"), "-D", str(data),
+ "-l", str(Path(tmp) / "server.log"),
+ "-o", f"-F -k {tmp} -c listen_addresses=''", "-w", "start"],
+ env=env)
+ yield env
+ finally:
+ if (data / "postmaster.pid").exists():
+ run([str(bindir / "pg_ctl"), "-D", str(data), "-m", "immediate",
+ "-w", "stop"], env=env)
+
+
+class Database:
+ """A fixed database connection; choosing another database creates a new handle."""
+
+ def __init__(self, name, bindir, env, sql_dir):
+ self.name = name
+ self.command = [str(bindir / "psql"), "-X", "-qAt", "-v", "ON_ERROR_STOP=1"]
+ self.env = dict(env, PGDATABASE=name)
+ self.sql_dir = sql_dir
+
+ def sql(self, statement):
+ return run(self.command, input=statement, env=self.env)
+
+ def apply_migration(self, version):
+ self.apply_file(self.sql_dir / f"merchant-{version:04}.sql")
+
+ def apply_file(self, path):
+ return run(self.command + ["-f", str(path)], env=self.env)
+
+ def add_instance(self, number, *, legacy=False):
+ self.sql(f"""
+ INSERT INTO merchant.merchant_instances
+ (merchant_serial, merchant_id, merchant_name, merchant_pub,
+ address, jurisdiction, default_wire_transfer_delay, default_pay_delay)
+ VALUES ({number}, 'test-{number}', 'Test',
+ decode(lpad(to_hex({number}),64,'0'),'hex'), '{{}}', '{{}}', 1, 1)
+ """)
+ # Runtime procedure bundles are not loaded in this migration-only fixture.
+ # Invoke the schema constructor explicitly instead of its runtime trigger.
+ if not legacy:
+ self.sql(f"SELECT merchant.create_instance_schema({number})")
+ return OrderFixture(self, number, legacy=legacy)
+
+
+class OrderFixture:
+ """Seed only the order columns needed for the sequence migration scenarios."""
+
+ def __init__(self, db, instance, *, legacy=False):
+ self.db = db
+ self.schema = "merchant" if legacy else f"merchant_instance_{instance}"
+ self.sequence = f"{self.schema}.merchant_orders_order_serial_seq"
+ self.instance_column = "merchant_serial," if legacy else ""
+ self.instance_value = f"{instance}," if legacy else ""
+ # Old statistics triggers need runtime procedures absent from the fixture.
+ self.seed_setup = "SET session_replication_role=replica;" if legacy else ""
+
+ def add_order(self, serial=None):
+ """An explicit serial seeds history; omitting it exercises ID allocation."""
+ serial_value = "DEFAULT" if serial is None else str(serial)
+ order_id = "new-order" if serial is None else f"order-{serial}"
+ return int(self.db.sql(f"""
+ {self.seed_setup}
+ INSERT INTO {self.schema}.merchant_orders
+ ({self.instance_column}order_serial, order_id, claim_token,
+ h_post_data, pay_deadline, creation_time, contract_terms)
+ VALUES ({self.instance_value}{serial_value}, '{order_id}',
+ decode(repeat('01',16),'hex'), decode(repeat('02',64),'hex'),
+ 2000000000000000, 1788800253000000, '{{}}')
+ RETURNING order_serial
+ """))
+
+ def add_paid_contract(self, serial):
+ self.db.sql(f"""
+ {self.seed_setup}
+ INSERT INTO {self.schema}.merchant_contract_terms
+ ({self.instance_column}order_serial, order_id, contract_terms,
+ h_contract_terms, creation_time, pay_deadline, refund_deadline,
+ claim_token, paid)
+ VALUES ({self.instance_value}{serial}, 'order-{serial}', '{{}}',
+ decode(lpad(to_hex({serial}),128,'0'),'hex'),
+ 1788719948000000, 2000000000000000, 2000000000000000,
+ decode(repeat('01',16),'hex'), true)
+ """)
+
+ def set_sequence(self, *, last_value, is_called):
+ self.db.sql(f"SELECT setval('{self.sequence}', {last_value}, "
+ f"{str(is_called).lower()})")
+
+ def sequence_state(self):
+ last_value, is_called = self.db.sql(
+ f"SELECT last_value, is_called FROM {self.sequence}"
+ ).split("|")
+ return int(last_value), is_called == "t"
+
+ def snapshot(self):
+ return self.db.sql(f"""
+ SELECT jsonb_agg(to_jsonb(t) ORDER BY order_serial)
+ FROM {self.schema}.merchant_orders t;
+ SELECT jsonb_agg(to_jsonb(t) ORDER BY order_serial)
+ FROM {self.schema}.merchant_contract_terms t;
+ """)
+
+ def repair_statement(self):
+ return f"CALL merchant.merchant_0047_init('{self.schema}');"
+
+
+class OrderSequenceMigrations(unittest.TestCase):
+ """Each test gets its own clone; no scenario depends on an earlier test."""
+
+ def database_before(self, version):
+ self.admin.sql(f"CREATE DATABASE {self._testMethodName} "
+ f"TEMPLATE before_{version}")
+ return Database(self._testMethodName, self.bindir, self.cluster_env,
+ self.sql_dir)
+
+ def assert_sequence(self, orders, *, last_value, is_called):
+ self.assertEqual(orders.sequence_state(), (last_value, is_called),
+ f"Unexpected sequence state in {orders.schema}")
+
+ def test_0036_keeps_ids_from_both_tables(self):
+ db = self.database_before(36)
+ paid = db.add_instance(1, legacy=True)
+ unpaid = db.add_instance(2, legacy=True)
+ db.add_instance(3, legacy=True)
+ paid.add_paid_contract(78) # The corresponding order has expired.
+ unpaid.add_order(90)
+
+ db.apply_migration(36)
+
+ self.assertEqual(OrderFixture(db, 1).add_order(), 79)
+ self.assertEqual(OrderFixture(db, 2).add_order(), 91)
+ self.assertEqual(OrderFixture(db, 3).add_order(), 1)
+
+ def check_0036_preserves_sequence(self, *, is_called, expected_next):
+ db = self.database_before(36)
+ paid = db.add_instance(1, legacy=True)
+ db.add_instance(2, legacy=True)
+ paid.add_paid_contract(78)
+ paid.set_sequence(last_value=200, is_called=is_called)
+
+ db.apply_migration(36)
+
+ # Both new sequences inherit the shared sequence's higher position.
+ self.assertEqual(OrderFixture(db, 1).add_order(), expected_next)
+ self.assertEqual(OrderFixture(db, 2).add_order(), expected_next)
+
+ def test_0036_preserves_called_sequence(self):
+ self.check_0036_preserves_sequence(is_called=True, expected_next=201)
+
+ def test_0036_preserves_uncalled_sequence(self):
+ self.check_0036_preserves_sequence(is_called=False, expected_next=200)
+
+ def test_0036_rejects_exhausted_sequence(self):
+ db = self.database_before(36)
+ orders = db.add_instance(1, legacy=True)
+ orders.set_sequence(last_value=MAX_SERIAL, is_called=True)
+
+ with self.assertRaisesRegex(RuntimeError, "Order serial sequence exhausted"):
+ db.apply_migration(36)
+
+ self.assertEqual(db.sql("SELECT count(*) FROM _v.patches "
+ "WHERE patch_name='merchant-0036'"), "0")
+
+ def test_0047_repairs_restarted_ids_without_changing_orders(self):
+ db = self.database_before(47)
+ paid = db.add_instance(1)
+ paid.add_paid_contract(78)
+ # Reproduce the reported history: new IDs 1-4 follow historical ID 78.
+ for serial in range(1, 5):
+ paid.add_order(serial)
+ paid.add_paid_contract(serial)
+ paid.set_sequence(last_value=4, is_called=True)
+ unpaid = db.add_instance(2)
+ unpaid.add_paid_contract(80)
+ unpaid.add_order(90)
+ paid_before, unpaid_before = paid.snapshot(), unpaid.snapshot()
+
+ db.apply_migration(47)
+
+ self.assert_sequence(paid, last_value=79, is_called=False)
+ self.assert_sequence(unpaid, last_value=91, is_called=False)
+ self.assertEqual(paid.snapshot(), paid_before)
+ self.assertEqual(unpaid.snapshot(), unpaid_before)
+
+ # Reapplying the registered fixup must not consume or rewind IDs.
+ db.sql("CALL merchant.fixup_instance_schema(47::INT8)")
+ self.assert_sequence(paid, last_value=79, is_called=False)
+ self.assert_sequence(unpaid, last_value=91, is_called=False)
+ self.assertEqual(paid.add_order(), 79)
+ self.assertEqual(unpaid.add_order(), 91)
+
+ # At the database level, claiming can copy the new serial without a
+ # primary-key collision. This is not an HTTP/backend claim test.
+ db.sql(f"""
+ INSERT INTO {paid.schema}.merchant_contract_terms
+ (order_serial, order_id, contract_terms, h_contract_terms,
+ creation_time, pay_deadline, refund_deadline, claim_token)
+ SELECT order_serial, order_id, contract_terms,
+ decode(repeat('ff',64),'hex'), creation_time,
+ pay_deadline, pay_deadline, claim_token
+ FROM {paid.schema}.merchant_orders WHERE order_serial=79
+ """)
+ self.assertEqual(db.sql(
+ f"SELECT order_serial FROM {paid.schema}.merchant_contract_terms "
+ "WHERE order_id='new-order'"), "79")
+
+ def test_0047_preserves_safe_sequence_states(self):
+ db = self.database_before(47)
+ called = db.add_instance(1)
+ called.set_sequence(last_value=200, is_called=True)
+ uncalled = db.add_instance(2)
+ uncalled.set_sequence(last_value=200, is_called=False)
+ just_above_history = db.add_instance(3)
+ just_above_history.add_paid_contract(78)
+ just_above_history.set_sequence(last_value=79, is_called=False)
+
+ db.apply_migration(47)
+
+ self.assert_sequence(called, last_value=200, is_called=True)
+ self.assert_sequence(uncalled, last_value=200, is_called=False)
+ self.assert_sequence(just_above_history, last_value=79, is_called=False)
+
+ def test_0047_keeps_empty_and_new_instances_starting_at_one(self):
+ db = self.database_before(47)
+ empty = db.add_instance(1)
+
+ db.apply_migration(47)
+ new = db.add_instance(2)
+
+ self.assert_sequence(empty, last_value=1, is_called=False)
+ self.assertEqual(empty.add_order(), 1)
+ self.assertEqual(new.add_order(), 1)
+
+ def test_0047_sequence_restart_rolls_back(self):
+ db = self.database_before(47)
+ orders = db.add_instance(1)
+ orders.add_paid_contract(78)
+ db.apply_migration(47)
+ orders.set_sequence(last_value=4, is_called=True)
+
+ db.sql(f"BEGIN; {orders.repair_statement()} ROLLBACK;")
+
+ self.assert_sequence(orders, last_value=4, is_called=True)
+
+ def test_0047_later_failure_rolls_back_earlier_repair(self):
+ db = self.database_before(47)
+ repairable = db.add_instance(1)
+ exhausted = db.add_instance(2)
+ repairable.add_paid_contract(78)
+ db.apply_migration(47)
+ repairable.set_sequence(last_value=4, is_called=True)
+ exhausted.set_sequence(last_value=MAX_SERIAL, is_called=True)
+
+ with self.assertRaisesRegex(RuntimeError, "Order serial sequence exhausted"):
+ db.sql(f"BEGIN; {repairable.repair_statement()} "
+ f"{exhausted.repair_statement()} COMMIT;")
+
+ self.assert_sequence(repairable, last_value=4, is_called=True)
+
+ def test_0047_rejects_exhausted_stored_ids(self):
+ db = self.database_before(47)
+ orders = db.add_instance(1)
+ orders.add_order(MAX_SERIAL)
+
+ with self.assertRaisesRegex(RuntimeError, "Order serial sequence exhausted"):
+ db.apply_migration(47)
+
+ self.assert_sequence(orders, last_value=1, is_called=False)
+
+
+def main():
+ source, build = (Path(arg).resolve() for arg in sys.argv[1:])
+ if not shutil.which("pg_config"):
+ print("PostgreSQL server tools unavailable")
+ return 77
+ bindir = Path(run(["pg_config", "--bindir"]))
+ if os.geteuid() == 0 or not all((bindir / tool).exists() for tool in
+ ("initdb", "pg_ctl", "psql")):
+ print("Need PostgreSQL server tools and an unprivileged user")
+ return 77
+
+ with postgres_cluster(bindir) as env:
+ admin = Database("template1", bindir, env, build)
+ admin.sql("CREATE DATABASE before_36")
+ before_36 = Database("before_36", bindir, env, build)
+ before_36.apply_file(source / "versioning.sql")
+ for version in range(1, 36):
+ before_36.apply_migration(version)
+ admin.sql("CREATE DATABASE before_47 TEMPLATE before_36")
+ before_47 = Database("before_47", bindir, env, build)
+ for version in range(36, 47):
+ before_47.apply_migration(version)
+
+ OrderSequenceMigrations.admin = admin
+ OrderSequenceMigrations.bindir = bindir
+ OrderSequenceMigrations.cluster_env = env
+ OrderSequenceMigrations.sql_dir = build
+ suite = unittest.defaultTestLoader.loadTestsFromTestCase(OrderSequenceMigrations)
+ result = unittest.TextTestRunner(verbosity=2).run(suite)
+ return 0 if result.wasSuccessful() else 1
+
+
+if __name__ == "__main__":
+ sys.exit(main())
