merchant

Merchant backend to process payments, run by merchants
Log | Files | Refs | Submodules | README | LICENSE

report_deliveries.sql (7681B)


      1 --
      2 -- This file is part of TALER
      3 -- Copyright (C) 2026 Taler Systems SA
      4 --
      5 -- TALER is free software; you can redistribute it and/or modify it under the
      6 -- terms of the GNU General Public License as published by the Free Software
      7 -- Foundation; either version 3, or (at your option) any later version.
      8 --
      9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
     10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
     11 -- A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
     12 --
     13 -- You should have received a copy of the GNU General Public License along with
     14 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
     15 
     16 -- These functions execute with the caller's instance search path.
     17 CREATE OR REPLACE FUNCTION merchant.queue_report_delivery(
     18   schedule_id BIGINT, due BIGINT, descr TEXT, program TEXT, source TEXT,
     19   target TEXT, mime TEXT, created BIGINT)
     20 RETURNS BIGINT LANGUAGE plpgsql AS $$
     21 DECLARE
     22   job BIGINT;
     23   delivery BIGINT;
     24 BEGIN
     25   -- Order tests by enqueue time and keep overdue schedules in their due-time order.
     26   INSERT INTO merchant_reports
     27     (report_description,report_program_section,data_source,target_address,mime_type,
     28      report_token,frequency,frequency_shift,next_transmission,one_shot_hidden)
     29     VALUES (descr,program,source,target,mime,public.gen_random_bytes(32),0,0,COALESCE(due,created),TRUE)
     30     RETURNING report_serial INTO job;
     31   -- Hold the instance's place in the history until commit, so a cursor
     32   -- cannot advance past a lower delivery ID that is still uncommitted.
     33   PERFORM pg_advisory_xact_lock('merchant_report_deliveries'::regclass::oid::bigint);
     34   INSERT INTO merchant_report_deliveries
     35     (report_serial,job_id,origin,due_at,description,program_section,data_source,
     36      target_address,mime_type,created_at)
     37     VALUES (schedule_id,job,CASE WHEN due IS NULL THEN 'test' ELSE 'scheduled' END,
     38             due,descr,program,source,target,mime,created)
     39     RETURNING delivery_id INTO delivery;
     40   IF due IS NULL THEN
     41     -- TALER_DBEVENT_MERCHANT_REPORT_UPDATE, delivered at commit.
     42     NOTIFY XSSAB8NCBQR1K2VK7H2M6SMY3V5TNJT1C3BW0SN4F2QV0KHR3PRB0;
     43   END IF;
     44   RETURN delivery;
     45 END $$;
     46 
     47 -- NULL means rescan, zero means a legacy internal notification.
     48 CREATE OR REPLACE FUNCTION merchant.claim_report_delivery(
     49   job BIGINT, due BIGINT, next_due BIGINT, ts BIGINT, timeout_ec INT)
     50 RETURNS BIGINT LANGUAGE plpgsql AS $$
     51 DECLARE
     52   r RECORD;
     53   d RECORD;
     54 BEGIN
     55   SELECT * INTO r FROM merchant_reports WHERE report_serial=job
     56     AND next_transmission=due AND next_transmission<=ts FOR UPDATE;
     57   IF NOT FOUND THEN RETURN NULL; END IF;
     58   IF NOT r.one_shot_hidden THEN
     59     -- Freeze the source as well as the delivery parameters. Editing the
     60     -- recurring schedule cannot change the data behind this job's fetch token.
     61     UPDATE merchant_reports SET next_transmission=next_due WHERE report_serial=job;
     62     IF EXISTS (SELECT FROM merchant_report_deliveries
     63                 WHERE report_serial=job AND due_at=due) THEN
     64       RETURN NULL;
     65     END IF;
     66     PERFORM merchant.queue_report_delivery(job,due,r.report_description,
     67       r.report_program_section,r.data_source,r.target_address,r.mime_type,ts);
     68     RETURN NULL;
     69   END IF;
     70   -- Reserve every one-shot job before the worker calls the delivery program.
     71   -- Internal notifications have no delivery history row.
     72   UPDATE merchant_reports SET next_transmission=next_due WHERE report_serial=job;
     73   SELECT * INTO d FROM merchant_report_deliveries WHERE job_id=job FOR UPDATE;
     74   IF NOT FOUND THEN RETURN 0; END IF;
     75   -- Timestamps are in microseconds. A queued test expires after 24 hours.
     76   IF d.origin='test' AND d.created_at <= ts - 86400000000 THEN
     77     UPDATE merchant_report_deliveries SET job_id=NULL, finished_at=ts,
     78       error_code=timeout_ec, error_detail='Test expired before processing'
     79       WHERE delivery_id=d.delivery_id;
     80     DELETE FROM merchant_reports WHERE report_serial=job;
     81     RETURN NULL;
     82   END IF;
     83   UPDATE merchant_report_deliveries SET started_at=ts,worker_pid=pg_backend_pid(),
     84     worker_start=(SELECT backend_start FROM pg_stat_activity WHERE pid=pg_backend_pid())
     85     WHERE delivery_id=d.delivery_id;
     86   RETURN d.delivery_id;
     87 END $$;
     88 
     89 CREATE OR REPLACE FUNCTION merchant.finish_report_delivery(
     90   delivery BIGINT, ts BIGINT, ec INT, detail TEXT)
     91 RETURNS VOID LANGUAGE plpgsql AS $$
     92 DECLARE
     93   d RECORD;
     94 BEGIN
     95   -- Serialize concurrent completions before choosing the latest schedule
     96   -- error. A test never writes or locks its parent schedule.
     97   PERFORM FROM merchant_reports r JOIN merchant_report_deliveries h
     98     ON r.report_serial=h.report_serial
     99     WHERE h.delivery_id=delivery AND h.origin='scheduled' FOR UPDATE OF r;
    100   UPDATE merchant_report_deliveries SET finished_at=ts,error_code=NULLIF(ec,0),
    101     error_detail=detail WHERE delivery_id=delivery AND finished_at IS NULL
    102     RETURNING * INTO d;
    103   IF NOT FOUND THEN RETURN; END IF;
    104   DELETE FROM merchant_reports WHERE report_serial=d.job_id;
    105   UPDATE merchant_report_deliveries SET job_id=NULL WHERE delivery_id=delivery;
    106   IF d.origin='scheduled' THEN
    107     UPDATE merchant_reports SET last_error_code=NULLIF(ec,0),last_error_detail=detail
    108       WHERE report_serial=d.report_serial AND NOT EXISTS
    109         (SELECT FROM merchant_report_deliveries WHERE report_serial=d.report_serial
    110           AND due_at>d.due_at AND finished_at IS NOT NULL);
    111   END IF;
    112 END $$;
    113 
    114 CREATE OR REPLACE FUNCTION merchant.read_report_deliveries(
    115   wanted BIGINT, schedule_id BIGINT, cursor_id BIGINT, page_size BIGINT, ts BIGINT,
    116   timeout_ec INT)
    117 RETURNS JSON LANGUAGE plpgsql STABLE AS $$
    118 DECLARE
    119   queued_test_cutoff CONSTANT BIGINT := ts - 86400000000; -- 24 hours in microseconds
    120   history_cutoff CONSTANT BIGINT := ts - 2592000000000; -- 30 days in microseconds
    121 BEGIN
    122   RETURN (
    123   SELECT COALESCE(json_agg(json_strip_nulls(json_build_object(
    124     'delivery_id',delivery_id,'report_serial',report_serial,'origin',origin,
    125     'description',description,'program_section',program_section,'data_source',data_source,
    126     'target_address',target_address,'mime_type',mime_type,
    127     'created_at',json_build_object('t_s',created_at/1000000),
    128     'started_at',CASE WHEN started_at IS NOT NULL THEN json_build_object('t_s',started_at/1000000) END,
    129     'finished_at',CASE WHEN finished_at IS NOT NULL THEN json_build_object('t_s',finished_at/1000000) END,
    130     'status',CASE WHEN error_code IS NOT NULL OR expired THEN 'failed'
    131                   WHEN finished_at IS NOT NULL THEN 'succeeded'
    132                   WHEN started_at IS NULL THEN 'queued'
    133                   WHEN EXISTS (SELECT FROM pg_stat_activity a WHERE a.pid=worker_pid
    134                                 AND a.backend_start=worker_start) THEN 'processing'
    135                   ELSE 'unknown' END,
    136     'error_code',CASE WHEN expired THEN timeout_ec ELSE error_code END,
    137     'error_detail',CASE WHEN expired THEN 'Test expired before processing' ELSE error_detail END
    138   )) ORDER BY CASE WHEN page_size<0 THEN delivery_id END DESC,
    139              CASE WHEN page_size>=0 THEN delivery_id END ASC),'[]'::JSON)
    140   FROM (SELECT *, (origin='test' AND started_at IS NULL AND finished_at IS NULL
    141                     AND created_at <= queued_test_cutoff) AS expired
    142         FROM merchant_report_deliveries
    143         WHERE created_at > history_cutoff AND (wanted=0 OR delivery_id=wanted)
    144           AND (schedule_id=0 OR report_serial=schedule_id)
    145           AND CASE WHEN page_size<0 THEN delivery_id<cursor_id ELSE delivery_id>cursor_id END
    146         ORDER BY CASE WHEN page_size<0 THEN delivery_id END DESC,
    147              CASE WHEN page_size>=0 THEN delivery_id END ASC
    148         LIMIT abs(page_size)) d);
    149 END
    150 $$;