gc.sql (3677B)
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 -- The cast is required: taler_amount is (INT8,INT4) and 54 -- (0,0) is (INT4,INT4), which PostgreSQL would refuse to compare. 55 AND current_balance = ROW(0,0)::taler_amount); 56 57 DELETE FROM reserves_close 58 WHERE reserve_pub IN ( 59 SELECT reserve_pub 60 FROM reserves 61 WHERE gc_date < in_now 62 AND current_balance = ROW(0,0)::taler_amount); 63 64 SELECT withdraw_id 65 INTO withdraw_min 66 FROM withdraw 67 ORDER BY withdraw_id ASC 68 LIMIT 1; 69 70 DELETE FROM recoup 71 WHERE withdraw_id < withdraw_min; 72 73 DELETE FROM reserves 74 WHERE gc_date < in_now 75 AND current_balance = ROW(0,0)::taler_amount; 76 77 -- FIXME: this query will be horribly slow; 78 -- need to find another way to formulate it... 79 DELETE FROM denominations 80 WHERE expire_legal < in_now 81 AND denominations_serial NOT IN 82 (SELECT DISTINCT UNNEST(denom_serials) 83 FROM withdraw) 84 AND denominations_serial NOT IN 85 (SELECT DISTINCT UNNEST(denom_serials) 86 FROM refresh) 87 AND denominations_serial NOT IN 88 (SELECT DISTINCT denominations_serial 89 FROM known_coins 90 WHERE coin_pub IN 91 (SELECT DISTINCT coin_pub 92 FROM recoup)) 93 AND denominations_serial NOT IN 94 (SELECT DISTINCT denominations_serial 95 FROM known_coins 96 WHERE coin_pub IN 97 (SELECT DISTINCT coin_pub 98 FROM recoup_refresh)); 99 100 SELECT known_coin_id 101 INTO coin_min 102 FROM known_coins 103 ORDER BY known_coin_id ASC 104 LIMIT 1; 105 106 DELETE FROM recoup_refresh 107 WHERE known_coin_id < coin_min; 108 109 SELECT batch_deposit_serial_id 110 INTO batch_deposit_min 111 FROM coin_deposits 112 ORDER BY batch_deposit_serial_id ASC 113 LIMIT 1; 114 115 DELETE FROM refunds 116 WHERE batch_deposit_serial_id < batch_deposit_min; 117 DELETE FROM aggregation_tracking 118 WHERE batch_deposit_serial_id < batch_deposit_min; 119 DELETE FROM coin_deposits 120 WHERE batch_deposit_serial_id < batch_deposit_min; 121 122 SELECT denominations_serial 123 INTO denom_min 124 FROM denominations 125 ORDER BY denominations_serial ASC 126 LIMIT 1; 127 128 DELETE FROM cs_nonce_locks 129 WHERE max_denomination_serial < denom_min; 130 131 END $$;