commit 5686dd370a535cd2fc4d03ed3fee2c590343cc6f parent 34e461f8233f416b10ca55a10951e799a6f74943 Author: Christian Grothoff <christian@grothoff.org> Date: Mon, 3 Jun 2024 21:14:03 +0200 fix DB init for v5 Diffstat:
18 files changed, 428 insertions(+), 330 deletions(-)
diff --git a/src/exchangedb/0002-reserves_in.sql b/src/exchangedb/0002-reserves_in.sql @@ -93,7 +93,7 @@ BEGIN EXECUTE FORMAT ( 'COMMENT ON INDEX ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx ' 'IS ' || quote_literal ('for pg_select_reserves_in_above_serial_id_by_account') || ';' - ); + ); END $$; diff --git a/src/exchangedb/0005-aml_history.sql b/src/exchangedb/0005-aml_history.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE FUNCTION create_table_aml_history5( +CREATE FUNCTION alter_table_aml_history5( IN partition_suffix TEXT DEFAULT NULL ) RETURNS VOID @@ -70,7 +70,7 @@ INSERT INTO exchange_tables VALUES ('aml_history5' ,'exchange-0005' - ,'create' + ,'alter' ,TRUE ,FALSE), ('aml_history5' diff --git a/src/exchangedb/0005-aml_status.sql b/src/exchangedb/0005-aml_status.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE FUNCTION create_table_aml_status5( +CREATE FUNCTION alter_table_aml_status5( IN partition_suffix TEXT DEFAULT NULL ) RETURNS VOID @@ -41,6 +41,6 @@ INSERT INTO exchange_tables VALUES ('aml_status5' ,'exchange-0005' - ,'create' + ,'alter' ,TRUE ,FALSE); diff --git a/src/exchangedb/0005-kyc_attributes.sql b/src/exchangedb/0005-kyc_attributes.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE FUNCTION create_table_kyc_attributes5( +CREATE FUNCTION alter_table_kyc_attributes5( IN partition_suffix TEXT DEFAULT NULL ) RETURNS VOID @@ -50,7 +50,7 @@ BEGIN END $$; -CREATE OR REPLACE FUNCTION foreign_table_kyc_attributes5() +CREATE FUNCTION foreign_table_kyc_attributes5() RETURNS void LANGUAGE plpgsql AS $$ @@ -75,7 +75,7 @@ INSERT INTO exchange_tables VALUES ('kyc_attributes5' ,'exchange-0005' - ,'create' + ,'alter' ,TRUE ,FALSE), ('kyc_attributes5' diff --git a/src/exchangedb/0005-legitimization_measures.sql b/src/exchangedb/0005-legitimization_measures.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE FUNCTION create_table_legitimization_measures5( +CREATE FUNCTION create_table_legitimization_measures( IN partition_suffix TEXT DEFAULT NULL ) RETURNS VOID @@ -31,7 +31,7 @@ BEGIN ',is_finished BOOL NOT NULL DEFAULT(FALSE)' ') %s ;' ,'legitimization_measures' - ,'PARTITION BY HASH (target_token)' + ,'PARTITION BY HASH (access_token)' ,partition_suffix ); PERFORM comment_partitioned_table( @@ -80,7 +80,7 @@ $$; -CREATE FUNCTION constrain_table_legitimization_measures5( +CREATE FUNCTION constrain_table_legitimization_measures( IN partition_suffix TEXT ) RETURNS VOID @@ -106,7 +106,7 @@ END $$; -CREATE FUNCTION foreign_table_legitimization_measures5() +CREATE FUNCTION foreign_table_legitimization_measures() RETURNS void LANGUAGE plpgsql AS $$ @@ -130,17 +130,17 @@ INSERT INTO exchange_tables ,partitioned ,by_range) VALUES - ('legitimization_measures5' + ('legitimization_measures' ,'exchange-0005' ,'create' ,TRUE ,FALSE), - ('legitimization_measures5' + ('legitimization_measures' ,'exchange-0005' ,'constrain' ,TRUE ,FALSE), - ('legitimization_measures5' + ('legitimization_measures' ,'exchange-0005' ,'foreign' ,TRUE diff --git a/src/exchangedb/0005-legitimization_outcomes.sql b/src/exchangedb/0005-legitimization_outcomes.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE FUNCTION create_table_legitimization_outcomes5( +CREATE FUNCTION create_table_legitimization_outcomes( IN partition_suffix TEXT DEFAULT NULL ) RETURNS VOID @@ -93,7 +93,7 @@ END $$; -CREATE FUNCTION constrain_table_legitimization_outcomes5( +CREATE FUNCTION constrain_table_legitimization_outcomes( IN partition_suffix TEXT ) RETURNS VOID @@ -115,7 +115,7 @@ END $$; -CREATE FUNCTION foreign_table_legitimization_outcomes5() +CREATE FUNCTION foreign_table_legitimization_outcomes() RETURNS void LANGUAGE plpgsql AS $$ @@ -144,17 +144,17 @@ INSERT INTO exchange_tables ,partitioned ,by_range) VALUES - ('legitimization_outcomes5' + ('legitimization_outcomes' ,'exchange-0005' ,'create' ,TRUE ,FALSE), - ('legitimization_outcomes5' + ('legitimization_outcomes' ,'exchange-0005' ,'constrain' ,TRUE ,FALSE), - ('legitimization_outcomes5' + ('legitimization_outcomes' ,'exchange-0005' ,'foreign' ,TRUE diff --git a/src/exchangedb/0005-legitimization_processes.sql b/src/exchangedb/0005-legitimization_processes.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE FUNCTION create_table_legitimization_processes5( +CREATE FUNCTION alter_table_legitimization_processes5( IN shard_suffix TEXT DEFAULT NULL ) RETURNS VOID @@ -22,15 +22,22 @@ LANGUAGE plpgsql AS $$ BEGIN PERFORM create_partitioned_table( - 'ALTER TABLE %I' + 'ALTER TABLE legitimization_processes' ' ADD COLUMN legitimization_measure_serial_id BIGINT' - ',RENAME COLUMN provider_section TO provider_name' ',ADD COLUMN measure_index INT4' ';' ,'legitimization_processes' ,'' ,shard_suffix ); + PERFORM create_partitioned_table( + 'ALTER TABLE %I' + ' RENAME provider_section TO provider_name' + ';' + ,'legitimization_processes' + ,'' + ,shard_suffix + ); PERFORM comment_partitioned_column( 'measure that enabled this setup, NULL if client voluntarily initiated the process' ,'legitimization_measure_serial_id' @@ -73,7 +80,7 @@ INSERT INTO exchange_tables VALUES ('legitimization_processes5' ,'exchange-0005' - ,'create' + ,'alter' ,TRUE ,FALSE), ('legitimization_processes5' diff --git a/src/exchangedb/0005-legitimization_requirements.sql b/src/exchangedb/0005-legitimization_requirements.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE FUNCTION create_table_legitimization_requirements5( +CREATE FUNCTION alter_table_legitimization_requirements5( IN partition_suffix TEXT DEFAULT NULL ) RETURNS VOID @@ -40,6 +40,6 @@ INSERT INTO exchange_tables VALUES ('legitimization_requirements5' ,'exchange-0005' - ,'create' + ,'alter' ,TRUE ,FALSE); diff --git a/src/exchangedb/0005-wire_targets.sql b/src/exchangedb/0005-wire_targets.sql @@ -25,37 +25,53 @@ RETURNS BYTEA LANGUAGE 'sql' VOLATILE; -CREATE FUNCTION create_table_wire_targets5( - IN partition_suffix TEXT DEFAULT NULL -) +CREATE FUNCTION alter_table_wire_targets5() RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - PERFORM create_partitioned_table( - 'ALTER TABLE %I' - ' ADD COLUMN access_token BYTEA UNIQUE CHECK(LENGTH(access_token)=32) DEFAULT random_bytea(32)' - ',ADD COLUMN target_pub BYTEA CHECK(LENGTH(target_pub)=32) DEFAULT NULL' + EXECUTE FORMAT ( + 'ALTER TABLE wire_targets' + ' ADD COLUMN access_token BYTEA CHECK(LENGTH(access_token)=32)' + ' DEFAULT random_bytea(32)' + ',ADD COLUMN target_pub BYTEA CHECK(LENGTH(target_pub)=32)' + ' DEFAULT NULL' ';' - ,'wire_targets' - ,partition_suffix ); PERFORM comment_partitioned_column( 'high-entropy random value that is used as a bearer token used to authenticate access to the KYC SPA and its state (without requiring a signature)' ,'access_token' ,'wire_targets' - ,partition_suffix + ,NULL ); PERFORM comment_partitioned_column( 'Public key of a merchant instance or reserve to authenticate access; NULL if KYC is not allowed for the account (if there was no incoming KYC wire transfer yet); updated, thus NOT available to the auditor' ,'target_pub' ,'wire_targets' - ,partition_suffix + ,NULL ); END $$; +CREATE FUNCTION constrain_table_wire_targets5( + IN partition_suffix TEXT +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'wire_targets'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_wire_target_access_token_unique' + ' UNIQUE (access_token)' + ); +END +$$; + INSERT INTO exchange_tables (name ,version @@ -65,6 +81,11 @@ INSERT INTO exchange_tables VALUES ('wire_targets5' ,'exchange-0005' - ,'create' + ,'alter' + ,TRUE + ,FALSE), + ('wire_targets5' + ,'exchange-0005' + ,'constrain' ,TRUE ,FALSE); diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql @@ -42,7 +42,7 @@ COMMENT ON COLUMN exchange_tables.name COMMENT ON COLUMN exchange_tables.version IS 'Version of the DB in which the given action happened'; COMMENT ON COLUMN exchange_tables.action - IS 'Action to take on the table (e.g. create, constrain, or foreign). Create is done for the master table and each partition; constrain is only for partitions or for master if there are no partitions; master only on master (takes no argument); foreign only on master if there are no partitions.'; + IS 'Action to take on the table (e.g. create, alter, constrain, or foreign). Create is done for the master table and each partition; constrain is only for partitions or for master if there are no partitions; master only on master (takes no argument); foreign only on master if there are no partitions.'; COMMENT ON COLUMN exchange_tables.partitioned IS 'TRUE if the table is partitioned'; COMMENT ON COLUMN exchange_tables.by_range @@ -51,246 +51,4 @@ COMMENT ON COLUMN exchange_tables.finished IS 'TRUE if the respective migration has been run'; -CREATE FUNCTION create_partitioned_table( - IN table_definition TEXT -- SQL template for table creation - ,IN table_name TEXT -- base name of the table - ,IN main_table_partition_str TEXT -- declaration for how to partition the table - ,IN partition_suffix TEXT DEFAULT NULL -- NULL: no partitioning, 0: yes partitioning, no sharding, >0: sharding -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - IF (partition_suffix IS NULL) - THEN - -- no partitioning, disable option - main_table_partition_str = ''; - ELSE - IF (partition_suffix::int > 0) - THEN - -- sharding, add shard name - table_name=table_name || '_' || partition_suffix; - END IF; - END IF; - EXECUTE FORMAT( - table_definition, - table_name, - main_table_partition_str - ); -END $$; - -COMMENT ON FUNCTION create_partitioned_table - IS 'Generic function to create a table that is partitioned or sharded.'; - - -CREATE FUNCTION comment_partitioned_table( - IN table_comment TEXT - ,IN table_name TEXT - ,IN partition_suffix TEXT DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - IF ( (partition_suffix IS NOT NULL) AND - (partition_suffix::int > 0) ) - THEN - -- sharding, add shard name - table_name=table_name || '_' || partition_suffix; - END IF; - EXECUTE FORMAT( - 'COMMENT ON TABLE %s IS %s' - ,table_name - ,quote_literal(table_comment) - ); -END $$; - -COMMENT ON FUNCTION comment_partitioned_table - IS 'Generic function to create a comment on table that is partitioned.'; - - -CREATE FUNCTION comment_partitioned_column( - IN table_comment TEXT - ,IN column_name TEXT - ,IN table_name TEXT - ,IN partition_suffix TEXT DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - IF ( (partition_suffix IS NOT NULL) AND - (partition_suffix::int > 0) ) - THEN - -- sharding, add shard name - table_name=table_name || '_' || partition_suffix; - END IF; - EXECUTE FORMAT( - 'COMMENT ON COLUMN %s.%s IS %s' - ,table_name - ,column_name - ,quote_literal(table_comment) - ); -END $$; - -COMMENT ON FUNCTION comment_partitioned_column - IS 'Generic function to create a comment on column of a table that is partitioned.'; - - ---------------------------------------------------------------------------- --- Main DB setup loop ---------------------------------------------------------------------------- - -CREATE FUNCTION do_create_tables( - num_partitions INTEGER --- NULL: no partitions, add foreign constraints --- 0: no partitions, no foreign constraints --- 1: only 1 default partition --- > 1: normal partitions -) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -DECLARE - tc CURSOR FOR - SELECT table_serial_id - ,name - ,action - ,partitioned - ,by_range - FROM exchange.exchange_tables - WHERE NOT finished - ORDER BY table_serial_id ASC; -BEGIN - FOR rec IN tc - LOOP - CASE rec.action - -- "create" actions apply to master and partitions - WHEN 'create' - THEN - IF (rec.partitioned AND - (num_partitions IS NOT NULL)) - THEN - -- Create master table with partitioning. - EXECUTE FORMAT( - 'SELECT exchange.%s_table_%s (%s)'::text - ,rec.action - ,rec.name - ,quote_literal('0') - ); - IF (rec.by_range OR - (num_partitions = 0)) - THEN - -- Create default partition. - IF (rec.by_range) - THEN - -- Range partition - EXECUTE FORMAT( - 'CREATE TABLE exchange.%s_default' - ' PARTITION OF %s' - ' DEFAULT' - ,rec.name - ,rec.name - ); - ELSE - -- Hash partition - EXECUTE FORMAT( - 'CREATE TABLE exchange.%s_default' - ' PARTITION OF %s' - ' FOR VALUES WITH (MODULUS 1, REMAINDER 0)' - ,rec.name - ,rec.name - ); - END IF; - ELSE - FOR i IN 1..num_partitions LOOP - -- Create num_partitions - EXECUTE FORMAT( - 'CREATE TABLE exchange.%I' - ' PARTITION OF %I' - ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)' - ,rec.name || '_' || i - ,rec.name - ,num_partitions - ,i-1 - ); - END LOOP; - END IF; - ELSE - -- Only create master table. No partitions. - EXECUTE FORMAT( - 'SELECT exchange.%s_table_%s ()'::text - ,rec.action - ,rec.name - ); - END IF; - -- Constrain action apply to master OR each partition - WHEN 'constrain' - THEN - ASSERT rec.partitioned, 'constrain action only applies to partitioned tables'; - IF (num_partitions IS NULL) - THEN - -- Constrain master table - EXECUTE FORMAT( - 'SELECT exchange.%s_table_%s (NULL)'::text - ,rec.action - ,rec.name - ); - ELSE - IF ( (num_partitions = 0) OR - (rec.by_range) ) - THEN - -- Constrain default table - EXECUTE FORMAT( - 'SELECT exchange.%s_table_%s (%s)'::text - ,rec.action - ,rec.name - ,quote_literal('default') - ); - ELSE - -- Constrain each partition - FOR i IN 1..num_partitions LOOP - EXECUTE FORMAT( - 'SELECT exchange.%s_table_%s (%s)'::text - ,rec.action - ,rec.name - ,quote_literal(i) - ); - END LOOP; - END IF; - END IF; - -- Foreign actions only apply if partitioning is off - WHEN 'foreign' - THEN - IF (num_partitions IS NULL) - THEN - -- Add foreign constraints - EXECUTE FORMAT( - 'SELECT exchange.%s_table_%s (%s)'::text - ,rec.action - ,rec.name - ,NULL - ); - END IF; - WHEN 'master' - THEN - EXECUTE FORMAT( - 'SELECT exchange.%s_table_%s ()'::text - ,rec.action - ,rec.name - ); - ELSE - ASSERT FALSE, 'unsupported action type: ' || rec.action; - END CASE; -- END CASE (rec.action) - -- Mark as finished - UPDATE exchange.exchange_tables - SET finished=TRUE - WHERE table_serial_id=rec.table_serial_id; - END LOOP; -- create/alter/drop actions -END $$; - -COMMENT ON FUNCTION do_create_tables - IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.'; - - COMMIT; diff --git a/src/exchangedb/exchange_do_comment_partitioned_column.sql b/src/exchangedb/exchange_do_comment_partitioned_column.sql @@ -0,0 +1,42 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +CREATE OR REPLACE FUNCTION comment_partitioned_column( + IN table_comment TEXT + ,IN column_name TEXT + ,IN table_name TEXT + ,IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + IF ( (partition_suffix IS NOT NULL) AND + (partition_suffix::int > 0) ) + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; + END IF; + EXECUTE FORMAT( + 'COMMENT ON COLUMN %s.%s IS %s' + ,table_name + ,column_name + ,quote_literal(table_comment) + ); +END $$; + +COMMENT ON FUNCTION comment_partitioned_column + IS 'Generic function to create a comment on column of a table that is partitioned.'; diff --git a/src/exchangedb/exchange_do_comment_partitioned_table.sql b/src/exchangedb/exchange_do_comment_partitioned_table.sql @@ -0,0 +1,40 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +CREATE OR REPLACE FUNCTION comment_partitioned_table( + IN table_comment TEXT + ,IN table_name TEXT + ,IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + IF ( (partition_suffix IS NOT NULL) AND + (partition_suffix::int > 0) ) + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; + END IF; + EXECUTE FORMAT( + 'COMMENT ON TABLE %s IS %s' + ,table_name + ,quote_literal(table_comment) + ); +END $$; + +COMMENT ON FUNCTION comment_partitioned_table + IS 'Generic function to create a comment on table that is partitioned.'; diff --git a/src/exchangedb/exchange_do_create_partitioned_table.sql b/src/exchangedb/exchange_do_create_partitioned_table.sql @@ -0,0 +1,47 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + + +CREATE OR REPLACE FUNCTION create_partitioned_table( + IN table_definition TEXT -- SQL template for table creation + ,IN table_name TEXT -- base name of the table + ,IN main_table_partition_str TEXT -- declaration for how to partition the table + ,IN partition_suffix TEXT DEFAULT NULL -- NULL: no partitioning, 0: yes partitioning, no sharding, >0: sharding +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + IF (partition_suffix IS NULL) + THEN + -- no partitioning, disable option + main_table_partition_str = ''; + ELSE + IF (partition_suffix::int > 0) + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; + END IF; + END IF; + EXECUTE FORMAT( + table_definition, + table_name, + main_table_partition_str + ); +END $$; + +COMMENT ON FUNCTION create_partitioned_table + IS 'Generic function to create a table that is partitioned or sharded.'; diff --git a/src/exchangedb/exchange_do_create_tables.sql b/src/exchangedb/exchange_do_create_tables.sql @@ -0,0 +1,173 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +--------------------------------------------------------------------------- +-- Main DB setup loop +--------------------------------------------------------------------------- + + +CREATE OR REPLACE FUNCTION do_create_tables( + num_partitions INTEGER +-- NULL: no partitions, add foreign constraints +-- 0: no partitions, no foreign constraints +-- 1: only 1 default partition +-- > 1: normal partitions +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT table_serial_id + ,name + ,action + ,partitioned + ,by_range + FROM exchange.exchange_tables + WHERE NOT finished + ORDER BY table_serial_id ASC; +BEGIN + FOR rec IN tc + LOOP + CASE rec.action + -- "create" actions apply to master and partitions + WHEN 'create' + THEN + IF (rec.partitioned AND + (num_partitions IS NOT NULL)) + THEN + -- Create master table with partitioning. + EXECUTE FORMAT( + 'SELECT exchange.create_table_%s (%s)'::text + ,rec.name + ,quote_literal('0') + ); + IF (rec.by_range OR + (num_partitions = 0)) + THEN + -- Create default partition. + IF (rec.by_range) + THEN + -- Range partition + EXECUTE FORMAT( + 'CREATE TABLE exchange.%s_default' + ' PARTITION OF %s' + ' DEFAULT' + ,rec.name + ,rec.name + ); + ELSE + -- Hash partition + EXECUTE FORMAT( + 'CREATE TABLE exchange.%s_default' + ' PARTITION OF %s' + ' FOR VALUES WITH (MODULUS 1, REMAINDER 0)' + ,rec.name + ,rec.name + ); + END IF; + ELSE + FOR i IN 1..num_partitions LOOP + -- Create num_partitions + EXECUTE FORMAT( + 'CREATE TABLE exchange.%I' + ' PARTITION OF %I' + ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)' + ,rec.name || '_' || i + ,rec.name + ,num_partitions + ,i-1 + ); + END LOOP; + END IF; + ELSE + -- Only create master table. No partitions. + EXECUTE FORMAT( + 'SELECT exchange.create_table_%s ()'::text + ,rec.name + ); + END IF; + + -- "alter" actions apply to master and partitions + WHEN 'alter' + THEN + -- Alter master table. + EXECUTE FORMAT( + 'SELECT exchange.alter_table_%s ()'::text + ,rec.name + ); + -- Constrain action apply to master OR each partition + WHEN 'constrain' + THEN + ASSERT rec.partitioned, 'constrain action only applies to partitioned tables'; + IF (num_partitions IS NULL) + THEN + -- Constrain master table + EXECUTE FORMAT( + 'SELECT exchange.constrain_table_%s (NULL)'::text + ,rec.name + ); + ELSE + IF ( (num_partitions = 0) OR + (rec.by_range) ) + THEN + -- Constrain default table + EXECUTE FORMAT( + 'SELECT exchange.constrain_table_%s (%s)'::text + ,rec.name + ,quote_literal('default') + ); + ELSE + -- Constrain each partition + FOR i IN 1..num_partitions LOOP + EXECUTE FORMAT( + 'SELECT exchange.constrain_table_%s (%s)'::text + ,rec.name + ,quote_literal(i) + ); + END LOOP; + END IF; + END IF; + -- Foreign actions only apply if partitioning is off + WHEN 'foreign' + THEN + IF (num_partitions IS NULL) + THEN + -- Add foreign constraints + EXECUTE FORMAT( + 'SELECT exchange.foreign_table_%s (%s)'::text + ,rec.name + ,NULL + ); + END IF; + WHEN 'master' + THEN + EXECUTE FORMAT( + 'SELECT exchange.master_table_%s ()'::text + ,rec.name + ); + ELSE + ASSERT FALSE, 'unsupported action type: ' || rec.action; + END CASE; -- END CASE (rec.action) + -- Mark as finished + UPDATE exchange.exchange_tables + SET finished=TRUE + WHERE table_serial_id=rec.table_serial_id; + END LOOP; -- create/alter/drop actions +END $$; + +COMMENT ON FUNCTION do_create_tables + IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.'; diff --git a/src/exchangedb/exchange_do_insert_aml_decision.sql b/src/exchangedb/exchange_do_insert_aml_decision.sql @@ -82,7 +82,7 @@ INSERT INTO legitimization_outcomes (in_h_payto ,in_decision_time ,in_expiration_time - ,in_properties, + ,in_properties ,in_to_investigate ,in_new_rules ) diff --git a/src/exchangedb/pg_create_tables.c b/src/exchangedb/pg_create_tables.c @@ -33,20 +33,13 @@ TEH_PG_create_tables (void *cls, { struct PostgresClosure *pg = cls; struct GNUNET_PQ_Context *conn; - enum GNUNET_GenericReturnValue ret = GNUNET_OK; + enum GNUNET_GenericReturnValue ret; struct GNUNET_PQ_QueryParam params[] = { support_partitions ? GNUNET_PQ_query_param_uint32 (&num_partitions) : GNUNET_PQ_query_param_null (), GNUNET_PQ_query_param_end }; - struct GNUNET_PQ_PreparedStatement ps[] = { - GNUNET_PQ_make_prepare ("create_tables", - "SELECT" - " exchange.do_create_tables" - " ($1);"), - GNUNET_PQ_PREPARED_STATEMENT_END - }; struct GNUNET_PQ_ExecuteStatement es[] = { GNUNET_PQ_make_try_execute ("SET search_path TO exchange;"), GNUNET_PQ_EXECUTE_STATEMENT_END @@ -56,17 +49,30 @@ TEH_PG_create_tables (void *cls, "exchangedb-postgres", "exchange-", es, - ps); + NULL); if (NULL == conn) return GNUNET_SYSERR; - if (0 > - GNUNET_PQ_eval_prepared_non_select (conn, - "create_tables", - params)) - ret = GNUNET_SYSERR; + ret = GNUNET_PQ_exec_sql (conn, + "procedures"); + GNUNET_break (GNUNET_OK == ret); if (GNUNET_OK == ret) - ret = GNUNET_PQ_exec_sql (conn, - "procedures"); + { + struct GNUNET_PQ_Context *tconn; + + tconn = pg->conn; + pg->conn = conn; + PREPARE (pg, + "create_tables", + "SELECT" + " exchange.do_create_tables" + " ($1::INTEGER);"); + pg->conn = tconn; + if (0 > + GNUNET_PQ_eval_prepared_non_select (conn, + "create_tables", + params)) + ret = GNUNET_SYSERR; + } GNUNET_PQ_disconnect (conn); return ret; } diff --git a/src/exchangedb/pg_helper.h b/src/exchangedb/pg_helper.h @@ -101,37 +101,37 @@ struct PostgresClosure * @param sql actual SQL text */ #define PREPARE(pg,name,sql) \ - do { \ - static struct { \ - unsigned long long cnt; \ - struct PostgresClosure *pg; \ - } preps[2]; /* 2 ctrs for taler-auditor-sync*/ \ - unsigned int off = 0; \ + do { \ + static struct { \ + unsigned long long cnt; \ + struct PostgresClosure *pg; \ + } preps[2]; /* 2 ctrs for taler-auditor-sync*/ \ + unsigned int off = 0; \ \ - while ( (NULL != preps[off].pg) && \ - (pg != preps[off].pg) && \ - (off < sizeof(preps) / sizeof(*preps)) ) \ - off++; \ - GNUNET_assert (off < \ - sizeof(preps) / sizeof(*preps)); \ - if (preps[off].cnt < pg->prep_gen) \ - { \ - struct GNUNET_PQ_PreparedStatement ps[] = { \ - GNUNET_PQ_make_prepare (name, sql), \ - GNUNET_PQ_PREPARED_STATEMENT_END \ - }; \ + while ( (NULL != preps[off].pg) && \ + (pg != preps[off].pg) && \ + (off < sizeof(preps) / sizeof(*preps)) ) \ + off++; \ + GNUNET_assert (off < \ + sizeof(preps) / sizeof(*preps)); \ + if (preps[off].cnt < pg->prep_gen) \ + { \ + struct GNUNET_PQ_PreparedStatement ps[] = { \ + GNUNET_PQ_make_prepare (name, sql), \ + GNUNET_PQ_PREPARED_STATEMENT_END \ + }; \ \ - if (GNUNET_OK != \ - GNUNET_PQ_prepare_statements (pg->conn, \ - ps)) \ - { \ - GNUNET_break (0); \ - return GNUNET_DB_STATUS_HARD_ERROR; \ - } \ - preps[off].pg = pg; \ - preps[off].cnt = pg->prep_gen; \ - } \ - } while (0) + if (GNUNET_OK != \ + GNUNET_PQ_prepare_statements (pg->conn, \ + ps)) \ + { \ + GNUNET_break (0); \ + return GNUNET_DB_STATUS_HARD_ERROR; \ + } \ + preps[off].pg = pg; \ + preps[off].cnt = pg->prep_gen; \ + } \ + } while (0) /** @@ -143,7 +143,7 @@ struct PostgresClosure */ #define TALER_PQ_RESULT_SPEC_AMOUNT(field, \ amountp) TALER_PQ_result_spec_amount ( \ - field,pg->currency,amountp) + field,pg->currency,amountp) #endif diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in @@ -1,6 +1,6 @@ -- -- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA +-- Copyright (C) 2014--2024 Taler Systems SA -- -- TALER is free software; you can redistribute it and/or modify it under the -- terms of the GNU General Public License as published by the Free Software @@ -18,6 +18,10 @@ BEGIN; SET search_path TO exchange; +#include "exchange_do_create_partitioned_table.sql" +#include "exchange_do_comment_partitioned_table.sql" +#include "exchange_do_comment_partitioned_column.sql" +#include "exchange_do_create_tables.sql" #include "exchange_do_amount_specific.sql" #include "exchange_do_batch_withdraw.sql" #include "exchange_do_batch_withdraw_insert.sql"