merchant-0038.sql (4178B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 16 -- @file merchant-0038.sql 17 -- @brief Track the number of currently locked units per product in a 18 -- denormalized total_locked/total_locked_frac counter on 19 -- merchant_inventory. The counter is kept up to date by a trigger on 20 -- the two lock tables (merchant_inventory_locks and merchant_order_locks) 21 -- and is used to report the quantity actually available for new orders 22 -- (which excludes units reserved by shopping carts and unpaid orders). 23 -- @author Christian Grothoff 24 25 BEGIN; 26 27 SELECT _v.register_patch('merchant-0038', NULL, NULL); 28 29 SET search_path TO merchant; 30 31 CREATE PROCEDURE merchant.merchant_0038_init(s TEXT) 32 LANGUAGE plpgsql 33 AS $OUTER$ 34 BEGIN 35 EXECUTE format('SET LOCAL search_path TO %I', s); 36 37 -- Denormalized counter of how many units of each product are currently 38 -- reserved by locks (shopping cart locks plus unpaid order locks). Kept 39 -- in sync by update_inventory_locked() below. total_locked_frac is in 40 -- units of 1/1000000 of the base value, mirroring total_stock_frac etc. 41 ALTER TABLE merchant_inventory 42 ADD COLUMN total_locked INT8 NOT NULL DEFAULT 0, 43 ADD COLUMN total_locked_frac INT4 NOT NULL DEFAULT 0; 44 COMMENT ON COLUMN merchant_inventory.total_locked 45 IS 'Number of units currently reserved by locks (cart and unpaid order locks); these units are unavailable for new orders. Maintained by the update_inventory_locked() trigger.'; 46 COMMENT ON COLUMN merchant_inventory.total_locked_frac 47 IS 'Fractional part of total_locked in units of 1/1000000 of the base value'; 48 49 -- Backfill the counter for instances that already hold locks. We 50 -- temporarily disable the user triggers on merchant_inventory so that 51 -- the backfill does not emit spurious "inventory_updated" webhooks. 52 ALTER TABLE merchant_inventory DISABLE TRIGGER USER; 53 UPDATE merchant_inventory mi 54 SET total_locked = div (agg.micros, 1000000)::INT8, 55 total_locked_frac = (agg.micros % 1000000)::INT4 56 FROM ( 57 SELECT product_serial, 58 SUM(micros) AS micros 59 FROM ( 60 SELECT product_serial, 61 total_locked::NUMERIC * 1000000 62 + total_locked_frac::NUMERIC AS micros 63 FROM merchant_inventory_locks 64 UNION ALL 65 SELECT product_serial, 66 total_locked::NUMERIC * 1000000 67 + total_locked_frac::NUMERIC 68 FROM merchant_order_locks 69 ) all_locks 70 GROUP BY product_serial 71 ) agg 72 WHERE mi.product_serial = agg.product_serial; 73 ALTER TABLE merchant_inventory ENABLE TRIGGER USER; 74 75 -- Dummy function so we can create the trigger, actual 76 -- trigger will be loaded later via instance_procedures.sql. 77 CREATE OR REPLACE FUNCTION update_inventory_locked() 78 RETURNS TRIGGER 79 LANGUAGE plpgsql 80 AS $LOCKED$ 81 BEGIN 82 RETURN NULL; 83 END $LOCKED$; 84 85 -- Add trigger to keep the new columns always up-to-date 86 CREATE TRIGGER trigger_inventory_locks_locked 87 AFTER INSERT OR UPDATE OR DELETE 88 ON merchant_inventory_locks 89 FOR EACH ROW EXECUTE FUNCTION update_inventory_locked(); 90 91 CREATE TRIGGER trigger_order_locks_locked 92 AFTER INSERT OR UPDATE OR DELETE 93 ON merchant_order_locks 94 FOR EACH ROW EXECUTE FUNCTION update_inventory_locked(); 95 END 96 $OUTER$; 97 98 INSERT INTO merchant.instance_fixups 99 (migration_name 100 ,version) 101 VALUES 102 ('merchant_0038_init' 103 ,38); 104 -- Apply new fix-up to existing instances 105 CALL merchant.fixup_instance_schema (38::INT8); 106 107 COMMIT;