[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-exchange] branch master updated: stay with partitions for now - f
|
From: |
gnunet |
|
Subject: |
[taler-exchange] branch master updated: stay with partitions for now - find shards on dev/boss/sharding |
|
Date: |
Tue, 01 Mar 2022 16:01:29 +0100 |
This is an automated email from the git hooks/post-receive script.
marco-boss pushed a commit to branch master
in repository exchange.
The following commit(s) were added to refs/heads/master by this push:
new b4f7fef2 stay with partitions for now - find shards on
dev/boss/sharding
new 3716592a Merge branch 'dev/boss/sharding'
b4f7fef2 is described below
commit b4f7fef224c7edc075636b14d68f7c20a7f1eb35
Author: Marco Boss <bossm8@bfh.ch>
AuthorDate: Tue Mar 1 15:54:47 2022 +0100
stay with partitions for now - find shards on dev/boss/sharding
---
src/exchangedb/Makefile.am | 1 -
src/exchangedb/drop0001.sql | 3 +-
src/exchangedb/exchange-0001.sql | 3101 ++++++++++++++++++++++++--------------
src/exchangedb/exchange-0002.sql | 1814 ----------------------
4 files changed, 1946 insertions(+), 2973 deletions(-)
diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am
index 05e06675..3145c3c0 100644
--- a/src/exchangedb/Makefile.am
+++ b/src/exchangedb/Makefile.am
@@ -19,7 +19,6 @@ sql_DATA = \
benchmark-0001.sql \
exchange-0000.sql \
exchange-0001.sql \
- exchange-0002.sql \
drop0001.sql
EXTRA_DIST = \
diff --git a/src/exchangedb/drop0001.sql b/src/exchangedb/drop0001.sql
index 8cc19191..fe8df06f 100644
--- a/src/exchangedb/drop0001.sql
+++ b/src/exchangedb/drop0001.sql
@@ -55,6 +55,7 @@ DROP TABLE IF EXISTS reserves CASCADE;
DROP TABLE IF EXISTS denomination_revocations CASCADE;
DROP TABLE IF EXISTS denominations CASCADE;
+
DROP FUNCTION IF EXISTS
exchange_do_withdraw(bigint,int,bytea,bytea,bytea,bytea,bytea,bigint,bigint) ;
DROP FUNCTION IF EXISTS
exchange_do_withdraw_limit_check(bytea,bigint,bigint,int) ;
@@ -73,6 +74,4 @@ DROP FUNCTION IF EXISTS exchange_do_recoup_to_reserve;
-- And we're out of here...
-SELECT _v.unregister_patch('exchange-0002');
-
COMMIT;
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 62660349..b8c291c2 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -20,970 +20,827 @@ BEGIN;
-- Check patch versioning is in place.
SELECT _v.register_patch('exchange-0001', NULL, NULL);
-CREATE OR REPLACE FUNCTION create_partitioned_table(
- IN table_definition VARCHAR
- ,IN table_name VARCHAR
- ,IN main_table_partition_str 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(
- table_definition,
- table_name,
- main_table_partition_str
+CREATE TABLE IF NOT EXISTS denominations
+ (denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64)
+ ,denom_type INT4 NOT NULL DEFAULT (1) -- 1 == RSA (for now, remove default
later!)
+ ,age_mask INT4 NOT NULL DEFAULT (0)
+ ,denom_pub BYTEA NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,valid_from INT8 NOT NULL
+ ,expire_withdraw INT8 NOT NULL
+ ,expire_deposit INT8 NOT NULL
+ ,expire_legal INT8 NOT NULL
+ ,coin_val INT8 NOT NULL
+ ,coin_frac INT4 NOT NULL
+ ,fee_withdraw_val INT8 NOT NULL
+ ,fee_withdraw_frac INT4 NOT NULL
+ ,fee_deposit_val INT8 NOT NULL
+ ,fee_deposit_frac INT4 NOT NULL
+ ,fee_refresh_val INT8 NOT NULL
+ ,fee_refresh_frac INT4 NOT NULL
+ ,fee_refund_val INT8 NOT NULL
+ ,fee_refund_frac INT4 NOT NULL
);
-
-END
-$$;
-
-COMMENT ON FUNCTION create_partitioned_table
- IS 'Create a table which may be partitioned. If shard_suffix is null, it is
assumed
- that the table is a main table. Which means that it will be partitioned
by
- main_table_partition_str. If it is not null a table named
`table_name_shard_suffix`
- (not partitioned) will be created. The table must include `%I` as
placeholder for
- the table name, and `%s ;` as placeholder for the partitioning method';
-
-CREATE OR REPLACE FUNCTION create_table_denominations()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS denominations
- (denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64)
- ,denom_type INT4 NOT NULL DEFAULT (1) -- 1 == RSA (for now, remove default
later!)
- ,age_mask INT4 NOT NULL DEFAULT (0)
- ,denom_pub BYTEA NOT NULL
- ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
- ,valid_from INT8 NOT NULL
- ,expire_withdraw INT8 NOT NULL
- ,expire_deposit INT8 NOT NULL
- ,expire_legal INT8 NOT NULL
- ,coin_val INT8 NOT NULL
- ,coin_frac INT4 NOT NULL
- ,fee_withdraw_val INT8 NOT NULL
- ,fee_withdraw_frac INT4 NOT NULL
- ,fee_deposit_val INT8 NOT NULL
- ,fee_deposit_frac INT4 NOT NULL
- ,fee_refresh_val INT8 NOT NULL
- ,fee_refresh_frac INT4 NOT NULL
- ,fee_refund_val INT8 NOT NULL
- ,fee_refund_frac INT4 NOT NULL
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_denominations_revocations()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS denomination_revocations
- (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
- ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
- );
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_wire_targets(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
- ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=64)'
- ',payto_uri VARCHAR NOT NULL'
- ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)'
- ',external_id VARCHAR'
- ') %s ;'
- ,'wire_targets'
- ,'PARTITION BY HASH (h_payto)'
- ,shard_suffix
+COMMENT ON TABLE denominations
+ IS 'Main denominations table. All the valid denominations the exchange knows
about.';
+COMMENT ON COLUMN denominations.denom_type
+ IS 'determines cipher type for blind signatures used with this denomination;
0 is for RSA';
+COMMENT ON COLUMN denominations.age_mask
+ IS 'bitmask with the age restrictions that are being used for this
denomination; 0 if denomination does not support the use of age restrictions';
+COMMENT ON COLUMN denominations.denominations_serial
+ IS 'needed for exchange-auditor replication logic';
+
+CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index
+ ON denominations
+ (expire_legal);
+
+
+CREATE TABLE IF NOT EXISTS denomination_revocations
+ (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
);
+COMMENT ON TABLE denomination_revocations
+ IS 'remembering which denomination keys have been revoked';
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS wire_targets
- ADD CONSTRAINT wire_targets_wire_target_serial_id_key
- UNIQUE (wire_target_serial_id)
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_wire_targets
- IS 'Create the wire_targets table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-CREATE OR REPLACE FUNCTION create_table_reserves(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
- ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)'
- ',current_balance_val INT8 NOT NULL'
- ',current_balance_frac INT4 NOT NULL'
- ',expiration_date INT8 NOT NULL'
- ',gc_date INT8 NOT NULL'
- ') %s ;'
- ,'reserves'
- ,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
+CREATE TABLE IF NOT EXISTS wire_targets
+ (wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=64)
+ ,payto_uri VARCHAR NOT NULL
+ ,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)
+ ,external_id VARCHAR
+ )
+ PARTITION BY HASH (h_payto);
+COMMENT ON TABLE wire_targets
+ IS 'All senders and recipients of money via the exchange';
+COMMENT ON COLUMN wire_targets.payto_uri
+ IS 'Can be a regular bank account, or also be a URI identifying a
reserve-account (for P2P payments)';
+COMMENT ON COLUMN wire_targets.h_payto
+ IS 'Unsalted hash of payto_uri';
+COMMENT ON COLUMN wire_targets.kyc_ok
+ IS 'true if the KYC check was passed successfully';
+COMMENT ON COLUMN wire_targets.external_id
+ IS 'Name of the user that was used for OAuth 2.0-based legitimization';
+CREATE TABLE IF NOT EXISTS wire_targets_default
+ PARTITION OF wire_targets
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+-- FIXME partition by serial_id rather than h_payto,
+-- it is used more in join conditions - crucial for sharding to select this.
+-- Author: (Boss Marco)
+CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index
+ ON wire_targets
+ (wire_target_serial_id
);
-END
-$$;
-
-COMMENT ON FUNCTION create_table_reserves
- IS 'Create the reserves table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-CREATE OR REPLACE FUNCTION create_table_reserves_in(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
- ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON
DELETE CASCADE'
- ',wire_reference INT8 NOT NULL'
- ',credit_val INT8 NOT NULL'
- ',credit_frac INT4 NOT NULL'
- ',wire_source_serial_id INT8 NOT NULL' -- REFERENCES wire_targets
(wire_target_serial_id)'
- ',exchange_account_section TEXT NOT NULL'
- ',execution_date INT8 NOT NULL'
- ') %s ;'
- ,'reserves_in'
- ,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
+CREATE TABLE IF NOT EXISTS reserves
+ (reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)
+ ,current_balance_val INT8 NOT NULL
+ ,current_balance_frac INT4 NOT NULL
+ ,expiration_date INT8 NOT NULL
+ ,gc_date INT8 NOT NULL
+ )
+ PARTITION BY HASH (reserve_pub);
+COMMENT ON TABLE reserves
+ IS 'Summarizes the balance of a reserve. Updated when new funds are added or
withdrawn.';
+COMMENT ON COLUMN reserves.reserve_pub
+ IS 'EdDSA public key of the reserve. Knowledge of the private key implies
ownership over the balance.';
+COMMENT ON COLUMN reserves.current_balance_val
+ IS 'Current balance remaining with the reserve';
+COMMENT ON COLUMN reserves.expiration_date
+ IS 'Used to trigger closing of reserves that have not been drained after
some time';
+COMMENT ON COLUMN reserves.gc_date
+ IS 'Used to forget all information about a reserve during garbage
collection';
+CREATE TABLE IF NOT EXISTS reserves_default
+ PARTITION OF reserves
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS reserves_by_expiration_index
+ ON reserves
+ (expiration_date
+ ,current_balance_val
+ ,current_balance_frac
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS reserves_in
- ADD CONSTRAINT reserves_in_reserve_serial_id_key
- UNIQUE (reserve_in_serial_id)
- ;
- ELSE
- ALTER TABLE IF EXISTS reserves_in
- ADD CONSTRAINT reserves_in_reserve_pub_fkey
- FOREIGN KEY (reserve_pub) REFERENCES reserves (reserve_pub) ON DELETE
CASCADE
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_reserves_in
- IS 'Create the reserves_in table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_reserves_close(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE /
PRIMARY KEY'
- ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON
DELETE CASCADE'
- ',execution_date INT8 NOT NULL'
- ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)'
- ',wire_target_serial_id INT8 NOT NULL' -- REFERENCES wire_targets
(wire_target_serial_id)'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ',closing_fee_val INT8 NOT NULL'
- ',closing_fee_frac INT4 NOT NULL'
- ') %s ;'
- ,'reserves_close'
- ,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
+COMMENT ON INDEX reserves_by_expiration_index
+ IS 'used in get_expired_reserves';
+CREATE INDEX IF NOT EXISTS reserves_by_reserve_uuid_index
+ ON reserves
+ (reserve_uuid);
+CREATE INDEX IF NOT EXISTS reserves_by_gc_date_index
+ ON reserves
+ (gc_date);
+COMMENT ON INDEX reserves_by_gc_date_index
+ IS 'for reserve garbage collection';
+
+
+CREATE TABLE IF NOT EXISTS reserves_in
+ (reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,reserve_pub BYTEA PRIMARY KEY REFERENCES reserves (reserve_pub) ON DELETE
CASCADE
+ ,wire_reference INT8 NOT NULL
+ ,credit_val INT8 NOT NULL
+ ,credit_frac INT4 NOT NULL
+ ,wire_source_serial_id INT8 NOT NULL -- REFERENCES wire_targets
(wire_target_serial_id)
+ ,exchange_account_section TEXT NOT NULL
+ ,execution_date INT8 NOT NULL
+ )
+ PARTITION BY HASH (reserve_pub);
+COMMENT ON TABLE reserves_in
+ IS 'list of transfers of funds into the reserves, one per incoming wire
transfer';
+COMMENT ON COLUMN reserves_in.wire_source_serial_id
+ IS 'Identifies the debited bank account and KYC status';
+COMMENT ON COLUMN reserves_in.reserve_pub
+ IS 'Public key of the reserve. Private key signifies ownership of the
remaining balance.';
+COMMENT ON COLUMN reserves_in.credit_val
+ IS 'Amount that was transferred into the reserve';
+CREATE TABLE IF NOT EXISTS reserves_in_default
+ PARTITION OF reserves_in
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS reserves_in_by_reserve_in_serial_id_index
+ ON reserves_in
+ (reserve_in_serial_id);
+CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_section_execution_date_index
+ ON reserves_in
+ (exchange_account_section
+ ,execution_date
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS reserves_close
- ADD CONSTRAINT reserves_close_close_uuid_pkey
- PRIMARY KEY (close_uuid)
- ;
- ELSE
- ALTER TABLE IF EXISTS reserves_close
- ADD CONSTRAINT reserves_close_reserve_pub_fkey
- FOREIGN KEY (reserve_pub) REFERENCES reserves (reserve_pub) ON DELETE
CASCADE
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_reserves_close
- IS 'Create the reserves_close table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_reserves_out(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
- ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial)'
- ',denom_sig BYTEA NOT NULL'
- ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON
DELETE CASCADE'
- ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
- ',execution_date INT8 NOT NULL'
- ',amount_with_fee_val INT8 NOT NULL'
- ',amount_with_fee_frac INT4 NOT NULL'
- ') %s ;'
- ,'reserves_out'
- ,'PARTITION BY HASH (h_blind_ev)'
- ,shard_suffix
+CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_reserve_in_serial_id_index
+ ON reserves_in
+ (exchange_account_section,
+ reserve_in_serial_id DESC
);
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS reserves_out
- ADD CONSTRAINT reserves_out_reserve_out_serial_id_key
- UNIQUE (reserve_out_serial_id)
- ;
- ELSE
- -- FIXME once denominations are replicated we can safely add the fkey on
table creation
- ALTER TABLE IF EXISTS reserves_out
- ADD CONSTRAINT reserves_out_denominations_serial_fkey
- FOREIGN KEY (denominations_serial) REFERENCES denominations
(denominations_serial)
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_reserves_out
- IS 'Create the reserves_out table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_auditors()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS auditors
- (auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32)
- ,auditor_name VARCHAR NOT NULL
- ,auditor_url VARCHAR NOT NULL
- ,is_active BOOLEAN NOT NULL
- ,last_change INT8 NOT NULL
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_auditor_denom_sigs()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS auditor_denom_sigs
- (auditor_denom_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,auditor_uuid INT8 NOT NULL REFERENCES auditors (auditor_uuid) ON DELETE
CASCADE
- ,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
- ,auditor_sig BYTEA CHECK (LENGTH(auditor_sig)=64)
- ,PRIMARY KEY (denominations_serial, auditor_uuid)
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_exchange_sign_keys()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS exchange_sign_keys
- (esk_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32)
- ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
- ,valid_from INT8 NOT NULL
- ,expire_sign INT8 NOT NULL
- ,expire_legal INT8 NOT NULL
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_signkey_revocations()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS signkey_revocations
- (signkey_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
UNIQUE
- ,esk_serial INT8 PRIMARY KEY REFERENCES exchange_sign_keys (esk_serial) ON
DELETE CASCADE
- ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_extensions()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS extensions
- (extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,name VARCHAR NOT NULL UNIQUE
- ,config BYTEA
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_known_coins(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial) ON DELETE CASCADE'
- ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)'
- ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)'
- ',denom_sig BYTEA NOT NULL'
- ',remaining_val INT8 NOT NULL'
- ',remaining_frac INT4 NOT NULL'
- ') %s ;'
- ,'known_coins'
- ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial?
or multi-level partitioning?;
- ,shard_suffix
+CREATE TABLE IF NOT EXISTS reserves_close
+ (close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE / PRIMARY KEY
+ ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE
CASCADE
+ ,execution_date INT8 NOT NULL
+ ,wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)
+ ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets
(wire_target_serial_id)
+ ,amount_val INT8 NOT NULL
+ ,amount_frac INT4 NOT NULL
+ ,closing_fee_val INT8 NOT NULL
+ ,closing_fee_frac INT4 NOT NULL)
+ PARTITION BY HASH (reserve_pub);
+COMMENT ON TABLE reserves_close
+ IS 'wire transfers executed by the reserve to close reserves';
+COMMENT ON COLUMN reserves_close.wire_target_serial_id
+ IS 'Identifies the credited bank account (and KYC status). Note that closing
does not depend on KYC.';
+CREATE TABLE IF NOT EXISTS reserves_close_default
+ PARTITION OF reserves_close
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS reserves_close_by_close_uuid_index
+ ON reserves_close
+ (close_uuid);
+CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index
+ ON reserves_close
+ (reserve_pub);
+
+
+CREATE TABLE IF NOT EXISTS reserves_out
+ (reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE
+ ,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial)
+ ,denom_sig BYTEA NOT NULL
+ ,reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE
CASCADE
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,execution_date INT8 NOT NULL
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ )
+ PARTITION BY HASH (h_blind_ev);
+COMMENT ON TABLE reserves_out
+ IS 'Withdraw operations performed on reserves.';
+COMMENT ON COLUMN reserves_out.h_blind_ev
+ IS '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).';
+COMMENT ON COLUMN reserves_out.denominations_serial
+ IS 'We do not CASCADE ON DELETE here, we may keep the denomination data
alive';
+CREATE TABLE IF NOT EXISTS reserves_out_default
+ PARTITION OF reserves_out
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index
+ ON reserves_out
+ (reserve_out_serial_id);
+CREATE INDEX IF NOT EXISTS
reserves_out_by_reserve_uuid_and_execution_date_index
+ ON reserves_out
+ (reserve_uuid, execution_date);
+COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index
+ IS 'for get_reserves_out and exchange_do_withdraw_limit_check';
+
+
+CREATE TABLE IF NOT EXISTS auditors
+ (auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32)
+ ,auditor_name VARCHAR NOT NULL
+ ,auditor_url VARCHAR NOT NULL
+ ,is_active BOOLEAN NOT NULL
+ ,last_change INT8 NOT NULL
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS known_coins
- ADD CONSTRAINT known_coins_known_coin_id_key
- UNIQUE (known_coin_id)
- ;
- ELSE
- -- FIXME once denominations are replicated we can safely add the fkey on
table creation
- ALTER TABLE IF EXISTS known_coins
- ADD CONSTRAINT known_coins_denominations_serial_fkey
- FOREIGN KEY (denominations_serial) REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_known_coins
- IS 'Create the known_coins table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_refresh_commitments(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)'
- ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON
DELETE CASCADE'
- ',h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32)'
- ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)'
- ',amount_with_fee_val INT8 NOT NULL'
- ',amount_with_fee_frac INT4 NOT NULL'
- ',noreveal_index INT4 NOT NULL'
- ') %s ;'
- ,'refresh_commitments'
- ,'PARTITION BY HASH (rc)'
- ,shard_suffix
+COMMENT ON TABLE auditors
+ IS 'Table with auditors the exchange uses or has used in the past. Entries
never expire as we need to remember the last_change column indefinitely.';
+COMMENT ON COLUMN auditors.auditor_pub
+ IS 'Public key of the auditor.';
+COMMENT ON COLUMN auditors.auditor_url
+ IS 'The base URL of the auditor.';
+COMMENT ON COLUMN auditors.is_active
+ IS 'true if we are currently supporting the use of this auditor.';
+COMMENT ON COLUMN auditors.last_change
+ IS 'Latest time when active status changed. Used to detect replays of old
messages.';
+
+
+CREATE TABLE IF NOT EXISTS auditor_denom_sigs
+ (auditor_denom_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,auditor_uuid INT8 NOT NULL REFERENCES auditors (auditor_uuid) ON DELETE
CASCADE
+ ,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
+ ,auditor_sig BYTEA CHECK (LENGTH(auditor_sig)=64)
+ ,PRIMARY KEY (denominations_serial, auditor_uuid)
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS refresh_commitments
- ADD CONSTRAINT refresh_commitments_melt_serial_id_key
- UNIQUE (melt_serial_id)
- ;
- ELSE
- ALTER TABLE IF EXISTS refresh_commitments
- ADD CONSTRAINT refresh_commitments_old_coin_pub_fkey
- FOREIGN KEY (old_coin_pub) REFERENCES known_coins (coin_pub) ON DELETE
CASCADE
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_refresh_commitments
- IS 'Create the refresh_commitments table, if argument `shard_suffix` is
empty, a partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE'
- ',freshcoin_index INT4 NOT NULL'
- ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial) ON DELETE CASCADE'
- ',coin_ev BYTEA NOT NULL' -- UNIQUE'
- ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE'
- ',ev_sig BYTEA NOT NULL'
- ',ewv BYTEA NOT NULL'
- -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard
- ') %s ;'
- ,'refresh_revealed_coins'
- ,'PARTITION BY HASH (melt_serial_id)'
- ,shard_suffix
+COMMENT ON TABLE auditor_denom_sigs
+ IS 'Table with auditor signatures on exchange denomination keys.';
+COMMENT ON COLUMN auditor_denom_sigs.auditor_uuid
+ IS 'Identifies the auditor.';
+COMMENT ON COLUMN auditor_denom_sigs.denominations_serial
+ IS 'Denomination the signature is for.';
+COMMENT ON COLUMN auditor_denom_sigs.auditor_sig
+ IS 'Signature of the auditor, of purpose
TALER_SIGNATURE_AUDITOR_EXCHANGE_KEYS.';
+
+
+CREATE TABLE IF NOT EXISTS exchange_sign_keys
+ (esk_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32)
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,valid_from INT8 NOT NULL
+ ,expire_sign INT8 NOT NULL
+ ,expire_legal INT8 NOT NULL
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS refresh_revealed_coins
- ADD CONSTRAINT refresh_revealed_coins_rrc_serial_key
- UNIQUE (rrc_serial)
- ,ADD CONSTRAINT refresh_revealed_coins_coin_ev_key
- UNIQUE (coin_ev)
- ,ADD CONSTRAINT refresh_revealed_coins_h_coin_ev_key
- UNIQUE (h_coin_ev)
- ,ADD CONSTRAINT
refresh_revealed_coins_melt_serial_id_freshcoin_index_pkey
- PRIMARY KEY (melt_serial_id, freshcoin_index)
- ;
- ELSE
- -- FIXME once denominations are replicated we can safely add the fkey on
table creation
- ALTER TABLE IF EXISTS refresh_revealed_coins
- ADD CONSTRAINT refresh_revealed_coins_denominations_serial_fkey
- FOREIGN KEY (denominations_serial) REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_refresh_revealed_coins
- IS 'Create the refresh_revealed_coins table, if argument `shard_suffix` is
empty, a partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE'
- ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)'
- ',transfer_privs BYTEA NOT NULL'
- ') %s ;'
- ,'refresh_transfer_keys'
- ,'PARTITION BY HASH (melt_serial_id)'
- ,shard_suffix
+COMMENT ON TABLE exchange_sign_keys
+ IS 'Table with master public key signatures on exchange online signing
keys.';
+COMMENT ON COLUMN exchange_sign_keys.exchange_pub
+ IS 'Public online signing key of the exchange.';
+COMMENT ON COLUMN exchange_sign_keys.master_sig
+ IS 'Signature affirming the validity of the signing key of purpose
TALER_SIGNATURE_MASTER_SIGNING_KEY_VALIDITY.';
+COMMENT ON COLUMN exchange_sign_keys.valid_from
+ IS 'Time when this online signing key will first be used to sign messages.';
+COMMENT ON COLUMN exchange_sign_keys.expire_sign
+ IS 'Time when this online signing key will no longer be used to sign.';
+COMMENT ON COLUMN exchange_sign_keys.expire_legal
+ IS 'Time when this online signing key legally expires.';
+
+
+CREATE TABLE IF NOT EXISTS signkey_revocations
+ (signkey_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,esk_serial INT8 PRIMARY KEY REFERENCES exchange_sign_keys (esk_serial) ON
DELETE CASCADE
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
);
+COMMENT ON TABLE signkey_revocations
+ IS 'Table storing which online signing keys have been revoked';
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS refresh_transfer_keys
- ADD CONSTRAINT refresh_transfer_keys_rtx_serial_key
- UNIQUE (rtc_serial)
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_refresh_transfer_keys
- IS 'Create the refresh_transfer_keys table, if argument `shard_suffix` is
empty, a partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_extension_details()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS extension_details
- (extension_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
PRIMARY KEY
- ,extension_options VARCHAR);
-
-END
-$$;
-CREATE OR REPLACE FUNCTION create_table_deposits(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY
KEY'
- ',shard INT8 NOT NULL'
- ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)
ON DELETE CASCADE'
- ',amount_with_fee_val INT8 NOT NULL'
- ',amount_with_fee_frac INT4 NOT NULL'
- ',wallet_timestamp INT8 NOT NULL'
- ',exchange_timestamp INT8 NOT NULL'
- ',refund_deadline INT8 NOT NULL'
- ',wire_deadline INT8 NOT NULL'
- ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)'
- ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)'
- ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)'
- ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)'
- ',wire_target_serial_id INT8 NOT NULL' -- REFERENCES wire_targets
(wire_target_serial_id)'
- ',tiny BOOLEAN NOT NULL DEFAULT FALSE'
- ',done BOOLEAN NOT NULL DEFAULT FALSE'
- ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE'
- ',extension_details_serial_id INT8' -- REFERENCES extension_details
(extension_details_serial_id) ON DELETE CASCADE'
- ',UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms)'
- ') %s ;'
- ,'deposits'
- ,'PARTITION BY HASH (shard)'
- ,shard_suffix
+CREATE TABLE IF NOT EXISTS extensions
+ (extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,name VARCHAR NOT NULL UNIQUE
+ ,config BYTEA
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS deposits
- ADD CONSTRAINT deposits_deposit_by_serial_id_pkey
- PRIMARY KEY (deposit_serial_id)
- ;
- ELSE
- ALTER TABLE IF EXISTS Deposits
- ADD CONSTRAINT deposits_extension_details_serial_id_fkey
- FOREIGN KEY (extension_details_serial_id) REFERENCES extension_details
(extension_details_serial_id) ON DELETE CASCADE
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_deposits
- IS 'Create the deposits table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-CREATE OR REPLACE FUNCTION create_table_refunds(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits
(deposit_serial_id) ON DELETE CASCADE'
- ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)'
- ',rtransaction_id INT8 NOT NULL'
- ',amount_with_fee_val INT8 NOT NULL'
- ',amount_with_fee_frac INT4 NOT NULL'
- -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
- ') %s ;'
- ,'refunds'
- ,'PARTITION BY HASH (deposit_serial_id)'
- ,shard_suffix
+COMMENT ON TABLE extensions
+ IS 'Configurations of the activated extensions';
+COMMENT ON COLUMN extensions.name
+ IS 'Name of the extension';
+COMMENT ON COLUMN extensions.config
+ IS 'Configuration of the extension as JSON-blob, maybe NULL';
+
+
+CREATE TABLE IF NOT EXISTS known_coins
+ (known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
+ ,coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)
+ ,age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)
+ ,denom_sig BYTEA NOT NULL
+ ,remaining_val INT8 NOT NULL
+ ,remaining_frac INT4 NOT NULL
+ )
+ PARTITION BY HASH (coin_pub); -- FIXME: or include denominations_serial? or
multi-level partitioning?
+COMMENT ON TABLE known_coins
+ IS 'information about coins and their signatures, so we do not have to store
the signatures more than once if a coin is involved in multiple operations';
+COMMENT ON COLUMN known_coins.denominations_serial
+ IS 'Denomination of the coin, determines the value of the original coin and
applicable fees for coin-specific operations.';
+COMMENT ON COLUMN known_coins.coin_pub
+ IS 'EdDSA public key of the coin';
+COMMENT ON COLUMN known_coins.remaining_val
+ IS 'Value of the coin that remains to be spent';
+COMMENT ON COLUMN known_coins.age_commitment_hash
+ IS 'Optional hash of the age commitment for age restrictions as per DD 24
(active if denom_type has the respective bit set)';
+COMMENT ON COLUMN known_coins.denom_sig
+ IS 'This is the signature of the exchange that affirms that the coin is a
valid coin. The specific signature type depends on denom_type of the
denomination.';
+CREATE TABLE IF NOT EXISTS known_coins_default
+ PARTITION OF known_coins
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS known_coins_by_known_coin_id_index
+ ON known_coins
+ (known_coin_id);
+
+
+CREATE TABLE IF NOT EXISTS refresh_commitments
+ (melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)
+ ,old_coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE
CASCADE
+ ,h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32)
+ ,old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,noreveal_index INT4 NOT NULL
+ )
+ PARTITION BY HASH (rc);
+COMMENT ON TABLE refresh_commitments
+ IS 'Commitments made when melting coins and the gamma value chosen by the
exchange.';
+COMMENT ON COLUMN refresh_commitments.noreveal_index
+ IS 'The gamma value chosen by the exchange in the cut-and-choose protocol';
+COMMENT ON COLUMN refresh_commitments.rc
+ IS 'Commitment made by the client, hash over the various client inputs in
the cut-and-choose protocol';
+COMMENT ON COLUMN refresh_commitments.old_coin_pub
+ IS 'Coin being melted in the refresh process.';
+COMMENT ON COLUMN refresh_commitments.h_age_commitment
+ IS 'The (optional) age commitment that was involved in the minting process
of the coin, may be NULL.';
+CREATE TABLE IF NOT EXISTS refresh_commitments_default
+ PARTITION OF refresh_commitments
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS refresh_commitments_by_melt_serial_id_index
+ ON refresh_commitments
+ (melt_serial_id);
+CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
+ ON refresh_commitments
+ (old_coin_pub);
+
+
+CREATE TABLE IF NOT EXISTS refresh_revealed_coins
+ (rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,melt_serial_id INT8 NOT NULL -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE
+ ,freshcoin_index INT4 NOT NULL
+ ,link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)
+ ,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
+ ,coin_ev BYTEA NOT NULL -- UNIQUE
+ ,h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64) -- UNIQUE
+ ,ev_sig BYTEA NOT NULL
+ ,ewv BYTEA NOT NULL
+ -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard
+ )
+ PARTITION BY HASH (melt_serial_id);
+COMMENT ON TABLE refresh_revealed_coins
+ IS 'Revelations about the new coins that are to be created during a melting
session.';
+COMMENT ON COLUMN refresh_revealed_coins.rrc_serial
+ IS 'needed for exchange-auditor replication logic';
+COMMENT ON COLUMN refresh_revealed_coins.melt_serial_id
+ IS 'Identifies the refresh commitment (rc) of the melt operation.';
+COMMENT ON COLUMN refresh_revealed_coins.freshcoin_index
+ IS 'index of the fresh coin being created (one melt operation may result in
multiple fresh coins)';
+COMMENT ON COLUMN refresh_revealed_coins.coin_ev
+ IS 'envelope of the new coin to be signed';
+COMMENT ON COLUMN refresh_revealed_coins.ewv
+ IS 'exchange contributed values in the creation of the fresh coin (see
/csr)';
+COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
+ IS 'hash of the envelope of the new coin to be signed (for lookups)';
+COMMENT ON COLUMN refresh_revealed_coins.ev_sig
+ IS 'exchange signature over the envelope';
+CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default
+ PARTITION OF refresh_revealed_coins
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+-- We do require this primary key on each shard!
+ALTER TABLE refresh_revealed_coins_default
+ ADD PRIMARY KEY (melt_serial_id, freshcoin_index);
+
+CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_rrc_serial_index
+ ON refresh_revealed_coins
+ (rrc_serial);
+CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index
+ ON refresh_revealed_coins
+ (melt_serial_id);
+
+
+CREATE TABLE IF NOT EXISTS refresh_transfer_keys
+ (rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,melt_serial_id INT8 PRIMARY KEY -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE
+ ,transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)
+ ,transfer_privs BYTEA NOT NULL
+ )
+ PARTITION BY HASH (melt_serial_id);
+COMMENT ON TABLE refresh_transfer_keys
+ IS 'Transfer keys of a refresh operation (the data revealed to the
exchange).';
+COMMENT ON COLUMN refresh_transfer_keys.rtc_serial
+ IS 'needed for exchange-auditor replication logic';
+COMMENT ON COLUMN refresh_transfer_keys.melt_serial_id
+ IS 'Identifies the refresh commitment (rc) of the operation.';
+COMMENT ON COLUMN refresh_transfer_keys.transfer_pub
+ IS 'transfer public key for the gamma index';
+COMMENT ON COLUMN refresh_transfer_keys.transfer_privs
+ IS 'array of TALER_CNC_KAPPA - 1 transfer private keys that have been
revealed, with the gamma entry being skipped';
+CREATE TABLE IF NOT EXISTS refresh_transfer_keys_default
+ PARTITION OF refresh_transfer_keys
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS refresh_transfer_keys_by_rtc_serial_index
+ ON refresh_transfer_keys
+ (rtc_serial);
+
+
+CREATE TABLE IF NOT EXISTS extension_details
+ (extension_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY
KEY
+ ,extension_options VARCHAR);
+COMMENT ON TABLE extension_details
+ IS 'Extensions that were provided with deposits (not yet used).';
+COMMENT ON COLUMN extension_details.extension_options
+ IS 'JSON object with options set that the exchange needs to consider when
executing a deposit. Supported details depend on the extensions supported by
the exchange.';
+
+
+CREATE TABLE IF NOT EXISTS deposits
+ (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
+ ,shard INT8 NOT NULL
+ ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id) ON
DELETE CASCADE
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,wallet_timestamp INT8 NOT NULL
+ ,exchange_timestamp INT8 NOT NULL
+ ,refund_deadline INT8 NOT NULL
+ ,wire_deadline INT8 NOT NULL
+ ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)
+ ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
+ ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)
+ ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)
+ ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets
(wire_target_serial_id)
+ ,tiny BOOLEAN NOT NULL DEFAULT FALSE
+ ,done BOOLEAN NOT NULL DEFAULT FALSE
+ ,extension_blocked BOOLEAN NOT NULL DEFAULT FALSE
+ ,extension_details_serial_id INT8 REFERENCES extension_details
(extension_details_serial_id) ON DELETE CASCADE
+ ,UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms)
+ )
+ PARTITION BY HASH (shard);
+CREATE TABLE IF NOT EXISTS deposits_default
+ PARTITION OF deposits
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+COMMENT ON TABLE deposits
+ IS 'Deposits we have received and for which we need to make (aggregate) wire
transfers (and manage refunds).';
+COMMENT ON COLUMN deposits.shard
+ IS 'Used for load sharding. Should be set based on h_payto and merchant_pub.
64-bit value because we need an *unsigned* 32-bit value.';
+COMMENT ON COLUMN deposits.wire_target_serial_id
+ IS 'Identifies the target bank account and KYC status';
+COMMENT ON COLUMN deposits.wire_salt
+ IS 'Salt used when hashing the payto://-URI to get the h_wire';
+COMMENT ON COLUMN deposits.done
+ IS 'Set to TRUE once we have included this deposit in some aggregate wire
transfer to the merchant';
+COMMENT ON COLUMN deposits.extension_blocked
+ IS 'True if the aggregation of the deposit is currently blocked by some
extension mechanism. Used to filter out deposits that must not be processed by
the canonical deposit logic.';
+COMMENT ON COLUMN deposits.extension_details_serial_id
+ IS 'References extensions table, NULL if extensions are not used';
+COMMENT ON COLUMN deposits.tiny
+ IS 'Set to TRUE if we decided that the amount is too small to ever trigger a
wire transfer by itself (requires real aggregation)';
+
+-- FIXME: check if we can ALWAYS include the shard in the WHERE clauses,
+-- thereby resulting in a much better use of the index: we could do
(shard,deposit_serial_id)!
+CREATE INDEX IF NOT EXISTS deposits_deposit_by_serial_id_index
+ ON deposits
+ (deposit_serial_id);
+CREATE INDEX IF NOT EXISTS deposits_for_get_ready_index
+ ON deposits
+ (shard ASC
+ ,done
+ ,extension_blocked
+ ,tiny
+ ,wire_deadline ASC
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS refunds
- ADD CONSTRAINT refunds_refund_serial_id_key
- UNIQUE (refund_serial_id)
- ,ADD CONSTRAINT refunds_deposit_serial_id_rtransaction_id_pkey
- PRIMARY KEY (deposit_serial_id, rtransaction_id)
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_refunds
- IS 'Create the refunds table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-CREATE OR REPLACE FUNCTION create_table_wire_out(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY'
- ',execution_date INT8 NOT NULL'
- ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)'
- ',wire_target_serial_id INT8 NOT NULL' -- REFERENCES wire_targets
(wire_target_serial_id)'
- ',exchange_account_section TEXT NOT NULL'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ') %s ;'
- ,'wire_out'
- ,'PARTITION BY HASH (wtid_raw)'
- ,shard_suffix
+COMMENT ON INDEX deposits_for_get_ready_index
+ IS 'for deposits_get_ready';
+-- FIXME: check if we can ALWAYS include the shard in the WHERE clauses,
+-- thereby resulting in a much better use of the index: we could do
(shard,merchant_pub, ...)!
+CREATE INDEX IF NOT EXISTS deposits_for_iterate_matching_index
+ ON deposits
+ (merchant_pub
+ ,wire_target_serial_id
+ ,done
+ ,extension_blocked
+ ,refund_deadline ASC
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS wire_out
- ADD CONSTRAINT wire_out_wireout_uuid_pkey
- PRIMARY KEY (wireout_uuid)
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_wire_out
- IS 'Create the wire_out table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-CREATE OR REPLACE FUNCTION create_table_aggregation_tracking(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
- ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits
(deposit_serial_id) ON DELETE CASCADE'
- ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES
wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE'
- ') %s ;'
- ,'aggregation_tracking'
- ,'PARTITION BY HASH (deposit_serial_id)'
- ,shard_suffix
+COMMENT ON INDEX deposits_for_iterate_matching_index
+ IS 'for deposits_iterate_matching';
+
+
+CREATE TABLE IF NOT EXISTS refunds
+ (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,deposit_serial_id INT8 NOT NULL -- REFERENCES deposits (deposit_serial_id)
ON DELETE CASCADE
+ ,merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)
+ ,rtransaction_id INT8 NOT NULL
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
+ )
+ PARTITION BY HASH (deposit_serial_id);
+COMMENT ON TABLE refunds
+ IS 'Data on coins that were refunded. Technically, refunds always apply
against specific deposit operations involving a coin. The combination of
coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique,
and we usually select by coin_pub so that one goes first.';
+COMMENT ON COLUMN refunds.deposit_serial_id
+ IS 'Identifies ONLY the merchant_pub, h_contract_terms and known_coin_id.
Multiple deposits may match a refund, this only identifies one of them.';
+COMMENT ON COLUMN refunds.rtransaction_id
+ IS 'used by the merchant to make refunds unique in case the same coin for
the same deposit gets a subsequent (higher) refund';
+CREATE TABLE IF NOT EXISTS refunds_default
+ PARTITION OF refunds
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+ALTER TABLE refunds_default
+ ADD PRIMARY KEY (deposit_serial_id, rtransaction_id);
+
+CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index
+ ON refunds
+ (refund_serial_id);
+
+
+CREATE TABLE IF NOT EXISTS wire_out
+ (wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
+ ,execution_date INT8 NOT NULL
+ ,wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)
+ ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets
(wire_target_serial_id)
+ ,exchange_account_section TEXT NOT NULL
+ ,amount_val INT8 NOT NULL
+ ,amount_frac INT4 NOT NULL
+ )
+ PARTITION BY HASH (wtid_raw);
+COMMENT ON TABLE wire_out
+ IS 'wire transfers the exchange has executed';
+COMMENT ON COLUMN wire_out.exchange_account_section
+ IS 'identifies the configuration section with the debit account of this
payment';
+COMMENT ON COLUMN wire_out.wire_target_serial_id
+ IS 'Identifies the credited bank account and KYC status';
+CREATE TABLE IF NOT EXISTS wire_out_default
+ PARTITION OF wire_out
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS wire_out_by_wireout_uuid_index
+ ON wire_out
+ (wireout_uuid);
+CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_serial_id_index
+ ON wire_out
+ (wire_target_serial_id);
+
+
+
+CREATE TABLE IF NOT EXISTS aggregation_tracking
+ (aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits
(deposit_serial_id) ON DELETE CASCADE
+ ,wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES
wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE
+ )
+ PARTITION BY HASH (deposit_serial_id);
+COMMENT ON TABLE aggregation_tracking
+ IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)';
+COMMENT ON COLUMN aggregation_tracking.wtid_raw
+ IS 'We first create entries in the aggregation_tracking table and then
finally the wire_out entry once we know the total amount. Hence the constraint
must be deferrable and we cannot use a wireout_uuid here, because we do not
have it when these rows are created. Changing the logic to first INSERT a dummy
row into wire_out and then UPDATEing that row in the same transaction would
theoretically reduce per-deposit storage costs by 5 percent (24/~460 bytes).';
+CREATE TABLE IF NOT EXISTS aggregation_tracking_default
+ PARTITION OF aggregation_tracking
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS aggregation_tracking_by_aggregation_serial_id_index
+ ON aggregation_tracking
+ (aggregation_serial_id);
+CREATE INDEX IF NOT EXISTS aggregation_tracking_by_wtid_raw_index
+ ON aggregation_tracking
+ (wtid_raw);
+COMMENT ON INDEX aggregation_tracking_by_wtid_raw_index
+ IS 'for lookup_transactions';
+
+
+CREATE TABLE IF NOT EXISTS wire_fee
+ (wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,wire_method VARCHAR NOT NULL
+ ,start_date INT8 NOT NULL
+ ,end_date INT8 NOT NULL
+ ,wire_fee_val INT8 NOT NULL
+ ,wire_fee_frac INT4 NOT NULL
+ ,closing_fee_val INT8 NOT NULL
+ ,closing_fee_frac INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,PRIMARY KEY (wire_method, start_date)
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS aggregation_tracking
- ADD CONSTRAINT aggregation_tracking_aggregation_tracking_serial_id_key
- UNIQUE (aggergation_tracking_serial_id)
- ;
- ELSE
- ALTER TABLE IF EXISTS aggregation_tracking
- ADD CONSTRAINT wire_out_ref
- FOREIGN KEY (wtid_raw) REFERENCES wire_out (wtid_raw) ON DELETE
CASCADE DEFERRABLE
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_aggregation_tracking
- IS 'Create the aggregation_tracking table, if argument `shard_suffix` is
empty, a partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_wire_fee()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS wire_fee
- (wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,wire_method VARCHAR NOT NULL
- ,start_date INT8 NOT NULL
- ,end_date INT8 NOT NULL
- ,wire_fee_val INT8 NOT NULL
- ,wire_fee_frac INT4 NOT NULL
- ,closing_fee_val INT8 NOT NULL
- ,closing_fee_frac INT4 NOT NULL
- ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
- ,PRIMARY KEY (wire_method, start_date)
- );
-
-END
-$$;
-
-
-CREATE OR REPLACE FUNCTION create_table_recoup(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)'
- ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
- ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ',recoup_timestamp INT8 NOT NULL'
- ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out
(reserve_out_serial_id) ON DELETE CASCADE'
- ') %s ;'
- ,'recoup'
- ,'PARTITION BY HASH (known_coin_id);'
- ,shard_suffix
+COMMENT ON TABLE wire_fee
+ IS 'list of the wire fees of this exchange, by date';
+COMMENT ON COLUMN wire_fee.wire_fee_serial
+ IS 'needed for exchange-auditor replication logic';
+
+CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
+ ON wire_fee
+ (end_date);
+
+
+CREATE TABLE IF NOT EXISTS recoup
+ (recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
+ ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
+ ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
+ ,amount_val INT8 NOT NULL
+ ,amount_frac INT4 NOT NULL
+ ,recoup_timestamp INT8 NOT NULL
+ ,reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves_out
(reserve_out_serial_id) ON DELETE CASCADE
+ )
+ PARTITION BY HASH (known_coin_id);
+COMMENT ON TABLE recoup
+ IS 'Information about recoups that were executed between a coin and a
reserve. In this type of recoup, the amount is credited back to the reserve
from which the coin originated.';
+COMMENT ON COLUMN recoup.known_coin_id
+ IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the
coin_pub, as we may keep the coin alive!';
+COMMENT ON COLUMN recoup.reserve_out_serial_id
+ IS 'Identifies the h_blind_ev of the recouped coin and provides the link to
the credited reserve.';
+COMMENT ON COLUMN recoup.coin_sig
+ IS 'Signature by the coin affirming the recoup, of type
TALER_SIGNATURE_WALLET_COIN_RECOUP';
+COMMENT ON COLUMN recoup.coin_blind
+ IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the withdraw operation.';
+CREATE TABLE IF NOT EXISTS recoup_default
+ PARTITION OF recoup
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index
+ ON recoup
+ (recoup_uuid);
+CREATE INDEX IF NOT EXISTS recoup_by_reserve_out_serial_id_index
+ ON recoup
+ (reserve_out_serial_id);
+CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index
+ ON recoup
+ (known_coin_id);
+
+
+CREATE TABLE IF NOT EXISTS recoup_refresh
+ (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
+ ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
+ ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
+ ,amount_val INT8 NOT NULL
+ ,amount_frac INT4 NOT NULL
+ ,recoup_timestamp INT8 NOT NULL
+ ,rrc_serial INT8 NOT NULL -- REFERENCES refresh_revealed_coins (rrc_serial)
ON DELETE CASCADE -- UNIQUE
+ )
+ PARTITION BY HASH (known_coin_id);
+COMMENT ON TABLE recoup_refresh
+ IS 'Table of coins that originated from a refresh operation and that were
recouped. Links the (fresh) coin to the melted operation (and thus the old
coin). A recoup on a refreshed coin credits the old coin and debits the fresh
coin.';
+COMMENT ON COLUMN recoup_refresh.known_coin_id
+ IS 'Refreshed coin of a revoked denomination where the residual value is
credited to the old coin. Do not CASCADE ON DROP on the known_coin_id, as we
may keep the coin alive!';
+COMMENT ON COLUMN recoup_refresh.rrc_serial
+ IS 'Link to the refresh operation. Also identifies the h_blind_ev of the
recouped coin (as h_coin_ev).';
+COMMENT ON COLUMN recoup_refresh.coin_blind
+ IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the refresh operation.';
+CREATE TABLE IF NOT EXISTS recoup_refresh_default
+ PARTITION OF recoup_refresh
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
+ ON recoup_refresh
+ (recoup_refresh_uuid);
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
+ ON recoup_refresh
+ (rrc_serial);
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_known_coin_id_index
+ ON recoup_refresh
+ (known_coin_id);
+
+
+CREATE TABLE IF NOT EXISTS prewire
+ (prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+ ,wire_method TEXT NOT NULL
+ ,finished BOOLEAN NOT NULL DEFAULT false
+ ,failed BOOLEAN NOT NULL DEFAULT false
+ ,buf BYTEA NOT NULL
+ )
+ PARTITION BY HASH (prewire_uuid);
+COMMENT ON TABLE prewire
+ IS 'pre-commit data for wire transfers we are about to execute';
+COMMENT ON COLUMN prewire.failed
+ IS 'set to TRUE if the bank responded with a non-transient failure to our
transfer request';
+COMMENT ON COLUMN prewire.finished
+ IS 'set to TRUE once bank confirmed receiving the wire transfer request';
+COMMENT ON COLUMN prewire.buf
+ IS 'serialized data to send to the bank to execute the wire transfer';
+CREATE TABLE IF NOT EXISTS prewire_default
+ PARTITION OF prewire
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE INDEX IF NOT EXISTS prewire_by_finished_index
+ ON prewire
+ (finished);
+COMMENT ON INDEX prewire_by_finished_index
+ IS 'for gc_prewire';
+-- FIXME: find a way to combine these two indices?
+CREATE INDEX IF NOT EXISTS prewire_by_failed_finished_index
+ ON prewire
+ (failed,finished);
+COMMENT ON INDEX prewire_by_failed_finished_index
+ IS 'for wire_prepare_data_get';
+
+
+CREATE TABLE IF NOT EXISTS wire_accounts
+ (payto_uri VARCHAR PRIMARY KEY
+ ,master_sig BYTEA CHECK (LENGTH(master_sig)=64)
+ ,is_active BOOLEAN NOT NULL
+ ,last_change INT8 NOT NULL
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS recoup
- ADD CONSTRAINT recoup_refresh_recoup_uuid_key
- UNIQUE (recoup_uuid)
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_recoup
- IS 'Create the recoup table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-CREATE OR REPLACE FUNCTION create_table_recoup_refresh(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)'
- ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
- ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ',recoup_timestamp INT8 NOT NULL'
- ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins
(rrc_serial) ON DELETE CASCADE -- UNIQUE'
- ') %s ;'
- ,'recoup_refresh'
- ,'PARTITION BY HASH (known_coin_id)'
- ,shard_suffix
+COMMENT ON TABLE wire_accounts
+ IS 'Table with current and historic bank accounts of the exchange. Entries
never expire as we need to remember the last_change column indefinitely.';
+COMMENT ON COLUMN wire_accounts.payto_uri
+ IS 'payto URI (RFC 8905) with the bank account of the exchange.';
+COMMENT ON COLUMN wire_accounts.master_sig
+ IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS';
+COMMENT ON COLUMN wire_accounts.is_active
+ IS 'true if we are currently supporting the use of this account.';
+COMMENT ON COLUMN wire_accounts.last_change
+ IS 'Latest time when active status changed. Used to detect replays of old
messages.';
+-- "wire_accounts" has no sequence because it is a 'mutable' table
+-- and is of no concern to the auditor
+
+
+CREATE TABLE IF NOT EXISTS cs_nonce_locks
+ (cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)
+ ,op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)
+ ,max_denomination_serial INT8 NOT NULL
+ )
+ PARTITION BY HASH (nonce);
+COMMENT ON TABLE cs_nonce_locks
+ IS 'ensures a Clause Schnorr client nonce is locked for use with an
operation identified by a hash';
+COMMENT ON COLUMN cs_nonce_locks.nonce
+ IS 'actual nonce submitted by the client';
+COMMENT ON COLUMN cs_nonce_locks.op_hash
+ IS 'hash (RC for refresh, blind coin hash for withdraw) the nonce may be
used with';
+COMMENT ON COLUMN cs_nonce_locks.max_denomination_serial
+ IS 'Maximum number of a CS denomination serial the nonce could be used with,
for GC';
+CREATE TABLE IF NOT EXISTS cs_nonce_locks_default
+ PARTITION OF cs_nonce_locks
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+CREATE TABLE IF NOT EXISTS work_shards
+ (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,last_attempt INT8 NOT NULL
+ ,start_row INT8 NOT NULL
+ ,end_row INT8 NOT NULL
+ ,completed BOOLEAN NOT NULL DEFAULT FALSE
+ ,job_name VARCHAR NOT NULL
+ ,PRIMARY KEY (job_name, start_row)
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS recoup_refresh
- ADD CONSTRAINT recoup_refresh_recoup_refresh_uuid_key
- UNIQUE (recoup_refresh_uuid)
- ,ADD CONSTRAINT recoup_refresh_rrc_serial_key
- UNIQUE (rrc_serial)
- ;
- END IF;
-
-END
-$$;
-
-COMMENT ON FUNCTION create_table_recoup_refresh
- IS 'Create the recoup_refresh table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-CREATE OR REPLACE FUNCTION create_table_prewire(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'
- ',wire_method TEXT NOT NULL'
- ',finished BOOLEAN NOT NULL DEFAULT false'
- ',failed BOOLEAN NOT NULL DEFAULT false'
- ',buf BYTEA NOT NULL'
- ') %s ;'
- ,'prewire'
- ,'PARTITION BY HASH (prewire_uuid)'
- ,shard_suffix
+COMMENT ON TABLE work_shards
+ IS 'coordinates work between multiple processes working on the same job';
+COMMENT ON COLUMN work_shards.shard_serial_id
+ IS 'unique serial number identifying the shard';
+COMMENT ON COLUMN work_shards.last_attempt
+ IS 'last time a worker attempted to work on the shard';
+COMMENT ON COLUMN work_shards.completed
+ IS 'set to TRUE once the shard is finished by a worker';
+COMMENT ON COLUMN work_shards.start_row
+ IS 'row at which the shard scope starts, inclusive';
+COMMENT ON COLUMN work_shards.end_row
+ IS 'row at which the shard scope ends, exclusive';
+COMMENT ON COLUMN work_shards.job_name
+ IS 'unique name of the job the workers on this shard are performing';
+
+CREATE INDEX IF NOT EXISTS work_shards_by_job_name_completed_last_attempt_index
+ ON work_shards
+ (job_name
+ ,completed
+ ,last_attempt
);
-END
-$$;
-
-COMMENT ON FUNCTION create_table_prewire
- IS 'Create the prewire table, if argument `shard_suffix` is empty, a
partitioned master table
- without partitions will be created. If not empty, a shard table will be
created';
-
-
-CREATE OR REPLACE FUNCTION create_table_wire_accounts()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS wire_accounts
- (payto_uri VARCHAR PRIMARY KEY
- ,master_sig BYTEA CHECK (LENGTH(master_sig)=64)
- ,is_active BOOLEAN NOT NULL
- ,last_change INT8 NOT NULL
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks(
- shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
- ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)'
- ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)'
- ',max_denomination_serial INT8 NOT NULL'
- ') %s ;'
- ,'cs_nonce_locks'
- ,'PARTITION BY HASH (nonce)'
- ,shard_suffix
+CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
+ (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,last_attempt INT8 NOT NULL
+ ,start_row INT4 NOT NULL
+ ,end_row INT4 NOT NULL
+ ,active BOOLEAN NOT NULL DEFAULT FALSE
+ ,job_name VARCHAR NOT NULL
+ ,PRIMARY KEY (job_name, start_row)
);
-
- IF shard_suffix IS NOT NULL THEN
- ALTER TABLE IF EXISTS cs_nonce_locks
- ADD CONSTRAINT cs_nonce_locks_cs_nonce_lock_serial_id_key
- UNIQUE (cs_nonce_lock_serial_id)
- ;
- END IF;
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_work_shards()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE TABLE IF NOT EXISTS work_shards
- (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,last_attempt INT8 NOT NULL
- ,start_row INT8 NOT NULL
- ,end_row INT8 NOT NULL
- ,completed BOOLEAN NOT NULL DEFAULT FALSE
- ,job_name VARCHAR NOT NULL
- ,PRIMARY KEY (job_name, start_row)
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_revolving_work_shards()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
- (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,last_attempt INT8 NOT NULL
- ,start_row INT4 NOT NULL
- ,end_row INT4 NOT NULL
- ,active BOOLEAN NOT NULL DEFAULT FALSE
- ,job_name VARCHAR NOT NULL
- ,PRIMARY KEY (job_name, start_row)
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_foreign_table(
- source_table_name VARCHAR
- ,modulus INTEGER
- ,shard_suffix VARCHAR
- ,current_shard_num INTEGER
- )
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Creating %_% on %', source_table_name, shard_suffix,
shard_suffix;
-
- EXECUTE FORMAT(
- 'CREATE FOREIGN TABLE IF NOT EXISTS %I '
- 'PARTITION OF %I '
- 'FOR VALUES WITH (MODULUS %s, REMAINDER %s) '
- 'SERVER %I'
- ,source_table_name || '_' || shard_suffix
- ,source_table_name
- ,modulus
- ,current_shard_num-1
- ,shard_suffix
+COMMENT ON TABLE revolving_work_shards
+ IS 'coordinates work between multiple processes working on the same job with
partitions that need to be repeatedly processed; unlogged because on system
crashes the locks represented by this table will have to be cleared anyway,
typically using "taler-exchange-dbinit -s"';
+COMMENT ON COLUMN revolving_work_shards.shard_serial_id
+ IS 'unique serial number identifying the shard';
+COMMENT ON COLUMN revolving_work_shards.last_attempt
+ IS 'last time a worker attempted to work on the shard';
+COMMENT ON COLUMN revolving_work_shards.active
+ IS 'set to TRUE when a worker is active on the shard';
+COMMENT ON COLUMN revolving_work_shards.start_row
+ IS 'row at which the shard scope starts, inclusive';
+COMMENT ON COLUMN revolving_work_shards.end_row
+ IS 'row at which the shard scope ends, exclusive';
+COMMENT ON COLUMN revolving_work_shards.job_name
+ IS 'unique name of the job the workers on this shard are performing';
+
+CREATE INDEX IF NOT EXISTS
revolving_work_shards_by_job_name_active_last_attempt_index
+ ON revolving_work_shards
+ (job_name
+ ,active
+ ,last_attempt
);
- EXECUTE FORMAT(
- 'ALTER FOREIGN TABLE %I OWNER TO "taler-exchange-httpd"',
- source_table_name || '_' || shard_suffix
- );
-END
-$$;
+-- Partitions
+
CREATE OR REPLACE FUNCTION create_table_partition(
source_table_name VARCHAR
@@ -1201,264 +1058,1196 @@ BEGIN
END
$$;
-CREATE OR REPLACE FUNCTION master_prepare_sharding()
-RETURNS VOID
+-- Stored procedures
+
+
+CREATE OR REPLACE FUNCTION exchange_do_withdraw(
+ IN cs_nonce BYTEA,
+ IN amount_val INT8,
+ IN amount_frac INT4,
+ IN h_denom_pub BYTEA,
+ IN rpub BYTEA,
+ IN reserve_sig BYTEA,
+ IN h_coin_envelope BYTEA,
+ IN denom_sig BYTEA,
+ IN now INT8,
+ IN min_reserve_gc INT8,
+ OUT reserve_found BOOLEAN,
+ OUT balance_ok BOOLEAN,
+ OUT kycok BOOLEAN,
+ OUT account_uuid INT8,
+ OUT ruuid INT8)
LANGUAGE plpgsql
AS $$
+DECLARE
+ reserve_gc INT8;
+DECLARE
+ denom_serial INT8;
+DECLARE
+ reserve_val INT8;
+DECLARE
+ reserve_frac INT4;
BEGIN
+-- Shards: reserves by reserve_pub (SELECT)
+-- reserves_out (INSERT, with CONFLICT detection) by wih
+-- reserves by reserve_pub (UPDATE)
+-- reserves_in by reserve_pub (SELECT)
+-- wire_targets by wire_target_serial_id
+
+SELECT denominations_serial
+ INTO denom_serial
+ FROM denominations
+ WHERE denom_pub_hash=h_denom_pub;
+
+IF NOT FOUND
+THEN
+ -- denomination unknown, should be impossible!
+ reserve_found=FALSE;
+ balance_ok=FALSE;
+ kycok=FALSE;
+ account_uuid=0;
+ ruuid=0;
+ ASSERT false, 'denomination unknown';
+ RETURN;
+END IF;
+
+
+SELECT
+ current_balance_val
+ ,current_balance_frac
+ ,gc_date
+ ,reserve_uuid
+ INTO
+ reserve_val
+ ,reserve_frac
+ ,reserve_gc
+ ,ruuid
+ FROM reserves
+ WHERE reserves.reserve_pub=rpub;
+
+IF NOT FOUND
+THEN
+ -- reserve unknown
+ reserve_found=FALSE;
+ balance_ok=FALSE;
+ kycok=FALSE;
+ account_uuid=0;
+ ruuid=2;
+ RETURN;
+END IF;
+
+-- We optimistically insert, and then on conflict declare
+-- the query successful due to idempotency.
+INSERT INTO reserves_out
+ (h_blind_ev
+ ,denominations_serial
+ ,denom_sig
+ ,reserve_uuid
+ ,reserve_sig
+ ,execution_date
+ ,amount_with_fee_val
+ ,amount_with_fee_frac)
+VALUES
+ (h_coin_envelope
+ ,denom_serial
+ ,denom_sig
+ ,ruuid
+ ,reserve_sig
+ ,now
+ ,amount_val
+ ,amount_frac)
+ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+ -- idempotent query, all constraints must be satisfied
+ reserve_found=TRUE;
+ balance_ok=TRUE;
+ kycok=TRUE;
+ account_uuid=0;
+ RETURN;
+END IF;
+
+-- Check reserve balance is sufficient.
+IF (reserve_val > amount_val)
+THEN
+ IF (reserve_frac >= amount_frac)
+ THEN
+ reserve_val=reserve_val - amount_val;
+ reserve_frac=reserve_frac - amount_frac;
+ ELSE
+ reserve_val=reserve_val - amount_val - 1;
+ reserve_frac=reserve_frac + 100000000 - amount_frac;
+ END IF;
+ELSE
+ IF (reserve_val = amount_val) AND (reserve_frac >= amount_frac)
+ THEN
+ reserve_val=0;
+ reserve_frac=reserve_frac - amount_frac;
+ ELSE
+ reserve_found=TRUE;
+ balance_ok=FALSE;
+ kycok=FALSE; -- we do not really know or care
+ account_uuid=0;
+ RETURN;
+ END IF;
+END IF;
+
+-- Calculate new expiration dates.
+min_reserve_gc=GREATEST(min_reserve_gc,reserve_gc);
+
+-- Update reserve balance.
+UPDATE reserves SET
+ gc_date=min_reserve_gc
+ ,current_balance_val=reserve_val
+ ,current_balance_frac=reserve_frac
+WHERE
+ reserves.reserve_pub=rpub;
+
+reserve_found=TRUE;
+balance_ok=TRUE;
+
+
+
+-- Special actions needed for a CS withdraw?
+IF NOT NULL cs_nonce
+THEN
+ -- Cache CS signature to prevent replays in the future
+ -- (and check if cached signature exists at the same time).
+ INSERT INTO cs_nonce_locks
+ (nonce
+ ,max_denomination_serial
+ ,op_hash)
+ VALUES
+ (cs_nonce
+ ,denom_serial
+ ,h_coin_envelope)
+ ON CONFLICT DO NOTHING;
+
+ IF NOT FOUND
+ THEN
+ -- See if the existing entry is identical.
+ SELECT 1
+ FROM cs_nonce_locks
+ WHERE nonce=cs_nonce
+ AND op_hash=h_coin_envelope;
+ IF NOT FOUND
+ THEN
+ reserve_found=FALSE;
+ balance_ok=FALSE;
+ kycok=FALSE;
+ account_uuid=0;
+ ruuid=1; -- FIXME: return error message more nicely!
+ ASSERT false, 'nonce reuse attempted by client';
+ END IF;
+ END IF;
+END IF;
- PERFORM detach_default_partitions();
- ALTER TABLE IF EXISTS wire_targets
- DROP CONSTRAINT IF EXISTS wire_targets_pkey CASCADE
- ;
- ALTER TABLE IF EXISTS reserves
- DROP CONSTRAINT IF EXISTS reserves_pkey CASCADE
- ;
+-- Obtain KYC status based on the last wire transfer into
+-- this reserve. FIXME: likely not adequate for reserves that got P2P
transfers!
+SELECT
+ kyc_ok
+ ,wire_source_serial_id
+ INTO
+ kycok
+ ,account_uuid
+ FROM reserves_in
+ JOIN wire_targets ON (wire_source_serial_id = wire_target_serial_id)
+ WHERE reserve_pub=rpub
+ LIMIT 1; -- limit 1 should not be required (without p2p transfers)
- ALTER TABLE IF EXISTS reserves_in
- DROP CONSTRAINT IF EXISTS reserves_in_pkey CASCADE
- ;
- ALTER TABLE IF EXISTS reserves_close
- DROP CONSTRAINT IF EXISTS reserves_close_pkey CASCADE
- ;
+END $$;
- ALTER TABLE IF EXISTS reserves_out
- DROP CONSTRAINT IF EXISTS reserves_out_pkey CASCADE
- ,DROP CONSTRAINT IF EXISTS reserves_out_denominations_serial_fkey
- ,DROP CONSTRAINT IF EXISTS reserves_out_h_blind_ev_key
- ;
+COMMENT ON FUNCTION exchange_do_withdraw(BYTEA, INT8, INT4, BYTEA, BYTEA,
BYTEA, BYTEA, BYTEA, INT8, INT8)
+ IS 'Checks whether the reserve has sufficient balance for a withdraw
operation (or the request is repeated and was previously approved) and if so
updates the database with the result';
- ALTER TABLE IF EXISTS known_coins
- DROP CONSTRAINT IF EXISTS known_coins_pkey CASCADE
- ,DROP CONSTRAINT IF EXISTS known_coins_denominations_serial_fkey
- ;
- ALTER TABLE IF EXISTS refresh_commitments
- DROP CONSTRAINT IF EXISTS refresh_commitments_pkey CASCADE
- ,DROP CONSTRAINT IF EXISTS refresh_old_coin_pub_fkey
- ;
- ALTER TABLE IF EXISTS refresh_revealed_coins
- DROP CONSTRAINT IF EXISTS refresh_revealed_coins_pkey CASCADE
- ,DROP CONSTRAINT IF EXISTS refresh_revealed_coins_denominations_serial_fkey
- ;
+CREATE OR REPLACE FUNCTION exchange_do_withdraw_limit_check(
+ IN ruuid INT8,
+ IN start_time INT8,
+ IN upper_limit_val INT8,
+ IN upper_limit_frac INT4,
+ OUT below_limit BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ total_val INT8;
+DECLARE
+ total_frac INT8; -- INT4 could overflow during accumulation!
+BEGIN
+-- NOTE: Read-only, but crosses shards.
+-- Shards: reserves by reserve_pub
+-- reserves_out by reserve_uuid -- crosses shards!!
+
+
+SELECT
+ SUM(amount_with_fee_val) -- overflow here is not plausible
+ ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits
+ INTO
+ total_val
+ ,total_frac
+ FROM reserves_out
+ WHERE reserve_uuid=ruuid
+ AND execution_date > start_time;
+
+-- normalize result
+total_val = total_val + total_frac / 100000000;
+total_frac = total_frac % 100000000;
+
+-- compare to threshold
+below_limit = (total_val < upper_limit_val) OR
+ ( (total_val = upper_limit_val) AND
+ (total_frac <= upper_limit_frac) );
+END $$;
+
+COMMENT ON FUNCTION exchange_do_withdraw_limit_check(INT8, INT8, INT8, INT4)
+ IS 'Check whether the withdrawals from the given reserve since the given
time are below the given threshold';
+
+
+-- NOTE: experiment, currently dead, see postgres_Start_deferred_wire_out;
+-- now done inline. FIXME: Remove code here once inline version is confirmed
working nicely!
+CREATE OR REPLACE PROCEDURE defer_wire_out()
+LANGUAGE plpgsql
+AS $$
+BEGIN
- ALTER TABLE IF EXISTS refresh_transfer_keys
- DROP CONSTRAINT IF EXISTS refresh_transfer_keys_pkey CASCADE
- ;
+IF EXISTS (
+ SELECT 1
+ FROM information_Schema.constraint_column_usage
+ WHERE table_name='wire_out'
+ AND constraint_name='wire_out_ref')
+THEN
+ SET CONSTRAINTS wire_out_ref DEFERRED;
+END IF;
+
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_deposit(
+ IN in_amount_with_fee_val INT8,
+ IN in_amount_with_fee_frac INT4,
+ IN in_h_contract_terms BYTEA,
+ IN in_wire_salt BYTEA,
+ IN in_wallet_timestamp INT8,
+ IN in_exchange_timestamp INT8,
+ IN in_refund_deadline INT8,
+ IN in_wire_deadline INT8,
+ IN in_merchant_pub BYTEA,
+ IN in_receiver_wire_account VARCHAR,
+ IN in_h_payto BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_pub BYTEA,
+ IN in_coin_sig BYTEA,
+ IN in_shard INT8,
+ IN in_extension_blocked BOOLEAN,
+ IN in_extension_details VARCHAR,
+ OUT out_exchange_timestamp INT8,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ wtsi INT8; -- wire target serial id
+DECLARE
+ xdi INT8; -- eXstension details serial id
+BEGIN
+-- Shards: INSERT extension_details (by extension_details_serial_id)
+-- INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING;
+-- INSERT deposits (by shard + known_coin_id, merchant_pub,
h_contract_terms), ON CONFLICT DO NOTHING;
+-- UPDATE known_coins (by coin_pub)
+
+IF NOT NULL in_extension_details
+THEN
+ INSERT INTO extension_details
+ (extension_options)
+ VALUES
+ (in_extension_details)
+ RETURNING extension_details_serial_id INTO xdi;
+ELSE
+ xdi=NULL;
+END IF;
+
+
+INSERT INTO wire_targets
+ (h_payto
+ ,payto_uri)
+ VALUES
+ (in_h_payto
+ ,in_receiver_wire_account)
+ON CONFLICT DO NOTHING -- for CONFLICT ON (h_payto)
+ RETURNING wire_target_serial_id INTO wtsi;
+
+IF NOT FOUND
+THEN
+ SELECT wire_target_serial_id
+ INTO wtsi
+ FROM wire_targets
+ WHERE h_payto=in_h_payto;
+END IF;
+
+
+INSERT INTO deposits
+ (shard
+ ,known_coin_id
+ ,amount_with_fee_val
+ ,amount_with_fee_frac
+ ,wallet_timestamp
+ ,exchange_timestamp
+ ,refund_deadline
+ ,wire_deadline
+ ,merchant_pub
+ ,h_contract_terms
+ ,coin_sig
+ ,wire_salt
+ ,wire_target_serial_id
+ ,extension_blocked
+ ,extension_details_serial_id
+ )
+ VALUES
+ (in_shard
+ ,in_known_coin_id
+ ,in_amount_with_fee_val
+ ,in_amount_with_fee_frac
+ ,in_wallet_timestamp
+ ,in_exchange_timestamp
+ ,in_refund_deadline
+ ,in_wire_deadline
+ ,in_merchant_pub
+ ,in_h_contract_terms
+ ,in_coin_sig
+ ,in_wire_salt
+ ,wtsi
+ ,in_extension_blocked
+ ,xdi)
+ ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+ -- Idempotency check: see if an identical record exists.
+ -- Note that by checking 'coin_sig', we implicitly check
+ -- identity over everything that the signature covers.
+ -- We do select over merchant_pub and h_contract_terms
+ -- primarily here to maximally use the existing index.
+ SELECT
+ exchange_timestamp
+ INTO
+ out_exchange_timestamp
+ FROM deposits
+ WHERE
+ shard=in_shard AND
+ known_coin_id=in_known_coin_id AND
+ merchant_pub=in_merchant_pub AND
+ h_contract_terms=in_h_contract_terms AND
+ coin_sig=in_coin_sig;
+
+ IF NOT FOUND
+ THEN
+ -- Deposit exists, but with differences. Not allowed.
+ out_balance_ok=FALSE;
+ out_conflict=TRUE;
+ RETURN;
+ END IF;
- ALTER TABLE IF EXISTS deposits
- DROP CONSTRAINT IF EXISTS deposits_pkey CASCADE
- ,DROP CONSTRAINT IF EXISTS deposits_extension_details_serial_id_fkey
- ,DROP CONSTRAINT IF EXISTS
deposits_shard_known_coin_id_merchant_pub_h_contract_terms_key CASCADE
- ;
+ -- Idempotent request known, return success.
+ out_balance_ok=TRUE;
+ out_conflict=FALSE;
+
+ RETURN;
+END IF;
+
+
+out_exchange_timestamp=in_exchange_timestamp;
+
+-- Check and update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=remaining_frac-in_amount_with_fee_frac
+ + CASE
+ WHEN remaining_frac < in_amount_with_fee_frac
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining_val=remaining_val-in_amount_with_fee_val
+ - CASE
+ WHEN remaining_frac < in_amount_with_fee_frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_coin_pub
+ AND ( (remaining_val > in_amount_with_fee_val) OR
+ ( (remaining_frac >= in_amount_with_fee_frac) AND
+ (remaining_val >= in_amount_with_fee_val) ) );
+
+IF NOT FOUND
+THEN
+ -- Insufficient balance.
+ out_balance_ok=FALSE;
+ out_conflict=FALSE;
+ RETURN;
+END IF;
+
+-- Everything fine, return success!
+out_balance_ok=TRUE;
+out_conflict=FALSE;
+
+END $$;
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_melt(
+ IN in_cs_rms BYTEA,
+ IN in_amount_with_fee_val INT8,
+ IN in_amount_with_fee_frac INT4,
+ IN in_rc BYTEA,
+ IN in_old_coin_pub BYTEA,
+ IN in_old_coin_sig BYTEA,
+ IN in_known_coin_id INT8, -- not used, but that's OK
+ IN in_h_age_commitment BYTEA,
+ IN in_noreveal_index INT4,
+ IN in_zombie_required BOOLEAN,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_zombie_bad BOOLEAN,
+ OUT out_noreveal_index INT4)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ denom_max INT8;
+BEGIN
+-- Shards: INSERT refresh_commitments (by rc)
+-- (rare:) SELECT refresh_commitments (by old_coin_pub) -- crosses shards!
+-- (rare:) SEELCT refresh_revealed_coins (by melt_serial_id)
+-- (rare:) PERFORM recoup_refresh (by rrc_serial) -- crosses shards!
+-- UPDATE known_coins (by coin_pub)
+
+INSERT INTO refresh_commitments
+ (rc
+ ,old_coin_pub
+ ,old_coin_sig
+ ,amount_with_fee_val
+ ,amount_with_fee_frac
+ ,h_age_commitment
+ ,noreveal_index
+ )
+ VALUES
+ (in_rc
+ ,in_old_coin_pub
+ ,in_old_coin_sig
+ ,in_amount_with_fee_val
+ ,in_amount_with_fee_frac
+ ,in_h_age_commitment
+ ,in_noreveal_index)
+ ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+ -- Idempotency check: see if an identical record exists.
+ out_noreveal_index=-1;
+ SELECT
+ noreveal_index
+ INTO
+ out_noreveal_index
+ FROM refresh_commitments
+ WHERE rc=in_rc;
+ out_balance_ok=FOUND;
+ out_zombie_bad=FALSE; -- zombie is OK
+ RETURN;
+END IF;
+
+
+IF in_zombie_required
+THEN
+ -- Check if this coin was part of a refresh
+ -- operation that was subsequently involved
+ -- in a recoup operation. We begin by all
+ -- refresh operations our coin was involved
+ -- with, then find all associated reveal
+ -- operations, and then see if any of these
+ -- reveal operations was involved in a recoup.
+ PERFORM
+ FROM recoup_refresh
+ WHERE rrc_serial IN
+ (SELECT rrc_serial
+ FROM refresh_revealed_coins
+ WHERE melt_serial_id IN
+ (SELECT melt_serial_id
+ FROM refresh_commitments
+ WHERE old_coin_pub=in_old_coin_pub));
+ IF NOT FOUND
+ THEN
+ out_zombie_bad=TRUE;
+ out_balance_ok=FALSE;
+ RETURN;
+ END IF;
+END IF;
+
+out_zombie_bad=FALSE; -- zombie is OK
+
+
+-- Check and update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=remaining_frac-in_amount_with_fee_frac
+ + CASE
+ WHEN remaining_frac < in_amount_with_fee_frac
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining_val=remaining_val-in_amount_with_fee_val
+ - CASE
+ WHEN remaining_frac < in_amount_with_fee_frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_old_coin_pub
+ AND ( (remaining_val > in_amount_with_fee_val) OR
+ ( (remaining_frac >= in_amount_with_fee_frac) AND
+ (remaining_val >= in_amount_with_fee_val) ) );
+
+IF NOT FOUND
+THEN
+ -- Insufficient balance.
+ out_noreveal_index=-1;
+ out_balance_ok=FALSE;
+ RETURN;
+END IF;
+
+
+
+-- Special actions needed for a CS melt?
+IF NOT NULL in_cs_rms
+THEN
+ -- Get maximum denominations serial value in
+ -- existence, this will determine how long the
+ -- nonce will be locked.
+ SELECT
+ denominations_serial
+ INTO
+ denom_max
+ FROM denominations
+ ORDER BY denominations_serial DESC
+ LIMIT 1;
+
+ -- Cache CS signature to prevent replays in the future
+ -- (and check if cached signature exists at the same time).
+ INSERT INTO cs_nonce_locks
+ (nonce
+ ,max_denomination_serial
+ ,op_hash)
+ VALUES
+ (cs_rms
+ ,denom_serial
+ ,in_rc)
+ ON CONFLICT DO NOTHING;
+
+ IF NOT FOUND
+ THEN
+ -- Record exists, make sure it is the same
+ SELECT 1
+ FROM cs_nonce_locks
+ WHERE nonce=cs_rms
+ AND op_hash=in_rc;
+
+ IF NOT FOUND
+ THEN
+ -- Nonce reuse detected
+ out_balance_ok=FALSE;
+ out_zombie_bad=FALSE;
+ out_noreveal_index=42; -- FIXME: return error message more nicely!
+ ASSERT false, 'nonce reuse attempted by client';
+ END IF;
+ END IF;
+END IF;
- ALTER TABLE IF EXISTS refunds
- DROP CONSTRAINT IF EXISTS refunds_pkey CASCADE
- ;
- ALTER TABLE IF EXISTS wire_out
- DROP CONSTRAINT IF EXISTS wire_out_pkey CASCADE
- ,DROP CONSTRAINT IF EXISTS wire_out_wtid_raw_key CASCADE
- ;
- ALTER TABLE IF EXISTS aggregation_tracking
- DROP CONSTRAINT IF EXISTS aggregation_tracking_pkey CASCADE
- ,DROP CONSTRAINT IF EXISTS aggregation_tracking_wtid_raw_fkey
- ;
- ALTER TABLE IF EXISTS recoup
- DROP CONSTRAINT IF EXISTS recoup_pkey CASCADE
- ;
+-- Everything fine, return success!
+out_balance_ok=TRUE;
+out_noreveal_index=in_noreveal_index;
- ALTER TABLE IF EXISTS recoup_refresh
- DROP CONSTRAINT IF EXISTS recoup_refresh_pkey CASCADE
- ;
+END $$;
- ALTER TABLE IF EXISTS prewire
- DROP CONSTRAINT IF EXISTS prewire_pkey CASCADE
- ;
- ALTER TABLE IF EXISTS cs_nonce_locks
- DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE
- ;
-END
-$$;
-
-
-CREATE OR REPLACE FUNCTION create_shard_server(
- shard_suffix VARCHAR
- ,total_num_shards INTEGER
- ,current_shard_num INTEGER
- ,remote_host VARCHAR
- ,remote_user VARCHAR
- ,remote_user_password VARCHAR
- ,remote_db_name VARCHAR DEFAULT 'taler-exchange'
- ,remote_port INTEGER DEFAULT '5432'
-)
-RETURNS VOID
+CREATE OR REPLACE FUNCTION exchange_do_refund(
+ IN in_amount_with_fee_val INT8,
+ IN in_amount_with_fee_frac INT4,
+ IN in_amount_val INT8,
+ IN in_amount_frac INT4,
+ IN in_deposit_fee_val INT8,
+ IN in_deposit_fee_frac INT4,
+ IN in_h_contract_terms BYTEA,
+ IN in_rtransaction_id INT8,
+ IN in_deposit_shard INT8,
+ IN in_known_coin_id INT8,
+ IN in_coin_pub BYTEA,
+ IN in_merchant_pub BYTEA,
+ IN in_merchant_sig BYTEA,
+ OUT out_not_found BOOLEAN,
+ OUT out_refund_ok BOOLEAN,
+ OUT out_gone BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ dsi INT8; -- ID of deposit being refunded
+DECLARE
+ tmp_val INT8; -- total amount refunded
+DECLARE
+ tmp_frac INT8; -- total amount refunded
+DECLARE
+ deposit_val INT8; -- amount that was originally deposited
+DECLARE
+ deposit_frac INT8; -- amount that was originally deposited
+BEGIN
+-- Shards: SELECT deposits (by shard, known_coin_id,h_contract_terms,
merchant_pub)
+-- INSERT refunds (by deposit_serial_id, rtransaction_id) ON CONFLICT
DO NOTHING
+-- SELECT refunds (by deposit_serial_id)
+-- UPDATE known_coins (by coin_pub)
+
+SELECT
+ deposit_serial_id
+ ,amount_with_fee_val
+ ,amount_with_fee_frac
+ ,done
+INTO
+ dsi
+ ,deposit_val
+ ,deposit_frac
+ ,out_gone
+FROM deposits
+WHERE shard=in_deposit_shard
+ AND known_coin_id=in_known_coin_id
+ AND h_contract_terms=in_h_contract_terms
+ AND merchant_pub=in_merchant_pub;
+
+IF NOT FOUND
+THEN
+ -- No matching deposit found!
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=TRUE;
+ out_gone=FALSE;
+ RETURN;
+END IF;
+
+
+INSERT INTO refunds
+ (deposit_serial_id
+ ,merchant_sig
+ ,rtransaction_id
+ ,amount_with_fee_val
+ ,amount_with_fee_frac
+ )
+ VALUES
+ (dsi
+ ,in_merchant_sig
+ ,in_rtransaction_id
+ ,in_amount_with_fee_val
+ ,in_amount_with_fee_frac)
+ ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+ -- Idempotency check: see if an identical record exists.
+ -- Note that by checking 'coin_sig', we implicitly check
+ -- identity over everything that the signature covers.
+ -- We do select over merchant_pub and h_contract_terms
+ -- primarily here to maximally use the existing index.
+ PERFORM
+ FROM refunds
+ WHERE
+ deposit_serial_id=dsi AND
+ rtransaction_id=in_rtransaction_id AND
+ amount_with_fee_val=in_amount_with_fee_val AND
+ amount_with_fee_frac=in_amount_with_fee_frac;
+
+ IF NOT FOUND
+ THEN
+ -- Deposit exists, but have conflicting refund.
+ out_refund_ok=FALSE;
+ out_conflict=TRUE;
+ out_not_found=FALSE;
+ RETURN;
+ END IF;
+
+ -- Idempotent request known, return success.
+ out_refund_ok=TRUE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ out_gone=FALSE;
+ RETURN;
+END IF;
+
+
+IF out_gone
+THEN
+ -- money already sent to the merchant. Tough luck.
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ RETURN;
+END IF;
+
+
+
+-- Check refund balance invariant.
+SELECT
+ SUM(amount_with_fee_val) -- overflow here is not plausible
+ ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits
+ INTO
+ tmp_val
+ ,tmp_frac
+ FROM refunds
+ WHERE
+ deposit_serial_id=dsi;
+IF tmp_val IS NULL
+THEN
+ RAISE NOTICE 'failed to sum up existing refunds';
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ RETURN;
+END IF;
+
+-- Normalize result before continuing
+tmp_val = tmp_val + tmp_frac / 100000000;
+tmp_frac = tmp_frac % 100000000;
+
+-- Actually check if the deposits are sufficient for the refund. Verbosely. ;-)
+IF (tmp_val < deposit_val)
+THEN
+ out_refund_ok=TRUE;
+ELSE
+ IF (tmp_val = deposit_val) AND (tmp_frac <= deposit_frac)
+ THEN
+ out_refund_ok=TRUE;
+ ELSE
+ out_refund_ok=FALSE;
+ END IF;
+END IF;
+
+IF (tmp_val = deposit_val) AND (tmp_frac = deposit_frac)
+THEN
+ -- Refunds have reached the full value of the original
+ -- deposit. Also refund the deposit fee.
+ in_amount_frac = in_amount_frac + in_deposit_fee_frac;
+ in_amount_val = in_amount_val + in_deposit_fee_val;
+
+ -- Normalize result before continuing
+ in_amount_val = in_amount_val + in_amount_frac / 100000000;
+ in_amount_frac = in_amount_frac % 100000000;
+END IF;
+
+-- Update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=remaining_frac+in_amount_frac
+ - CASE
+ WHEN remaining_frac+in_amount_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining_val=remaining_val+in_amount_val
+ + CASE
+ WHEN remaining_frac+in_amount_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_coin_pub;
+
+
+out_conflict=FALSE;
+out_not_found=FALSE;
+
+END $$;
+
+-- COMMENT ON FUNCTION exchange_do_refund(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN)
+-- IS 'Executes a refund operation, checking that the corresponding deposit
was sufficient to cover the refunded amount';
+
+
+CREATE OR REPLACE FUNCTION exchange_do_recoup_to_reserve(
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_out_serial_id INT8,
+ IN in_coin_blind BYTEA,
+ IN in_coin_pub BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_sig BYTEA,
+ IN in_reserve_gc INT8,
+ IN in_reserve_expiration INT8,
+ IN in_recoup_timestamp INT8,
+ OUT out_recoup_ok BOOLEAN,
+ OUT out_internal_failure BOOLEAN,
+ OUT out_recoup_timestamp INT8)
LANGUAGE plpgsql
AS $$
+DECLARE
+ tmp_val INT8; -- amount recouped
+DECLARE
+ tmp_frac INT8; -- amount recouped
BEGIN
+-- Shards: SELECT known_coins (by coin_pub)
+-- SELECT recoup (by known_coin_id)
+-- UPDATE known_coins (by coin_pub)
+-- UPDATE reserves (by reserve_pub)
+-- INSERT recoup (by known_coin_id)
+
+out_internal_failure=FALSE;
+
+
+-- Check remaining balance of the coin.
+SELECT
+ remaining_frac
+ ,remaining_val
+ INTO
+ tmp_frac
+ ,tmp_val
+FROM known_coins
+ WHERE coin_pub=in_coin_pub;
+
+IF NOT FOUND
+THEN
+ out_internal_failure=TRUE;
+ out_recoup_ok=FALSE;
+ RETURN;
+END IF;
+
+IF tmp_val + tmp_frac = 0
+THEN
+ -- Check for idempotency
+ SELECT
+ recoup_timestamp
+ INTO
+ out_recoup_timestamp
+ FROM recoup
+ WHERE known_coin_id=in_known_coin_id;
+
+ out_recoup_ok=FOUND;
+ RETURN;
+END IF;
+
+
+-- Update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=0
+ ,remaining_val=0
+ WHERE coin_pub=in_coin_pub;
+
+
+-- Credit the reserve and update reserve timers.
+UPDATE reserves
+ SET
+ current_balance_frac=current_balance_frac+tmp_frac
+ - CASE
+ WHEN current_balance_frac+tmp_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ current_balance_val=current_balance_val+tmp_val
+ + CASE
+ WHEN current_balance_frac+tmp_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END,
+ gc_date=GREATEST(gc_date, in_reserve_gc),
+ expiration_date=GREATEST(expiration_date, in_reserve_expiration)
+ WHERE reserve_pub=in_reserve_pub;
+
+
+IF NOT FOUND
+THEN
+ RAISE NOTICE 'failed to increase reserve balance from recoup';
+ out_recoup_ok=TRUE;
+ out_internal_failure=TRUE;
+ RETURN;
+END IF;
+
+
+INSERT INTO recoup
+ (known_coin_id
+ ,coin_sig
+ ,coin_blind
+ ,amount_val
+ ,amount_frac
+ ,recoup_timestamp
+ ,reserve_out_serial_id
+ )
+VALUES
+ (in_known_coin_id
+ ,in_coin_sig
+ ,in_coin_blind
+ ,tmp_val
+ ,tmp_frac
+ ,in_recoup_timestamp
+ ,in_reserve_out_serial_id);
- RAISE NOTICE 'Creating server %s', remote_host;
+-- Normal end, everything is fine.
+out_recoup_ok=TRUE;
+out_recoup_timestamp=in_recoup_timestamp;
- EXECUTE FORMAT(
- 'CREATE SERVER IF NOT EXISTS %I '
- 'FOREIGN DATA WRAPPER postgres_fdw '
- 'OPTIONS (dbname %L, host %L, port %L)'
- ,shard_suffix
- ,remote_db_name
- ,remote_host
- ,remote_port
- );
+END $$;
- EXECUTE FORMAT(
- 'CREATE USER MAPPING IF NOT EXISTS '
- 'FOR "taler-exchange-httpd" SERVER %I '
- 'OPTIONS (user %L, password %L)'
- ,shard_suffix
- ,remote_user
- ,remote_user_password
- );
+-- COMMENT ON FUNCTION exchange_do_recoup_to_reserve(INT8, INT4, BYTEA,
BOOLEAN, BOOLEAN)
+-- IS 'Executes a recoup of a coin that was withdrawn from a reserve';
- PERFORM create_foreign_table(
- 'wire_targets'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'reserves'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'reserves_in'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'reserves_out'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'reserves_close'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'known_coins'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'refresh_commitments'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'refresh_revealed_coins'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'refresh_transfer_keys'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'deposits'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'refunds'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'wire_out'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'aggregation_tracking'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'recoup'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'recoup_refresh'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'prewire'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
- PERFORM create_foreign_table(
- 'cs_nonce_locks'
- ,total_num_shards
- ,shard_suffix
- ,current_shard_num
- );
-END
-$$;
-CREATE OR REPLACE FUNCTION setup_shard_db(
- shard_suffix VARCHAR
-)
-RETURNS VOID
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_recoup_to_coin(
+ IN in_old_coin_pub BYTEA,
+ IN in_rrc_serial INT8,
+ IN in_coin_blind BYTEA,
+ IN in_coin_pub BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_sig BYTEA,
+ IN in_recoup_timestamp INT8,
+ OUT out_recoup_ok BOOLEAN,
+ OUT out_internal_failure BOOLEAN,
+ OUT out_recoup_timestamp INT8)
LANGUAGE plpgsql
AS $$
+DECLARE
+ tmp_val INT8; -- amount recouped
+DECLARE
+ tmp_frac INT8; -- amount recouped
BEGIN
- PERFORM create_table_wire_targets(shard_suffix);
- PERFORM create_table_reserves(shard_suffix);
- PERFORM create_table_reserves_in(shard_suffix);
- PERFORM create_table_reserves_out(shard_suffix);
- PERFORM create_table_reserves_close(shard_suffix);
- PERFORM create_table_known_coins(shard_suffix);
- PERFORM create_table_refresh_commitments(shard_suffix);
- PERFORM create_table_refresh_revealed_coins(shard_suffix);
- PERFORM create_table_refresh_transfer_keys(shard_suffix);
- PERFORM create_table_deposits(shard_suffix);
- PERFORM create_table_refunds(shard_suffix);
- PERFORM create_table_wire_out(shard_suffix);
- PERFORM create_table_aggregation_tracking(shard_suffix);
- PERFORM create_table_recoup(shard_suffix);
- PERFORM create_table_recoup_refresh(shard_suffix);
- PERFORM create_table_prewire(shard_suffix);
- PERFORM create_table_cs_nonce_locks(shard_suffix);
+-- Shards: UPDATE known_coins (by coin_pub)
+-- SELECT recoup_refresh (by known_coin_id)
+-- UPDATE known_coins (by coin_pub)
+-- INSERT recoup_refresh (by known_coin_id)
+
+
+out_internal_failure=FALSE;
+
+
+-- Check remaining balance of the coin.
+SELECT
+ remaining_frac
+ ,remaining_val
+ INTO
+ tmp_frac
+ ,tmp_val
+FROM known_coins
+ WHERE coin_pub=in_coin_pub;
+
+IF NOT FOUND
+THEN
+ out_internal_failure=TRUE;
+ out_recoup_ok=FALSE;
+ RETURN;
+END IF;
+
+IF tmp_val + tmp_frac = 0
+THEN
+ -- Check for idempotency
+ SELECT
+ recoup_timestamp
+ INTO
+ out_recoup_timestamp
+ FROM recoup_refresh
+ WHERE known_coin_id=in_known_coin_id;
+ out_recoup_ok=FOUND;
+ RETURN;
+END IF;
+
+-- Update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=0
+ ,remaining_val=0
+ WHERE coin_pub=in_coin_pub;
+
+
+-- Credit the old coin.
+UPDATE known_coins
+ SET
+ remaining_frac=remaining_frac+tmp_frac
+ - CASE
+ WHEN remaining_frac+tmp_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining_val=remaining_val+tmp_val
+ + CASE
+ WHEN remaining_frac+tmp_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_old_coin_pub;
+
+
+IF NOT FOUND
+THEN
+ RAISE NOTICE 'failed to increase old coin balance from recoup';
+ out_recoup_ok=TRUE;
+ out_internal_failure=TRUE;
+ RETURN;
+END IF;
+
+
+INSERT INTO recoup_refresh
+ (known_coin_id
+ ,coin_sig
+ ,coin_blind
+ ,amount_val
+ ,amount_frac
+ ,recoup_timestamp
+ ,rrc_serial
+ )
+VALUES
+ (in_known_coin_id
+ ,in_coin_sig
+ ,in_coin_blind
+ ,tmp_val
+ ,tmp_frac
+ ,in_recoup_timestamp
+ ,in_rrc_serial);
-END
-$$;
+-- Normal end, everything is fine.
+out_recoup_ok=TRUE;
+out_recoup_timestamp=in_recoup_timestamp;
+
+END $$;
+
+
+-- COMMENT ON FUNCTION exchange_do_recoup_to_coin(INT8, INT4, BYTEA, BOOLEAN,
BOOLEAN)
+-- IS 'Executes a recoup-refresh of a coin that was obtained from a
refresh-reveal process';
+
+
+
+CREATE OR REPLACE PROCEDURE exchange_do_gc(
+ IN in_ancient_date INT8,
+ IN in_now INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ reserve_uuid_min INT8; -- minimum reserve UUID still alive
+DECLARE
+ melt_min INT8; -- minimum melt still alive
+DECLARE
+ coin_min INT8; -- minimum known_coin still alive
+DECLARE
+ deposit_min INT8; -- minimum deposit still alive
+DECLARE
+ reserve_out_min INT8; -- minimum reserve_out still alive
+DECLARE
+ denom_min INT8; -- minimum denomination still alive
+BEGIN
+DELETE FROM prewire
+ WHERE finished=TRUE;
+
+DELETE FROM wire_fee
+ WHERE end_date < in_ancient_date;
+
+-- TODO: use closing fee as threshold?
+DELETE FROM reserves
+ WHERE gc_date < in_now
+ AND current_balance_val = 0
+ AND current_balance_frac = 0;
+
+SELECT
+ reserve_out_serial_id
+ INTO
+ reserve_out_min
+ FROM reserves_out
+ ORDER BY reserve_out_serial_id ASC
+ LIMIT 1;
+
+DELETE FROM recoup
+ WHERE reserve_out_serial_id < reserve_out_min;
+
+
+SELECT
+ reserve_uuid
+ INTO
+ reserve_uuid_min
+ FROM reserves
+ ORDER BY reserve_uuid ASC
+ LIMIT 1;
+
+DELETE FROM reserves_out
+ WHERE reserve_uuid < reserve_uuid_min;
+
+
+DELETE FROM denominations
+ WHERE expire_legal < in_now
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM reserves_out)
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM known_coins
+ WHERE known_coin_id IN
+ (SELECT DISTINCT known_coin_id
+ FROM recoup))
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM known_coins
+ WHERE known_coin_id IN
+ (SELECT DISTINCT known_coin_id
+ FROM recoup_refresh));
+
+SELECT
+ melt_serial_id
+ INTO
+ melt_min
+ FROM refresh_commitments
+ ORDER BY melt_serial_id ASC
+ LIMIT 1;
+
+DELETE FROM refresh_revealed_coins
+ WHERE melt_serial_id < melt_min;
+
+DELETE FROM refresh_transfer_keys
+ WHERE melt_serial_id < melt_min;
+
+SELECT
+ known_coin_id
+ INTO
+ coin_min
+ FROM known_coins
+ ORDER BY known_coin_id ASC
+ LIMIT 1;
+
+DELETE FROM deposits
+ WHERE known_coin_id < coin_min;
+
+SELECT
+ deposit_serial_id
+ INTO
+ deposit_min
+ FROM deposits
+ ORDER BY deposit_serial_id ASC
+ LIMIT 1;
+
+DELETE FROM refunds
+ WHERE deposit_serial_id < deposit_min;
+
+DELETE FROM aggregation_tracking
+ WHERE deposit_serial_id < deposit_min;
+
+SELECT
+ denominations_serial
+ INTO
+ denom_min
+ FROM denominations
+ ORDER BY denominations_serial ASC
+ LIMIT 1;
+
+DELETE FROM cs_nonce_locks
+ WHERE max_denomination_serial <= denom_min;
+
+END $$;
+
+
+-- Complete transaction
COMMIT;
diff --git a/src/exchangedb/exchange-0002.sql b/src/exchangedb/exchange-0002.sql
deleted file mode 100644
index 0973a8d2..00000000
--- a/src/exchangedb/exchange-0002.sql
+++ /dev/null
@@ -1,1814 +0,0 @@
---
--- 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/>
---
-
--- Everything in one big transaction
-BEGIN;
-
--- Check patch versioning is in place.
-SELECT _v.register_patch('exchange-0002', NULL, NULL);
-
-SELECT create_table_denominations();
-
-COMMENT ON TABLE denominations
- IS 'Main denominations table. All the valid denominations the exchange knows
about.';
-COMMENT ON COLUMN denominations.denom_type
- IS 'determines cipher type for blind signatures used with this denomination;
0 is for RSA';
-COMMENT ON COLUMN denominations.age_mask
- IS 'bitmask with the age restrictions that are being used for this
denomination; 0 if denomination does not support the use of age restrictions';
-COMMENT ON COLUMN denominations.denominations_serial
- IS 'needed for exchange-auditor replication logic';
-
-CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index
- ON denominations
- (expire_legal);
-
-SELECT create_table_denominations_revocations();
-
-COMMENT ON TABLE denomination_revocations
- IS 'remembering which denomination keys have been revoked';
-
-
-SELECT create_table_wire_targets();
-
-COMMENT ON TABLE wire_targets
- IS 'All senders and recipients of money via the exchange';
-COMMENT ON COLUMN wire_targets.payto_uri
- IS 'Can be a regular bank account, or also be a URI identifying a
reserve-account (for P2P payments)';
-COMMENT ON COLUMN wire_targets.h_payto
- IS 'Unsalted hash of payto_uri';
-COMMENT ON COLUMN wire_targets.kyc_ok
- IS 'true if the KYC check was passed successfully';
-COMMENT ON COLUMN wire_targets.external_id
- IS 'Name of the user that was used for OAuth 2.0-based legitimization';
-
-CREATE TABLE IF NOT EXISTS wire_targets_default
- PARTITION OF wire_targets
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
--- FIXME partition by serial_id rather than h_payto,
--- it is used more in join conditions - crucial for sharding to select this.
--- Author: (Boss Marco)
-CREATE INDEX IF NOT EXISTS wire_targets_wire_target_serial_id_index
- ON wire_targets
- (wire_target_serial_id);
-COMMENT ON INDEX wire_targets_wire_target_serial_id_index
- IS 'used in exchange_do_withdraw';
-
-
-SELECT create_table_reserves();
-
-COMMENT ON TABLE reserves
- IS 'Summarizes the balance of a reserve. Updated when new funds are added or
withdrawn.';
-COMMENT ON COLUMN reserves.reserve_pub
- IS 'EdDSA public key of the reserve. Knowledge of the private key implies
ownership over the balance.';
-COMMENT ON COLUMN reserves.current_balance_val
- IS 'Current balance remaining with the reserve';
-COMMENT ON COLUMN reserves.expiration_date
- IS 'Used to trigger closing of reserves that have not been drained after
some time';
-COMMENT ON COLUMN reserves.gc_date
- IS 'Used to forget all information about a reserve during garbage
collection';
-CREATE TABLE IF NOT EXISTS reserves_default
- PARTITION OF reserves
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS reserves_by_expiration_index
- ON reserves
- (expiration_date
- ,current_balance_val
- ,current_balance_frac
- );
-COMMENT ON INDEX reserves_by_expiration_index
- IS 'used in get_expired_reserves';
-CREATE INDEX IF NOT EXISTS reserves_by_reserve_uuid_index
- ON reserves
- (reserve_uuid);
-CREATE INDEX IF NOT EXISTS reserves_by_gc_date_index
- ON reserves
- (gc_date);
-COMMENT ON INDEX reserves_by_gc_date_index
- IS 'for reserve garbage collection';
-
-
-SELECT create_table_reserves_in();
-
-COMMENT ON TABLE reserves_in
- IS 'list of transfers of funds into the reserves, one per incoming wire
transfer';
-COMMENT ON COLUMN reserves_in.wire_source_serial_id
- IS 'Identifies the debited bank account and KYC status';
-COMMENT ON COLUMN reserves_in.reserve_pub
- IS 'Public key of the reserve. Private key signifies ownership of the
remaining balance.';
-COMMENT ON COLUMN reserves_in.credit_val
- IS 'Amount that was transferred into the reserve';
-CREATE TABLE IF NOT EXISTS reserves_in_default
- PARTITION OF reserves_in
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS reserves_in_by_reserve_in_serial_id_index
- ON reserves_in
- (reserve_in_serial_id);
-CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_section_execution_date_index
- ON reserves_in
- (exchange_account_section
- ,execution_date
- );
-CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_reserve_in_serial_id_index
- ON reserves_in
- (exchange_account_section,
- reserve_in_serial_id DESC
- );
-
-
-SELECT create_table_reserves_close();
-
-COMMENT ON TABLE reserves_close
- IS 'wire transfers executed by the reserve to close reserves';
-COMMENT ON COLUMN reserves_close.wire_target_serial_id
- IS 'Identifies the credited bank account (and KYC status). Note that closing
does not depend on KYC.';
-CREATE TABLE IF NOT EXISTS reserves_close_default
- PARTITION OF reserves_close
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS reserves_close_by_close_uuid_index
- ON reserves_close
- (close_uuid);
-CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index
- ON reserves_close
- (reserve_pub);
-
-
-SELECT create_table_reserves_out();
-
-COMMENT ON TABLE reserves_out
- IS 'Withdraw operations performed on reserves.';
-COMMENT ON COLUMN reserves_out.h_blind_ev
- IS '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).';
-COMMENT ON COLUMN reserves_out.denominations_serial
- IS 'We do not CASCADE ON DELETE here, we may keep the denomination data
alive';
-CREATE TABLE IF NOT EXISTS reserves_out_default
- PARTITION OF reserves_out
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index
- ON reserves_out
- (reserve_out_serial_id);
-CREATE INDEX IF NOT EXISTS
reserves_out_by_reserve_uuid_and_execution_date_index
- ON reserves_out
- (reserve_uuid, execution_date);
-COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index
- IS 'for get_reserves_out and exchange_do_withdraw_limit_check';
-
-
-SELECT create_table_auditors();
-
-COMMENT ON TABLE auditors
- IS 'Table with auditors the exchange uses or has used in the past. Entries
never expire as we need to remember the last_change column indefinitely.';
-COMMENT ON COLUMN auditors.auditor_pub
- IS 'Public key of the auditor.';
-COMMENT ON COLUMN auditors.auditor_url
- IS 'The base URL of the auditor.';
-COMMENT ON COLUMN auditors.is_active
- IS 'true if we are currently supporting the use of this auditor.';
-COMMENT ON COLUMN auditors.last_change
- IS 'Latest time when active status changed. Used to detect replays of old
messages.';
-
-
-SELECT create_table_auditor_denom_sigs();
-
-COMMENT ON TABLE auditor_denom_sigs
- IS 'Table with auditor signatures on exchange denomination keys.';
-COMMENT ON COLUMN auditor_denom_sigs.auditor_uuid
- IS 'Identifies the auditor.';
-COMMENT ON COLUMN auditor_denom_sigs.denominations_serial
- IS 'Denomination the signature is for.';
-COMMENT ON COLUMN auditor_denom_sigs.auditor_sig
- IS 'Signature of the auditor, of purpose
TALER_SIGNATURE_AUDITOR_EXCHANGE_KEYS.';
-
-
-SELECT create_table_exchange_sign_keys();
-
-COMMENT ON TABLE exchange_sign_keys
- IS 'Table with master public key signatures on exchange online signing
keys.';
-COMMENT ON COLUMN exchange_sign_keys.exchange_pub
- IS 'Public online signing key of the exchange.';
-COMMENT ON COLUMN exchange_sign_keys.master_sig
- IS 'Signature affirming the validity of the signing key of purpose
TALER_SIGNATURE_MASTER_SIGNING_KEY_VALIDITY.';
-COMMENT ON COLUMN exchange_sign_keys.valid_from
- IS 'Time when this online signing key will first be used to sign messages.';
-COMMENT ON COLUMN exchange_sign_keys.expire_sign
- IS 'Time when this online signing key will no longer be used to sign.';
-COMMENT ON COLUMN exchange_sign_keys.expire_legal
- IS 'Time when this online signing key legally expires.';
-
-
-SELECT create_table_signkey_revocations();
-
-COMMENT ON TABLE signkey_revocations
- IS 'Table storing which online signing keys have been revoked';
-
-
-SELECT create_table_extensions();
-
-COMMENT ON TABLE extensions
- IS 'Configurations of the activated extensions';
-COMMENT ON COLUMN extensions.name
- IS 'Name of the extension';
-COMMENT ON COLUMN extensions.config
- IS 'Configuration of the extension as JSON-blob, maybe NULL';
-
-
-SELECT create_table_known_coins();
-
-COMMENT ON TABLE known_coins
- IS 'information about coins and their signatures, so we do not have to store
the signatures more than once if a coin is involved in multiple operations';
-COMMENT ON COLUMN known_coins.denominations_serial
- IS 'Denomination of the coin, determines the value of the original coin and
applicable fees for coin-specific operations.';
-COMMENT ON COLUMN known_coins.coin_pub
- IS 'EdDSA public key of the coin';
-COMMENT ON COLUMN known_coins.remaining_val
- IS 'Value of the coin that remains to be spent';
-COMMENT ON COLUMN known_coins.age_commitment_hash
- IS 'Optional hash of the age commitment for age restrictions as per DD 24
(active if denom_type has the respective bit set)';
-COMMENT ON COLUMN known_coins.denom_sig
- IS 'This is the signature of the exchange that affirms that the coin is a
valid coin. The specific signature type depends on denom_type of the
denomination.';
-CREATE TABLE IF NOT EXISTS known_coins_default
- PARTITION OF known_coins
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS known_coins_by_known_coin_id_index
- ON known_coins
- (known_coin_id);
-
-
-SELECT create_table_refresh_commitments();
-
-COMMENT ON TABLE refresh_commitments
- IS 'Commitments made when melting coins and the gamma value chosen by the
exchange.';
-COMMENT ON COLUMN refresh_commitments.noreveal_index
- IS 'The gamma value chosen by the exchange in the cut-and-choose protocol';
-COMMENT ON COLUMN refresh_commitments.rc
- IS 'Commitment made by the client, hash over the various client inputs in
the cut-and-choose protocol';
-COMMENT ON COLUMN refresh_commitments.old_coin_pub
- IS 'Coin being melted in the refresh process.';
-COMMENT ON COLUMN refresh_commitments.h_age_commitment
- IS 'The (optional) age commitment that was involved in the minting process
of the coin, may be NULL.';
-CREATE TABLE IF NOT EXISTS refresh_commitments_default
- PARTITION OF refresh_commitments
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS refresh_commitments_by_melt_serial_id_index
- ON refresh_commitments
- (melt_serial_id);
-CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
- ON refresh_commitments
- (old_coin_pub);
-
-
-SELECT create_table_refresh_revealed_coins();
-
-COMMENT ON TABLE refresh_revealed_coins
- IS 'Revelations about the new coins that are to be created during a melting
session.';
-COMMENT ON COLUMN refresh_revealed_coins.rrc_serial
- IS 'needed for exchange-auditor replication logic';
-COMMENT ON COLUMN refresh_revealed_coins.melt_serial_id
- IS 'Identifies the refresh commitment (rc) of the melt operation.';
-COMMENT ON COLUMN refresh_revealed_coins.freshcoin_index
- IS 'index of the fresh coin being created (one melt operation may result in
multiple fresh coins)';
-COMMENT ON COLUMN refresh_revealed_coins.coin_ev
- IS 'envelope of the new coin to be signed';
-COMMENT ON COLUMN refresh_revealed_coins.ewv
- IS 'exchange contributed values in the creation of the fresh coin (see
/csr)';
-COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
- IS 'hash of the envelope of the new coin to be signed (for lookups)';
-COMMENT ON COLUMN refresh_revealed_coins.ev_sig
- IS 'exchange signature over the envelope';
-CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default
- PARTITION OF refresh_revealed_coins
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
--- We do require this primary key on each shard!
-ALTER TABLE refresh_revealed_coins_default
- ADD PRIMARY KEY (melt_serial_id, freshcoin_index);
-
-CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_rrc_serial_index
- ON refresh_revealed_coins
- (rrc_serial);
-CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index
- ON refresh_revealed_coins
- (melt_serial_id);
-
-
-SELECT create_table_refresh_transfer_keys();
-
-COMMENT ON TABLE refresh_transfer_keys
- IS 'Transfer keys of a refresh operation (the data revealed to the
exchange).';
-COMMENT ON COLUMN refresh_transfer_keys.rtc_serial
- IS 'needed for exchange-auditor replication logic';
-COMMENT ON COLUMN refresh_transfer_keys.melt_serial_id
- IS 'Identifies the refresh commitment (rc) of the operation.';
-COMMENT ON COLUMN refresh_transfer_keys.transfer_pub
- IS 'transfer public key for the gamma index';
-COMMENT ON COLUMN refresh_transfer_keys.transfer_privs
- IS 'array of TALER_CNC_KAPPA - 1 transfer private keys that have been
revealed, with the gamma entry being skipped';
-CREATE TABLE IF NOT EXISTS refresh_transfer_keys_default
- PARTITION OF refresh_transfer_keys
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS refresh_transfer_keys_by_rtc_serial_index
- ON refresh_transfer_keys
- (rtc_serial);
-
-
-SELECT create_table_extension_details();
-
-COMMENT ON TABLE extension_details
- IS 'Extensions that were provided with deposits (not yet used).';
-COMMENT ON COLUMN extension_details.extension_options
- IS 'JSON object with options set that the exchange needs to consider when
executing a deposit. Supported details depend on the extensions supported by
the exchange.';
-
-
-SELECT create_table_deposits();
-
-CREATE TABLE IF NOT EXISTS deposits_default
- PARTITION OF deposits
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-COMMENT ON TABLE deposits
- IS 'Deposits we have received and for which we need to make (aggregate) wire
transfers (and manage refunds).';
-COMMENT ON COLUMN deposits.shard
- IS 'Used for load sharding. Should be set based on h_payto and merchant_pub.
64-bit value because we need an *unsigned* 32-bit value.';
-COMMENT ON COLUMN deposits.wire_target_serial_id
- IS 'Identifies the target bank account and KYC status';
-COMMENT ON COLUMN deposits.wire_salt
- IS 'Salt used when hashing the payto://-URI to get the h_wire';
-COMMENT ON COLUMN deposits.done
- IS 'Set to TRUE once we have included this deposit in some aggregate wire
transfer to the merchant';
-COMMENT ON COLUMN deposits.extension_blocked
- IS 'True if the aggregation of the deposit is currently blocked by some
extension mechanism. Used to filter out deposits that must not be processed by
the canonical deposit logic.';
-COMMENT ON COLUMN deposits.extension_details_serial_id
- IS 'References extensions table, NULL if extensions are not used';
-COMMENT ON COLUMN deposits.tiny
- IS 'Set to TRUE if we decided that the amount is too small to ever trigger a
wire transfer by itself (requires real aggregation)';
-
--- FIXME: check if we can ALWAYS include the shard in the WHERE clauses,
--- thereby resulting in a much better use of the index: we could do
(shard,deposit_serial_id)!
-CREATE INDEX IF NOT EXISTS deposits_deposit_by_serial_id_index
- ON deposits
- (deposit_serial_id);
-CREATE INDEX IF NOT EXISTS deposits_for_get_ready_index
- ON deposits
- (shard ASC
- ,done
- ,extension_blocked
- ,tiny
- ,wire_deadline ASC
- );
-COMMENT ON INDEX deposits_for_get_ready_index
- IS 'for deposits_get_ready';
--- FIXME: check if we can ALWAYS include the shard in the WHERE clauses,
--- thereby resulting in a much better use of the index: we could do
(shard,merchant_pub, ...)!
-CREATE INDEX IF NOT EXISTS deposits_for_iterate_matching_index
- ON deposits
- (merchant_pub
- ,wire_target_serial_id
- ,done
- ,extension_blocked
- ,refund_deadline ASC
- );
-COMMENT ON INDEX deposits_for_iterate_matching_index
- IS 'for deposits_iterate_matching';
-
-
-SELECT create_table_refunds();
-
-COMMENT ON TABLE refunds
- IS 'Data on coins that were refunded. Technically, refunds always apply
against specific deposit operations involving a coin. The combination of
coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique,
and we usually select by coin_pub so that one goes first.';
-COMMENT ON COLUMN refunds.deposit_serial_id
- IS 'Identifies ONLY the merchant_pub, h_contract_terms and known_coin_id.
Multiple deposits may match a refund, this only identifies one of them.';
-COMMENT ON COLUMN refunds.rtransaction_id
- IS 'used by the merchant to make refunds unique in case the same coin for
the same deposit gets a subsequent (higher) refund';
-CREATE TABLE IF NOT EXISTS refunds_default
- PARTITION OF refunds
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-ALTER TABLE refunds_default
- ADD PRIMARY KEY (deposit_serial_id, rtransaction_id);
-
-CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index
- ON refunds
- (refund_serial_id);
-
-
-SELECT create_table_wire_out();
-
-COMMENT ON TABLE wire_out
- IS 'wire transfers the exchange has executed';
-COMMENT ON COLUMN wire_out.exchange_account_section
- IS 'identifies the configuration section with the debit account of this
payment';
-COMMENT ON COLUMN wire_out.wire_target_serial_id
- IS 'Identifies the credited bank account and KYC status';
-CREATE TABLE IF NOT EXISTS wire_out_default
- PARTITION OF wire_out
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS wire_out_by_wireout_uuid_index
- ON wire_out
- (wireout_uuid);
-CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_serial_id_index
- ON wire_out
- (wire_target_serial_id);
-
-
-SELECT create_table_aggregation_tracking();
-
-COMMENT ON TABLE aggregation_tracking
- IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)';
-COMMENT ON COLUMN aggregation_tracking.wtid_raw
- IS 'We first create entries in the aggregation_tracking table and then
finally the wire_out entry once we know the total amount. Hence the constraint
must be deferrable and we cannot use a wireout_uuid here, because we do not
have it when these rows are created. Changing the logic to first INSERT a dummy
row into wire_out and then UPDATEing that row in the same transaction would
theoretically reduce per-deposit storage costs by 5 percent (24/~460 bytes).';
-CREATE TABLE IF NOT EXISTS aggregation_tracking_default
- PARTITION OF aggregation_tracking
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS aggregation_tracking_by_aggregation_serial_id_index
- ON aggregation_tracking
- (aggregation_serial_id);
-CREATE INDEX IF NOT EXISTS aggregation_tracking_by_wtid_raw_index
- ON aggregation_tracking
- (wtid_raw);
-COMMENT ON INDEX aggregation_tracking_by_wtid_raw_index
- IS 'for lookup_transactions';
-
-
-SELECT create_table_wire_fee();
-
-COMMENT ON TABLE wire_fee
- IS 'list of the wire fees of this exchange, by date';
-COMMENT ON COLUMN wire_fee.wire_fee_serial
- IS 'needed for exchange-auditor replication logic';
-
-CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
- ON wire_fee
- (end_date);
-
-
-SELECT create_table_recoup();
-
-COMMENT ON TABLE recoup
- IS 'Information about recoups that were executed between a coin and a
reserve. In this type of recoup, the amount is credited back to the reserve
from which the coin originated.';
-COMMENT ON COLUMN recoup.known_coin_id
- IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the
coin_pub, as we may keep the coin alive!';
-COMMENT ON COLUMN recoup.reserve_out_serial_id
- IS 'Identifies the h_blind_ev of the recouped coin and provides the link to
the credited reserve.';
-COMMENT ON COLUMN recoup.coin_sig
- IS 'Signature by the coin affirming the recoup, of type
TALER_SIGNATURE_WALLET_COIN_RECOUP';
-COMMENT ON COLUMN recoup.coin_blind
- IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the withdraw operation.';
-CREATE TABLE IF NOT EXISTS recoup_default
- PARTITION OF recoup
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index
- ON recoup
- (recoup_uuid);
-CREATE INDEX IF NOT EXISTS recoup_by_reserve_out_serial_id_index
- ON recoup
- (reserve_out_serial_id);
-CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index
- ON recoup
- (known_coin_id);
-
-
-SELECT create_table_recoup_refresh();
-
-COMMENT ON TABLE recoup_refresh
- IS 'Table of coins that originated from a refresh operation and that were
recouped. Links the (fresh) coin to the melted operation (and thus the old
coin). A recoup on a refreshed coin credits the old coin and debits the fresh
coin.';
-COMMENT ON COLUMN recoup_refresh.known_coin_id
- IS 'Refreshed coin of a revoked denomination where the residual value is
credited to the old coin. Do not CASCADE ON DROP on the known_coin_id, as we
may keep the coin alive!';
-COMMENT ON COLUMN recoup_refresh.rrc_serial
- IS 'Link to the refresh operation. Also identifies the h_blind_ev of the
recouped coin (as h_coin_ev).';
-COMMENT ON COLUMN recoup_refresh.coin_blind
- IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the refresh operation.';
-CREATE TABLE IF NOT EXISTS recoup_refresh_default
- PARTITION OF recoup_refresh
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
- ON recoup_refresh
- (recoup_refresh_uuid);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
- ON recoup_refresh
- (rrc_serial);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_known_coin_id_index
- ON recoup_refresh
- (known_coin_id);
-
-
-SELECT create_table_prewire();
-
-COMMENT ON TABLE prewire
- IS 'pre-commit data for wire transfers we are about to execute';
-COMMENT ON COLUMN prewire.failed
- IS 'set to TRUE if the bank responded with a non-transient failure to our
transfer request';
-COMMENT ON COLUMN prewire.finished
- IS 'set to TRUE once bank confirmed receiving the wire transfer request';
-COMMENT ON COLUMN prewire.buf
- IS 'serialized data to send to the bank to execute the wire transfer';
-CREATE TABLE IF NOT EXISTS prewire_default
- PARTITION OF prewire
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE INDEX IF NOT EXISTS prewire_by_finished_index
- ON prewire
- (finished);
-COMMENT ON INDEX prewire_by_finished_index
- IS 'for gc_prewire';
--- FIXME: find a way to combine these two indices?
-CREATE INDEX IF NOT EXISTS prewire_by_failed_finished_index
- ON prewire
- (failed,finished);
-COMMENT ON INDEX prewire_by_failed_finished_index
- IS 'for wire_prepare_data_get';
-
-
-SELECT create_table_wire_accounts();
-
-COMMENT ON TABLE wire_accounts
- IS 'Table with current and historic bank accounts of the exchange. Entries
never expire as we need to remember the last_change column indefinitely.';
-COMMENT ON COLUMN wire_accounts.payto_uri
- IS 'payto URI (RFC 8905) with the bank account of the exchange.';
-COMMENT ON COLUMN wire_accounts.master_sig
- IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS';
-COMMENT ON COLUMN wire_accounts.is_active
- IS 'true if we are currently supporting the use of this account.';
-COMMENT ON COLUMN wire_accounts.last_change
- IS 'Latest time when active status changed. Used to detect replays of old
messages.';
--- "wire_accounts" has no sequence because it is a 'mutable' table
--- and is of no concern to the auditor
-
-
-SELECT create_table_cs_nonce_locks();
-
-COMMENT ON TABLE cs_nonce_locks
- IS 'ensures a Clause Schnorr client nonce is locked for use with an
operation identified by a hash';
-COMMENT ON COLUMN cs_nonce_locks.nonce
- IS 'actual nonce submitted by the client';
-COMMENT ON COLUMN cs_nonce_locks.op_hash
- IS 'hash (RC for refresh, blind coin hash for withdraw) the nonce may be
used with';
-COMMENT ON COLUMN cs_nonce_locks.max_denomination_serial
- IS 'Maximum number of a CS denomination serial the nonce could be used with,
for GC';
-CREATE TABLE IF NOT EXISTS cs_nonce_locks_default
- PARTITION OF cs_nonce_locks
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-SELECT create_table_work_shards();
-
-COMMENT ON TABLE work_shards
- IS 'coordinates work between multiple processes working on the same job';
-COMMENT ON COLUMN work_shards.shard_serial_id
- IS 'unique serial number identifying the shard';
-COMMENT ON COLUMN work_shards.last_attempt
- IS 'last time a worker attempted to work on the shard';
-COMMENT ON COLUMN work_shards.completed
- IS 'set to TRUE once the shard is finished by a worker';
-COMMENT ON COLUMN work_shards.start_row
- IS 'row at which the shard scope starts, inclusive';
-COMMENT ON COLUMN work_shards.end_row
- IS 'row at which the shard scope ends, exclusive';
-COMMENT ON COLUMN work_shards.job_name
- IS 'unique name of the job the workers on this shard are performing';
-
-CREATE INDEX IF NOT EXISTS work_shards_by_job_name_completed_last_attempt_index
- ON work_shards
- (job_name
- ,completed
- ,last_attempt
- );
-
-SELECT create_table_revolving_work_shards();
-
-COMMENT ON TABLE revolving_work_shards
- IS 'coordinates work between multiple processes working on the same job with
partitions that need to be repeatedly processed; unlogged because on system
crashes the locks represented by this table will have to be cleared anyway,
typically using "taler-exchange-dbinit -s"';
-COMMENT ON COLUMN revolving_work_shards.shard_serial_id
- IS 'unique serial number identifying the shard';
-COMMENT ON COLUMN revolving_work_shards.last_attempt
- IS 'last time a worker attempted to work on the shard';
-COMMENT ON COLUMN revolving_work_shards.active
- IS 'set to TRUE when a worker is active on the shard';
-COMMENT ON COLUMN revolving_work_shards.start_row
- IS 'row at which the shard scope starts, inclusive';
-COMMENT ON COLUMN revolving_work_shards.end_row
- IS 'row at which the shard scope ends, exclusive';
-COMMENT ON COLUMN revolving_work_shards.job_name
- IS 'unique name of the job the workers on this shard are performing';
-
-CREATE INDEX IF NOT EXISTS
revolving_work_shards_by_job_name_active_last_attempt_index
- ON revolving_work_shards
- (job_name
- ,active
- ,last_attempt
- );
-
-
--- Stored procedures
-
-
-CREATE OR REPLACE FUNCTION exchange_do_withdraw(
- IN cs_nonce BYTEA,
- IN amount_val INT8,
- IN amount_frac INT4,
- IN h_denom_pub BYTEA,
- IN rpub BYTEA,
- IN reserve_sig BYTEA,
- IN h_coin_envelope BYTEA,
- IN denom_sig BYTEA,
- IN now INT8,
- IN min_reserve_gc INT8,
- OUT reserve_found BOOLEAN,
- OUT balance_ok BOOLEAN,
- OUT kycok BOOLEAN,
- OUT account_uuid INT8,
- OUT ruuid INT8)
-LANGUAGE plpgsql
-AS $$
-DECLARE
- reserve_gc INT8;
-DECLARE
- denom_serial INT8;
-DECLARE
- reserve_val INT8;
-DECLARE
- reserve_frac INT4;
-BEGIN
--- Shards: reserves by reserve_pub (SELECT)
--- reserves_out (INSERT, with CONFLICT detection) by wih
--- reserves by reserve_pub (UPDATE)
--- reserves_in by reserve_pub (SELECT)
--- wire_targets by wire_target_serial_id
-
-SELECT denominations_serial
- INTO denom_serial
- FROM denominations
- WHERE denom_pub_hash=h_denom_pub;
-
-IF NOT FOUND
-THEN
- -- denomination unknown, should be impossible!
- reserve_found=FALSE;
- balance_ok=FALSE;
- kycok=FALSE;
- account_uuid=0;
- ruuid=0;
- ASSERT false, 'denomination unknown';
- RETURN;
-END IF;
-
-
-SELECT
- current_balance_val
- ,current_balance_frac
- ,gc_date
- ,reserve_uuid
- INTO
- reserve_val
- ,reserve_frac
- ,reserve_gc
- ,ruuid
- FROM reserves
- WHERE reserves.reserve_pub=rpub;
-
-IF NOT FOUND
-THEN
- -- reserve unknown
- reserve_found=FALSE;
- balance_ok=FALSE;
- kycok=FALSE;
- account_uuid=0;
- ruuid=2;
- RETURN;
-END IF;
-
--- We optimistically insert, and then on conflict declare
--- the query successful due to idempotency.
-INSERT INTO reserves_out
- (h_blind_ev
- ,denominations_serial
- ,denom_sig
- ,reserve_uuid
- ,reserve_sig
- ,execution_date
- ,amount_with_fee_val
- ,amount_with_fee_frac)
-VALUES
- (h_coin_envelope
- ,denom_serial
- ,denom_sig
- ,ruuid
- ,reserve_sig
- ,now
- ,amount_val
- ,amount_frac)
-ON CONFLICT DO NOTHING;
-
-IF NOT FOUND
-THEN
- -- idempotent query, all constraints must be satisfied
- reserve_found=TRUE;
- balance_ok=TRUE;
- kycok=TRUE;
- account_uuid=0;
- RETURN;
-END IF;
-
--- Check reserve balance is sufficient.
-IF (reserve_val > amount_val)
-THEN
- IF (reserve_frac >= amount_frac)
- THEN
- reserve_val=reserve_val - amount_val;
- reserve_frac=reserve_frac - amount_frac;
- ELSE
- reserve_val=reserve_val - amount_val - 1;
- reserve_frac=reserve_frac + 100000000 - amount_frac;
- END IF;
-ELSE
- IF (reserve_val = amount_val) AND (reserve_frac >= amount_frac)
- THEN
- reserve_val=0;
- reserve_frac=reserve_frac - amount_frac;
- ELSE
- reserve_found=TRUE;
- balance_ok=FALSE;
- kycok=FALSE; -- we do not really know or care
- account_uuid=0;
- RETURN;
- END IF;
-END IF;
-
--- Calculate new expiration dates.
-min_reserve_gc=GREATEST(min_reserve_gc,reserve_gc);
-
--- Update reserve balance.
-UPDATE reserves SET
- gc_date=min_reserve_gc
- ,current_balance_val=reserve_val
- ,current_balance_frac=reserve_frac
-WHERE
- reserves.reserve_pub=rpub;
-
-reserve_found=TRUE;
-balance_ok=TRUE;
-
-
-
--- Special actions needed for a CS withdraw?
-IF NOT NULL cs_nonce
-THEN
- -- Cache CS signature to prevent replays in the future
- -- (and check if cached signature exists at the same time).
- INSERT INTO cs_nonce_locks
- (nonce
- ,max_denomination_serial
- ,op_hash)
- VALUES
- (cs_nonce
- ,denom_serial
- ,h_coin_envelope)
- ON CONFLICT DO NOTHING;
-
- IF NOT FOUND
- THEN
- -- See if the existing entry is identical.
- SELECT 1
- FROM cs_nonce_locks
- WHERE nonce=cs_nonce
- AND op_hash=h_coin_envelope;
- IF NOT FOUND
- THEN
- reserve_found=FALSE;
- balance_ok=FALSE;
- kycok=FALSE;
- account_uuid=0;
- ruuid=1; -- FIXME: return error message more nicely!
- ASSERT false, 'nonce reuse attempted by client';
- END IF;
- END IF;
-END IF;
-
-
-
--- Obtain KYC status based on the last wire transfer into
--- this reserve. FIXME: likely not adequate for reserves that got P2P
transfers!
-SELECT
- kyc_ok
- ,wire_source_serial_id
- INTO
- kycok
- ,account_uuid
- FROM reserves_in
- JOIN wire_targets ON (wire_source_serial_id = wire_target_serial_id)
- WHERE reserve_pub=rpub
- LIMIT 1; -- limit 1 should not be required (without p2p transfers)
-
-
-END $$;
-
-COMMENT ON FUNCTION exchange_do_withdraw(BYTEA, INT8, INT4, BYTEA, BYTEA,
BYTEA, BYTEA, BYTEA, INT8, INT8)
- IS 'Checks whether the reserve has sufficient balance for a withdraw
operation (or the request is repeated and was previously approved) and if so
updates the database with the result';
-
-
-
-CREATE OR REPLACE FUNCTION exchange_do_withdraw_limit_check(
- IN ruuid INT8,
- IN start_time INT8,
- IN upper_limit_val INT8,
- IN upper_limit_frac INT4,
- OUT below_limit BOOLEAN)
-LANGUAGE plpgsql
-AS $$
-DECLARE
- total_val INT8;
-DECLARE
- total_frac INT8; -- INT4 could overflow during accumulation!
-BEGIN
--- NOTE: Read-only, but crosses shards.
--- Shards: reserves by reserve_pub
--- reserves_out by reserve_uuid -- crosses shards!!
-
-
-SELECT
- SUM(amount_with_fee_val) -- overflow here is not plausible
- ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits
- INTO
- total_val
- ,total_frac
- FROM reserves_out
- WHERE reserve_uuid=ruuid
- AND execution_date > start_time;
-
--- normalize result
-total_val = total_val + total_frac / 100000000;
-total_frac = total_frac % 100000000;
-
--- compare to threshold
-below_limit = (total_val < upper_limit_val) OR
- ( (total_val = upper_limit_val) AND
- (total_frac <= upper_limit_frac) );
-END $$;
-
-COMMENT ON FUNCTION exchange_do_withdraw_limit_check(INT8, INT8, INT8, INT4)
- IS 'Check whether the withdrawals from the given reserve since the given
time are below the given threshold';
-
-
--- NOTE: experiment, currently dead, see postgres_Start_deferred_wire_out;
--- now done inline. FIXME: Remove code here once inline version is confirmed
working nicely!
-CREATE OR REPLACE PROCEDURE defer_wire_out()
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
-IF EXISTS (
- SELECT 1
- FROM information_Schema.constraint_column_usage
- WHERE table_name='wire_out'
- AND constraint_name='wire_out_ref')
-THEN
- SET CONSTRAINTS wire_out_ref DEFERRED;
-END IF;
-
-END $$;
-
-
-CREATE OR REPLACE FUNCTION exchange_do_deposit(
- IN in_amount_with_fee_val INT8,
- IN in_amount_with_fee_frac INT4,
- IN in_h_contract_terms BYTEA,
- IN in_wire_salt BYTEA,
- IN in_wallet_timestamp INT8,
- IN in_exchange_timestamp INT8,
- IN in_refund_deadline INT8,
- IN in_wire_deadline INT8,
- IN in_merchant_pub BYTEA,
- IN in_receiver_wire_account VARCHAR,
- IN in_h_payto BYTEA,
- IN in_known_coin_id INT8,
- IN in_coin_pub BYTEA,
- IN in_coin_sig BYTEA,
- IN in_shard INT8,
- IN in_extension_blocked BOOLEAN,
- IN in_extension_details VARCHAR,
- OUT out_exchange_timestamp INT8,
- OUT out_balance_ok BOOLEAN,
- OUT out_conflict BOOLEAN)
-LANGUAGE plpgsql
-AS $$
-DECLARE
- wtsi INT8; -- wire target serial id
-DECLARE
- xdi INT8; -- eXstension details serial id
-BEGIN
--- Shards: INSERT extension_details (by extension_details_serial_id)
--- INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING;
--- INSERT deposits (by shard + known_coin_id, merchant_pub,
h_contract_terms), ON CONFLICT DO NOTHING;
--- UPDATE known_coins (by coin_pub)
-
-IF NOT NULL in_extension_details
-THEN
- INSERT INTO extension_details
- (extension_options)
- VALUES
- (in_extension_details)
- RETURNING extension_details_serial_id INTO xdi;
-ELSE
- xdi=NULL;
-END IF;
-
-
-INSERT INTO wire_targets
- (h_payto
- ,payto_uri)
- VALUES
- (in_h_payto
- ,in_receiver_wire_account)
-ON CONFLICT DO NOTHING -- for CONFLICT ON (h_payto)
- RETURNING wire_target_serial_id INTO wtsi;
-
-IF NOT FOUND
-THEN
- SELECT wire_target_serial_id
- INTO wtsi
- FROM wire_targets
- WHERE h_payto=in_h_payto;
-END IF;
-
-
-INSERT INTO deposits
- (shard
- ,known_coin_id
- ,amount_with_fee_val
- ,amount_with_fee_frac
- ,wallet_timestamp
- ,exchange_timestamp
- ,refund_deadline
- ,wire_deadline
- ,merchant_pub
- ,h_contract_terms
- ,coin_sig
- ,wire_salt
- ,wire_target_serial_id
- ,extension_blocked
- ,extension_details_serial_id
- )
- VALUES
- (in_shard
- ,in_known_coin_id
- ,in_amount_with_fee_val
- ,in_amount_with_fee_frac
- ,in_wallet_timestamp
- ,in_exchange_timestamp
- ,in_refund_deadline
- ,in_wire_deadline
- ,in_merchant_pub
- ,in_h_contract_terms
- ,in_coin_sig
- ,in_wire_salt
- ,wtsi
- ,in_extension_blocked
- ,xdi)
- ON CONFLICT DO NOTHING;
-
-IF NOT FOUND
-THEN
- -- Idempotency check: see if an identical record exists.
- -- Note that by checking 'coin_sig', we implicitly check
- -- identity over everything that the signature covers.
- -- We do select over merchant_pub and h_contract_terms
- -- primarily here to maximally use the existing index.
- SELECT
- exchange_timestamp
- INTO
- out_exchange_timestamp
- FROM deposits
- WHERE
- shard=in_shard AND
- known_coin_id=in_known_coin_id AND
- merchant_pub=in_merchant_pub AND
- h_contract_terms=in_h_contract_terms AND
- coin_sig=in_coin_sig;
-
- IF NOT FOUND
- THEN
- -- Deposit exists, but with differences. Not allowed.
- out_balance_ok=FALSE;
- out_conflict=TRUE;
- RETURN;
- END IF;
-
- -- Idempotent request known, return success.
- out_balance_ok=TRUE;
- out_conflict=FALSE;
-
- RETURN;
-END IF;
-
-
-out_exchange_timestamp=in_exchange_timestamp;
-
--- Check and update balance of the coin.
-UPDATE known_coins
- SET
- remaining_frac=remaining_frac-in_amount_with_fee_frac
- + CASE
- WHEN remaining_frac < in_amount_with_fee_frac
- THEN 100000000
- ELSE 0
- END,
- remaining_val=remaining_val-in_amount_with_fee_val
- - CASE
- WHEN remaining_frac < in_amount_with_fee_frac
- THEN 1
- ELSE 0
- END
- WHERE coin_pub=in_coin_pub
- AND ( (remaining_val > in_amount_with_fee_val) OR
- ( (remaining_frac >= in_amount_with_fee_frac) AND
- (remaining_val >= in_amount_with_fee_val) ) );
-
-IF NOT FOUND
-THEN
- -- Insufficient balance.
- out_balance_ok=FALSE;
- out_conflict=FALSE;
- RETURN;
-END IF;
-
--- Everything fine, return success!
-out_balance_ok=TRUE;
-out_conflict=FALSE;
-
-END $$;
-
-
-
-CREATE OR REPLACE FUNCTION exchange_do_melt(
- IN in_cs_rms BYTEA,
- IN in_amount_with_fee_val INT8,
- IN in_amount_with_fee_frac INT4,
- IN in_rc BYTEA,
- IN in_old_coin_pub BYTEA,
- IN in_old_coin_sig BYTEA,
- IN in_known_coin_id INT8, -- not used, but that's OK
- IN in_h_age_commitment BYTEA,
- IN in_noreveal_index INT4,
- IN in_zombie_required BOOLEAN,
- OUT out_balance_ok BOOLEAN,
- OUT out_zombie_bad BOOLEAN,
- OUT out_noreveal_index INT4)
-LANGUAGE plpgsql
-AS $$
-DECLARE
- denom_max INT8;
-BEGIN
--- Shards: INSERT refresh_commitments (by rc)
--- (rare:) SELECT refresh_commitments (by old_coin_pub) -- crosses shards!
--- (rare:) SEELCT refresh_revealed_coins (by melt_serial_id)
--- (rare:) PERFORM recoup_refresh (by rrc_serial) -- crosses shards!
--- UPDATE known_coins (by coin_pub)
-
-INSERT INTO refresh_commitments
- (rc
- ,old_coin_pub
- ,old_coin_sig
- ,amount_with_fee_val
- ,amount_with_fee_frac
- ,h_age_commitment
- ,noreveal_index
- )
- VALUES
- (in_rc
- ,in_old_coin_pub
- ,in_old_coin_sig
- ,in_amount_with_fee_val
- ,in_amount_with_fee_frac
- ,in_h_age_commitment
- ,in_noreveal_index)
- ON CONFLICT DO NOTHING;
-
-IF NOT FOUND
-THEN
- -- Idempotency check: see if an identical record exists.
- out_noreveal_index=-1;
- SELECT
- noreveal_index
- INTO
- out_noreveal_index
- FROM refresh_commitments
- WHERE rc=in_rc;
- out_balance_ok=FOUND;
- out_zombie_bad=FALSE; -- zombie is OK
- RETURN;
-END IF;
-
-
-IF in_zombie_required
-THEN
- -- Check if this coin was part of a refresh
- -- operation that was subsequently involved
- -- in a recoup operation. We begin by all
- -- refresh operations our coin was involved
- -- with, then find all associated reveal
- -- operations, and then see if any of these
- -- reveal operations was involved in a recoup.
- PERFORM
- FROM recoup_refresh
- WHERE rrc_serial IN
- (SELECT rrc_serial
- FROM refresh_revealed_coins
- WHERE melt_serial_id IN
- (SELECT melt_serial_id
- FROM refresh_commitments
- WHERE old_coin_pub=in_old_coin_pub));
- IF NOT FOUND
- THEN
- out_zombie_bad=TRUE;
- out_balance_ok=FALSE;
- RETURN;
- END IF;
-END IF;
-
-out_zombie_bad=FALSE; -- zombie is OK
-
-
--- Check and update balance of the coin.
-UPDATE known_coins
- SET
- remaining_frac=remaining_frac-in_amount_with_fee_frac
- + CASE
- WHEN remaining_frac < in_amount_with_fee_frac
- THEN 100000000
- ELSE 0
- END,
- remaining_val=remaining_val-in_amount_with_fee_val
- - CASE
- WHEN remaining_frac < in_amount_with_fee_frac
- THEN 1
- ELSE 0
- END
- WHERE coin_pub=in_old_coin_pub
- AND ( (remaining_val > in_amount_with_fee_val) OR
- ( (remaining_frac >= in_amount_with_fee_frac) AND
- (remaining_val >= in_amount_with_fee_val) ) );
-
-IF NOT FOUND
-THEN
- -- Insufficient balance.
- out_noreveal_index=-1;
- out_balance_ok=FALSE;
- RETURN;
-END IF;
-
-
-
--- Special actions needed for a CS melt?
-IF NOT NULL in_cs_rms
-THEN
- -- Get maximum denominations serial value in
- -- existence, this will determine how long the
- -- nonce will be locked.
- SELECT
- denominations_serial
- INTO
- denom_max
- FROM denominations
- ORDER BY denominations_serial DESC
- LIMIT 1;
-
- -- Cache CS signature to prevent replays in the future
- -- (and check if cached signature exists at the same time).
- INSERT INTO cs_nonce_locks
- (nonce
- ,max_denomination_serial
- ,op_hash)
- VALUES
- (cs_rms
- ,denom_serial
- ,in_rc)
- ON CONFLICT DO NOTHING;
-
- IF NOT FOUND
- THEN
- -- Record exists, make sure it is the same
- SELECT 1
- FROM cs_nonce_locks
- WHERE nonce=cs_rms
- AND op_hash=in_rc;
-
- IF NOT FOUND
- THEN
- -- Nonce reuse detected
- out_balance_ok=FALSE;
- out_zombie_bad=FALSE;
- out_noreveal_index=42; -- FIXME: return error message more nicely!
- ASSERT false, 'nonce reuse attempted by client';
- END IF;
- END IF;
-END IF;
-
-
-
-
--- Everything fine, return success!
-out_balance_ok=TRUE;
-out_noreveal_index=in_noreveal_index;
-
-END $$;
-
-
-
-CREATE OR REPLACE FUNCTION exchange_do_refund(
- IN in_amount_with_fee_val INT8,
- IN in_amount_with_fee_frac INT4,
- IN in_amount_val INT8,
- IN in_amount_frac INT4,
- IN in_deposit_fee_val INT8,
- IN in_deposit_fee_frac INT4,
- IN in_h_contract_terms BYTEA,
- IN in_rtransaction_id INT8,
- IN in_deposit_shard INT8,
- IN in_known_coin_id INT8,
- IN in_coin_pub BYTEA,
- IN in_merchant_pub BYTEA,
- IN in_merchant_sig BYTEA,
- OUT out_not_found BOOLEAN,
- OUT out_refund_ok BOOLEAN,
- OUT out_gone BOOLEAN,
- OUT out_conflict BOOLEAN)
-LANGUAGE plpgsql
-AS $$
-DECLARE
- dsi INT8; -- ID of deposit being refunded
-DECLARE
- tmp_val INT8; -- total amount refunded
-DECLARE
- tmp_frac INT8; -- total amount refunded
-DECLARE
- deposit_val INT8; -- amount that was originally deposited
-DECLARE
- deposit_frac INT8; -- amount that was originally deposited
-BEGIN
--- Shards: SELECT deposits (by shard, known_coin_id,h_contract_terms,
merchant_pub)
--- INSERT refunds (by deposit_serial_id, rtransaction_id) ON CONFLICT
DO NOTHING
--- SELECT refunds (by deposit_serial_id)
--- UPDATE known_coins (by coin_pub)
-
-SELECT
- deposit_serial_id
- ,amount_with_fee_val
- ,amount_with_fee_frac
- ,done
-INTO
- dsi
- ,deposit_val
- ,deposit_frac
- ,out_gone
-FROM deposits
-WHERE shard=in_deposit_shard
- AND known_coin_id=in_known_coin_id
- AND h_contract_terms=in_h_contract_terms
- AND merchant_pub=in_merchant_pub;
-
-IF NOT FOUND
-THEN
- -- No matching deposit found!
- out_refund_ok=FALSE;
- out_conflict=FALSE;
- out_not_found=TRUE;
- out_gone=FALSE;
- RETURN;
-END IF;
-
-
-INSERT INTO refunds
- (deposit_serial_id
- ,merchant_sig
- ,rtransaction_id
- ,amount_with_fee_val
- ,amount_with_fee_frac
- )
- VALUES
- (dsi
- ,in_merchant_sig
- ,in_rtransaction_id
- ,in_amount_with_fee_val
- ,in_amount_with_fee_frac)
- ON CONFLICT DO NOTHING;
-
-IF NOT FOUND
-THEN
- -- Idempotency check: see if an identical record exists.
- -- Note that by checking 'coin_sig', we implicitly check
- -- identity over everything that the signature covers.
- -- We do select over merchant_pub and h_contract_terms
- -- primarily here to maximally use the existing index.
- PERFORM
- FROM refunds
- WHERE
- deposit_serial_id=dsi AND
- rtransaction_id=in_rtransaction_id AND
- amount_with_fee_val=in_amount_with_fee_val AND
- amount_with_fee_frac=in_amount_with_fee_frac;
-
- IF NOT FOUND
- THEN
- -- Deposit exists, but have conflicting refund.
- out_refund_ok=FALSE;
- out_conflict=TRUE;
- out_not_found=FALSE;
- RETURN;
- END IF;
-
- -- Idempotent request known, return success.
- out_refund_ok=TRUE;
- out_conflict=FALSE;
- out_not_found=FALSE;
- out_gone=FALSE;
- RETURN;
-END IF;
-
-
-IF out_gone
-THEN
- -- money already sent to the merchant. Tough luck.
- out_refund_ok=FALSE;
- out_conflict=FALSE;
- out_not_found=FALSE;
- RETURN;
-END IF;
-
-
-
--- Check refund balance invariant.
-SELECT
- SUM(amount_with_fee_val) -- overflow here is not plausible
- ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits
- INTO
- tmp_val
- ,tmp_frac
- FROM refunds
- WHERE
- deposit_serial_id=dsi;
-IF tmp_val IS NULL
-THEN
- RAISE NOTICE 'failed to sum up existing refunds';
- out_refund_ok=FALSE;
- out_conflict=FALSE;
- out_not_found=FALSE;
- RETURN;
-END IF;
-
--- Normalize result before continuing
-tmp_val = tmp_val + tmp_frac / 100000000;
-tmp_frac = tmp_frac % 100000000;
-
--- Actually check if the deposits are sufficient for the refund. Verbosely. ;-)
-IF (tmp_val < deposit_val)
-THEN
- out_refund_ok=TRUE;
-ELSE
- IF (tmp_val = deposit_val) AND (tmp_frac <= deposit_frac)
- THEN
- out_refund_ok=TRUE;
- ELSE
- out_refund_ok=FALSE;
- END IF;
-END IF;
-
-IF (tmp_val = deposit_val) AND (tmp_frac = deposit_frac)
-THEN
- -- Refunds have reached the full value of the original
- -- deposit. Also refund the deposit fee.
- in_amount_frac = in_amount_frac + in_deposit_fee_frac;
- in_amount_val = in_amount_val + in_deposit_fee_val;
-
- -- Normalize result before continuing
- in_amount_val = in_amount_val + in_amount_frac / 100000000;
- in_amount_frac = in_amount_frac % 100000000;
-END IF;
-
--- Update balance of the coin.
-UPDATE known_coins
- SET
- remaining_frac=remaining_frac+in_amount_frac
- - CASE
- WHEN remaining_frac+in_amount_frac >= 100000000
- THEN 100000000
- ELSE 0
- END,
- remaining_val=remaining_val+in_amount_val
- + CASE
- WHEN remaining_frac+in_amount_frac >= 100000000
- THEN 1
- ELSE 0
- END
- WHERE coin_pub=in_coin_pub;
-
-
-out_conflict=FALSE;
-out_not_found=FALSE;
-
-END $$;
-
--- COMMENT ON FUNCTION exchange_do_refund(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN)
--- IS 'Executes a refund operation, checking that the corresponding deposit
was sufficient to cover the refunded amount';
-
-
-CREATE OR REPLACE FUNCTION exchange_do_recoup_to_reserve(
- IN in_reserve_pub BYTEA,
- IN in_reserve_out_serial_id INT8,
- IN in_coin_blind BYTEA,
- IN in_coin_pub BYTEA,
- IN in_known_coin_id INT8,
- IN in_coin_sig BYTEA,
- IN in_reserve_gc INT8,
- IN in_reserve_expiration INT8,
- IN in_recoup_timestamp INT8,
- OUT out_recoup_ok BOOLEAN,
- OUT out_internal_failure BOOLEAN,
- OUT out_recoup_timestamp INT8)
-LANGUAGE plpgsql
-AS $$
-DECLARE
- tmp_val INT8; -- amount recouped
-DECLARE
- tmp_frac INT8; -- amount recouped
-BEGIN
--- Shards: SELECT known_coins (by coin_pub)
--- SELECT recoup (by known_coin_id)
--- UPDATE known_coins (by coin_pub)
--- UPDATE reserves (by reserve_pub)
--- INSERT recoup (by known_coin_id)
-
-out_internal_failure=FALSE;
-
-
--- Check remaining balance of the coin.
-SELECT
- remaining_frac
- ,remaining_val
- INTO
- tmp_frac
- ,tmp_val
-FROM known_coins
- WHERE coin_pub=in_coin_pub;
-
-IF NOT FOUND
-THEN
- out_internal_failure=TRUE;
- out_recoup_ok=FALSE;
- RETURN;
-END IF;
-
-IF tmp_val + tmp_frac = 0
-THEN
- -- Check for idempotency
- SELECT
- recoup_timestamp
- INTO
- out_recoup_timestamp
- FROM recoup
- WHERE known_coin_id=in_known_coin_id;
-
- out_recoup_ok=FOUND;
- RETURN;
-END IF;
-
-
--- Update balance of the coin.
-UPDATE known_coins
- SET
- remaining_frac=0
- ,remaining_val=0
- WHERE coin_pub=in_coin_pub;
-
-
--- Credit the reserve and update reserve timers.
-UPDATE reserves
- SET
- current_balance_frac=current_balance_frac+tmp_frac
- - CASE
- WHEN current_balance_frac+tmp_frac >= 100000000
- THEN 100000000
- ELSE 0
- END,
- current_balance_val=current_balance_val+tmp_val
- + CASE
- WHEN current_balance_frac+tmp_frac >= 100000000
- THEN 1
- ELSE 0
- END,
- gc_date=GREATEST(gc_date, in_reserve_gc),
- expiration_date=GREATEST(expiration_date, in_reserve_expiration)
- WHERE reserve_pub=in_reserve_pub;
-
-
-IF NOT FOUND
-THEN
- RAISE NOTICE 'failed to increase reserve balance from recoup';
- out_recoup_ok=TRUE;
- out_internal_failure=TRUE;
- RETURN;
-END IF;
-
-
-INSERT INTO recoup
- (known_coin_id
- ,coin_sig
- ,coin_blind
- ,amount_val
- ,amount_frac
- ,recoup_timestamp
- ,reserve_out_serial_id
- )
-VALUES
- (in_known_coin_id
- ,in_coin_sig
- ,in_coin_blind
- ,tmp_val
- ,tmp_frac
- ,in_recoup_timestamp
- ,in_reserve_out_serial_id);
-
--- Normal end, everything is fine.
-out_recoup_ok=TRUE;
-out_recoup_timestamp=in_recoup_timestamp;
-
-END $$;
-
--- COMMENT ON FUNCTION exchange_do_recoup_to_reserve(INT8, INT4, BYTEA,
BOOLEAN, BOOLEAN)
--- IS 'Executes a recoup of a coin that was withdrawn from a reserve';
-
-
-
-
-
-
-CREATE OR REPLACE FUNCTION exchange_do_recoup_to_coin(
- IN in_old_coin_pub BYTEA,
- IN in_rrc_serial INT8,
- IN in_coin_blind BYTEA,
- IN in_coin_pub BYTEA,
- IN in_known_coin_id INT8,
- IN in_coin_sig BYTEA,
- IN in_recoup_timestamp INT8,
- OUT out_recoup_ok BOOLEAN,
- OUT out_internal_failure BOOLEAN,
- OUT out_recoup_timestamp INT8)
-LANGUAGE plpgsql
-AS $$
-DECLARE
- tmp_val INT8; -- amount recouped
-DECLARE
- tmp_frac INT8; -- amount recouped
-BEGIN
-
--- Shards: UPDATE known_coins (by coin_pub)
--- SELECT recoup_refresh (by known_coin_id)
--- UPDATE known_coins (by coin_pub)
--- INSERT recoup_refresh (by known_coin_id)
-
-
-out_internal_failure=FALSE;
-
-
--- Check remaining balance of the coin.
-SELECT
- remaining_frac
- ,remaining_val
- INTO
- tmp_frac
- ,tmp_val
-FROM known_coins
- WHERE coin_pub=in_coin_pub;
-
-IF NOT FOUND
-THEN
- out_internal_failure=TRUE;
- out_recoup_ok=FALSE;
- RETURN;
-END IF;
-
-IF tmp_val + tmp_frac = 0
-THEN
- -- Check for idempotency
- SELECT
- recoup_timestamp
- INTO
- out_recoup_timestamp
- FROM recoup_refresh
- WHERE known_coin_id=in_known_coin_id;
- out_recoup_ok=FOUND;
- RETURN;
-END IF;
-
--- Update balance of the coin.
-UPDATE known_coins
- SET
- remaining_frac=0
- ,remaining_val=0
- WHERE coin_pub=in_coin_pub;
-
-
--- Credit the old coin.
-UPDATE known_coins
- SET
- remaining_frac=remaining_frac+tmp_frac
- - CASE
- WHEN remaining_frac+tmp_frac >= 100000000
- THEN 100000000
- ELSE 0
- END,
- remaining_val=remaining_val+tmp_val
- + CASE
- WHEN remaining_frac+tmp_frac >= 100000000
- THEN 1
- ELSE 0
- END
- WHERE coin_pub=in_old_coin_pub;
-
-
-IF NOT FOUND
-THEN
- RAISE NOTICE 'failed to increase old coin balance from recoup';
- out_recoup_ok=TRUE;
- out_internal_failure=TRUE;
- RETURN;
-END IF;
-
-
-INSERT INTO recoup_refresh
- (known_coin_id
- ,coin_sig
- ,coin_blind
- ,amount_val
- ,amount_frac
- ,recoup_timestamp
- ,rrc_serial
- )
-VALUES
- (in_known_coin_id
- ,in_coin_sig
- ,in_coin_blind
- ,tmp_val
- ,tmp_frac
- ,in_recoup_timestamp
- ,in_rrc_serial);
-
--- Normal end, everything is fine.
-out_recoup_ok=TRUE;
-out_recoup_timestamp=in_recoup_timestamp;
-
-END $$;
-
-
--- COMMENT ON FUNCTION exchange_do_recoup_to_coin(INT8, INT4, BYTEA, BOOLEAN,
BOOLEAN)
--- IS 'Executes a recoup-refresh of a coin that was obtained from a
refresh-reveal process';
-
-
-
-CREATE OR REPLACE PROCEDURE exchange_do_gc(
- IN in_ancient_date INT8,
- IN in_now INT8)
-LANGUAGE plpgsql
-AS $$
-DECLARE
- reserve_uuid_min INT8; -- minimum reserve UUID still alive
-DECLARE
- melt_min INT8; -- minimum melt still alive
-DECLARE
- coin_min INT8; -- minimum known_coin still alive
-DECLARE
- deposit_min INT8; -- minimum deposit still alive
-DECLARE
- reserve_out_min INT8; -- minimum reserve_out still alive
-DECLARE
- denom_min INT8; -- minimum denomination still alive
-BEGIN
-
-DELETE FROM prewire
- WHERE finished=TRUE;
-
-DELETE FROM wire_fee
- WHERE end_date < in_ancient_date;
-
--- TODO: use closing fee as threshold?
-DELETE FROM reserves
- WHERE gc_date < in_now
- AND current_balance_val = 0
- AND current_balance_frac = 0;
-
-SELECT
- reserve_out_serial_id
- INTO
- reserve_out_min
- FROM reserves_out
- ORDER BY reserve_out_serial_id ASC
- LIMIT 1;
-
-DELETE FROM recoup
- WHERE reserve_out_serial_id < reserve_out_min;
-
-
-SELECT
- reserve_uuid
- INTO
- reserve_uuid_min
- FROM reserves
- ORDER BY reserve_uuid ASC
- LIMIT 1;
-
-DELETE FROM reserves_out
- WHERE reserve_uuid < reserve_uuid_min;
-
-
-DELETE FROM denominations
- WHERE expire_legal < in_now
- AND denominations_serial NOT IN
- (SELECT DISTINCT denominations_serial
- FROM reserves_out)
- AND denominations_serial NOT IN
- (SELECT DISTINCT denominations_serial
- FROM known_coins
- WHERE known_coin_id IN
- (SELECT DISTINCT known_coin_id
- FROM recoup))
- AND denominations_serial NOT IN
- (SELECT DISTINCT denominations_serial
- FROM known_coins
- WHERE known_coin_id IN
- (SELECT DISTINCT known_coin_id
- FROM recoup_refresh));
-
-SELECT
- melt_serial_id
- INTO
- melt_min
- FROM refresh_commitments
- ORDER BY melt_serial_id ASC
- LIMIT 1;
-
-DELETE FROM refresh_revealed_coins
- WHERE melt_serial_id < melt_min;
-
-DELETE FROM refresh_transfer_keys
- WHERE melt_serial_id < melt_min;
-
-SELECT
- known_coin_id
- INTO
- coin_min
- FROM known_coins
- ORDER BY known_coin_id ASC
- LIMIT 1;
-
-DELETE FROM deposits
- WHERE known_coin_id < coin_min;
-
-SELECT
- deposit_serial_id
- INTO
- deposit_min
- FROM deposits
- ORDER BY deposit_serial_id ASC
- LIMIT 1;
-
-DELETE FROM refunds
- WHERE deposit_serial_id < deposit_min;
-
-DELETE FROM aggregation_tracking
- WHERE deposit_serial_id < deposit_min;
-
-SELECT
- denominations_serial
- INTO
- denom_min
- FROM denominations
- ORDER BY denominations_serial ASC
- LIMIT 1;
-
-DELETE FROM cs_nonce_locks
- WHERE max_denomination_serial <= denom_min;
-
-END $$;
-
-
--- Complete transaction
-COMMIT;
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [taler-exchange] branch master updated: stay with partitions for now - find shards on dev/boss/sharding,
gnunet <=