gc.sql (3573B)
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 denominations_serial 86 FROM known_coins 87 WHERE coin_pub IN 88 (SELECT DISTINCT coin_pub 89 FROM recoup)) 90 AND denominations_serial NOT IN 91 (SELECT DISTINCT denominations_serial 92 FROM known_coins 93 WHERE coin_pub IN 94 (SELECT DISTINCT coin_pub 95 FROM recoup_refresh)); 96 97 SELECT known_coin_id 98 INTO coin_min 99 FROM known_coins 100 ORDER BY known_coin_id ASC 101 LIMIT 1; 102 103 DELETE FROM recoup_refresh 104 WHERE known_coin_id < coin_min; 105 106 SELECT batch_deposit_serial_id 107 INTO batch_deposit_min 108 FROM coin_deposits 109 ORDER BY batch_deposit_serial_id ASC 110 LIMIT 1; 111 112 DELETE FROM refunds 113 WHERE batch_deposit_serial_id < batch_deposit_min; 114 DELETE FROM aggregation_tracking 115 WHERE batch_deposit_serial_id < batch_deposit_min; 116 DELETE FROM coin_deposits 117 WHERE batch_deposit_serial_id < batch_deposit_min; 118 119 SELECT denominations_serial 120 INTO denom_min 121 FROM denominations 122 ORDER BY denominations_serial ASC 123 LIMIT 1; 124 125 DELETE FROM cs_nonce_locks 126 WHERE max_denomination_serial < denom_min; 127 128 END $$;