do_fountain_withdraw.sql (3374B)
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 DROP FUNCTION IF EXISTS merchant_do_fountain_withdraw; 18 CREATE FUNCTION merchant_do_fountain_withdraw( 19 IN in_fountain_serial INT8 20 ,IN in_token_family_serials INT8[] 21 ,IN in_slot_starts INT8[] 22 ,IN in_num_requested INT8[] 23 ,OUT out_failed_index INT4 24 ,OUT out_no_grant BOOL 25 ,OUT out_exceeded BOOL) 26 LANGUAGE plpgsql 27 AS $$ 28 -- Atomically checks and consumes per-period withdrawal quota for a 29 -- fountain. The parallel input arrays hold one entry per requested 30 -- (token family, key slot) pair; entries must be distinct and sorted 31 -- by (token_family_serial, slot_start) by the caller so that 32 -- concurrent requests acquire row locks in a deterministic order. 33 -- 34 -- All-or-nothing: if any entry has no matching grant or would exceed 35 -- its tokens_per_period_limit, no counter is modified and 36 -- out_failed_index reports the offending (1-based) entry. 37 DECLARE 38 my_limit INT8; 39 my_count INT8; 40 i INT4; 41 BEGIN 42 out_failed_index = 0; 43 out_no_grant = FALSE; 44 out_exceeded = FALSE; 45 46 -- Pass 1: lock counters and check limits without modifying them. 47 FOR i IN 1..COALESCE(array_length (in_token_family_serials, 1), 0) 48 LOOP 49 SELECT tokens_per_period_limit 50 INTO my_limit 51 FROM merchant_fountain_grants 52 WHERE fountain_serial = in_fountain_serial 53 AND token_family_serial = in_token_family_serials[i]; 54 IF NOT FOUND 55 THEN 56 out_no_grant = TRUE; 57 out_failed_index = i; 58 RETURN; 59 END IF; 60 61 -- Ensure the counter row exists (a zero-count row is semantically 62 -- inert, so leaving it behind on failure is harmless), then lock it. 63 INSERT INTO merchant_fountain_withdrawals 64 (fountain_serial 65 ,token_family_serial 66 ,slot_start 67 ,num_withdrawn 68 ) VALUES 69 (in_fountain_serial 70 ,in_token_family_serials[i] 71 ,in_slot_starts[i] 72 ,0) 73 ON CONFLICT DO NOTHING; 74 75 SELECT num_withdrawn 76 INTO my_count 77 FROM merchant_fountain_withdrawals 78 WHERE fountain_serial = in_fountain_serial 79 AND token_family_serial = in_token_family_serials[i] 80 AND slot_start = in_slot_starts[i] 81 FOR UPDATE; 82 83 IF my_count + in_num_requested[i] > my_limit 84 THEN 85 out_exceeded = TRUE; 86 out_failed_index = i; 87 RETURN; 88 END IF; 89 END LOOP; 90 91 -- Pass 2: all entries fit, consume the quota. 92 FOR i IN 1..COALESCE(array_length (in_token_family_serials, 1), 0) 93 LOOP 94 UPDATE merchant_fountain_withdrawals 95 SET num_withdrawn = num_withdrawn + in_num_requested[i] 96 WHERE fountain_serial = in_fountain_serial 97 AND token_family_serial = in_token_family_serials[i] 98 AND slot_start = in_slot_starts[i]; 99 END LOOP; 100 101 END $$;