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 $$;