commit 4f6fdb48998eae82e7bafa5bbce2f351e8802150
parent 8c00b38fb8bdbe3628c6566a811dc738ce90ad1f
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Sat, 22 Mar 2025 16:29:51 +0100
-debugging of new statistic logic
Diffstat:
3 files changed, 136 insertions(+), 48 deletions(-)
diff --git a/src/backenddb/merchant-0014.sql b/src/backenddb/merchant-0014.sql
@@ -155,7 +155,7 @@ CREATE TABLE merchant_statistic_counter_event
REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
,slot INT8 NOT NULL
,delta INT8 NOT NULL
- ,UNIQUE (imeta_serial_id, slot)
+ ,UNIQUE (imeta_serial_id, merchant_serial, slot)
);
COMMENT ON TABLE merchant_statistic_counter_event
IS 'number to decrement an interval statistic by when a certain time value is reached';
@@ -236,7 +236,7 @@ CREATE TABLE merchant_statistic_interval_amount
,curr VARCHAR(12) NOT NULL
,cumulative_value INT8 NOT NULL
,cumulative_frac INT4 NOT NULL
- ,UNIQUE (imeta_serial_id,merchant_serial,curr)
+ ,UNIQUE (imeta_serial_id,merchant_serial,curr,range)
);
COMMENT ON TABLE merchant_statistic_interval_amount
IS 'various amount statistics (in various currencies) being tracked';
diff --git a/src/backenddb/pg_statistics_examples.sql b/src/backenddb/pg_statistics_examples.sql
@@ -28,6 +28,7 @@ BEGIN;
SET search_path TO example_statistics,merchant;
+
-- Setup statistic: what do we want to track for 'deposits'?
-- (Note: this is basically the one "manual" step we might not keep hard-coded)
INSERT INTO merchant_statistic_bucket_meta
@@ -42,7 +43,8 @@ VALUES
,'amount'
,ARRAY['second'::statistic_range, 'minute', 'day', 'month', 'quarter', 'year']
,ARRAY[120, 120, 95, 36, 40, 100] -- track last 120 s, 120 minutes, 95 days, 36 months, 40 quarters & 100 years
- );
+ )
+ON CONFLICT DO NOTHING;
INSERT INTO merchant_statistic_interval_meta
(slug
@@ -60,8 +62,20 @@ VALUES
,ARRAY[1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
5, 5, 5, 5, 5,
60, 60 * 60, 24 * 60 * 60]
- );
+ ),
+ ('products-sold'
+ ,'number of products sold'
+ ,'number'
+ ,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]
+ )
+ON CONFLICT DO NOTHING;
+DROP FUNCTION IF EXISTS merchant_deposits_statistics_trigger CASCADE;
CREATE FUNCTION merchant_deposits_statistics_trigger()
RETURNS trigger
LANGUAGE plpgsql
@@ -127,8 +141,10 @@ VALUES
,'number'
,ARRAY['second'::statistic_range, 'minute', 'day', 'week', 'month', 'quarter', 'year']
,ARRAY[120, 120, 60, 12, 24, 8, 10] -- track last 120s, 120 minutes, 60 days, 12 weeks, 24 months, 8 quarters and 10 years
- );
+ )
+ON CONFLICT DO NOTHING;
+DROP FUNCTION IF EXISTS merchant_products_sold_statistics_trigger CASCADE;
CREATE FUNCTION merchant_products_sold_statistics_trigger()
RETURNS trigger
LANGUAGE plpgsql
@@ -155,14 +171,38 @@ CREATE TRIGGER merchant_products_on_sold
ON merchant_inventory
FOR EACH ROW EXECUTE FUNCTION merchant_products_sold_statistics_trigger();
-call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP, 1);
+delete from merchant.merchant_statistic_bucket_counter ;
+delete from merchant.merchant_statistic_bucket_amount ;
+delete from merchant.merchant_statistic_interval_counter;
+delete from merchant.merchant_statistic_interval_amount;
+delete from merchant.merchant_statistic_amount_event;
+delete from merchant.merchant_statistic_counter_event;
-call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP, (2,2,'EUR')::taler_amount_currency);
-select * from merchant_statistic_interval_number_get ('deposits', 'default');
+call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 minutes', 1);
+call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 minutes', (1,1,'EUR')::taler_amount_currency);
+call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 seconds', 2);
+call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '1 minute', (2,2,'EUR')::taler_amount_currency);
+call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 seconds', (4,4,'EUR')::taler_amount_currency);
+call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '1 second', (8,8,'EUR')::taler_amount_currency);
+call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP, 4);
+call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP, (16,16,'EUR')::taler_amount_currency);
+
+select * from merchant_statistic_interval_number_get ('products-sold', 'default');
select * from merchant_statistic_interval_amount_get ('deposits', 'default');
+select * from merchant.merchant_statistic_amount_event;
+
+select * from merchant.merchant_statistic_counter_event;
+
+select * from merchant.merchant_statistic_interval_counter;
+
+select * from merchant.merchant_statistic_interval_amount;
+
+select * from merchant.merchant_statistic_bucket_counter ;
+select * from merchant.merchant_statistic_bucket_amount ;
-ROLLBACK;
+-- ROLLBACK;
+COMMIT;
diff --git a/src/backenddb/pg_statistics_helpers.sql b/src/backenddb/pg_statistics_helpers.sql
@@ -189,15 +189,17 @@ DECLARE
my_now INT8;
my_record RECORD;
my_meta INT8;
- my_range0 INT8;
- my_precision0 INT8;
+ my_ranges INT8[];
+ my_precisions INT8[];
+ my_rangex INT8;
+ my_precisionx INT8;
my_start INT8;
my_event INT8;
BEGIN
- my_now = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8;
+ my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
SELECT imeta_serial_id
- ,ranges[1] AS range0
- ,precisions[1] AS precision0
+ ,ranges AS ranges
+ ,precisions AS precisions
INTO my_record
FROM merchant_statistic_interval_meta
WHERE slug=in_slug
@@ -207,12 +209,29 @@ BEGIN
RETURN;
END IF;
+ my_start = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8 / 1000 / 1000; -- convert to seconds
+ my_precisions = my_record.precisions;
+ my_ranges = my_record.ranges;
+ my_rangex = NULL;
+ FOR my_x IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ IF my_now - my_ranges[my_x] < my_start
+ THEN
+ my_rangex = my_ranges[my_x];
+ my_precisionx = my_precisions[my_x];
+ EXIT;
+ END IF;
+ END LOOP;
+ IF my_rangex IS NULL
+ THEN
+ -- event is beyond the ranges we care about
+ RETURN;
+ END IF;
+
my_meta = my_record.imeta_serial_id;
- my_precision0 = my_record.precision0;
- my_start = my_now / 1000 / 1000; -- convert to seconds
- my_start = my_start - my_start % my_precision0; -- round down
+ my_start = my_start - my_start % my_precisionx; -- round down
- INSERT INTO metchant_statistic_counter_event
+ INSERT INTO merchant_statistic_counter_event AS msce
(imeta_serial_id
,merchant_serial
,slot
@@ -224,7 +243,7 @@ BEGIN
,in_delta)
ON CONFLICT (imeta_serial_id, merchant_serial, slot)
DO UPDATE SET
- delta = delta + in_delta
+ delta = msce.delta + in_delta
RETURNING nevent_serial_id
INTO my_event;
@@ -232,7 +251,7 @@ BEGIN
SET cumulative_number = cumulative_number + in_delta
WHERE imeta_serial_id = my_meta
AND merchant_serial = in_merchant_serial
- AND range=my_range0;
+ AND range=my_rangex;
IF NOT FOUND
THEN
INSERT INTO merchant_statistic_interval_counter
@@ -244,7 +263,7 @@ BEGIN
) VALUES (
my_meta
,in_merchant_serial
- ,my_range0
+ ,my_rangex
,my_event
,in_delta);
END IF;
@@ -267,15 +286,18 @@ DECLARE
my_now INT8;
my_record RECORD;
my_meta INT8;
- my_range0 INT8;
- my_precision0 INT8;
+ my_ranges INT8[];
+ my_precisions INT8[];
+ my_x INT;
+ my_rangex INT8;
+ my_precisionx INT8;
my_start INT8;
my_event INT8;
BEGIN
- my_now = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8;
+ my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
SELECT imeta_serial_id
- ,ranges[1] AS range0
- ,precisions[1] AS precision0
+ ,ranges
+ ,precisions
INTO my_record
FROM merchant_statistic_interval_meta
WHERE slug=in_slug
@@ -285,11 +307,26 @@ BEGIN
RETURN;
END IF;
+ my_start = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8 / 1000 / 1000; -- convert to seconds since epoch
+ my_precisions = my_record.precisions;
+ my_ranges = my_record.ranges;
+ my_rangex = NULL;
+ FOR my_x IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ IF my_now - my_ranges[my_x] < my_start
+ THEN
+ my_rangex = my_ranges[my_x];
+ my_precisionx = my_precisions[my_x];
+ EXIT;
+ END IF;
+ END LOOP;
+ IF my_rangex IS NULL
+ THEN
+ -- event is beyond the ranges we care about
+ RETURN;
+ END IF;
+ my_start = my_start - my_start % my_precisionx; -- round down
my_meta = my_record.imeta_serial_id;
- my_precision0 = my_record.precision0;
- my_range0 = my_record.range0;
- my_start = my_now / 1000 / 1000; -- convert to seconds
- my_start = my_start - my_start % my_precision0; -- round down
INSERT INTO merchant_statistic_amount_event AS msae
(imeta_serial_id
@@ -339,7 +376,7 @@ BEGIN
END
WHERE imeta_serial_id=my_meta
AND merchant_serial=in_merchant_serial
- AND range=my_range0
+ AND range=my_rangex
AND curr=(in_delta).curr;
IF NOT FOUND
THEN
@@ -354,7 +391,7 @@ BEGIN
) VALUES (
my_meta
,in_merchant_serial
- ,my_range0
+ ,my_rangex
,my_event
,(in_delta).curr
,(in_delta).val
@@ -408,7 +445,7 @@ RETURNS SETOF merchant_statistic_interval_number_get_return_value
LANGUAGE plpgsql
AS $$
DECLARE
- my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM NOW()) * 1000000)::INT8;
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
my_ranges INT8[];
my_range INT8;
my_delta INT8;
@@ -453,8 +490,11 @@ BEGIN
WHERE imeta_serial_id = my_meta
AND range = my_range
AND merchant_serial = my_instance_id;
- my_max_serial = my_irec.event_delimiter;
- my_rval.rvalue = my_rval.rvalue + my_irec.cumulative_number;
+ IF FOUND
+ THEN
+ my_max_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
@@ -464,8 +504,10 @@ BEGIN
WHERE imeta_serial_id = my_meta
AND merchant_serial = my_instance_id
AND slot < my_time - my_range
- AND imeta_serial_id >= my_max_serial;
-
+ 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;
@@ -530,7 +572,7 @@ RETURNS SETOF merchant_statistic_interval_amount_get_return_value
LANGUAGE plpgsql
AS $$
DECLARE
- my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM NOW()) * 1000000)::INT8;
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
my_ranges INT8[];
my_range INT8;
my_delta_value INT8;
@@ -590,14 +632,17 @@ BEGIN
AND merchant_serial = my_instance_id
AND curr = my_currency
AND range = my_range;
-
- 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
+
+ IF FOUND
THEN
- my_rval.rvalue.frac = (my_rval.rvalue).frac - 100000000;
- my_rval.rvalue.val = (my_rval.rvalue).val + 1;
+ 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;
+ END IF;
END IF;
-- Check if we have events that left the applicable range
@@ -610,7 +655,10 @@ BEGIN
AND merchant_serial = my_instance_id
AND delta_curr = my_currency
AND slot < my_time - my_range
- AND imeta_serial_id > my_max_serial;
+ 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
@@ -827,7 +875,7 @@ BEGIN
,precisions
FROM merchant_statistic_interval_meta
LOOP
-
+
my_meta = my_rec.imeta_serial_id;
my_ranges = my_rec.ranges;
my_precisions = my_rec.precisions;
@@ -909,7 +957,7 @@ DECLARE
my_now INT8;
my_end INT8;
BEGIN
- my_now = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)); -- seconds since epoch
+ my_now = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)::TIMESTAMP); -- seconds since epoch
FOR my_rec IN
SELECT bmeta_serial_id
,stype
