commit a8c97f04c501eed04d2ffeb6497270c8d996d8eb
parent 22fc947093199808c81a6635d404ff178ade3fcd
Author: Florian Dold <dold@taler.net>
Date: Mon, 7 Sep 2026 12:36:51 +0200
exchange statistics: use the exchange clock in UTC
Propagate the process time offset to PostgreSQL on connection and
reconnection. Use a shared SQL clock for statistics so event aging and
interval totals follow time travel independently of the session timezone.
Cover cumulative amount and number totals, aging, reconnection and
ordinary SQL clients in the statistics regression scenario.
Issue: https://bugs.taler.net/n/9639
Diffstat:
5 files changed, 149 insertions(+), 7 deletions(-)
diff --git a/src/exchangedb/pg.c b/src/exchangedb/pg.c
@@ -97,6 +97,29 @@ reconnect_cb (struct TALER_EXCHANGEDB_PostgresContext *pg,
GNUNET_break (0);
return;
}
+ {
+ char *set_time;
+ enum GNUNET_GenericReturnValue ret;
+
+ GNUNET_asprintf (&set_time,
+ "SET taler.timetravel_us = '%lld'",
+ GNUNET_TIME_get_offset ());
+ {
+ struct GNUNET_PQ_ExecuteStatement time_es[] = {
+ GNUNET_PQ_make_execute (set_time),
+ GNUNET_PQ_EXECUTE_STATEMENT_END
+ };
+
+ ret = GNUNET_PQ_exec_statements (pq,
+ time_es);
+ }
+ GNUNET_free (set_time);
+ if (GNUNET_OK != ret)
+ {
+ GNUNET_break (0);
+ return;
+ }
+ }
TEH_PG_prep_gen_++;
}
diff --git a/src/exchangedb/sql-schema/exchange_now.sql b/src/exchangedb/sql-schema/exchange_now.sql
@@ -0,0 +1,32 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2026 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+-- Statistics use the same time-travel offset as the exchange process. The
+-- session setting is initialized on connection (including reconnection).
+-- Plain SQL clients use real time unless they explicitly set the offset.
+-- Return UTC civil time so EXTRACT(epoch) and statistic buckets do not depend
+-- on the PostgreSQL session timezone.
+CREATE OR REPLACE FUNCTION exchange_now()
+RETURNS TIMESTAMP
+LANGUAGE SQL
+STABLE
+AS $$
+ SELECT (CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC')
+ + COALESCE(NULLIF(current_setting('taler.timetravel_us', true), ''), '0')::BIGINT
+ * INTERVAL '1 microsecond';
+$$;
+COMMENT ON FUNCTION exchange_now
+ IS 'transaction time with the exchange session time-travel offset';
diff --git a/src/exchangedb/sql-schema/exchange_statistics_helpers.sql b/src/exchangedb/sql-schema/exchange_statistics_helpers.sql
@@ -191,7 +191,7 @@ DECLARE
my_start INT8;
my_event INT8;
BEGIN
- my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_now = ROUND(EXTRACT(epoch FROM exchange_now()) * 1000000)::INT8 / 1000 / 1000;
SELECT imeta_serial_id
,ranges AS ranges
,precisions AS precisions
@@ -289,7 +289,7 @@ DECLARE
my_start INT8;
my_event INT8;
BEGIN
- my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_now = ROUND(EXTRACT(epoch FROM exchange_now()) * 1000000)::INT8 / 1000 / 1000;
SELECT imeta_serial_id
,ranges
,precisions
@@ -431,7 +431,7 @@ RETURNS SETOF exchange_statistic_interval_number_get_return_value
LANGUAGE plpgsql
AS $$
DECLARE
- my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM exchange_now()) * 1000000)::INT8 / 1000 / 1000;
my_ranges INT8[];
my_range INT8;
my_delta INT8;
@@ -568,7 +568,7 @@ RETURNS SETOF exchange_statistic_interval_amount_get_return_value
LANGUAGE plpgsql
AS $$
DECLARE
- my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM exchange_now()) * 1000000)::INT8 / 1000 / 1000;
my_ranges INT8[];
my_range INT8;
my_delta_value INT8;
@@ -752,7 +752,7 @@ CREATE OR REPLACE PROCEDURE exchange_statistic_counter_gc ()
LANGUAGE plpgsql
AS $$
DECLARE
- my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM exchange_now()) * 1000000)::INT8 / 1000 / 1000;
my_h_payto BYTEA;
my_rec RECORD;
my_sum RECORD;
@@ -862,7 +862,7 @@ CREATE OR REPLACE PROCEDURE exchange_statistic_amount_gc ()
LANGUAGE plpgsql
AS $$
DECLARE
- my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM exchange_now()) * 1000000)::INT8 / 1000 / 1000;
my_h_payto BYTEA;
my_rec RECORD;
my_sum RECORD;
@@ -979,7 +979,7 @@ DECLARE
my_now INT8;
my_end INT8;
BEGIN
- my_now = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)::TIMESTAMP); -- seconds since epoch
+ my_now = EXTRACT(EPOCH FROM exchange_now()); -- seconds since epoch
FOR my_rec IN
SELECT bmeta_serial_id
,stype
diff --git a/src/exchangedb/sql-schema/meson.build b/src/exchangedb/sql-schema/meson.build
@@ -43,6 +43,7 @@ procedures_sql = [
'../insert_aml_program_failure.sql',
'../update_to_aml_locked.sql',
'../insert_sanction_list_hit.sql',
+ 'exchange_now.sql',
'exchange_statistics_helpers.sql',
'exchange_trigger_purse_requests_insert.sql',
'exchange_trigger_withdraw_delete.sql',
diff --git a/src/exchangedb/test_regressions.c b/src/exchangedb/test_regressions.c
@@ -845,6 +845,90 @@ check_import_credits_advances_shard (void)
/**
+ * Statistics must return cumulative totals for ranges without their own
+ * events, and age those events using the exchange process's clock.
+ */
+static int
+check_statistics_time_travel (void)
+{
+ struct TALER_EXCHANGEDB_PostgresContext *original_pg = pg;
+ long long original_offset = GNUNET_TIME_get_offset ();
+ const long long future = 60LL * 24 * 60 * 60 * 1000000;
+ int ret = 1;
+
+ /* The ordinary connection starts without time travel. */
+ FAILIF (GNUNET_OK != exec_sql (
+ "SET TIME ZONE 'Europe/Berlin';"
+ "DO $$ BEGIN "
+ " ASSERT exchange_now() = (CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC');"
+ "END $$;"
+ "INSERT INTO exchange_statistic_interval_meta"
+ " (origin,slug,description,stype,ranges,precisions) VALUES"
+ " ('test','clock-amount','test','amount',"
+ " ARRAY[604800,2419200,31449600], ARRAY[86400,86400,86400]),"
+ " ('test','clock-number','test','number',"
+ " ARRAY[604800,2419200,31449600], ARRAY[86400,86400,86400]);"
+ "CALL exchange_do_bump_amount_stat('clock-amount',"
+ " decode(repeat('ab',32),'hex'),exchange_now(),(1,25000000)::taler_amount);"
+ "CALL exchange_do_bump_number_stat('clock-number',"
+ " decode(repeat('ab',32),'hex'),exchange_now(),1);"
+ "DO $$ BEGIN "
+ " ASSERT (SELECT count(*) = 3 AND bool_and(rvalue = (1,25000000)::taler_amount)"
+ " FROM exchange_statistic_interval_amount_get('clock-amount',decode(repeat('ab',32),'hex')));"
+ " ASSERT (SELECT count(*) = 3 AND bool_and(rvalue = 1)"
+ " FROM exchange_statistic_interval_number_get('clock-number',decode(repeat('ab',32),'hex')));"
+ "END $$;"));
+
+ GNUNET_TIME_set_offset (future);
+ pg = TALER_EXCHANGEDB_connect_admin (original_pg->cfg);
+ if (NULL == pg)
+ goto cleanup;
+ if (GNUNET_OK != exec_sql (
+ "DO $$ BEGIN "
+ " ASSERT exchange_now() = (CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC') + INTERVAL '60 days';"
+ "END $$;"
+ "CALL exchange_do_bump_amount_stat('clock-amount',"
+ " decode(repeat('ab',32),'hex'),exchange_now(),(0,75000000)::taler_amount);"
+ "CALL exchange_do_bump_number_stat('clock-number',"
+ " decode(repeat('ab',32),'hex'),exchange_now(),1);"
+ "DO $$ BEGIN "
+ " ASSERT (SELECT count(*) = 3 AND bool_and(rvalue = CASE WHEN range < 31449600"
+ " THEN (0,75000000)::taler_amount ELSE (2,0)::taler_amount END)"
+ " FROM exchange_statistic_interval_amount_get('clock-amount',decode(repeat('ab',32),'hex')));"
+ " ASSERT (SELECT count(*) = 3 AND bool_and(rvalue = CASE WHEN range < 31449600 THEN 1 ELSE 2 END)"
+ " FROM exchange_statistic_interval_number_get('clock-number',decode(repeat('ab',32),'hex')));"
+ "END $$;"))
+ goto cleanup;
+
+ /* Deliberately break this connection. The reconnect callback must restore
+ the offset as well as the ordinary database session settings. */
+ fprintf (stderr,
+ "Deliberately terminating statistics test connection to test reconnect\n");
+ if (GNUNET_SYSERR != exec_sql ("SELECT pg_terminate_backend(pg_backend_pid())"))
+ goto cleanup;
+ GNUNET_PQ_reconnect_if_down (pg->conn);
+ if (GNUNET_OK != exec_sql (
+ "DO $$ BEGIN "
+ " ASSERT exchange_now() = (CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC') + INTERVAL '60 days';"
+ "END $$;"
+ /* A plain SQL client with no setting falls back to ordinary UTC time. */
+ "SET TIME ZONE 'America/New_York';"
+ "RESET taler.timetravel_us;"
+ "DO $$ BEGIN "
+ " ASSERT exchange_now() = (CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC');"
+ "END $$;"))
+ goto cleanup;
+ ret = 0;
+cleanup:
+ if (NULL != pg)
+ TALER_EXCHANGEDB_disconnect (pg);
+ pg = original_pg;
+ GNUNET_TIME_set_offset (original_offset);
+ return ret;
+}
+
+
+/**
* All checks we know about.
*/
static const struct
@@ -852,6 +936,8 @@ static const struct
const char *name;
int (*fn)(void);
} tests[] = {
+ { "statistics-time-travel",
+ &check_statistics_time_travel },
{ "aggregate-refund-below-deposit-fee",
&check_aggregate_refund_below_deposit_fee },
{ "commit-detects-rolled-back-transaction",
