commit 257a54ddbc6755fb7ce9e11a0f7d3d588e4f3098
parent ccec414661e62b79501f54086ce8a9c6fb792076
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Mon, 24 Mar 2025 16:35:47 +0100
adapt exchange_statistic_helpers.sql to exchange version of tables
Diffstat:
2 files changed, 107 insertions(+), 177 deletions(-)
diff --git a/src/exchangedb/exchange-xx10.sql b/src/exchangedb/exchange-xx10.sql
@@ -151,7 +151,7 @@ BEGIN
,partition_suffix
);
PERFORM comment_partitioned_table (
- 'various amount statistics (in various currencies) being tracked',
+ 'various amount statistics being tracked',
,'exchange_statistic_bucket_amount'
,partition_suffix
);
diff --git a/src/exchangedb/exchange_statistics_helpers.sql b/src/exchangedb/exchange_statistics_helpers.sql
@@ -14,12 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
--- FIXME: adapt procedures to changes in table structure for the exchange:
--- - remove multi-currency support, consistent use of exchange amount type
--- - replace instances by h_payto (or NULL!)
--- - new 'origin' field in _meta
-
-
+-- FIXME: add helper function to drop data based on new 'origin' field in _meta
SET search_path TO exchange;
DROP FUNCTION IF EXISTS interval_to_start;
@@ -40,7 +35,7 @@ COMMENT ON FUNCTION interval_to_start
DROP PROCEDURE IF EXISTS exchange_do_bump_number_bucket_stat;
CREATE OR REPLACE PROCEDURE exchange_do_bump_number_bucket_stat(
in_slug TEXT,
- in_exchange_serial BIGINT,
+ in_h_payto BYTEA,
in_timestamp TIMESTAMP,
in_delta INT8
)
@@ -77,20 +72,20 @@ BEGIN
UPDATE exchange_statistic_bucket_counter
SET cumulative_number = cumulative_number + in_delta
WHERE bmeta_serial_id=my_meta
- AND exchange_serial=in_exchange_serial
+ AND h_payto=in_h_payto
AND bucket_start=my_bucket_start
AND bucket_range=my_range;
IF NOT FOUND
THEN
INSERT INTO exchange_statistic_bucket_counter
(bmeta_serial_id
- ,exchange_serial
+ ,h_payto
,bucket_start
,bucket_range
,cumulative_number
) VALUES (
my_meta
- ,in_exchange_serial
+ ,in_h_payto
,my_bucket_start
,my_range
,in_delta);
@@ -103,9 +98,9 @@ END $$;
DROP PROCEDURE IF EXISTS exchange_do_bump_amount_bucket_stat;
CREATE OR REPLACE PROCEDURE exchange_do_bump_amount_bucket_stat(
in_slug TEXT,
- in_exchange_serial BIGINT,
+ in_h_payto BYTEA,
in_timestamp TIMESTAMP,
- in_delta taler_amount_currency
+ in_delta taler_amount
)
LANGUAGE plpgsql
AS $$
@@ -139,20 +134,20 @@ BEGIN
UPDATE exchange_statistic_bucket_amount
SET
- cumulative_value = cumulative_value + (in_delta).val
+ cumulative_value.val = (cumulative_value).val + (in_delta).val
+ CASE
- WHEN (in_delta).frac + cumulative_frac >= 100000000
+ WHEN (in_delta).frac + (cumulative_value).frac >= 100000000
THEN 1
ELSE 0
END,
- cumulative_frac = cumulative_frac + (in_delta).frac
+ cumulative_value.frac = (cumulative_value).frac + (in_delta).frac
- CASE
- WHEN (in_delta).frac + cumulative_frac >= 100000000
+ WHEN (in_delta).frac + (cumulative_value).frac >= 100000000
THEN 100000000
ELSE 0
END
WHERE bmeta_serial_id=my_meta
- AND exchange_serial=in_exchange_serial
+ AND h_payto=in_h_payto
AND curr=(in_delta).curr
AND bucket_start=my_bucket_start
AND bucket_range=my_range;
@@ -160,20 +155,16 @@ BEGIN
THEN
INSERT INTO exchange_statistic_bucket_amount
(bmeta_serial_id
- ,exchange_serial
+ ,h_payto
,bucket_start
,bucket_range
- ,curr
,cumulative_value
- ,cumulative_frac
) VALUES (
my_meta
- ,in_exchange_serial
+ ,in_h_payto
,my_bucket_start
,my_range
- ,(in_delta).curr
- ,(in_delta).val
- ,(in_delta).frac);
+ ,in_delta);
END IF;
END LOOP;
CLOSE my_curs;
@@ -186,7 +177,7 @@ COMMENT ON PROCEDURE exchange_do_bump_amount_bucket_stat
DROP PROCEDURE IF EXISTS exchange_do_bump_number_interval_stat;
CREATE OR REPLACE PROCEDURE exchange_do_bump_number_interval_stat(
in_slug TEXT,
- in_exchange_serial BIGINT,
+ in_h_payto BYTEA,
in_timestamp TIMESTAMP,
in_delta INT8
)
@@ -240,15 +231,15 @@ BEGIN
INSERT INTO exchange_statistic_counter_event AS msce
(imeta_serial_id
- ,exchange_serial
+ ,h_payto
,slot
,delta)
VALUES
(my_meta
- ,in_exchange_serial
+ ,in_h_payto
,my_start
,in_delta)
- ON CONFLICT (imeta_serial_id, exchange_serial, slot)
+ ON CONFLICT (imeta_serial_id, h_payto, slot)
DO UPDATE SET
delta = msce.delta + in_delta
RETURNING nevent_serial_id
@@ -257,19 +248,19 @@ BEGIN
UPDATE exchange_statistic_interval_counter
SET cumulative_number = cumulative_number + in_delta
WHERE imeta_serial_id = my_meta
- AND exchange_serial = in_exchange_serial
+ AND h_payto = in_h_payto
AND range=my_rangex;
IF NOT FOUND
THEN
INSERT INTO exchange_statistic_interval_counter
(imeta_serial_id
- ,exchange_serial
+ ,h_payto
,range
,event_delimiter
,cumulative_number
) VALUES (
my_meta
- ,in_exchange_serial
+ ,in_h_payto
,my_rangex
,my_event
,in_delta);
@@ -283,9 +274,9 @@ COMMENT ON PROCEDURE exchange_do_bump_number_interval_stat
DROP PROCEDURE IF EXISTS exchange_do_bump_amount_interval_stat;
CREATE OR REPLACE PROCEDURE exchange_do_bump_amount_interval_stat(
in_slug TEXT,
- in_exchange_serial BIGINT,
+ in_h_payto BYTEA,
in_timestamp TIMESTAMP,
- in_delta taler_amount_currency -- new amount in table that we should add to the tracker
+ in_delta taler_amount
)
LANGUAGE plpgsql
AS $$
@@ -337,20 +328,20 @@ BEGIN
INSERT INTO exchange_statistic_amount_event AS msae
(imeta_serial_id
- ,exchange_serial
+ ,h_payto
,slot
,delta_curr
,delta_value
,delta_frac
) VALUES (
my_meta
- ,in_exchange_serial
+ ,in_h_payto
,my_start
,(in_delta).curr
,(in_delta).val
,(in_delta).frac
)
- ON CONFLICT (imeta_serial_id, exchange_serial, slot, delta_curr)
+ ON CONFLICT (imeta_serial_id, h_payto, slot, delta_curr)
DO UPDATE SET
delta_value = msae.delta_value + (in_delta).val
+ CASE
@@ -369,40 +360,35 @@ BEGIN
UPDATE exchange_statistic_interval_amount
SET
- cumulative_value = cumulative_value + (in_delta).val
+ cumulative_value.val = (cumulative_value).val + (in_delta).val
+ CASE
- WHEN (in_delta).frac + cumulative_frac >= 100000000
+ WHEN (in_delta).frac + (cumulative_value).frac >= 100000000
THEN 1
ELSE 0
END,
- cumulative_frac = cumulative_frac + (in_delta).frac
+ cumulative_value.frac = (cumulative_value).frac + (in_delta).frac
- CASE
- WHEN (in_delta).frac + cumulative_frac >= 100000000
+ WHEN (in_delta).frac + (cumulative_value).frac >= 100000000
THEN 100000000
ELSE 0
END
WHERE imeta_serial_id=my_meta
- AND exchange_serial=in_exchange_serial
- AND range=my_rangex
- AND curr=(in_delta).curr;
+ AND h_payto=in_h_payto
+ AND range=my_rangex;
IF NOT FOUND
THEN
INSERT INTO exchange_statistic_interval_amount
(imeta_serial_id
- ,exchange_serial
+ ,h_payto
,range
,event_delimiter
- ,curr
,cumulative_value
- ,cumulative_frac
) VALUES (
my_meta
- ,in_exchange_serial
+ ,in_h_payto
,my_rangex
,my_event
- ,(in_delta).curr
- ,(in_delta).val
- ,(in_delta).frac);
+ ,in_delta);
END IF;
END $$;
COMMENT ON PROCEDURE exchange_do_bump_amount_interval_stat
@@ -412,15 +398,15 @@ COMMENT ON PROCEDURE exchange_do_bump_amount_interval_stat
DROP PROCEDURE IF EXISTS exchange_do_bump_number_stat;
CREATE OR REPLACE PROCEDURE exchange_do_bump_number_stat(
in_slug TEXT,
- in_exchange_serial BIGINT,
+ in_h_payto BYTEA,
in_timestamp TIMESTAMP,
in_delta INT8
)
LANGUAGE plpgsql
AS $$
BEGIN
- CALL exchange_do_bump_number_bucket_stat (in_slug, in_exchange_serial, in_timestamp, in_delta);
- CALL exchange_do_bump_number_interval_stat (in_slug, in_exchange_serial, in_timestamp, in_delta);
+ CALL exchange_do_bump_number_bucket_stat (in_slug, in_h_payto, in_timestamp, in_delta);
+ CALL exchange_do_bump_number_interval_stat (in_slug, in_h_payto, in_timestamp, in_delta);
END $$;
COMMENT ON PROCEDURE exchange_do_bump_number_stat
IS 'Updates a numeric statistic (bucket or interval)';
@@ -429,15 +415,15 @@ COMMENT ON PROCEDURE exchange_do_bump_number_stat
DROP PROCEDURE IF EXISTS exchange_do_bump_amount_stat;
CREATE OR REPLACE PROCEDURE exchange_do_bump_amount_stat(
in_slug TEXT,
- in_exchange_serial BIGINT,
+ in_h_payto BYTEA,
in_timestamp TIMESTAMP,
- in_delta taler_amount_currency
+ in_delta taler_amount
)
LANGUAGE plpgsql
AS $$
BEGIN
- CALL exchange_do_bump_amount_bucket_stat (in_slug, in_exchange_serial, in_timestamp, in_delta);
- CALL exchange_do_bump_amount_interval_stat (in_slug, in_exchange_serial, in_timestamp, in_delta);
+ CALL exchange_do_bump_amount_bucket_stat (in_slug, in_h_payto, in_timestamp, in_delta);
+ CALL exchange_do_bump_amount_interval_stat (in_slug, in_h_payto, in_timestamp, in_delta);
END $$;
COMMENT ON PROCEDURE exchange_do_bump_amount_stat
IS 'Updates an amount statistic (bucket or interval)';
@@ -446,7 +432,7 @@ COMMENT ON PROCEDURE exchange_do_bump_amount_stat
DROP FUNCTION IF EXISTS exchange_statistic_interval_number_get;
CREATE OR REPLACE FUNCTION exchange_statistic_interval_number_get (
IN in_slug TEXT,
- IN in_instance_id TEXT
+ IN in_h_payto BYTA
)
RETURNS SETOF exchange_statistic_interval_number_get_return_value
LANGUAGE plpgsql
@@ -458,22 +444,12 @@ DECLARE
my_delta INT8;
my_meta INT8;
my_next_max_serial INT8;
- my_instance_id INT8;
my_rec RECORD;
my_irec RECORD;
my_i INT;
my_min_serial INT8 DEFAULT NULL;
my_rval exchange_statistic_interval_number_get_return_value;
BEGIN
- SELECT exchange_serial
- INTO my_instance_id
- FROM exchange_instances
- WHERE exchange_id=in_instance_id;
- IF NOT FOUND
- THEN
- RETURN;
- END IF;
-
SELECT imeta_serial_id
,ranges
,precisions
@@ -497,7 +473,7 @@ BEGIN
FROM exchange_statistic_interval_counter
WHERE imeta_serial_id = my_meta
AND range = my_range
- AND exchange_serial = my_instance_id;
+ AND h_payto = in_h_payto;
IF FOUND
THEN
my_min_serial = my_irec.event_delimiter;
@@ -508,7 +484,7 @@ BEGIN
INTO my_irec
FROM exchange_statistic_counter_event
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
+ AND h_payto = in_h_payto
AND slot < my_time - my_range
AND nevent_serial_id >= my_min_serial;
@@ -522,7 +498,7 @@ BEGIN
INTO my_next_max_serial
FROM exchange_statistic_counter_event
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
+ AND h_payto = in_h_payto
AND slot >= my_time - my_range
AND nevent_serial_id >= my_min_serial
ORDER BY slot ASC
@@ -536,13 +512,13 @@ BEGIN
SET cumulative_number = cumulative_number - my_delta,
event_delimiter = my_next_max_serial
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
+ AND h_payto = in_h_payto
AND range = my_range;
ELSE
-- actually, slot is now empty, remove it entirely
DELETE FROM exchange_statistic_interval_counter
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
+ AND h_payto = in_h_payto
AND range = my_range;
END IF;
IF (my_i < array_length(my_ranges,1))
@@ -552,19 +528,19 @@ BEGIN
cumulative_number = cumulative_number + my_delta,
event_delimiter = LEAST(usic.event_delimiter,my_min_serial)
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
+ AND h_payto = in_h_payto
AND range=my_ranges[my_i+1];
IF NOT FOUND
THEN
INSERT INTO exchange_statistic_interval_counter
(imeta_serial_id
- ,exchange_serial
+ ,h_payto
,range
,event_delimiter
,cumulative_number
) VALUES (
my_meta
- ,my_instance_id
+ ,in_h_payto
,my_ranges[my_i+1]
,my_min_serial
,my_delta);
@@ -573,7 +549,7 @@ BEGIN
-- events are obsolete, delete them
DELETE FROM exchange_statistic_counter_event
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
+ AND h_payto = in_h_payto
AND slot < my_time - my_range;
END IF;
END IF;
@@ -602,10 +578,9 @@ DECLARE
my_range INT8;
my_delta_value INT8;
my_delta_frac INT8;
+ my_delta taler_amount;
my_meta INT8;
- my_instance_id INT8;
my_next_max_serial INT8;
- my_currency TEXT;
my_rec RECORD;
my_irec RECORD;
my_jrec RECORD;
@@ -613,15 +588,6 @@ DECLARE
my_min_serial INT8 DEFAULT NULL;
my_rval exchange_statistic_interval_amount_get_return_value;
BEGIN
- SELECT exchange_serial
- INTO my_instance_id
- FROM exchange_instances
- WHERE exchange_id=in_instance_id;
- IF NOT FOUND
- THEN
- RETURN;
- END IF;
-
SELECT imeta_serial_id
,ranges
,precisions
@@ -636,34 +602,25 @@ BEGIN
my_meta = my_rec.imeta_serial_id;
my_ranges = my_rec.ranges;
- FOR my_currency IN
- SELECT DISTINCT delta_curr
- FROM exchange_statistic_amount_event
- WHERE imeta_serial_id = my_meta
- LOOP
-
my_rval.rvalue.val = 0;
my_rval.rvalue.frac = 0;
- my_rval.rvalue.curr = my_currency;
FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
LOOP
my_range = my_ranges[my_i];
SELECT event_delimiter
,cumulative_value
- ,cumulative_frac
INTO my_irec
FROM exchange_statistic_interval_amount
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
- AND curr = my_currency
+ AND h_payto = in_h_payto
AND range = my_range;
IF FOUND
THEN
my_min_serial = my_irec.event_delimiter;
- my_rval.rvalue.val = (my_rval.rvalue).val + my_irec.cumulative_value + my_irec.cumulative_frac / 100000000;
- my_rval.rvalue.frac = (my_rval.rvalue).frac + my_irec.cumulative_frac % 100000000;
+ my_rval.rvalue.val = (my_rval.rvalue).val + (my_irec.cumulative_value).val + (my_irec.cumulative_value).frac / 100000000;
+ my_rval.rvalue.frac = (my_rval.rvalue).frac + (my_irec.cumulative_value).frac % 100000000;
IF (my_rval.rvalue).frac > 100000000
THEN
my_rval.rvalue.frac = (my_rval.rvalue).frac - 100000000;
@@ -676,8 +633,7 @@ BEGIN
INTO my_jrec
FROM exchange_statistic_amount_event
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
- AND delta_curr = my_currency
+ AND h_payto = in_h_payto
AND slot < my_time - my_range
AND aevent_serial_id >= my_min_serial;
@@ -700,8 +656,7 @@ BEGIN
INTO my_next_max_serial
FROM exchange_statistic_amount_event
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
- AND delta_curr = my_currency
+ AND h_payto = in_h_payto
AND slot >= my_time - my_range
AND aevent_serial_id >= my_min_serial
ORDER BY slot ASC
@@ -710,75 +665,71 @@ BEGIN
THEN
-- remove expired events from the sum of the current slot
UPDATE exchange_statistic_interval_amount SET
- cumulative_value = cumulative_value - my_delta_value
+ cumulative_value.val = (cumulative_value).val - my_delta_value
- CASE
- WHEN cumulative_frac < my_delta_frac
+ WHEN (cumulative_value).frac < my_delta_frac
THEN 1
ELSE 0
END,
- cumulative_frac = cumulative_frac - my_delta_frac
+ cumulative_value.frac = (cumulative_value).frac - my_delta_frac
+ CASE
- WHEN cumulative_frac < my_delta_frac
+ WHEN (cumulative_value).frac < my_delta_frac
THEN 100000000
ELSE 0
END,
event_delimiter = my_next_max_serial
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
- AND curr = my_currency
+ AND h_payto = in_h_payto
AND range = my_range;
ELSE
-- actually, slot is now empty, remove it entirely
DELETE FROM exchange_statistic_interval_amount
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
- AND curr = my_currency
+ AND h_payto = in_h_payto
AND range = my_range;
END IF;
IF (my_i < array_length(my_ranges,1))
THEN
-- carry over all events into the next (larger) slot
UPDATE exchange_statistic_interval_amount AS msia SET
- cumulative_value = cumulative_value + my_delta_value
+ cumulative_value.val = (cumulative_value).val + my_delta_value
+ CASE
- WHEN cumulative_frac + my_delta_frac > 100000000
+ WHEN (cumulative_value).frac + my_delta_frac > 100000000
THEN 1
ELSE 0
END,
- cumulative_frac = cumulative_frac + my_delta_value
+ cumulative_value.frac = (cumulative_value).frac + my_delta_value
- CASE
- WHEN cumulative_frac + my_delta_frac > 100000000
+ WHEN (cumulative_value).frac + my_delta_frac > 100000000
THEN 100000000
ELSE 0
END,
event_delimiter = LEAST (msia.event_delimiter,my_min_serial)
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
+ AND h_payto = in_h_payto
AND range=my_ranges[my_i+1];
IF NOT FOUND
THEN
+ my_delta.val = my_delta_value;
+ my_delta.frac = my_delta_frac;
INSERT INTO exchange_statistic_interval_amount
(imeta_serial_id
- ,exchange_serial
+ ,h_payto
,event_delimiter
,range
- ,curr
,cumulative_value
- ,cumulative_frac
) VALUES (
my_meta
- ,my_instance_id
+ ,in_h_payto
,my_min_serial
,my_ranges[my_i+1]
- ,my_currency
- ,my_delta_value
- ,my_delta_frac);
+ ,my_delta);
END IF;
ELSE
-- events are obsolete, delete them
DELETE FROM exchange_statistic_amount_event
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance_id
+ AND h_payto = in_h_payto
AND slot < my_time - my_range;
END IF;
END IF;
@@ -787,11 +738,10 @@ BEGIN
RETURN NEXT my_rval;
END IF;
END LOOP; -- over my_ranges
- END LOOP; -- over my_currency
END $$;
COMMENT ON FUNCTION exchange_statistic_interval_amount_get
- IS 'Returns deposit statistic tracking deposited amounts over certain time intervals; we first trim the stored data to only track what is still in-range, and then return the remaining value; multiple values are returned, one per currency and range';
+ IS 'Returns deposit statistic tracking deposited amounts over certain time intervals; we first trim the stored data to only track what is still in-range, and then return the remaining value; multiple values are returned, one per range';
@@ -803,8 +753,7 @@ LANGUAGE plpgsql
AS $$
DECLARE
my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
- my_instance INT8;
- my_instance_name TEXT;
+ my_h_payto BYTEA;
my_rec RECORD;
my_sum RECORD;
my_meta INT8;
@@ -818,8 +767,8 @@ DECLARE
my_total INT8;
BEGIN
-- GC for all instances
- FOR my_instance IN
- SELECT DISTINCT exchange_serial
+ FOR my_h_payto IN
+ SELECT DISTINCT h_payto
FROM exchange_statistic_counter_event
LOOP
-- Do combination work for all numeric statistic events
@@ -831,11 +780,7 @@ BEGIN
FROM exchange_statistic_interval_meta
LOOP
-- First, we query the current interval statistic to update its counters
- SELECT exchange_id
- INTO my_instance_name
- FROM exchange_instances
- WHERE exchange_serial = my_instance;
- PERFORM FROM exchange_statistic_interval_number_get (my_rec.slug, my_instance_name);
+ PERFORM FROM exchange_statistic_interval_number_get (my_rec.slug, my_h_payto);
my_meta = my_rec.imeta_serial_id;
my_ranges = my_rec.ranges;
@@ -867,7 +812,7 @@ BEGIN
MIN(nevent_serial_id) AS rep_serial_id
INTO my_sum
FROM exchange_statistic_counter_event
- WHERE exchange_serial=my_instance
+ WHERE h_payto=my_instance
AND imeta_serial_id=my_meta
AND slot >= my_time - max_slot
AND slot < my_time - min_slot;
@@ -882,7 +827,7 @@ BEGIN
-- combine entries
DELETE FROM exchange_statistic_counter_event
- WHERE exchange_serial=my_instance
+ WHERE h_payto=my_instance
AND imeta_serial_id=my_meta
AND slot >= my_time - max_slot
AND slot < my_time - min_slot
@@ -891,7 +836,7 @@ BEGIN
UPDATE exchange_statistic_counter_event SET
delta = my_total
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance
+ AND h_payto = my_instance
AND nevent_serial_id = my_sum.rep_serial_id;
END IF;
min_slot = min_slot + my_precision;
@@ -901,7 +846,7 @@ BEGIN
RAISE NOTICE 'deleting entries of %/% before % - % = %', my_instance, my_meta, my_time, my_ranges[array_length(my_ranges,1)], my_time - my_ranges[array_length(my_ranges,1)];
DELETE FROM exchange_statistic_counter_event
- WHERE exchange_serial=my_instance
+ WHERE h_payto=my_instance
AND imeta_serial_id=my_meta
AND slot < my_time - my_ranges[array_length(my_ranges,1)];
END LOOP; -- my_rec loop
@@ -918,15 +863,13 @@ LANGUAGE plpgsql
AS $$
DECLARE
my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
- my_instance INT8;
- my_instance_name TEXT;
+ my_h_payto BYTEA;
my_rec RECORD;
my_sum RECORD;
my_meta INT8;
my_ranges INT8[];
my_precisions INT8[];
my_precision INT4;
- my_currency TEXT;
my_i INT4;
min_slot INT8;
max_slot INT8;
@@ -934,9 +877,9 @@ DECLARE
my_total_val INT8;
my_total_frac INT8;
BEGIN
- -- GC for all instances
- FOR my_instance IN
- SELECT DISTINCT exchange_serial
+ -- GC for all accounts
+ FOR my_h_payto IN
+ SELECT DISTINCT h_payto
FROM exchange_statistic_counter_event
LOOP
-- Do combination work for all numeric statistic events
@@ -948,21 +891,12 @@ BEGIN
FROM exchange_statistic_interval_meta
LOOP
- -- First, we query the current interval statistic to update its counters
- SELECT exchange_id
- INTO my_instance_name
- FROM exchange_instances
- WHERE exchange_serial = my_instance;
- PERFORM FROM exchange_statistic_interval_amount_get (my_rec.slug, my_instance_name);
+ -- First, we query the current interval statistic to update its counters
+ PERFORM FROM exchange_statistic_interval_amount_get (my_rec.slug, my_h_payto);
- my_meta = my_rec.imeta_serial_id;
- my_ranges = my_rec.ranges;
- my_precisions = my_rec.precisions;
- FOR my_currency IN
- SELECT DISTINCT delta_curr
- FROM exchange_statistic_amount_event
- WHERE imeta_serial_id = my_meta
- LOOP
+ my_meta = my_rec.imeta_serial_id;
+ my_ranges = my_rec.ranges;
+ my_precisions = my_rec.precisions;
FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
LOOP
@@ -992,8 +926,7 @@ BEGIN
INTO my_sum
FROM exchange_statistic_amount_event
WHERE imeta_serial_id=my_meta
- AND exchange_serial=my_instance
- AND delta_curr = my_currency
+ AND h_payto=my_h_payto
AND slot >= my_time - max_slot
AND slot < my_time - max_slot;
-- we only proceed if we had more then one match (optimization)
@@ -1006,8 +939,7 @@ BEGIN
-- combine entries
DELETE FROM exchange_statistic_amount_event
WHERE imeta_serial_id=my_meta
- AND exchange_serial=my_instance
- AND delta_curr = my_currency
+ AND h_payto=my_h_payto
AND slot >= my_time - max_slot
AND slot < my_time - max_slot
AND aevent_serial_id > my_sum.rep_serial_id;
@@ -1016,23 +948,21 @@ BEGIN
delta_value = my_total_value
,delta_frac = my_total_frac
WHERE imeta_serial_id = my_meta
- AND exchange_serial = my_instance
- AND delta_curr = my_currency
+ AND h_payto = my_h_payto
AND aevent_serial_id = my_sum.rep_serial_id;
END IF;
min_slot = min_slot + my_precision;
END LOOP; -- min_slot to end_slot by precision loop
END LOOP; -- my_i loop
- END LOOP; -- my_currency loop
- -- Finally, delete all events beyond the range we care about
-
- RAISE NOTICE 'deleting entries of %/% before % - % = %', my_instance, my_meta, my_time, my_ranges[array_length(my_ranges,1)], my_time - my_ranges[array_length(my_ranges,1)];
- DELETE FROM exchange_statistic_amount_event
- WHERE exchange_serial=my_instance
- AND imeta_serial_id=my_meta
- AND slot < my_time - my_ranges[array_length(my_ranges,1)];
- END LOOP; -- my_rec loop
- END LOOP; -- my_instance loop
+ -- Finally, delete all events beyond the range we care about
+
+ RAISE NOTICE 'deleting entries of %/% before % - % = %', my_h_payto, my_meta, my_time, my_ranges[array_length(my_ranges,1)], my_time - my_ranges[array_length(my_ranges,1)];
+ DELETE FROM exchange_statistic_amount_event
+ WHERE h_payto=my_h_payto
+ AND imeta_serial_id=my_meta
+ AND slot < my_time - my_ranges[array_length(my_ranges,1)];
+ END LOOP; -- my_rec loop
+ END LOOP; -- my_h_payto loop
END $$;
COMMENT ON PROCEDURE exchange_statistic_amount_gc
IS 'Performs garbage collection and compaction of the exchange_statistic_amount_event table';
