merchant-0036-setval.sql.fragment (10859B)
1 -- Advance every IDENTITY sequence in the new instance schema past the 2 -- maximum value just copied so future inserts do not collide. 3 PERFORM setval(pg_get_serial_sequence(s || '.merchant_accounts', 'account_serial'), COALESCE((SELECT MAX(account_serial) FROM merchant.merchant_accounts WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 4 PERFORM setval(pg_get_serial_sequence(s || '.merchant_categories', 'category_serial'), COALESCE((SELECT MAX(category_serial) FROM merchant.merchant_categories WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 5 PERFORM setval(pg_get_serial_sequence(s || '.merchant_contract_terms', 'order_serial'), COALESCE((SELECT MAX(order_serial) FROM merchant.merchant_contract_terms WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 6 PERFORM setval(pg_get_serial_sequence(s || '.merchant_custom_units', 'unit_serial'), COALESCE((SELECT MAX(unit_serial) FROM merchant.merchant_custom_units WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 7 PERFORM setval(pg_get_serial_sequence(s || '.merchant_donau_instances', 'donau_instances_serial'), COALESCE((SELECT MAX(donau_instances_serial) FROM merchant.merchant_donau_instances WHERE merchant_instance_serial = rec.merchant_serial), 0) + 1, false); 8 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); 9 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); 10 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); 11 -- Expired orders are removed from merchant_orders even when their paid 12 -- contracts remain. Preserve both those serials and the shared sequence's 13 -- allocation high-water mark when splitting it into per-instance sequences. 14 DECLARE 15 old_sequence REGCLASS; 16 new_sequence REGCLASS; 17 next_serial NUMERIC; 18 max_serial INT8; 19 BEGIN 20 old_sequence := pg_get_serial_sequence( 21 'merchant.merchant_orders', 'order_serial')::REGCLASS; 22 new_sequence := pg_get_serial_sequence( 23 format('%I.merchant_orders', s), 'order_serial')::REGCLASS; 24 EXECUTE format( 25 'SELECT last_value::NUMERIC + CASE WHEN is_called THEN 1 ELSE 0 END FROM %s', 26 old_sequence) 27 INTO next_serial; 28 SELECT GREATEST( 29 next_serial, 30 COALESCE((SELECT MAX(order_serial)::NUMERIC 31 FROM merchant.merchant_orders 32 WHERE merchant_serial = rec.merchant_serial), 0) + 1, 33 COALESCE((SELECT MAX(order_serial)::NUMERIC 34 FROM merchant.merchant_contract_terms 35 WHERE merchant_serial = rec.merchant_serial), 0) + 1) 36 INTO next_serial; 37 SELECT seqmax INTO max_serial FROM pg_sequence WHERE seqrelid = new_sequence; 38 IF next_serial > max_serial THEN 39 RAISE EXCEPTION 'Order serial sequence exhausted for instance %', s; 40 END IF; 41 EXECUTE format('ALTER SEQUENCE %s RESTART WITH %s', 42 new_sequence, next_serial); 43 END; 44 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); 45 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); 46 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); 47 PERFORM setval(pg_get_serial_sequence(s || '.merchant_statistic_amount_event', 'aevent_serial_id'), COALESCE((SELECT MAX(aevent_serial_id) FROM merchant.merchant_statistic_amount_event WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 48 PERFORM setval(pg_get_serial_sequence(s || '.merchant_statistic_bucket_meta', 'bmeta_serial_id'), COALESCE((SELECT MAX(bmeta_serial_id) FROM merchant.merchant_statistic_bucket_meta), 0) + 1, false); 49 PERFORM setval(pg_get_serial_sequence(s || '.merchant_statistic_counter_event', 'nevent_serial_id'), COALESCE((SELECT MAX(nevent_serial_id) FROM merchant.merchant_statistic_counter_event WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 50 PERFORM setval(pg_get_serial_sequence(s || '.merchant_statistic_interval_meta', 'imeta_serial_id'), COALESCE((SELECT MAX(imeta_serial_id) FROM merchant.merchant_statistic_interval_meta), 0) + 1, false); 51 PERFORM setval(pg_get_serial_sequence(s || '.merchant_template', 'template_serial'), COALESCE((SELECT MAX(template_serial) FROM merchant.merchant_template WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 52 PERFORM setval(pg_get_serial_sequence(s || '.merchant_token_families', 'token_family_serial'), COALESCE((SELECT MAX(token_family_serial) FROM merchant.merchant_token_families WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 53 PERFORM setval(pg_get_serial_sequence(s || '.merchant_webhook', 'webhook_serial'), COALESCE((SELECT MAX(webhook_serial) FROM merchant.merchant_webhook WHERE merchant_serial = rec.merchant_serial), 0) + 1, false); 54 -- Indirect-FK tables (owning instance via JOIN) 55 PERFORM setval(pg_get_serial_sequence(s || '.merchant_kyc', 'kyc_serial_id'), COALESCE((SELECT MAX(k.kyc_serial_id) FROM merchant.merchant_kyc k JOIN merchant.merchant_accounts a ON k.account_serial = a.account_serial WHERE a.merchant_serial = rec.merchant_serial), 0) + 1, false); 56 PERFORM setval(pg_get_serial_sequence(s || '.merchant_deposit_confirmations', 'deposit_confirmation_serial'), COALESCE((SELECT MAX(dc.deposit_confirmation_serial) FROM merchant.merchant_deposit_confirmations dc JOIN merchant.merchant_accounts a ON dc.account_serial = a.account_serial WHERE a.merchant_serial = rec.merchant_serial), 0) + 1, false); 57 PERFORM setval(pg_get_serial_sequence(s || '.merchant_expected_transfers', 'expected_credit_serial'), COALESCE((SELECT MAX(et.expected_credit_serial) FROM merchant.merchant_expected_transfers et JOIN merchant.merchant_accounts a ON et.account_serial = a.account_serial WHERE a.merchant_serial = rec.merchant_serial), 0) + 1, false); 58 PERFORM setval(pg_get_serial_sequence(s || '.merchant_transfers', 'credit_serial'), COALESCE((SELECT MAX(t.credit_serial) FROM merchant.merchant_transfers t JOIN merchant.merchant_accounts a ON t.account_serial = a.account_serial WHERE a.merchant_serial = rec.merchant_serial), 0) + 1, false); 59 PERFORM setval(pg_get_serial_sequence(s || '.merchant_deposits', 'deposit_serial'), COALESCE((SELECT MAX(d.deposit_serial) FROM merchant.merchant_deposits d JOIN merchant.merchant_deposit_confirmations dc ON d.deposit_confirmation_serial = dc.deposit_confirmation_serial JOIN merchant.merchant_accounts a ON dc.account_serial = a.account_serial WHERE a.merchant_serial = rec.merchant_serial), 0) + 1, false); 60 PERFORM setval(pg_get_serial_sequence(s || '.merchant_refunds', 'refund_serial'), COALESCE((SELECT MAX(r.refund_serial) FROM merchant.merchant_refunds r JOIN merchant.merchant_contract_terms ct ON r.order_serial = ct.order_serial WHERE ct.merchant_serial = rec.merchant_serial), 0) + 1, false); 61 PERFORM setval(pg_get_serial_sequence(s || '.merchant_token_family_keys', 'token_family_key_serial'), COALESCE((SELECT MAX(tfk.token_family_key_serial) FROM merchant.merchant_token_family_keys tfk JOIN merchant.merchant_token_families tf ON tfk.token_family_serial = tf.token_family_serial WHERE tf.merchant_serial = rec.merchant_serial), 0) + 1, false); 62 PERFORM setval(pg_get_serial_sequence(s || '.merchant_issued_tokens', 'issued_token_serial'), COALESCE((SELECT MAX(it.issued_token_serial) FROM merchant.merchant_issued_tokens it JOIN merchant.merchant_token_family_keys tfk ON it.token_family_key_serial = tfk.token_family_key_serial JOIN merchant.merchant_token_families tf ON tfk.token_family_serial = tf.token_family_serial WHERE tf.merchant_serial = rec.merchant_serial), 0) + 1, false); 63 PERFORM setval(pg_get_serial_sequence(s || '.merchant_used_tokens', 'spent_token_serial'), COALESCE((SELECT MAX(ut.spent_token_serial) FROM merchant.merchant_used_tokens ut JOIN merchant.merchant_token_family_keys tfk ON ut.token_family_key_serial = tfk.token_family_key_serial JOIN merchant.merchant_token_families tf ON tfk.token_family_serial = tf.token_family_serial WHERE tf.merchant_serial = rec.merchant_serial), 0) + 1, false); 64 PERFORM setval(pg_get_serial_sequence(s || '.merchant_unclaim_signatures', 'unclaim_serial'), COALESCE((SELECT MAX(us.unclaim_serial) FROM merchant.merchant_unclaim_signatures us JOIN merchant.merchant_contract_terms ct ON us.h_contract_terms = ct.h_contract_terms WHERE ct.merchant_serial = rec.merchant_serial), 0) + 1, false); 65 PERFORM setval(pg_get_serial_sequence(s || '.merchant_order_token_blinded_sigs','order_token_bs_serial'), COALESCE((SELECT MAX(otbs.order_token_bs_serial) FROM merchant.merchant_order_token_blinded_sigs otbs JOIN merchant.merchant_contract_terms ct ON otbs.order_serial = ct.order_serial WHERE ct.merchant_serial = rec.merchant_serial), 0) + 1, false);