exchange_do_create_tables.sql (6163B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2022 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 -- Main DB setup loop 19 --------------------------------------------------------------------------- 20 21 22 CREATE OR REPLACE FUNCTION exchange_do_create_tables( 23 num_partitions INTEGER 24 -- NULL: no partitions, add foreign constraints 25 -- 0: no partitions, no foreign constraints 26 -- 1: only 1 default partition 27 -- > 1: normal partitions 28 ) 29 RETURNS VOID 30 LANGUAGE plpgsql 31 AS $$ 32 DECLARE 33 tc CURSOR FOR 34 SELECT table_serial_id 35 ,name 36 ,action 37 ,partitioned 38 ,by_range 39 FROM exchange.exchange_tables 40 WHERE NOT finished 41 ORDER BY table_serial_id ASC; 42 BEGIN 43 FOR rec IN tc 44 LOOP 45 CASE rec.action 46 -- "create" actions apply to master and partitions, providing the partition ID to the creation function (if any) 47 WHEN 'create' 48 THEN 49 IF (rec.partitioned AND 50 (num_partitions IS NOT NULL)) 51 THEN 52 -- Create master table with partitioning. 53 EXECUTE FORMAT( 54 'SELECT exchange.create_table_%s (%s)'::text 55 ,rec.name 56 ,quote_literal('0') 57 ); 58 IF (rec.by_range OR 59 (num_partitions = 0)) 60 THEN 61 -- Create default partition. 62 IF (rec.by_range) 63 THEN 64 -- Range partition 65 EXECUTE FORMAT( 66 'CREATE TABLE exchange.%s_default' 67 ' PARTITION OF %s' 68 ' DEFAULT' 69 ,rec.name 70 ,rec.name 71 ); 72 ELSE 73 -- Hash partition 74 EXECUTE FORMAT( 75 'CREATE TABLE exchange.%s_default' 76 ' PARTITION OF %s' 77 ' FOR VALUES WITH (MODULUS 1, REMAINDER 0)' 78 ,rec.name 79 ,rec.name 80 ); 81 END IF; 82 ELSE 83 FOR i IN 1..num_partitions LOOP 84 -- Create num_partitions 85 EXECUTE FORMAT( 86 'CREATE TABLE exchange.%I' 87 ' PARTITION OF %I' 88 ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)' 89 ,rec.name || '_' || i 90 ,rec.name 91 ,num_partitions 92 ,i-1 93 ); 94 END LOOP; 95 END IF; 96 ELSE 97 -- Only create master table. No partitions. 98 EXECUTE FORMAT( 99 'SELECT exchange.create_table_%s ()'::text 100 ,rec.name 101 ); 102 END IF; 103 EXECUTE FORMAT( 104 'DROP FUNCTION exchange.create_table_%s'::text 105 ,rec.name 106 ); 107 -- "alter" actions apply to master and partitions (but are called without partition ID, as altering master applies to partitions for these); use when changing table layouts (adding or removing columns). 108 WHEN 'alter' 109 THEN 110 -- Alter master table. 111 EXECUTE FORMAT( 112 'SELECT exchange.alter_table_%s ()'::text 113 ,rec.name 114 ); 115 EXECUTE FORMAT( 116 'DROP FUNCTION exchange.alter_table_%s'::text 117 ,rec.name 118 ); 119 -- Constrain action apply to master OR each partition (but not on master if we have partitions); use to create (or remove) indices or constraints that apply to a partition and may not be aligned with the partition key 120 WHEN 'constrain' 121 THEN 122 ASSERT rec.partitioned, 'constrain action only applies to partitioned tables'; 123 IF (num_partitions IS NULL) 124 THEN 125 -- Constrain master table 126 EXECUTE FORMAT( 127 'SELECT exchange.constrain_table_%s (NULL)'::text 128 ,rec.name 129 ); 130 ELSE 131 IF ( (num_partitions = 0) OR 132 (rec.by_range) ) 133 THEN 134 -- Constrain default table 135 EXECUTE FORMAT( 136 'SELECT exchange.constrain_table_%s (%s)'::text 137 ,rec.name 138 ,quote_literal('default') 139 ); 140 ELSE 141 -- Constrain each partition 142 FOR i IN 1..num_partitions LOOP 143 EXECUTE FORMAT( 144 'SELECT exchange.constrain_table_%s (%s)'::text 145 ,rec.name 146 ,quote_literal(i) 147 ); 148 END LOOP; 149 END IF; 150 END IF; 151 EXECUTE FORMAT( 152 'DROP FUNCTION exchange.constrain_table_%s'::text 153 ,rec.name 154 ); 155 -- Foreign actions only apply if partitioning is off; use for foreign-key constraints that may span partitions 156 WHEN 'foreign' 157 THEN 158 IF (num_partitions IS NULL) 159 THEN 160 -- Add foreign constraints 161 EXECUTE FORMAT( 162 'SELECT exchange.foreign_table_%s (%s)'::text 163 ,rec.name 164 ,NULL 165 ); 166 END IF; 167 EXECUTE FORMAT( 168 'DROP FUNCTION exchange.foreign_table_%s'::text 169 ,rec.name 170 ); 171 -- "master" actions only apply to the master table 172 WHEN 'master' 173 THEN 174 EXECUTE FORMAT( 175 'SELECT exchange.master_table_%s ()'::text 176 ,rec.name 177 ); 178 EXECUTE FORMAT( 179 'DROP FUNCTION exchange.master_table_%s'::text 180 ,rec.name 181 ); 182 ELSE 183 ASSERT FALSE, 'unsupported action type: ' || rec.action; 184 END CASE; -- END CASE (rec.action) 185 -- Mark as finished 186 UPDATE exchange.exchange_tables 187 SET finished=TRUE 188 WHERE table_serial_id=rec.table_serial_id; 189 END LOOP; -- create/alter/drop actions 190 END $$; 191 192 COMMENT ON FUNCTION exchange_do_create_tables 193 IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.';