pg_update_inventory_locked.sql (3658B)
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 17 18 -- Trigger function that adjusts merchant_inventory.total_locked(_frac) 19 -- whenever a row in one of the lock tables is inserted, updated or 20 -- deleted (including cascade deletes when orders expire or are removed). 21 CREATE OR REPLACE FUNCTION update_inventory_locked() 22 RETURNS TRIGGER 23 LANGUAGE plpgsql 24 AS $$ 25 DECLARE 26 my_product_serial INT8; 27 my_delta NUMERIC := 0; 28 my_total NUMERIC; 29 -- Largest amount representable by (total_locked, total_locked_frac). 30 my_max CONSTANT NUMERIC := 9223372036854775807::NUMERIC * 1000000 31 + 999999; 32 BEGIN 33 CASE TG_OP 34 WHEN 'INSERT' THEN 35 my_product_serial := NEW.product_serial; 36 my_delta := NEW.total_locked::NUMERIC * 1000000 37 + NEW.total_locked_frac::NUMERIC; 38 WHEN 'DELETE' THEN 39 my_product_serial := OLD.product_serial; 40 my_delta := - (OLD.total_locked::NUMERIC * 1000000 41 + OLD.total_locked_frac::NUMERIC); 42 WHEN 'UPDATE' THEN 43 my_product_serial := NEW.product_serial; 44 my_delta := (NEW.total_locked::NUMERIC * 1000000 45 + NEW.total_locked_frac::NUMERIC) 46 - (OLD.total_locked::NUMERIC * 1000000 47 + OLD.total_locked_frac::NUMERIC); 48 ELSE 49 RETURN NULL; 50 END CASE; 51 IF (my_delta <> 0) 52 THEN 53 SELECT total_locked::NUMERIC * 1000000 54 + total_locked_frac::NUMERIC 55 INTO my_total 56 FROM merchant_inventory 57 WHERE product_serial = my_product_serial 58 FOR UPDATE; 59 IF NOT FOUND 60 THEN 61 -- Product is already gone (cascading delete), nothing to adjust. 62 RETURN NULL; 63 END IF; 64 my_total := my_total + my_delta; 65 -- Saturate rather than raise 'bigint out of range' (22003): 66 -- products carrying the INT64_MAX "unlimited stock" sentinel skip 67 -- the availability check, so their locks can sum up to more than 68 -- INT8 can hold. The counter is advisory, a 500 would not be. 69 IF (my_total < 0) 70 THEN 71 my_total := 0; 72 END IF; 73 IF (my_total > my_max) 74 THEN 75 my_total := my_max; 76 END IF; 77 -- Use div()/% (truncating, never rounding) to split the combined 78 -- micro-unit amount back into whole and fractional parts. 79 UPDATE merchant_inventory 80 SET total_locked = div (my_total, 81 1000000)::INT8, 82 total_locked_frac = (my_total % 1000000)::INT4 83 WHERE product_serial = my_product_serial; 84 END IF; 85 RETURN NULL; 86 END $$; 87 88 COMMENT ON FUNCTION update_inventory_locked 89 IS 'Trigger function that adjusts merchant_inventory.total_locked(_frac) whenever a row in one of the lock tables is inserted, updated or deleted (including cascade deletes when orders expire or are removed). The same trigger is installed on BOTH the merchant_inventory_locks and merchant_order_locks tables for INSERT, UPDATE and DELETE (and thus relies on the fact that the columns we are about have the same names in both tables).';