exchange_do_main_gc.sql (3388B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2025 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 CREATE OR REPLACE PROCEDURE exchange_do_main_gc( 18 IN in_ancient_date INT8, 19 IN in_now INT8) 20 LANGUAGE plpgsql 21 AS $$ 22 DECLARE 23 coin_min INT8; -- minimum known_coin still alive 24 batch_deposit_min INT8; -- minimum deposit still alive 25 withdraw_min INT8; -- minimum withdraw still alive 26 denom_min INT8; -- minimum denomination still alive 27 BEGIN 28 29 DELETE FROM prewire 30 WHERE finished=TRUE; 31 32 DELETE FROM wire_fee 33 WHERE end_date < in_ancient_date; 34 35 DELETE FROM refresh 36 WHERE execution_date < in_ancient_date; 37 38 DELETE FROM kycauths_in 39 WHERE execution_date < in_ancient_date; 40 41 DELETE FROM reserves_in 42 WHERE execution_date < in_ancient_date; 43 44 DELETE FROM batch_deposits 45 WHERE wire_deadline < in_ancient_date; 46 47 -- FIXME: use closing fee as threshold? 48 DELETE FROM withdraw 49 WHERE reserve_pub IN ( 50 SELECT reserve_pub 51 FROM reserves 52 WHERE gc_date < in_now 53 AND current_balance = (0, 0)); 54 55 DELETE FROM reserves_close 56 WHERE reserve_pub IN ( 57 SELECT reserve_pub 58 FROM reserves 59 WHERE gc_date < in_now 60 AND current_balance = (0, 0)); 61 62 SELECT withdraw_id 63 INTO withdraw_min 64 FROM withdraw 65 ORDER BY withdraw_id ASC 66 LIMIT 1; 67 68 DELETE FROM recoup 69 WHERE withdraw_id < withdraw_min; 70 71 DELETE FROM reserves 72 WHERE gc_date < in_now 73 AND current_balance = (0, 0); 74 75 -- FIXME: this query will be horribly slow; 76 -- need to find another way to formulate it... 77 DELETE FROM denominations 78 WHERE expire_legal < in_now 79 AND denominations_serial NOT IN 80 (SELECT DISTINCT UNNEST(denom_serials) 81 FROM withdraw) 82 AND denominations_serial NOT IN 83 (SELECT DISTINCT denominations_serial 84 FROM known_coins 85 WHERE coin_pub IN 86 (SELECT DISTINCT coin_pub 87 FROM recoup)) 88 AND denominations_serial NOT IN 89 (SELECT DISTINCT denominations_serial 90 FROM known_coins 91 WHERE coin_pub IN 92 (SELECT DISTINCT coin_pub 93 FROM recoup_refresh)); 94 95 DELETE FROM recoup_refresh 96 WHERE known_coin_id < coin_min; 97 98 SELECT known_coin_id 99 INTO coin_min 100 FROM known_coins 101 ORDER BY known_coin_id ASC 102 LIMIT 1; 103 104 SELECT batch_deposit_serial_id 105 INTO batch_deposit_min 106 FROM coin_deposits 107 ORDER BY batch_deposit_serial_id ASC 108 LIMIT 1; 109 110 DELETE FROM refunds 111 WHERE batch_deposit_serial_id < batch_deposit_min; 112 DELETE FROM aggregation_tracking 113 WHERE batch_deposit_serial_id < batch_deposit_min; 114 DELETE FROM coin_deposits 115 WHERE batch_deposit_serial_id < batch_deposit_min; 116 117 SELECT denominations_serial 118 INTO denom_min 119 FROM denominations 120 ORDER BY denominations_serial ASC 121 LIMIT 1; 122 123 DELETE FROM cs_nonce_locks 124 WHERE max_denomination_serial <= denom_min; 125 126 END $$;