commit 36cd7f60c1f747a8698578c2436062b4057fb7c7
parent 85e355c7f56ce900a76ea182246f5454c1d4a3d4
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Sat, 22 Mar 2025 10:42:34 +0100
-debugging, wip
Diffstat:
3 files changed, 55 insertions(+), 59 deletions(-)
diff --git a/src/backenddb/merchant-0014.sql b/src/backenddb/merchant-0014.sql
@@ -224,6 +224,7 @@ COMMENT ON COLUMN merchant_statistic_amount_event.delta_value
COMMENT ON COLUMN merchant_statistic_amount_event.delta_frac
IS 'total cumulative amount (fraction) that was added at the time identified by slot';
+
CREATE TABLE merchant_statistic_interval_amount
(imeta_serial_id INT8 NOT NULL
REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE
@@ -255,7 +256,7 @@ COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_frac
CREATE TYPE merchant_statistic_interval_number_get_return_value
AS
(range INT8
- ,value INT8
+ ,rvalue INT8
);
COMMENT ON TYPE merchant_statistic_interval_number_get_return_value
IS 'Return type for merchant_statistic_interval_number_get stored procedure';
diff --git a/src/backenddb/pg_statistics_examples.sql b/src/backenddb/pg_statistics_examples.sql
@@ -54,8 +54,12 @@ VALUES
('deposits'
,'sales (before refunds)'
,'amount'
- ,ARRAY[1,60, 24 * 60 * 60, 30 * 24 * 60 * 60, 365 * 24 * 60 * 60] -- second, minute, day, month, year
- ,ARRAY[1,1, 60, 60 * 60, 24 * 60 * 60] -- second, second, minute, hour, day
+ ,ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
+ 60, 120, 180, 240, 300,
+ 24 * 60 * 60, 30 * 24 * 60 * 60, 365 * 24 * 60 * 60] -- second, minute, day, month, year
+ ,ARRAY[1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 5, 5, 5, 5, 5,
+ 60, 60 * 60, 24 * 60 * 60]
);
CREATE FUNCTION merchant_deposits_statistics_trigger()
@@ -65,6 +69,7 @@ AS $$
DECLARE
my_instance INT8;
BEGIN
+-- SET search_path TO merchant;
SELECT mct.merchant_serial
INTO my_instance
FROM merchant_contract_terms mct
@@ -131,6 +136,7 @@ AS $$
DECLARE
my_sold INT8;
BEGIN
+-- SET search_path TO merchant;
my_sold = NEW.total_sold - OLD.total_sold;
IF (0 < my_sold)
THEN
diff --git a/src/backenddb/pg_statistics_helpers.sql b/src/backenddb/pg_statistics_helpers.sql
@@ -14,6 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
+SET search_path TO merchant;
DROP FUNCTION IF EXISTS interval_to_start;
CREATE OR REPLACE FUNCTION interval_to_start (
IN in_timestamp TIMESTAMP,
@@ -418,7 +419,7 @@ DECLARE
,delta
FROM merchant_statistic_counter_event
WHERE imeta_serial_id = meta_serial_id
- AND instance_id = instid
+ AND merchant_serial = instid
AND slot < my_time - range
AND imeta_serial_id >= min_serial
ORDER BY slot ASC;
@@ -460,7 +461,7 @@ BEGIN
FROM merchant_statistic_interval_counter
WHERE imeta_serial_id = my_meta
AND range = my_range
- AND merchant_serial = in_merchant_serial;
+ AND merchant_serial = my_instance_id;
my_max_serial = my_irec.event_delimiter;
my_rval.rvalue = my_rval.rvalue + my_irec.cumulative_number;
@@ -479,9 +480,9 @@ BEGIN
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 + 1
+ event_delimiter = my_max_serial
WHERE imeta_serial_id = my_meta
- AND merchant_serial = in_merchant_serial
+ AND merchant_serial = my_instance_id
AND range = my_range;
IF (my_i < array_length(my_ranges,1))
THEN
@@ -501,7 +502,7 @@ BEGIN
,cumulative_number
) VALUES (
my_meta
- ,in_merchant_serial
+ ,my_instance_id
,my_ranges[my_i+1]
,my_max_serial
,in_delta);
@@ -516,9 +517,9 @@ BEGIN
END IF;
my_rval.range = my_range;
- RETURN NEXT my_val;
+ RETURN NEXT my_rval;
+ CLOSE my_curs;
END LOOP;
- CLOSE my_curs;
END $$;
COMMENT ON FUNCTION merchant_statistic_interval_number_get
@@ -528,7 +529,7 @@ COMMENT ON FUNCTION merchant_statistic_interval_number_get
DROP FUNCTION IF EXISTS merchant_statistic_interval_amount_get;
CREATE OR REPLACE FUNCTION merchant_statistic_interval_amount_get (
IN in_slug TEXT,
- IN in_instance INT8
+ IN in_instance_id TEXT
)
RETURNS SETOF merchant_statistic_interval_amount_get_return_value
LANGUAGE plpgsql
@@ -542,19 +543,9 @@ DECLARE
my_meta INT8;
my_instance_id INT8;
my_currency TEXT;
- my_curs CURSOR (meta_serial_id INT8,instid INT8,curr TEXT,range INT8,min_serial INT8) FOR
- SELECT nevent_serial_id
- ,delta_value
- ,delta_frac
- FROM merchant_statistic_amount_event
- WHERE imeta_serial_id = meta_serial_id
- AND instance_id = instid
- AND delta_curr = curr
- AND slot < my_time - range
- AND imeta_serial_id >= min_serial
- ORDER BY slot ASC;
my_rec RECORD;
my_irec RECORD;
+ my_jrec RECORD;
my_i INT;
my_max_serial INT8 DEFAULT NULL;
my_rval merchant_statistic_interval_amount_get_return_value;
@@ -595,66 +586,66 @@ BEGIN
FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
LOOP
my_range = my_ranges[my_i];
- SELECT event_delimiter
+ SELECT aevent_serial_id
,cumulative_value
,cumulative_frac
INTO my_irec
FROM merchant_statistic_interval_amount
WHERE imeta_serial_id = my_meta
- AND merchant_serial = in_merchant_serial
+ AND merchant_serial = my_instance_id
AND curr = my_currency
AND range = my_range;
- my_max_serial = my_irec.event_delimiter;
- my_rval.rvalue.val = my_rval.rvalue.val + my_irec.cumulative_value + my_irec.cummulative_frac / 100000000;
- my_rval.rvalue.frac = my_rval.rvalue.frac + my_irec.cummulative_frac % 100000000;
+ my_max_serial = my_irec.aevent_serial_id;
+ 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
THEN
- my_rval.rvalue.frac = my_rval.rvalue.frac - 100000000;
- my_rval.rvalue.val = my_rval.rvalue.val + 1;
+ my_rval.rvalue.frac = (my_rval.rvalue).frac - 100000000;
+ my_rval.rvalue.val = (my_rval.rvalue).val + 1;
END IF;
- my_delta_value = 0;
- my_delta_frac = 0;
- OPEN my_curs (meta_serial_id := my_meta, instid := my_instance_id, curr = my_currency, range := my_range, min_serial := my_max_serial);
- LOOP
- FETCH NEXT FROM my_curs INTO my_irec;
- EXIT WHEN NOT FOUND;
- my_delta_value = my_delta_value + (my_irec).delta_value;
- my_delta_frac = my_delta_frac + (my_irec).delta_frac;
- my_max_serial = my_irec.nevent_serial_id;
- END LOOP;
+ 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 imeta_serial_id > my_max_serial;
- IF (0 != my_delta_value + my_delta_frac)
+ IF FOUND
THEN
-- Normalize my_delta first
my_delta_value = my_delta_value + my_delta_frac / 100000000;
my_delta_frac = my_delta_frac % 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)
+ 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;
+ 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 cummulative_frac < my_delta_frac
+ WHEN cumulative_frac < my_delta_frac
THEN 1
ELSE 0
END,
cumulative_frac = cumulative_frac - my_delta_frac
+ CASE
- WHEN cummulative_frac < my_delta_frac
+ WHEN cumulative_frac < my_delta_frac
THEN 100000000
ELSE 0
END,
- event_delimiter = my_max_serial + 1
+ aevent_serial_id = my_max_serial
WHERE imeta_serial_id = my_meta
- AND merchant_serial = in_merchant_serial
+ AND merchant_serial = my_instance_id
AND curr = my_currency
AND range = my_range;
IF (my_i < array_length(my_ranges,1))
@@ -663,18 +654,18 @@ BEGIN
UPDATE merchant_statistic_interval_amount SET
cumulative_value = cumulative_value + my_delta_value
+ CASE
- WHEN cummulative_frac + my_delta_frac > 100000000
+ WHEN cumulative_frac + my_delta_frac > 100000000
THEN 1
ELSE 0
END,
cumulative_frac = cumulative_frac + my_delta_value
- CASE
- WHEN cummulative_frac + my_delta_frac > 100000000
+ WHEN cumulative_frac + my_delta_frac > 100000000
THEN 100000000
ELSE 0
END
WHERE imeta_serial_id = my_meta
- AND merchant_serial = in_merchant_serial
+ AND merchant_serial = my_merchant_id
AND range=my_ranges[my_i+1];
IF NOT FOUND
THEN
@@ -684,11 +675,11 @@ BEGIN
,aevent_serial_id
,range
,curr
- ,cummulative_value
- ,cummulative_frac
+ ,cumulative_value
+ ,cumulative_frac
) VALUES (
my_meta
- ,in_merchant_serial
+ ,my_instance_id
,my_max_serial
,my_ranges[my_i+1]
,my_currency
@@ -706,9 +697,7 @@ BEGIN
my_rval.range = my_range;
RETURN NEXT my_val;
- END LOOP;
- CLOSE my_curs;
-
+ END LOOP; -- over my_ranges
END LOOP; -- over my_currency
END $$;
@@ -927,7 +916,7 @@ BEGIN
,stype
,ranges[array_length(ranges,1)] AS range
,ages[array_length(ages,1)] AS age
- FROM merchant_statistic_interval_meta
+ FROM merchant_statistic_bucket_meta
LOOP
my_end = my_now - age * range; -- age is given in multiples of the range (in seconds)
IF stype = 'amount'
@@ -944,6 +933,6 @@ BEGIN
END IF;
END LOOP;
END $$;
-COMMENT ON PROCEDURE merchant_statistic_counter_gc
+COMMENT ON PROCEDURE merchant_statistic_bucket_gc
IS 'Performs garbage collection of the merchant_statistic_bucket_counter and merchant_statistic_bucket_amount tables';
