magnet-bank-procedures.sql (15143B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025-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 SET search_path TO magnet_bank; 17 18 -- Remove all existing functions 19 DO 20 $do$ 21 DECLARE 22 _sql text; 23 BEGIN 24 SELECT INTO _sql 25 string_agg(format('DROP %s %s CASCADE;' 26 , CASE prokind 27 WHEN 'f' THEN 'FUNCTION' 28 WHEN 'p' THEN 'PROCEDURE' 29 END 30 , oid::regprocedure) 31 , E'\n') 32 FROM pg_proc 33 WHERE pronamespace = 'magnet_bank'::regnamespace; 34 35 IF _sql IS NOT NULL THEN 36 EXECUTE _sql; 37 END IF; 38 END 39 $do$; 40 41 CREATE FUNCTION register_tx_in( 42 IN in_code INT8, 43 IN in_amount taler_amount, 44 IN in_subject TEXT, 45 IN in_debit_account TEXT, 46 IN in_debit_name TEXT, 47 IN in_valued_at INT8, 48 IN in_type incoming_type, 49 IN in_metadata BYTEA, 50 IN in_now INT8, 51 -- Error status 52 OUT out_reserve_pub_reuse BOOLEAN, 53 OUT out_mapping_reuse BOOLEAN, 54 OUT out_unknown_mapping BOOLEAN, 55 -- Success return 56 OUT out_tx_row_id INT8, 57 OUT out_valued_at INT8, 58 OUT out_new BOOLEAN, 59 OUT out_pending BOOLEAN 60 ) 61 LANGUAGE plpgsql AS $$ 62 DECLARE 63 local_authorization_pub BYTEA; 64 local_authorization_sig BYTEA; 65 local_taler_in_id INT8; 66 BEGIN 67 out_pending=false; 68 -- Check for idempotence 69 SELECT tx_in_id, valued_at 70 INTO out_tx_row_id, out_valued_at 71 FROM tx_in 72 WHERE magnet_code = in_code; 73 out_new = NOT found; 74 IF NOT out_new THEN 75 RETURN; 76 END IF; 77 78 -- Resolve mapping logic 79 IF in_type = 'map' THEN 80 SELECT type, account_pub, authorization_pub, authorization_sig, 81 tx_in_id IS NOT NULL AND NOT recurrent, 82 tx_in_id IS NOT NULL AND recurrent 83 INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 84 FROM prepared_in 85 WHERE authorization_pub = in_metadata; 86 out_unknown_mapping = NOT FOUND; 87 IF out_unknown_mapping OR out_mapping_reuse THEN 88 RETURN; 89 END IF; 90 END IF; 91 92 -- Check conflict 93 out_reserve_pub_reuse=NOT out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE metadata = in_metadata AND type = 'reserve'); 94 IF out_reserve_pub_reuse THEN 95 RETURN; 96 END IF; 97 98 -- Insert new incoming transaction 99 out_valued_at = in_valued_at; 100 -- Lock for revenue id commit ordering 101 LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE; 102 INSERT INTO tx_in ( 103 magnet_code, 104 amount, 105 subject, 106 debit_account, 107 debit_name, 108 valued_at, 109 registered_at 110 ) VALUES ( 111 in_code, 112 in_amount, 113 in_subject, 114 in_debit_account, 115 in_debit_name, 116 in_valued_at, 117 in_now 118 ) 119 RETURNING tx_in_id INTO out_tx_row_id; 120 -- Notify new incoming transaction registration 121 PERFORM pg_notify('tx_in', out_tx_row_id || ''); 122 123 IF out_pending THEN 124 -- Delay talerable registration until mapping again 125 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 126 VALUES (out_tx_row_id, local_authorization_pub); 127 ELSIF in_type IS NOT NULL THEN 128 UPDATE prepared_in 129 SET tx_in_id = out_tx_row_id 130 WHERE ( 131 tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve' 132 ) OR authorization_pub = local_authorization_pub; 133 -- Lock for incoming history id commit ordering 134 LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE; 135 -- Insert new incoming talerable transaction 136 INSERT INTO taler_in ( 137 tx_in_id, 138 type, 139 metadata, 140 authorization_pub, 141 authorization_sig 142 ) VALUES ( 143 out_tx_row_id, 144 in_type, 145 in_metadata, 146 local_authorization_pub, 147 local_authorization_sig 148 ) RETURNING taler_in_id INTO local_taler_in_id; 149 -- Notify new incoming talerable transaction registration 150 PERFORM pg_notify('taler_in', local_taler_in_id::text); 151 END IF; 152 END $$; 153 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently'; 154 155 CREATE FUNCTION register_tx_out( 156 IN in_code INT8, 157 IN in_amount taler_amount, 158 IN in_subject TEXT, 159 IN in_credit_account TEXT, 160 IN in_credit_name TEXT, 161 IN in_valued_at INT8, 162 IN in_wtid BYTEA, 163 IN in_origin_exchange_url TEXT, 164 IN in_metadata TEXT, 165 IN in_bounced INT8, 166 IN in_now INT8, 167 -- Success return 168 OUT out_tx_row_id INT8, 169 OUT out_result register_result 170 ) 171 LANGUAGE plpgsql AS $$ 172 BEGIN 173 -- Check for idempotence 174 SELECT tx_out_id INTO out_tx_row_id 175 FROM tx_out WHERE magnet_code = in_code; 176 177 IF FOUND THEN 178 out_result = 'idempotent'; 179 RETURN; 180 END IF; 181 182 -- Lock for revenue and incoming history id commit ordering 183 LOCK TABLE tx_out IN SHARE ROW EXCLUSIVE MODE; 184 -- Insert new outgoing transaction 185 INSERT INTO tx_out ( 186 magnet_code, 187 amount, 188 subject, 189 credit_account, 190 credit_name, 191 valued_at, 192 registered_at 193 ) VALUES ( 194 in_code, 195 in_amount, 196 in_subject, 197 in_credit_account, 198 in_credit_name, 199 in_valued_at, 200 in_now 201 ) 202 RETURNING tx_out_id INTO out_tx_row_id; 203 -- Notify new outgoing transaction registration 204 PERFORM pg_notify('tx_out', out_tx_row_id || ''); 205 206 -- Update initiated status 207 UPDATE initiated 208 SET 209 tx_out_id = out_tx_row_id, 210 status = 'success', 211 status_msg = NULL 212 WHERE magnet_code = in_code; 213 IF FOUND THEN 214 out_result = 'known'; 215 ELSE 216 out_result = 'recovered'; 217 END IF; 218 219 IF in_wtid IS NOT NULL THEN 220 -- Insert new outgoing talerable transaction 221 INSERT INTO taler_out ( 222 tx_out_id, 223 wtid, 224 exchange_base_url, 225 metadata 226 ) VALUES ( 227 out_tx_row_id, 228 in_wtid, 229 in_origin_exchange_url, 230 in_metadata 231 ) ON CONFLICT (wtid) DO NOTHING; 232 IF FOUND THEN 233 -- Notify new outgoing talerable transaction registration 234 PERFORM pg_notify('taler_out', out_tx_row_id || ''); 235 END IF; 236 ELSIF in_bounced IS NOT NULL THEN 237 UPDATE initiated 238 SET 239 tx_out_id = out_tx_row_id, 240 status = 'success', 241 status_msg = NULL 242 FROM bounced JOIN tx_in USING (tx_in_id) 243 WHERE initiated.initiated_id = bounced.initiated_id AND tx_in.magnet_code = in_bounced; 244 END IF; 245 END $$; 246 COMMENT ON FUNCTION register_tx_out IS 'Register an outgoing transaction idempotently'; 247 248 CREATE FUNCTION register_tx_out_failure( 249 IN in_code INT8, 250 IN in_bounced INT8, 251 IN in_now INT8, 252 -- Success return 253 OUT out_initiated_id INT8, 254 OUT out_new BOOLEAN 255 ) 256 LANGUAGE plpgsql AS $$ 257 DECLARE 258 current_status transfer_status; 259 BEGIN 260 -- Found existing initiated transaction or bounced transaction 261 SELECT status, initiated_id 262 INTO current_status, out_initiated_id 263 FROM initiated 264 LEFT JOIN bounced USING (initiated_id) 265 LEFT JOIN tx_in USING (tx_in_id) 266 WHERE initiated.magnet_code = in_code OR tx_in.magnet_code = in_bounced; 267 268 -- Update status if new 269 out_new = FOUND AND current_status != 'permanent_failure'; 270 IF out_new THEN 271 UPDATE initiated 272 SET 273 status = 'permanent_failure', 274 status_msg = NULL 275 WHERE initiated_id = out_initiated_id; 276 END IF; 277 END $$; 278 COMMENT ON FUNCTION register_tx_out_failure IS 'Register an outgoing transaction failure idempotently'; 279 280 CREATE FUNCTION taler_transfer( 281 IN in_request_uid BYTEA, 282 IN in_wtid BYTEA, 283 IN in_subject TEXT, 284 IN in_amount taler_amount, 285 IN in_exchange_base_url TEXT, 286 IN in_metadata TEXT, 287 IN in_credit_account TEXT, 288 IN in_credit_name TEXT, 289 IN in_now INT8, 290 -- Error return 291 OUT out_request_uid_reuse BOOLEAN, 292 OUT out_wtid_reuse BOOLEAN, 293 -- Success return 294 OUT out_initiated_row_id INT8, 295 OUT out_initiated_at INT8 296 ) 297 LANGUAGE plpgsql AS $$ 298 BEGIN 299 -- Check for idempotence and conflict 300 SELECT (amount != in_amount 301 OR credit_account != in_credit_account 302 OR exchange_base_url != in_exchange_base_url 303 OR wtid != in_wtid 304 OR metadata IS DISTINCT FROM in_metadata) 305 ,initiated_id, initiated_at 306 INTO out_request_uid_reuse, out_initiated_row_id, out_initiated_at 307 FROM transfer JOIN initiated USING (initiated_id) 308 WHERE request_uid = in_request_uid; 309 IF FOUND THEN 310 RETURN; 311 END IF; 312 -- Check for wtid reuse 313 out_wtid_reuse = EXISTS(SELECT FROM transfer WHERE wtid=in_wtid); 314 IF out_wtid_reuse THEN 315 RETURN; 316 END IF; 317 -- Insert an initiated outgoing transaction 318 out_initiated_at = in_now; 319 INSERT INTO initiated ( 320 amount, 321 subject, 322 credit_account, 323 credit_name, 324 initiated_at 325 ) VALUES ( 326 in_amount, 327 in_subject, 328 in_credit_account, 329 in_credit_name, 330 in_now 331 ) RETURNING initiated_id 332 INTO out_initiated_row_id; 333 -- Insert a transfer operation 334 INSERT INTO transfer ( 335 initiated_id, 336 request_uid, 337 wtid, 338 exchange_base_url, 339 metadata 340 ) VALUES ( 341 out_initiated_row_id, 342 in_request_uid, 343 in_wtid, 344 in_exchange_base_url, 345 in_metadata 346 ); 347 PERFORM pg_notify('transfer', out_initiated_row_id || ''); 348 END $$; 349 350 CREATE FUNCTION initiated_status_update( 351 IN in_initiated_id INT8, 352 IN in_status transfer_status, 353 IN in_status_msg TEXT 354 ) 355 RETURNS void 356 LANGUAGE plpgsql AS $$ 357 DECLARE 358 current_status transfer_status; 359 BEGIN 360 -- Check current status 361 SELECT status INTO current_status FROM initiated 362 WHERE initiated_id = in_initiated_id; 363 IF FOUND THEN 364 -- Update unsettled transaction status 365 IF current_status = 'success' AND in_status = 'permanent_failure' THEN 366 UPDATE initiated 367 SET status = 'late_failure', status_msg = in_status_msg 368 WHERE initiated_id = in_initiated_id; 369 ELSIF current_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN 370 UPDATE initiated 371 SET status = in_status, status_msg = in_status_msg 372 WHERE initiated_id = in_initiated_id; 373 END IF; 374 END IF; 375 END $$; 376 377 CREATE FUNCTION register_bounce_tx_in( 378 IN in_code INT8, 379 IN in_amount taler_amount, 380 IN in_subject TEXT, 381 IN in_debit_account TEXT, 382 IN in_debit_name TEXT, 383 IN in_valued_at INT8, 384 IN in_reason TEXT, 385 IN in_now INT8, 386 -- Success return 387 OUT out_tx_row_id INT8, 388 OUT out_tx_new BOOLEAN, 389 OUT out_bounce_row_id INT8, 390 OUT out_bounce_new BOOLEAN 391 ) 392 LANGUAGE plpgsql AS $$ 393 BEGIN 394 -- Register incoming transaction idempotently 395 SELECT register_tx_in.out_tx_row_id, register_tx_in.out_new 396 INTO out_tx_row_id, out_tx_new 397 FROM register_tx_in(in_code, in_amount, in_subject, in_debit_account, in_debit_name, in_valued_at, NULL, NULL, in_now); 398 399 -- Check if already bounce 400 SELECT initiated_id 401 INTO out_bounce_row_id 402 FROM bounced JOIN initiated USING (initiated_id) 403 WHERE tx_in_id = out_tx_row_id; 404 out_bounce_new=NOT FOUND; 405 -- Else initiate the bounce transaction 406 IF out_bounce_new THEN 407 -- Initiate the bounce transaction 408 INSERT INTO initiated ( 409 amount, 410 subject, 411 credit_account, 412 credit_name, 413 initiated_at 414 ) VALUES ( 415 in_amount, 416 'bounce: ' || in_code, 417 in_debit_account, 418 in_debit_name, 419 in_now 420 ) 421 RETURNING initiated_id INTO out_bounce_row_id; 422 -- Register the bounce 423 INSERT INTO bounced ( 424 tx_in_id, 425 initiated_id, 426 reason 427 ) VALUES ( 428 out_tx_row_id, 429 out_bounce_row_id, 430 in_reason 431 ); 432 END IF; 433 END $$; 434 COMMENT ON FUNCTION register_bounce_tx_in IS 'Register an incoming transaction and bounce it idempotently'; 435 436 CREATE FUNCTION bounce_pending( 437 in_authorization_pub BYTEA, 438 in_timestamp INT8 439 ) 440 RETURNS void 441 LANGUAGE plpgsql AS $$ 442 DECLARE 443 local_tx_id INT8; 444 local_initiated_id INTEGER; 445 BEGIN 446 FOR local_tx_id IN 447 DELETE FROM pending_recurrent_in 448 WHERE authorization_pub = in_authorization_pub 449 RETURNING tx_in_id 450 LOOP 451 INSERT INTO initiated ( 452 amount, 453 subject, 454 credit_account, 455 credit_name, 456 initiated_at 457 ) 458 SELECT 459 amount, 460 CONCAT('bounce: ', magnet_code), 461 debit_account, 462 debit_name, 463 in_timestamp 464 FROM tx_in 465 WHERE tx_in_id = local_tx_id 466 RETURNING initiated_id INTO local_initiated_id; 467 468 INSERT INTO bounced (tx_in_id, initiated_id, reason) 469 VALUES (local_tx_id, local_initiated_id, 'cancelled mapping'); 470 END LOOP; 471 END; 472 $$; 473 474 CREATE FUNCTION register_prepared_transfers ( 475 IN in_type incoming_type, 476 IN in_account_pub BYTEA, 477 IN in_authorization_pub BYTEA, 478 IN in_authorization_sig BYTEA, 479 IN in_recurrent BOOLEAN, 480 IN in_timestamp INT8, 481 -- Error status 482 OUT out_reserve_pub_reuse BOOLEAN 483 ) 484 LANGUAGE plpgsql AS $$ 485 DECLARE 486 talerable_tx INT8; 487 local_taler_in_id INT8; 488 idempotent BOOLEAN; 489 BEGIN 490 491 -- Check idempotency 492 SELECT type = in_type 493 AND account_pub = in_account_pub 494 AND recurrent = in_recurrent 495 INTO idempotent 496 FROM prepared_in 497 WHERE authorization_pub = in_authorization_pub; 498 499 -- Check idempotency and delay garbage collection 500 IF FOUND AND idempotent THEN 501 UPDATE prepared_in 502 SET registered_at=in_timestamp,authorization_sig=in_authorization_sig 503 WHERE authorization_pub=in_authorization_pub; 504 RETURN; 505 END IF; 506 507 -- Check reserve pub reuse 508 out_reserve_pub_reuse=in_type = 'reserve' AND ( 509 EXISTS(SELECT FROM taler_in WHERE metadata = in_account_pub AND type = 'reserve') 510 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 511 ); 512 IF out_reserve_pub_reuse THEN 513 RETURN; 514 END IF; 515 516 IF in_recurrent THEN 517 -- Finalize one pending right now. Lock history only if there is a row to publish. 518 DELETE FROM pending_recurrent_in 519 WHERE tx_in_id = ( 520 SELECT tx_in_id 521 FROM pending_recurrent_in 522 JOIN tx_in USING (tx_in_id) 523 WHERE authorization_pub = in_authorization_pub 524 ORDER BY registered_at ASC 525 LIMIT 1 526 ) 527 RETURNING tx_in_id INTO talerable_tx; 528 IF talerable_tx IS NOT NULL THEN 529 -- Lock for incoming history id commit ordering 530 LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE; 531 INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig) 532 VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig) 533 RETURNING taler_in_id INTO local_taler_in_id; 534 PERFORM pg_notify('taler_in', local_taler_in_id::text); 535 END IF; 536 ELSE 537 -- Bounce all pending 538 PERFORM bounce_pending(in_authorization_pub, in_timestamp); 539 END IF; 540 541 -- Upsert registration 542 INSERT INTO prepared_in ( 543 type, 544 account_pub, 545 authorization_pub, 546 authorization_sig, 547 recurrent, 548 registered_at, 549 tx_in_id 550 ) VALUES ( 551 in_type, 552 in_account_pub, 553 in_authorization_pub, 554 in_authorization_sig, 555 in_recurrent, 556 in_timestamp, 557 talerable_tx 558 ) ON CONFLICT (authorization_pub) 559 DO UPDATE SET 560 type = EXCLUDED.type, 561 account_pub = EXCLUDED.account_pub, 562 recurrent = EXCLUDED.recurrent, 563 registered_at = EXCLUDED.registered_at, 564 tx_in_id = EXCLUDED.tx_in_id, 565 authorization_sig = EXCLUDED.authorization_sig; 566 END $$; 567 568 CREATE FUNCTION delete_prepared_transfers ( 569 IN in_authorization_pub BYTEA, 570 IN in_timestamp INT8, 571 OUT out_found BOOLEAN 572 ) 573 LANGUAGE plpgsql AS $$ 574 BEGIN 575 576 -- Bounce all pending 577 PERFORM bounce_pending(in_authorization_pub, in_timestamp); 578 579 -- Delete registration 580 DELETE FROM prepared_in 581 WHERE authorization_pub = in_authorization_pub; 582 out_found = FOUND; 583 584 END $$;