diff options
Diffstat (limited to 'src/exchangedb/0002-reserves_out.sql')
| -rw-r--r-- | src/exchangedb/0002-reserves_out.sql | 156 |
1 files changed, 124 insertions, 32 deletions
diff --git a/src/exchangedb/0002-reserves_out.sql b/src/exchangedb/0002-reserves_out.sql index e2aed930..52567289 100644 --- a/src/exchangedb/0002-reserves_out.sql +++ b/src/exchangedb/0002-reserves_out.sql @@ -14,8 +14,8 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_reserves_out( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_reserves_out( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -23,14 +23,13 @@ AS $$ DECLARE table_name VARCHAR default 'reserves_out'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + 'CREATE TABLE %I' + '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' - ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)' + ',denominations_serial INT8 NOT NULL' ',denom_sig BYTEA NOT NULL' - ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' + ',reserve_uuid INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' ',execution_date INT8 NOT NULL' ',amount_with_fee_val INT8 NOT NULL' @@ -38,19 +37,47 @@ BEGIN ') %s ;' ,'reserves_out' ,'PARTITION BY HASH (h_blind_ev)' - ,shard_suffix + ,partition_suffix + ); + PERFORM comment_partitioned_table ( + 'Withdraw operations performed on reserves.' + ,'reserves_out' + ,partition_suffix + ); + PERFORM comment_partitioned_column ( + 'Hash of the blinded coin, used as primary key here so that broken clients that use a non-random coin or blinding factor fail to withdraw (otherwise they would fail on deposit when the coin is not unique there).' + ,'h_blind_ev' + ,'reserves_out' + ,partition_suffix + ); + PERFORM comment_partitioned_column ( + 'We do not CASCADE ON DELETE for the foreign constrain here, as we may keep the denomination data alive' + ,'denominations_serial' + ,'reserves_out' + ,partition_suffix ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_reserves_out( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_out'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index ' - 'ON ' || table_name || ' ' - '(reserve_out_serial_id);' + 'ALTER TABLE ' || table_name || ' ' + 'ADD CONSTRAINT ' || table_name || '_reserve_out_serial_id_key ' + 'UNIQUE (reserve_out_serial_id)' ); -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well??? EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'CREATE INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index ' 'ON ' || table_name || ' ' '(reserve_uuid, execution_date);' ); @@ -58,29 +85,30 @@ BEGIN 'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index ' 'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';' ); - END $$; - -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( - IN partition_suffix VARCHAR -) +CREATE FUNCTION foreign_table_reserves_out() RETURNS void LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR default 'reserves_out'; BEGIN EXECUTE FORMAT ( - 'ALTER TABLE reserves_out_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key ' - 'UNIQUE (reserve_out_serial_id)' + 'ALTER TABLE ' || table_name || ' ' + 'ADD CONSTRAINT ' || table_name || '_foreign_denom ' + 'REFERENCES denominations (denominations_serial)' + 'ADD CONSTRAINT ' || table_name || '_foreign_reserve ' + 'REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' ); END $$; -CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve( - IN shard_suffix VARCHAR DEFAULT NULL + +CREATE FUNCTION create_table_reserves_out_by_reserve( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -88,29 +116,78 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'reserves_out_by_reserve'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' + 'CREATE TABLE %I' '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)' ') %s ' ,table_name ,'PARTITION BY HASH (reserve_uuid)' - ,shard_suffix + ,partition_suffix ); - - table_name = concat_ws('_', table_name, shard_suffix); - + PERFORM comment_partitioned_column ( + 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.' + ,table_name + ,partition_suffix + ); + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'CREATE INDEX ' || table_name || '_main_index ' 'ON ' || table_name || ' ' '(reserve_uuid);' ); - END $$; +CREATE FUNCTION reserves_out_by_reserve_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + INSERT INTO exchange.reserves_out_by_reserve + (reserve_uuid + ,h_blind_ev) + VALUES + (NEW.reserve_uuid + ,NEW.h_blind_ev); + RETURN NEW; +END $$; +COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger() + IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.'; + + +CREATE FUNCTION reserves_out_by_reserve_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + DELETE FROM exchange.reserves_out_by_reserve + WHERE reserve_uuid = OLD.reserve_uuid; + RETURN OLD; +END $$; +COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger() + IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.'; + + +CREATE FUNCTION master_table_reserves_out() +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + CREATE TRIGGER reserves_out_on_insert + AFTER INSERT + ON reserves_out + FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger(); + CREATE TRIGGER reserves_out_on_delete + AFTER DELETE + ON reserves_out + FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger(); +END $$; +COMMENT ON FUNCTION master_table_reserves_out() + IS 'Setup triggers to replicate reserve_out into reserve_out_by_reserve.'; + + INSERT INTO exchange_tables (name ,version @@ -123,8 +200,23 @@ INSERT INTO exchange_tables ,'create' ,TRUE ,FALSE), - ('reserves_out_by_reserve' -- FIXME: do like this? + ('reserves_out' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('reserves_out' + ,'exchange-0002' + ,'foreign' + ,TRUE + ,FALSE), + ('reserves_out_by_reserve' ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('reserves_out' + ,'exchange-0002' + ,'master' + ,TRUE ,FALSE); |