commit cd2a811845569941abe08d34cbdb83b54b9ffe20
parent 4f6fdb48998eae82e7bafa5bbce2f351e8802150
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Sat, 22 Mar 2025 17:29:35 +0100
-more fixes for merchant statistics
Diffstat:
2 files changed, 181 insertions(+), 146 deletions(-)
diff --git a/src/backenddb/merchant-0014.sql b/src/backenddb/merchant-0014.sql
@@ -230,7 +230,7 @@ CREATE TABLE merchant_statistic_interval_amount
REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE
,merchant_serial BIGINT NOT NULL
REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
- ,aevent_serial_id INT8 NOT NULL
+ ,event_delimiter INT8 NOT NULL
REFERENCES merchant_statistic_amount_event (aevent_serial_id) ON DELETE RESTRICT
,range INT8 NOT NULL
,curr VARCHAR(12) NOT NULL
diff --git a/src/backenddb/pg_statistics_helpers.sql b/src/backenddb/pg_statistics_helpers.sql
@@ -384,7 +384,7 @@ BEGIN
(imeta_serial_id
,merchant_serial
,range
- ,aevent_serial_id
+ ,event_delimiter
,curr
,cumulative_value
,cumulative_frac
@@ -450,11 +450,12 @@ DECLARE
my_range INT8;
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_max_serial INT8 DEFAULT NULL;
+ my_min_serial INT8 DEFAULT NULL;
my_rval merchant_statistic_interval_number_get_return_value;
BEGIN
SELECT merchant_serial
@@ -492,67 +493,84 @@ BEGIN
AND merchant_serial = my_instance_id;
IF FOUND
THEN
- my_max_serial = my_irec.event_delimiter;
+ my_min_serial = my_irec.event_delimiter;
my_rval.rvalue = my_rval.rvalue + my_irec.cumulative_number;
- END IF;
- -- Check if we have events that left the applicable range
- SELECT MAX(nevent_serial_id) AS max_serial_id
- ,SUM(delta) AS delta_sum
- INTO my_irec
- FROM merchant_statistic_counter_event
- WHERE imeta_serial_id = my_meta
- AND merchant_serial = my_instance_id
- AND slot < my_time - my_range
- AND nevent_serial_id >= my_max_serial;
- -- FIXME: we don't properly update my_max_serial here!
- -- (need to set it to the next still included value, but we set it to the last DELETED value!)
- -- => results in double-deletion in the next iteration!
- IF FOUND AND my_irec.delta_sum IS NOT NULL
- THEN
- my_delta = my_irec.delta_sum;
- my_max_serial = my_irec.max_serial_id;
- -- remove expired events from the sum of the current slot
- my_rval.rvalue = my_rval.rvalue - my_delta;
- UPDATE merchant_statistic_interval_counter
- SET cumulative_number = cumulative_number - my_delta,
- event_delimiter = my_max_serial
+ -- Check if we have events that left the applicable range
+ SELECT SUM(delta) AS delta_sum
+ INTO my_irec
+ FROM merchant_statistic_counter_event
WHERE imeta_serial_id = my_meta
AND merchant_serial = my_instance_id
- AND range = my_range;
- IF (my_i < array_length(my_ranges,1))
+ AND slot < my_time - my_range
+ AND nevent_serial_id >= my_min_serial;
+
+ IF FOUND AND my_irec.delta_sum IS NOT NULL
THEN
- -- carry over events into the next slot
- UPDATE merchant_statistic_interval_counter
- SET cumulative_number = cumulative_number + my_delta
+ my_delta = my_irec.delta_sum;
+ my_rval.rvalue = my_rval.rvalue - my_delta;
+
+ -- First find out the next event delimiter value
+ SELECT nevent_serial_id
+ INTO my_next_max_serial
+ FROM merchant_statistic_counter_event
WHERE imeta_serial_id = my_meta
AND merchant_serial = my_instance_id
- AND range=my_ranges[my_i+1];
- IF NOT FOUND
+ AND slot >= my_time - my_range
+ AND nevent_serial_id >= my_min_serial
+ ORDER BY slot ASC
+ LIMIT 1;
+
+ IF FOUND
THEN
- INSERT INTO merchant_statistic_interval_counter
- (imeta_serial_id
- ,merchant_serial
- ,range
- ,event_delimiter
- ,cumulative_number
- ) VALUES (
- my_meta
- ,my_instance_id
- ,my_ranges[my_i+1]
- ,my_max_serial
- ,my_delta);
+ -- remove expired events from the sum of the current slot
+
+ UPDATE merchant_statistic_interval_counter
+ SET cumulative_number = cumulative_number - my_delta,
+ event_delimiter = my_next_max_serial
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = my_instance_id
+ AND range = my_range;
+ ELSE
+ -- actually, slot is now empty, remove it entirely
+ DELETE FROM merchant_statistic_interval_counter
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = my_instance_id
+ AND range = my_range;
+ END IF;
+ IF (my_i < array_length(my_ranges,1))
+ THEN
+ -- carry over all events into the next slot
+ UPDATE merchant_statistic_interval_counter AS usic SET
+ cumulative_number = cumulative_number + my_delta,
+ event_delimiter = LEAST(usic.event_delimiter,my_min_serial)
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = my_instance_id
+ AND range=my_ranges[my_i+1];
+ IF NOT FOUND
+ THEN
+ INSERT INTO merchant_statistic_interval_counter
+ (imeta_serial_id
+ ,merchant_serial
+ ,range
+ ,event_delimiter
+ ,cumulative_number
+ ) VALUES (
+ my_meta
+ ,my_instance_id
+ ,my_ranges[my_i+1]
+ ,my_min_serial
+ ,my_delta);
+ END IF;
+ ELSE
+ -- events are obsolete, delete them
+ DELETE FROM merchant_statistic_counter_event
+ WHERE imeta_serial_id = my_meta
+ AND instance_id = my_instance_id
+ AND slot < my_time - my_range;
END IF;
- ELSE
- -- events are obsolete, delete them
- DELETE FROM merchant_statistic_counter_event
- WHERE imeta_serial_id = my_meta
- AND instance_id = my_instance_id
- AND slot < my_time - my_range;
END IF;
- END IF;
- IF my_max_serial IS NOT NULL
- THEN
+
my_rval.range = my_range;
RETURN NEXT my_rval;
END IF;
@@ -579,12 +597,13 @@ DECLARE
my_delta_frac INT8;
my_meta INT8;
my_instance_id INT8;
+ my_next_max_serial INT8;
my_currency TEXT;
my_rec RECORD;
my_irec RECORD;
my_jrec RECORD;
my_i INT;
- my_max_serial INT8 DEFAULT NULL;
+ my_min_serial INT8 DEFAULT NULL;
my_rval merchant_statistic_interval_amount_get_return_value;
BEGIN
SELECT merchant_serial
@@ -623,7 +642,7 @@ BEGIN
FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
LOOP
my_range = my_ranges[my_i];
- SELECT aevent_serial_id
+ SELECT event_delimiter
,cumulative_value
,cumulative_frac
INTO my_irec
@@ -635,7 +654,7 @@ BEGIN
IF FOUND
THEN
- my_max_serial = my_irec.aevent_serial_id;
+ 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;
IF (my_rval.rvalue).frac > 100000000
@@ -643,104 +662,120 @@ BEGIN
my_rval.rvalue.frac = (my_rval.rvalue).frac - 100000000;
my_rval.rvalue.val = (my_rval.rvalue).val + 1;
END IF;
- END IF;
- -- Check if we have events that left the applicable range
- SELECT MAX(aevent_serial_id) AS max_serial_id
- ,SUM(delta_value) AS value_sum
- ,SUM(delta_frac) AS frac_sum
- INTO my_jrec
- FROM merchant_statistic_amount_event
- WHERE imeta_serial_id = my_meta
- AND merchant_serial = my_instance_id
- AND delta_curr = my_currency
- AND slot < my_time - my_range
- AND aevent_serial_id >= my_max_serial;
- -- FIXME: we don't properly update my_max_serial here!
- -- (need to set it to the next still included value, but we set it to the last DELETED value!)
- -- => results in double-deletion in the next iteration!
-
- IF FOUND AND my_jrec.value_sum IS NOT NULL
- THEN
- -- Normalize sum first
- my_delta_value = my_jrec.value_sum + my_jrec.frac_sum / 100000000;
- my_delta_frac = my_jrec.frac_sum % 100000000;
- -- remove expired events from the sum of the current slot
- my_rval.rvalue.val = (my_rval.rvalue).val - my_delta_value;
- IF ((my_rval.rvalue).frac > my_delta_frac)
- THEN
- my_rval.rvalue.frac = (my_rval.rvalue).frac - my_delta_frac;
- ELSE
- my_rval.rvalue.frac = 100000000 + (my_rval.rvalue).frac - my_delta_frac;
- my_rval.rvalue.val = (my_rval.rvalue).val - 1;
- END IF;
- UPDATE merchant_statistic_interval_amount SET
- cumulative_value = cumulative_value - my_delta_value
- - CASE
- WHEN cumulative_frac < my_delta_frac
- THEN 1
- ELSE 0
- END,
- cumulative_frac = cumulative_frac - my_delta_frac
- + CASE
- WHEN cumulative_frac < my_delta_frac
- THEN 100000000
- ELSE 0
- END,
- aevent_serial_id = my_max_serial
+ -- Check if we have events that left the applicable range
+ SELECT SUM(delta_value) AS value_sum
+ ,SUM(delta_frac) AS frac_sum
+ INTO my_jrec
+ FROM merchant_statistic_amount_event
WHERE imeta_serial_id = my_meta
AND merchant_serial = my_instance_id
- AND curr = my_currency
- AND range = my_range;
- IF (my_i < array_length(my_ranges,1))
+ AND delta_curr = my_currency
+ AND slot < my_time - my_range
+ AND aevent_serial_id >= my_min_serial;
+
+ IF FOUND AND my_jrec.value_sum IS NOT NULL
THEN
- -- carry over events into the next (larger) slot
- UPDATE merchant_statistic_interval_amount SET
- cumulative_value = cumulative_value + my_delta_value
- + CASE
- WHEN cumulative_frac + my_delta_frac > 100000000
- THEN 1
- ELSE 0
- END,
- cumulative_frac = cumulative_frac + my_delta_value
- - CASE
- WHEN cumulative_frac + my_delta_frac > 100000000
- THEN 100000000
- ELSE 0
- END
+ -- Normalize sum
+ my_delta_value = my_jrec.value_sum + my_jrec.frac_sum / 100000000;
+ my_delta_frac = my_jrec.frac_sum % 100000000;
+ my_rval.rvalue.val = (my_rval.rvalue).val - my_delta_value;
+ IF ((my_rval.rvalue).frac >= my_delta_frac)
+ THEN
+ my_rval.rvalue.frac = (my_rval.rvalue).frac - my_delta_frac;
+ ELSE
+ my_rval.rvalue.frac = 100000000 + (my_rval.rvalue).frac - my_delta_frac;
+ my_rval.rvalue.val = (my_rval.rvalue).val - 1;
+ END IF;
+
+ -- First find out the next event delimiter value
+ SELECT aevent_serial_id
+ INTO my_next_max_serial
+ FROM merchant_statistic_amount_event
WHERE imeta_serial_id = my_meta
- AND merchant_serial = my_merchant_id
- AND range=my_ranges[my_i+1];
- IF NOT FOUND
+ AND merchant_serial = my_instance_id
+ AND delta_curr = my_currency
+ AND slot >= my_time - my_range
+ AND aevent_serial_id >= my_min_serial
+ ORDER BY slot ASC
+ LIMIT 1;
+ IF FOUND
THEN
- INSERT INTO merchant_statistic_interval_amount
- (imeta_serial_id
- ,merchant_serial
- ,aevent_serial_id
- ,range
- ,curr
- ,cumulative_value
- ,cumulative_frac
- ) VALUES (
- my_meta
- ,my_instance_id
- ,my_max_serial
- ,my_ranges[my_i+1]
- ,my_currency
- ,in_delta_value
- ,in_delta_frac);
+ -- remove expired events from the sum of the current slot
+ UPDATE merchant_statistic_interval_amount SET
+ cumulative_value = cumulative_value - my_delta_value
+ - CASE
+ WHEN cumulative_frac < my_delta_frac
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_frac = cumulative_frac - my_delta_frac
+ + CASE
+ WHEN cumulative_frac < my_delta_frac
+ THEN 100000000
+ ELSE 0
+ END,
+ event_delimiter = my_next_max_serial
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = my_instance_id
+ AND curr = my_currency
+ AND range = my_range;
+ ELSE
+ -- actually, slot is now empty, remove it entirely
+ DELETE FROM merchant_statistic_interval_amount
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = my_instance_id
+ AND curr = my_currency
+ 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 merchant_statistic_interval_amount AS msia SET
+ cumulative_value = cumulative_value + my_delta_value
+ + CASE
+ WHEN cumulative_frac + my_delta_frac > 100000000
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_frac = cumulative_frac + my_delta_value
+ - CASE
+ WHEN cumulative_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 merchant_serial = my_instance_id
+ AND range=my_ranges[my_i+1];
+ IF NOT FOUND
+ THEN
+ INSERT INTO merchant_statistic_interval_amount
+ (imeta_serial_id
+ ,merchant_serial
+ ,event_delimiter
+ ,range
+ ,curr
+ ,cumulative_value
+ ,cumulative_frac
+ ) VALUES (
+ my_meta
+ ,my_instance_id
+ ,my_min_serial
+ ,my_ranges[my_i+1]
+ ,my_currency
+ ,my_delta_value
+ ,my_delta_frac);
+ END IF;
+ ELSE
+ -- events are obsolete, delete them
+ DELETE FROM merchant_statistic_amount_event
+ WHERE imeta_serial_id = my_meta
+ AND instance_id = my_instance_id
+ AND slot < my_time - my_range;
END IF;
- ELSE
- -- events are obsolete, delete them
- DELETE FROM merchant_statistic_amount_event
- WHERE imeta_serial_id = my_meta
- AND instance_id = my_instance_id
- AND slot < my_time - my_range;
END IF;
- END IF;
- IF my_max_serial IS NOT NULL
- THEN
my_rval.range = my_range;
RETURN NEXT my_rval;
END IF;
