commit 104613dcc3a364a1ae098f2081579abf4e9bc2f7
parent 9dd99be8e97e12021326983dc4127dc8f7398e85
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Sat, 22 Mar 2025 06:46:25 +0100
-checkpoint
Diffstat:
3 files changed, 204 insertions(+), 63 deletions(-)
diff --git a/src/backenddb/merchant-0014.sql b/src/backenddb/merchant-0014.sql
@@ -80,7 +80,7 @@ COMMENT ON COLUMN merchant_statistic_bucket_counter.bmeta_serial_id
COMMENT ON COLUMN merchant_statistic_bucket_counter.merchant_serial
IS 'identifies the instance for which the statistic is kept';
COMMENT ON COLUMN merchant_statistic_bucket_counter.bucket_start
- IS 'start date for the bucket';
+ IS 'start date for the bucket in seconds since the epoch';
COMMENT ON COLUMN merchant_statistic_bucket_counter.bucket_range
IS 'range of the bucket';
COMMENT ON COLUMN merchant_statistic_bucket_counter.cumulative_number
@@ -106,7 +106,7 @@ COMMENT ON COLUMN merchant_statistic_bucket_amount.bmeta_serial_id
COMMENT ON COLUMN merchant_statistic_bucket_amount.merchant_serial
IS 'identifies the instance for which the statistic is kept';
COMMENT ON COLUMN merchant_statistic_bucket_amount.bucket_start
- IS 'start date for the bucket';
+ IS 'start date for the bucket in seconds since the epoch';
COMMENT ON COLUMN merchant_statistic_bucket_amount.bucket_range
IS 'range of the bucket';
COMMENT ON COLUMN merchant_statistic_bucket_amount.curr
@@ -205,7 +205,7 @@ CREATE TABLE merchant_statistic_amount_event
,delta_curr VARCHAR(12) NOT NULL
,delta_value INT8 NOT NULL
,delta_frac INT4 NOT NULL
- ,CONSTRAINT event_key UNIQUE (imeta_serial_id, merchant_serial, slot, delta_curr)
+ ,CONSTRAINT event_key UNIQUE (imeta_serial_id, merchant_serial, delta_curr, slot)
);
COMMENT ON TABLE merchant_statistic_amount_event
IS 'amount to decrement an interval statistic by when a certain time value is reached';
@@ -252,9 +252,20 @@ COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_value
COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_frac
IS 'amount in the respective currency, fraction in units of 1/100000000 of the base value';
+CREATE TYPE merchant_statistic_interval_number_get_return_value
+ AS
+ (range INT8
+ ,value INT8
+ );
+COMMENT ON TYPE merchant_statistic_interval_number_get_return_value
+ IS 'Return type for merchant_statistic_interval_number_get stored procedure';
--- FIXME: logic for statistics garbage collection (delete ancient buckets based on 'ages') is missing! (but not urgent at all).
--- Note: calling merchant_deposit_statistic_get() on each statistic to delete events should be part of the GC
--- (as otherwise events are kept around if the respective statistic is never requested).
+CREATE TYPE merchant_statistic_interval_amount_get_return_value
+ AS
+ (range INT8
+ ,rvalue taler_amount_currency
+ );
+COMMENT ON TYPE merchant_statistic_interval_amount_get_return_value
+ IS 'Return type for merchant_statistic_interval_amount_get stored procedure';
COMMIT;
diff --git a/src/backenddb/pg_statistics_examples.sql b/src/backenddb/pg_statistics_examples.sql
@@ -26,11 +26,11 @@ SELECT _v.register_patch('example-statistics-0001', NULL, NULL);
CREATE SCHEMA example_statistics;
-SET search_path TO example_statistics;
+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.merchant_statistic_bucket_meta
+INSERT INTO merchant_statistic_bucket_meta
(slug
,description
,stype
@@ -40,11 +40,11 @@ VALUES
('deposits'
,'sales (before refunds)'
,'amount'
- ,ARRAY['second'::statistic_range, 'minute' 'day', 'month', 'quarter', 'year']
+ ,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
);
-INSERT INTO merchant.merchant_statistic_interval_meta
+INSERT INTO merchant_statistic_interval_meta
(slug
,description
,stype
@@ -81,25 +81,28 @@ END $$;
COMMENT ON FUNCTION merchant_deposits_statistics_trigger
IS 'adds the deposited amount to the deposit statistics';
--- Import all existing deposits from the last year into the table.
-PERFORM FROM merchant_do_bump_amount_stat
- (in_slug
- ,in_merchant_serial
- ,in_timestamp
- ,in_delta)
- SELECT 'deposits'
- ,mct.merchant_serial
- ,TO_TIMESTAMP (mdc.deposit_timestamp / 1000.0 / 1000.0)
- ,mdc.amount_with_fee
- FROM merchant_deposit_confirmations
- JOIN merchant_contract_terms
- USING (order_serial)
- WHERE mdc.deposit_timestamp > (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) - 365*24*60*60) * 1000000;
+DO $$
+DECLARE
+ rec RECORD;
+BEGIN
+FOR rec IN
+ SELECT 'deposits' AS in_slug
+ ,mct.merchant_serial AS in_merchant_serial
+ ,TO_TIMESTAMP (mdc.deposit_timestamp / 1000.0 / 1000.0)::TIMESTAMP AS in_timestamp
+ ,mdc.total_without_fee AS in_delta
+ FROM merchant_deposit_confirmations mdc
+ JOIN merchant_contract_terms mct
+ USING (order_serial)
+ WHERE mdc.deposit_timestamp > (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) - 365*24*60*60) * 1000000
+LOOP
+ CALL merchant_do_bump_amount_stat (rec.in_slug, rec.in_merchant_serial, rec.in_timestamp, rec.in_delta);
+END LOOP;
+END $$;
-- Whenever a deposit is made, call our trigger to bump statistics
CREATE TRIGGER merchant_deposits_on_insert
AFTER INSERT
- ON merchant.merchant_deposits
+ ON merchant_deposits
FOR EACH ROW EXECUTE FUNCTION merchant_deposits_statistics_trigger();
@@ -107,7 +110,7 @@ CREATE TRIGGER merchant_deposits_on_insert
-- Setup statistic
-- (Note: this is basically the one "manual" step we might not keep hard-coded)
-INSERT INTO merchant.merchant_statistic_bucket_meta
+INSERT INTO merchant_statistic_bucket_meta
(slug
,description
,stype
@@ -117,7 +120,7 @@ VALUES
('products-sold'
,'products sold (only those tracked in inventory)'
,'number'
- ,ARRAY['second'::statistic_range, 'minute' 'day', 'week', 'month', 'quarter', 'year']
+ ,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
);
@@ -143,7 +146,7 @@ END $$;
-- Whenever inventory changes, call our trigger to bump statistics
CREATE TRIGGER merchant_products_on_sold
AFTER UPDATE
- ON merchant.merchant_inventory
+ ON merchant_inventory
FOR EACH ROW EXECUTE FUNCTION merchant_products_sold_statistics_trigger();
diff --git a/src/backenddb/pg_statistics_helpers.sql b/src/backenddb/pg_statistics_helpers.sql
@@ -14,6 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
+BEGIN;
+SET search_path TO merchant;
DROP FUNCTION IF EXISTS interval_to_start;
CREATE OR REPLACE FUNCTION interval_to_start (
@@ -267,9 +269,9 @@ DECLARE
my_now INT8;
my_record RECORD;
my_meta INT8;
+ my_range0 INT8;
my_precision0 INT8;
my_start INT8;
- my_range0 INT8;
my_event INT8;
BEGIN
my_now = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8;
@@ -323,7 +325,7 @@ BEGIN
RETURNING aevent_serial_id
INTO my_event;
- UPDATE merchant_statistic_interval_amount
+ UPDATE merchant_statistic_interval_amount
SET
cumulative_value = cumulative_value + (in_delta).val
+ CASE
@@ -399,14 +401,12 @@ COMMENT ON PROCEDURE merchant_do_bump_amount_stat
IS 'Updates an amount statistic (bucket or interval)';
--- FIXME: rewrite to return values for ALL ranges! (more sane!)
DROP FUNCTION IF EXISTS merchant_statistic_interval_number_get;
CREATE OR REPLACE FUNCTION merchant_statistic_interval_number_get (
IN in_slug TEXT,
- IN in_instance_id TEXT,
- IN in_range INT8,
- OUT out_value INT8
+ IN in_instance_id TEXT
)
+RETURNS SETOF merchant_statistic_interval_number_get_return_value
LANGUAGE plpgsql
AS $$
DECLARE
@@ -429,7 +429,7 @@ DECLARE
my_irec RECORD;
my_i INT;
my_max_serial INT8 DEFAULT NULL;
- my_rval INT8 DEFAULT 0;
+ my_rval merchant_statistic_interval_number_get_return_value;
BEGIN
SELECT merchant_serial
INTO my_instance_id
@@ -437,7 +437,6 @@ BEGIN
WHERE merchant_id=in_instance_id;
IF NOT FOUND
THEN
- out_value = 0;
RETURN;
END IF;
@@ -449,10 +448,9 @@ BEGIN
WHERE slug=in_slug;
IF NOT FOUND
THEN
- out_value = 0;
RETURN;
END IF;
-
+ my_rval.rvalue = 0;
my_ranges = my_rec.ranges;
my_meta = my_rec.imeta_serial_id;
-- my_precisions = my_rec.precisions;
@@ -469,7 +467,7 @@ BEGIN
AND range = my_range
AND merchant_serial = in_merchant_serial;
my_max_serial = my_irec.event_delimiter;
- my_rval = my_rval + my_irec.cumulative_number;
+ my_rval.rvalue = my_rval.rvalue + my_irec.cumulative_number;
my_delta = 0;
OPEN my_curs (meta_serial_id := my_meta, range := my_range, instid := my_instance_id, min_serial := my_max_serial);
@@ -483,7 +481,7 @@ BEGIN
IF (0 != my_delta)
THEN
-- remove expired events from the sum of the current slot
- my_rval = my_rval - my_delta;
+ 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
@@ -498,7 +496,6 @@ BEGIN
WHERE imeta_serial_id = my_meta
AND merchant_serial = in_merchant_serial
AND range=my_ranges[my_i+1];
- -- FIXME: here we should possibly combine multiple events by rounding down to my_precision!
ELSE
-- events are obsolete, delete them
DELETE FROM merchant_statistic_counter_event
@@ -508,19 +505,35 @@ BEGIN
END IF;
END IF;
- IF (my_range = in_range)
- THEN
- out_value = my_rval;
- END IF;
+ my_rval.range = my_range;
+ RETURN NEXT my_val;
END LOOP;
CLOSE my_curs;
END $$;
COMMENT ON FUNCTION merchant_statistic_interval_number_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';
+ 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 for each range';
+
+
+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
+)
+RETURNS SETOF merchant_statistic_interval_amount_get_return_value
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME: implement!
+END $$;
+
+COMMENT ON FUNCTION merchant_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';
+
+
+
--- FIXME: create dual of this function to GC the amount_events! (but needs additional loop per currency, and fancy amount handling!)
DROP PROCEDURE IF EXISTS merchant_statistic_counter_gc;
CREATE OR REPLACE PROCEDURE merchant_statistic_counter_gc ()
LANGUAGE plpgsql
@@ -608,31 +621,145 @@ COMMENT ON PROCEDURE merchant_statistic_counter_gc
IS 'Performs garbage collection and compaction of the merchant_statistic_counter_event table';
-DROP PROCEDURE IF EXISTS merchant_statistic_bucket_gc;
-CREATE OR REPLACE PROCEDURE merchant_statistic_bucket_gc ()
+
+DROP PROCEDURE IF EXISTS merchant_statistic_amount_gc;
+CREATE OR REPLACE PROCEDURE merchant_statistic_amount_gc ()
LANGUAGE plpgsql
AS $$
+DECLARE
+ my_instance INT8;
+ 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;
+ end_slot INT8;
+ my_total_val INT8;
+ my_total_frac INT8;
BEGIN
- -- FIXME: GC buckets!
+ -- GC for all instances
+ FOR my_instance IN
+ SELECT DISTINCT merchant_serial
+ FROM merchant_statistic_counter_event
+ LOOP
+ -- Do combination work for all numeric statistic events
+ FOR my_rec IN
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ FROM merchant_statistic_interval_meta
+ LOOP
+
+ 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 merchant_statistic_amount_event
+ WHERE imeta_serial_id = my_meta
+ LOOP
+
+ FOR my_i IN 1..COALESCE(array_length(my_ranges,1)-1,0)
+ LOOP
+ min_slot = my_ranges[my_i];
+ end_slot = my_ranges[my_i + 1];
+ my_precision = my_precisions[my_i];
+ LOOP
+ EXIT WHEN min_slot >= end_slot;
+ max_slot = min_slot + my_precision;
+ SELECT SUM(delta_value) AS total_val,
+ SUM(delta_frac) AS total_frac,
+ COUNT(*) AS matches
+ INTO my_sum
+ FROM merchant_statistic_counter_event
+ WHERE imeta_serial_id=my_meta
+ AND merchant_serial=my_instance
+ AND delta_curr = my_currency
+ AND slot >= min_slot
+ AND slot < max_slot;
+ -- we only proceed if we had more then one match (optimization)
+ IF FOUND AND my_sum.matches > 1
+ THEN
+ my_total_frac = my_sum.total_frac % 100000000;
+ my_total_val = my_sum.total_val + my_sum.total_frac / 100000000;
+ -- combine entries
+ DELETE FROM merchant_statistic_counter_event
+ WHERE imeta_serial_id=my_meta
+ AND merchant_serial=my_instance
+ AND delta_curr = my_currency
+ AND slot >= min_slot
+ AND slot < max_slot;
+ -- The previous delete ensures that this is a conflict-free INSERT
+ INSERT INTO merchant_statistic_counter_event
+ (imeta_serial_id
+ ,merchant_serial
+ ,slot
+ ,delta_curr
+ ,delta_value
+ ,delta_frac
+ ) VALUES
+ (my_meta
+ ,my_instance
+ ,min_slot
+ ,my_currency
+ ,my_total_val
+ ,my_total_frac);
+ END IF;
+ min_slot = min_slot + my_precision;
+ END LOOP; -- min_slot to end_slot by precision loop
+ END LOOP; -- my_i loop
+ -- Finally, delete all events beyond the range we care about
+ DELETE FROM merchant_statistic_counter_event
+ WHERE merchant_serial=my_instance
+ AND imeta_serial_id=my_meta
+ AND slot > my_ranges[array_length(my_ranges,1)];
+ END LOOP; -- my_currency loop
+ END LOOP; -- my_rec loop
+ END LOOP; -- my_instance loop
END $$;
-COMMENT ON PROCEDURE merchant_statistic_counter_gc
- IS 'Performs garbage collection of the merchant_statistic_bucket_counter and merchant_statistic_bucket_amount tables';
+COMMENT ON PROCEDURE merchant_statistic_amount_gc
+ IS 'Performs garbage collection and compaction of the merchant_statistic_amount_event table';
-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_range INT8
-)
-RETURNS SETOF taler_amount_currency
+
+DROP PROCEDURE IF EXISTS merchant_statistic_bucket_gc;
+CREATE OR REPLACE PROCEDURE merchant_statistic_bucket_gc ()
LANGUAGE plpgsql
AS $$
+DECLARE
+ my_rec RECORD;
+ my_now INT8;
+ my_end INT8;
BEGIN
- -- FIXME: implement!
+ my_now = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)); -- seconds since epoch
+ FOR my_rec IN
+ SELECT bmeta_serial_id
+ ,stype
+ ,ranges[array_length(ranges,1)] AS range
+ ,ages[array_length(ages,1)] AS age
+ FROM merchant_statistic_interval_meta
+ LOOP
+ my_end = my_now - age * range; -- age is given in multiples of the range (in seconds)
+ IF stype = 'amount'
+ THEN
+ DELETE
+ FROM merchant_statistic_bucket_amount
+ WHERE bmeta_serial_id = (my_rec).bmeta_serial_id
+ AND bucket_start >= my_end;
+ ELSE
+ DELETE
+ FROM merchant_statistic_bucket_counter
+ WHERE bmeta_serial_id = (my_rec).bmeta_serial_id
+ AND bucket_start >= my_end;
+ END IF;
+ END LOOP;
END $$;
-
-COMMENT ON FUNCTION merchant_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';
+COMMENT ON PROCEDURE merchant_statistic_counter_gc
+ IS 'Performs garbage collection of the merchant_statistic_bucket_counter and merchant_statistic_bucket_amount tables';
