diff options
author | Christian Grothoff <christian@grothoff.org> | 2022-11-27 02:00:38 +0100 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2022-11-27 02:00:38 +0100 |
commit | be2c11a1797d8d16b86439a80a4f110f82bb5829 (patch) | |
tree | f0a950be0f80ced5ab68b6f65d11edb5dc09a3b5 /src/exchangedb/exchange-0001.sql | |
parent | 9580dd19c23e5591cc022dce717eca7bc745c5b0 (diff) | |
download | exchange-be2c11a1797d8d16b86439a80a4f110f82bb5829.tar.xz |
more sql refactoring
Diffstat (limited to 'src/exchangedb/exchange-0001.sql')
-rw-r--r-- | src/exchangedb/exchange-0001.sql | 828 |
1 files changed, 174 insertions, 654 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index 6998320ce..f7bf15f6a 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -23,7 +23,7 @@ SET search_path TO exchange; -- General procedures for DB setup --------------------------------------------------------------------------- -CREATE TABLE IF NOT EXISTS exchange_tables +CREATE TABLE exchange_tables (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY ,name VARCHAR NOT NULL ,version VARCHAR NOT NULL @@ -38,7 +38,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, alter, constrain, unconstrain, or drop)'; + IS 'Action to take on the table (e.g. create, alter, constrain, foreign, or drop). Create, alter and drop are done for master and partitions; constrain is only for partitions or for master if there are no partitions; 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 @@ -47,8 +47,7 @@ COMMENT ON COLUMN exchange_tables.finished IS 'TRUE if the respective migration has been run'; - -CREATE OR REPLACE FUNCTION create_partitioned_table( +CREATE FUNCTION create_partitioned_table( IN table_definition VARCHAR ,IN table_name VARCHAR ,IN main_table_partition_str VARCHAR -- Used only when it is the main table - we do not partition shard tables @@ -58,28 +57,83 @@ RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - IF shard_suffix IS NOT NULL THEN table_name=table_name || '_' || shard_suffix; main_table_partition_str = ''; 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.'; + + +CREATE FUNCTION comment_partitioned_table( + IN table_comment VARCHAR + ,IN table_name VARCHAR + ,IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + IF shard_suffix IS NOT NULL THEN + table_name=table_name || '_' || shard_suffix; + main_table_partition_str = ''; + END IF; + EXECUTE FORMAT( + COMMENT ON TABLE %s IS '%s' + ,table_name + ,table_comment + ); END $$; +COMMENT ON FUNCTION create_partitioned_table + IS 'Generic function to create a comment on table that is partitioned.'; +CREATE FUNCTION comment_partitioned_column( + IN table_comment VARCHAR + ,IN column_name VARCHAR + ,IN table_name VARCHAR + ,IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + IF shard_suffix IS NOT NULL THEN + table_name=table_name || '_' || shard_suffix; + main_table_partition_str = ''; + END IF; + EXECUTE FORMAT( + COMMENT ON COLUMN %s.%s IS '%s' + ,table_name + ,column_name + ,table_comment + ); +END +$$; +COMMENT ON FUNCTION create_partitioned_table + IS 'Generic function to create a comment on column of a table that is partitioned.'; -CREATE OR REPLACE FUNCTION create_tables( + + + +CREATE FUNCTION create_tables( num_partitions INTEGER - ,shard_domain VARCHAR +-- FIXME: not implemented like this, but likely good: +-- NULL: no partitions, add foreign constraints +-- 0: no partitions, no foreign constraints +-- 1: only 1 default partition +-- > 1: normal partitions ) RETURNS VOID LANGUAGE plpgsql @@ -92,8 +146,36 @@ DECLARE ,by_range FROM exchange_tables WHERE NOT finished + AND partitioned + AND (action='create' + OR action='alter' + OR action='drop') + ORDER BY table_serial_id ASC; +DECLARE + ta CURSOR FOR + SELECT table_serial_id + ,name + ,action + ,by_range + FROM exchange_tables + WHERE NOT finished + AND partitioned + AND action='constrain' + ORDER BY table_serial_id ASC; +DECLARE + tf CURSOR FOR + SELECT table_serial_id + ,name + ,action + ,by_range + FROM exchange_tables + WHERE NOT finished + AND partitioned + AND action='foreign' ORDER BY table_serial_id ASC; BEGIN + + -- run create/alter/drop actions FOR rec IN tc LOOP -- First create the master table, either @@ -101,6 +183,7 @@ BEGIN -- master and the 'default' partition IF IS NULL num_partitions THEN + -- No partitions at all. EXECUTE FORMAT( 'PERFORM %s_table_%s (%s)'::text ,rec.action @@ -108,676 +191,113 @@ BEGIN ,NULL ); ELSE + -- One default partition only. EXECUTE FORMAT( 'PERFORM %s_table_%s (%s)'::text ,rec.action ,rec.name ,0 ); - END IF - IF NOT NULL shard_domain + IF NOT IS NULL num_partitions THEN - -- FIXME: attach shards! - -- FIXME: how will migration work with shards!? - FOR i IN 1..num_partitions LOOP + IF rec.by_range + THEN + -- range partitions (only create default) + -- Create default partition. EXECUTE FORMAT( - 'PERFORM %s_XXX_%s (%s)'::text - ,rec.action + 'CREATE TABLE %s_default PARTITION OF %s DEFAULT' ,rec.name - ,i::varchar - ); - END LOOP; - ELSE - FOR i IN 1..num_partitions LOOP - EXECUTE FORMAT( - 'PERFORM %s_table_%s (%s)'::text - ,rec.action ,rec.name - ,i::varchar ); - END LOOP; - END IF + ELSE + -- hash partitions + IF 0=num_partitions + THEN + -- Create default partition. + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %s_default PARTITION OF %s FOR VALUES WITH (MODULUS 1, REMAINDER 0)' + ,rec.name + ,rec.name + ); + END IF + FOR i IN 1..num_partitions LOOP + -- Create num_partitions + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %I' + ' PARTITION OF %I' + ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)' + ,rec.name || '_' || i + ,rec.name + ,num_partitions + ,i-1 + ); + END LOOP; + END IF; + END IF; + UPDATE exchange_tables + SET finished=TRUE + WHERE table_serial_id=rec.table_serial_id; + END LOOP; -- create/alter/drop actions - IF 0 < num_partitions + -- Run constrain actions + FOR rec IN ta + LOOP + IF IS NULL num_partitions THEN - -- FIXME: detach default partition! + -- Constrain master + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,NULL + ); END IF + IF 0=num_partitions + THEN + -- constrain default partition + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,0 + ); + END IF + FOR i IN 1..num_partitions LOOP + -- constrain each partition + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,i::varchar + ); + END LOOP; UPDATE exchange_tables SET finished=TRUE WHERE table_serial_id=rec.table_serial_id; END LOOP; -END -$$; - -COMMENT ON FUNCTION create_tables - IS 'Creates all tables for the given number of partitions that need creating.'; - - --- This is run last by dbinit, if partitions exist --- or if 'force_create_partitions' is set (otherwise, --- we are not expected to create partitions if there --- is only 1). -CREATE OR REPLACE FUNCTION create_partitions( - IN part_idx INTEGER -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - tc CURSOR FOR - SELECT name - ,action - ,partitioned - ,by_range - FROM exchange_tables - WHERE version=in_version - AND partitioned - ORDER BY table_seria_id ASC; -BEGIN - FOR rec IN tc + -- run foreign actions + FOR rec IN tf LOOP - EXECUTE FORMAT( - 'PERFORM %s_table_%s (%s)' - ,rec.action - ,rec.name - ,shard_idx::varchar - ); + IF IS NULL num_partitions + THEN + -- Add foreign constraints + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,NULL + ); + END IF + UPDATE exchange_tables + SET finished=TRUE + WHERE table_serial_id=rec.table_serial_id; END LOOP; -END -$$; - -COMMENT ON FUNCTION create_partitions - IS 'Creates all partitions that need creating.'; - - - - -CREATE OR REPLACE FUNCTION drop_default_partitions_NG() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -DECLARE - tc CURSOR FOR - SELECT name - FROM exchange_tables - WHERE partitioned - AND NOT by_range; -BEGIN - RAISE NOTICE 'Dropping default tables of partitioned tables'; - FOR rec IN tc - LOOP - EXECUTE FORMAT ( - 'DROP TABLE IF EXISTS %s_default ;'::text, - rec.name; -END -$$; - -COMMENT ON FUNCTION drop_default_partitions - IS 'Drop all default partitions once other partitions are attached. - Might be needed in sharding too.'; - -CREATE OR REPLACE FUNCTION detach_default_partitions_NG() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -DECLARE - tc CURSOR FOR - SELECT name - FROM exchange_tables - WHERE partitioned - AND NOT by_range; -BEGIN - RAISE NOTICE 'Detaching all default table partitions'; - FOR rec IN tc - LOOP - EXECUTE FORMAT ( - 'ALTER TABLE IF EXISTS %s DETACH PARTITION %s_default;'::text, - rec.name, - rec.name - ); - END LOOP; END $$; -COMMENT ON FUNCTION detach_default_partitions - IS 'We need to drop default and create new one before deleting the default partitions - otherwise constraints get lost too. Might be needed in sharding too'; - - -CREATE OR REPLACE FUNCTION create_hash_partition_NG( - source_table_name VARCHAR - ,modulus INTEGER - ,partition_num INTEGER - ) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Creating partition %_%', source_table_name, partition_num; - - EXECUTE FORMAT( - 'CREATE TABLE IF NOT EXISTS %I ' - 'PARTITION OF %I ' - 'FOR VALUES WITH (MODULUS %s, REMAINDER %s)' - ,source_table_name || '_' || partition_num - ,source_table_name - ,modulus - ,partition_num-1 - ); - -END -$$; - - -CREATE OR REPLACE FUNCTION create_partitions_NG( - num_partitions INTEGER -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - tc CURSOR FOR - SELECT name - FROM exchange_tables - WHERE partitioned - AND NOT by_range; -DECLARE - i INTEGER; -BEGIN - PERFORM detach_default_partitions(); - FOR rec IN tc - LOOP - i := num_partitions - LOOP - - PERFORM create_hash_partition( - quote_literal (rec.name) - ,num_partitions - ,i - ); - - i=i-1; - EXIT WHEN i=0; - END LOOP; -- i = num_partitions ... 0 - END LOOP; -- for all partitioned tables - - PERFORM drop_default_partitions(); - -END -$$; - - --- OLD LOGIC: -------------------------------------------------------------------- -------------------------- Partitions ------------------------------ -------------------------------------------------------------------- - - -CREATE OR REPLACE FUNCTION create_range_partition( - source_table_name VARCHAR - ,partition_num INTEGER -) - RETURNS void - LANGUAGE plpgsql -AS $$ -BEGIN - RAISE NOTICE 'TODO'; -END -$$; - -CREATE OR REPLACE FUNCTION detach_default_partitions() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Detaching all default table partitions'; - - ALTER TABLE IF EXISTS wire_targets - DETACH PARTITION wire_targets_default; - - ALTER TABLE IF EXISTS reserves - DETACH PARTITION reserves_default; - - ALTER TABLE IF EXISTS reserves_in - DETACH PARTITION reserves_in_default; - - ALTER TABLE IF EXISTS reserves_close - DETACH PARTITION reserves_close_default; - - ALTER TABLE IF EXISTS history_requests - DETACH partition history_requests_default; - - ALTER TABLE IF EXISTS close_requests - DETACH partition close_requests_default; - - ALTER TABLE IF EXISTS reserves_open_requests - DETACH partition reserves_open_requests_default; - - ALTER TABLE IF EXISTS reserves_out - DETACH PARTITION reserves_out_default; - - ALTER TABLE IF EXISTS reserves_out_by_reserve - DETACH PARTITION reserves_out_by_reserve_default; - - ALTER TABLE IF EXISTS known_coins - DETACH PARTITION known_coins_default; - - ALTER TABLE IF EXISTS refresh_commitments - DETACH PARTITION refresh_commitments_default; - - ALTER TABLE IF EXISTS refresh_revealed_coins - DETACH PARTITION refresh_revealed_coins_default; - - ALTER TABLE IF EXISTS refresh_transfer_keys - DETACH PARTITION refresh_transfer_keys_default; - - ALTER TABLE IF EXISTS deposits - DETACH PARTITION deposits_default; - ---- TODO range partitioning --- ALTER TABLE IF EXISTS deposits_by_ready --- DETACH PARTITION deposits_by_ready_default; --- --- ALTER TABLE IF EXISTS deposits_for_matching --- DETACH PARTITION deposits_default_for_matching_default; - - ALTER TABLE IF EXISTS refunds - DETACH PARTITION refunds_default; - - ALTER TABLE IF EXISTS wire_out - DETACH PARTITION wire_out_default; - - ALTER TABLE IF EXISTS aggregation_transient - DETACH PARTITION aggregation_transient_default; - - ALTER TABLE IF EXISTS aggregation_tracking - DETACH PARTITION aggregation_tracking_default; - - ALTER TABLE IF EXISTS recoup - DETACH PARTITION recoup_default; - - ALTER TABLE IF EXISTS recoup_by_reserve - DETACH PARTITION recoup_by_reserve_default; - - ALTER TABLE IF EXISTS recoup_refresh - DETACH PARTITION recoup_refresh_default; - - ALTER TABLE IF EXISTS prewire - DETACH PARTITION prewire_default; - - ALTER TABLE IF EXISTS cs_nonce_locks - DETACH partition cs_nonce_locks_default; - - ALTER TABLE IF EXISTS purse_requests - DETACH partition purse_requests_default; - - ALTER TABLE IF EXISTS purse_decision - DETACH partition purse_decision_default; - - ALTER TABLE IF EXISTS purse_merges - DETACH partition purse_merges_default; - - ALTER TABLE IF EXISTS account_merges - DETACH partition account_merges_default; - - ALTER TABLE IF EXISTS contracts - DETACH partition contracts_default; - - ALTER TABLE IF EXISTS purse_deposits - DETACH partition purse_deposits_default; - - ALTER TABLE IF EXISTS wad_out_entries - DETACH partition wad_out_entries_default; - - ALTER TABLE IF EXISTS wads_in - DETACH partition wads_in_default; - - ALTER TABLE IF EXISTS wad_in_entries - DETACH partition wad_in_entries_default; -END -$$; - -COMMENT ON FUNCTION detach_default_partitions - IS 'We need to drop default and create new one before deleting the default partitions - otherwise constraints get lost too. Might be needed in sharding too'; - - -CREATE OR REPLACE FUNCTION drop_default_partitions() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Dropping default table partitions'; - - DROP TABLE IF EXISTS wire_targets_default; - DROP TABLE IF EXISTS reserves_default; - DROP TABLE IF EXISTS reserves_in_default; - DROP TABLE IF EXISTS reserves_close_default; - DROP TABLE IF EXISTS reserves_open_requests_default; - DROP TABLE IF EXISTS history_requests_default; - DROP TABLE IF EXISTS close_requests_default; - - DROP TABLE IF EXISTS reserves_out_default; - DROP TABLE IF EXISTS reserves_out_by_reserve_default; - DROP TABLE IF EXISTS known_coins_default; - DROP TABLE IF EXISTS refresh_commitments_default; - DROP TABLE IF EXISTS refresh_revealed_coins_default; - DROP TABLE IF EXISTS refresh_transfer_keys_default; - DROP TABLE IF EXISTS deposits_default; ---DROP TABLE IF EXISTS deposits_by_ready_default; ---DROP TABLE IF EXISTS deposits_for_matching_default; - DROP TABLE IF EXISTS refunds_default; - DROP TABLE IF EXISTS wire_out_default; - DROP TABLE IF EXISTS aggregation_transient_default; - DROP TABLE IF EXISTS aggregation_tracking_default; - DROP TABLE IF EXISTS recoup_default; - DROP TABLE IF EXISTS recoup_by_reserve_default; - DROP TABLE IF EXISTS recoup_refresh_default; - DROP TABLE IF EXISTS prewire_default; - DROP TABLE IF EXISTS cs_nonce_locks_default; - - DROP TABLE IF EXISTS purse_requests_default; - DROP TABLE IF EXISTS purse_decision_default; - DROP TABLE IF EXISTS purse_merges_default; - DROP TABLE IF EXISTS account_merges_default; - DROP TABLE IF EXISTS purse_deposits_default; - DROP TABLE IF EXISTS contracts_default; - - DROP TABLE IF EXISTS wad_out_entries_default; - DROP TABLE IF EXISTS wads_in_default; - DROP TABLE IF EXISTS wad_in_entries_default; - -END -$$; - -COMMENT ON FUNCTION drop_default_partitions - IS 'Drop all default partitions once other partitions are attached. - Might be needed in sharding too.'; - -CREATE OR REPLACE FUNCTION create_partitions( - num_partitions INTEGER -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - modulus INTEGER; -BEGIN - - modulus := num_partitions; - - PERFORM detach_default_partitions(); - - LOOP - - PERFORM create_hash_partition( - 'wire_targets' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'reserves' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'reserves_in' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'reserves_close' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'reserves_out' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'reserves_out_by_reserve' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'known_coins' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'refresh_commitments' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'refresh_revealed_coins' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'refresh_transfer_keys' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'deposits' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_deposits_partition(num_partitions::varchar); - --- TODO: dynamically (!) creating/deleting deposits partitions: --- create new partitions 'as needed', drop old ones once the aggregator has made --- them empty; as 'new' deposits will always have deadlines in the future, this --- would basically guarantee no conflict between aggregator and exchange service! --- SEE also: https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/ --- (article is slightly wrong, as this works:) ---CREATE TABLE tab ( --- id bigint GENERATED ALWAYS AS IDENTITY, --- ts timestamp NOT NULL, --- data text --- PARTITION BY LIST ((ts::date)); --- CREATE TABLE tab_def PARTITION OF tab DEFAULT; --- BEGIN --- CREATE TABLE tab_part2 (LIKE tab); --- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo'); --- alter table tab attach partition tab_part2 for values in ('2022-03-21'); --- commit; --- Naturally, to ensure this is actually 100% conflict-free, we'd --- need to create tables at the granularity of the wire/refund deadlines; --- that is right now configurable via AGGREGATOR_SHIFT option. - --- FIXME: range partitioning --- PERFORM create_range_partition( --- 'deposits_by_ready' --- ,modulus --- ,num_partitions --- ); --- --- PERFORM create_range_partition( --- 'deposits_for_matching' --- ,modulus --- ,num_partitions --- ); - - PERFORM create_hash_partition( - 'refunds' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'wire_out' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'aggregation_transient' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'aggregation_tracking' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'recoup' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'recoup_by_reserve' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'recoup_refresh' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'prewire' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'cs_nonce_locks' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); - - - PERFORM create_hash_partition( - 'close_requests' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'reserves_open_requests' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_open_request_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'history_requests' - ,modulus - ,num_partitions - ); - - - ---------------- P2P ---------------------- - - PERFORM create_hash_partition( - 'purse_requests' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_purse_requests_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'purse_decision' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_purse_decision_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'purse_merges' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_purse_merges_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'account_merges' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_account_merges_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'contracts' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_contracts_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'purse_deposits' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_purse_deposits_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'wad_out_entries' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wad_out_entries_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'wads_in' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wads_in_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'wad_in_entries' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wad_in_entries_partition(num_partitions::varchar); - - num_partitions=num_partitions-1; - EXIT WHEN num_partitions=0; - - END LOOP; - - PERFORM drop_default_partitions(); - -END -$$; +COMMENT ON FUNCTION create_tables + IS 'Creates all tables for the given number of partitions that need creating.'; |