pg_statistics_helpers.sql (22426B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 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 17 18 DROP PROCEDURE IF EXISTS merchant_do_bump_number_bucket_stat; 19 CREATE OR REPLACE PROCEDURE merchant_do_bump_number_bucket_stat( 20 in_slug TEXT, 21 in_timestamp TIMESTAMP, 22 in_delta INT8 23 ) 24 LANGUAGE plpgsql 25 AS $$ 26 DECLARE 27 my_meta INT8; 28 my_range merchant.statistic_range; 29 my_bucket_start INT8; 30 my_curs CURSOR (arg_slug TEXT) 31 FOR SELECT UNNEST(ranges) 32 FROM merchant_statistic_bucket_meta 33 WHERE slug=arg_slug 34 AND stype='number'; -- a slug may be registered for both types 35 BEGIN 36 SELECT bmeta_serial_id 37 INTO my_meta 38 FROM merchant_statistic_bucket_meta 39 WHERE slug=in_slug 40 AND stype='number'; 41 IF NOT FOUND 42 THEN 43 RETURN; 44 END IF; 45 OPEN my_curs (arg_slug:=in_slug); 46 LOOP 47 FETCH NEXT 48 FROM my_curs 49 INTO my_range; 50 EXIT WHEN NOT FOUND; 51 SELECT * 52 INTO my_bucket_start 53 FROM merchant.interval_to_start (in_timestamp, my_range); 54 55 INSERT INTO merchant_statistic_bucket_counter AS msbc 56 (bmeta_serial_id 57 ,bucket_start 58 ,bucket_range 59 ,cumulative_number 60 ) VALUES ( 61 my_meta 62 ,my_bucket_start 63 ,my_range 64 ,in_delta) 65 ON CONFLICT (bmeta_serial_id, bucket_start, bucket_range) 66 DO UPDATE SET 67 cumulative_number = msbc.cumulative_number + in_delta; 68 END LOOP; 69 CLOSE my_curs; 70 END $$; 71 72 73 DROP PROCEDURE IF EXISTS merchant_do_bump_amount_bucket_stat; 74 CREATE OR REPLACE PROCEDURE merchant_do_bump_amount_bucket_stat( 75 in_slug TEXT, 76 in_timestamp TIMESTAMP, 77 in_delta merchant.taler_amount_currency 78 ) 79 LANGUAGE plpgsql 80 AS $$ 81 DECLARE 82 my_meta INT8; 83 my_range merchant.statistic_range; 84 my_bucket_start INT8; 85 my_curs CURSOR (arg_slug TEXT) 86 FOR SELECT UNNEST(ranges) 87 FROM merchant_statistic_bucket_meta 88 WHERE slug=arg_slug 89 AND stype='amount'; -- a slug may be registered for both types 90 BEGIN 91 SELECT bmeta_serial_id 92 INTO my_meta 93 FROM merchant_statistic_bucket_meta 94 WHERE slug=in_slug 95 AND stype='amount'; 96 IF NOT FOUND 97 THEN 98 RETURN; 99 END IF; 100 OPEN my_curs (arg_slug:=in_slug); 101 LOOP 102 FETCH NEXT 103 FROM my_curs 104 INTO my_range; 105 EXIT WHEN NOT FOUND; 106 SELECT * 107 INTO my_bucket_start 108 FROM merchant.interval_to_start (in_timestamp, my_range); 109 110 INSERT INTO merchant_statistic_bucket_amount AS msba 111 (bmeta_serial_id 112 ,bucket_start 113 ,bucket_range 114 ,curr 115 ,cumulative_value 116 ,cumulative_frac 117 ) VALUES ( 118 my_meta 119 ,my_bucket_start 120 ,my_range 121 ,(in_delta).curr 122 ,(in_delta).val 123 ,(in_delta).frac) 124 ON CONFLICT (bmeta_serial_id, curr, bucket_start, bucket_range) 125 DO UPDATE SET 126 cumulative_value = msba.cumulative_value + (in_delta).val 127 + CASE 128 WHEN (in_delta).frac + msba.cumulative_frac >= 100000000 129 THEN 1 130 ELSE 0 131 END, 132 cumulative_frac = msba.cumulative_frac + (in_delta).frac 133 - CASE 134 WHEN (in_delta).frac + msba.cumulative_frac >= 100000000 135 THEN 100000000 136 ELSE 0 137 END; 138 END LOOP; 139 CLOSE my_curs; 140 END $$; 141 142 COMMENT ON PROCEDURE merchant_do_bump_amount_bucket_stat 143 IS 'Updates an amount statistic tracked over buckets'; 144 145 146 DROP PROCEDURE IF EXISTS merchant_do_bump_number_interval_stat; 147 CREATE OR REPLACE PROCEDURE merchant_do_bump_number_interval_stat( 148 in_slug TEXT, 149 in_timestamp TIMESTAMP, 150 in_delta INT8 151 ) 152 LANGUAGE plpgsql 153 AS $$ 154 DECLARE 155 my_now INT8; 156 my_record RECORD; 157 my_meta INT8; 158 my_ranges INT8[]; 159 my_precisions INT8[]; 160 my_rangex INT8; 161 my_precisionx INT8; 162 my_start INT8; 163 my_event INT8; 164 BEGIN 165 my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000; 166 SELECT imeta_serial_id 167 ,ranges AS ranges 168 ,precisions AS precisions 169 INTO my_record 170 FROM merchant_statistic_interval_meta 171 WHERE slug=in_slug 172 AND stype='number'; 173 IF NOT FOUND 174 THEN 175 RETURN; 176 END IF; 177 178 my_start = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8 / 1000 / 1000; -- convert to seconds 179 my_precisions = my_record.precisions; 180 my_ranges = my_record.ranges; 181 my_rangex = NULL; 182 FOR my_x IN 1..COALESCE(array_length(my_ranges,1),0) 183 LOOP 184 IF my_now - my_ranges[my_x] < my_start 185 THEN 186 my_rangex = my_ranges[my_x]; 187 my_precisionx = my_precisions[my_x]; 188 EXIT; 189 END IF; 190 END LOOP; 191 IF my_rangex IS NULL 192 THEN 193 -- event is beyond the ranges we care about 194 RETURN; 195 END IF; 196 197 my_meta = my_record.imeta_serial_id; 198 my_start = my_start - my_start % my_precisionx; -- round down 199 200 INSERT INTO merchant_statistic_counter_event AS msce 201 (imeta_serial_id 202 ,slot 203 ,delta) 204 VALUES 205 (my_meta 206 ,my_start 207 ,in_delta) 208 ON CONFLICT (imeta_serial_id, slot) 209 DO UPDATE SET 210 delta = msce.delta + in_delta 211 RETURNING nevent_serial_id 212 INTO my_event; 213 214 INSERT INTO merchant_statistic_interval_counter AS msic 215 (imeta_serial_id 216 ,range 217 ,event_delimiter 218 ,cumulative_number 219 ) VALUES ( 220 my_meta 221 ,my_rangex 222 ,my_event 223 ,in_delta) 224 ON CONFLICT (imeta_serial_id, range) 225 DO UPDATE SET 226 cumulative_number = msic.cumulative_number + in_delta; 227 END $$; 228 229 COMMENT ON PROCEDURE merchant_do_bump_number_interval_stat 230 IS 'Updates a numeric statistic tracked over an interval'; 231 232 233 DROP PROCEDURE IF EXISTS merchant_do_bump_amount_interval_stat; 234 CREATE OR REPLACE PROCEDURE merchant_do_bump_amount_interval_stat( 235 in_slug TEXT, 236 in_timestamp TIMESTAMP, 237 in_delta merchant.taler_amount_currency -- new amount in table that we should add to the tracker 238 ) 239 LANGUAGE plpgsql 240 AS $$ 241 DECLARE 242 my_now INT8; 243 my_record RECORD; 244 my_meta INT8; 245 my_ranges INT8[]; 246 my_precisions INT8[]; 247 my_x INT; 248 my_rangex INT8; 249 my_precisionx INT8; 250 my_start INT8; 251 my_event INT8; 252 BEGIN 253 my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000; 254 SELECT imeta_serial_id 255 ,ranges 256 ,precisions 257 INTO my_record 258 FROM merchant_statistic_interval_meta 259 WHERE slug=in_slug 260 AND stype='amount'; 261 IF NOT FOUND 262 THEN 263 RETURN; 264 END IF; 265 266 my_start = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8 / 1000 / 1000; -- convert to seconds since epoch 267 my_precisions = my_record.precisions; 268 my_ranges = my_record.ranges; 269 my_rangex = NULL; 270 FOR my_x IN 1..COALESCE(array_length(my_ranges,1),0) 271 LOOP 272 IF my_now - my_ranges[my_x] < my_start 273 THEN 274 my_rangex = my_ranges[my_x]; 275 my_precisionx = my_precisions[my_x]; 276 EXIT; 277 END IF; 278 END LOOP; 279 IF my_rangex IS NULL 280 THEN 281 -- event is beyond the ranges we care about 282 RETURN; 283 END IF; 284 my_start = my_start - my_start % my_precisionx; -- round down 285 my_meta = my_record.imeta_serial_id; 286 287 INSERT INTO merchant_statistic_amount_event AS msae 288 (imeta_serial_id 289 ,slot 290 ,delta_curr 291 ,delta_value 292 ,delta_frac 293 ) VALUES ( 294 my_meta 295 ,my_start 296 ,(in_delta).curr 297 ,(in_delta).val 298 ,(in_delta).frac 299 ) 300 ON CONFLICT (imeta_serial_id, slot, delta_curr) 301 DO UPDATE SET 302 delta_value = msae.delta_value + (in_delta).val 303 + CASE 304 WHEN (in_delta).frac + msae.delta_frac >= 100000000 305 THEN 1 306 ELSE 0 307 END, 308 delta_frac = msae.delta_frac + (in_delta).frac 309 - CASE 310 WHEN (in_delta).frac + msae.delta_frac >= 100000000 311 THEN 100000000 312 ELSE 0 313 END 314 RETURNING aevent_serial_id 315 INTO my_event; 316 317 INSERT INTO merchant_statistic_interval_amount AS msia 318 (imeta_serial_id 319 ,range 320 ,event_delimiter 321 ,curr 322 ,cumulative_value 323 ,cumulative_frac 324 ) VALUES ( 325 my_meta 326 ,my_rangex 327 ,my_event 328 ,(in_delta).curr 329 ,(in_delta).val 330 ,(in_delta).frac) 331 ON CONFLICT (imeta_serial_id, curr, range) 332 DO UPDATE SET 333 cumulative_value = msia.cumulative_value + (in_delta).val 334 + CASE 335 WHEN (in_delta).frac + msia.cumulative_frac >= 100000000 336 THEN 1 337 ELSE 0 338 END, 339 cumulative_frac = msia.cumulative_frac + (in_delta).frac 340 - CASE 341 WHEN (in_delta).frac + msia.cumulative_frac >= 100000000 342 THEN 100000000 343 ELSE 0 344 END; 345 END $$; 346 COMMENT ON PROCEDURE merchant_do_bump_amount_interval_stat 347 IS 'Updates an amount statistic tracked over an interval'; 348 349 350 DROP PROCEDURE IF EXISTS merchant_do_bump_number_stat; 351 CREATE OR REPLACE PROCEDURE merchant_do_bump_number_stat( 352 in_slug TEXT, 353 in_timestamp TIMESTAMP, 354 in_delta INT8 355 ) 356 LANGUAGE plpgsql 357 AS $$ 358 BEGIN 359 -- Complain loudly about statistics that nobody ever registered: both 360 -- subroutines below silently do nothing for an unknown slug, so a typo 361 -- in a trigger would otherwise never be noticed. 362 PERFORM 363 FROM (SELECT 1 364 FROM merchant_statistic_bucket_meta 365 WHERE slug=in_slug 366 AND stype='number' 367 UNION ALL 368 SELECT 1 369 FROM merchant_statistic_interval_meta 370 WHERE slug=in_slug 371 AND stype='number') AS known; 372 IF NOT FOUND 373 THEN 374 RAISE WARNING 'Numeric statistic "%" is not registered, event dropped', in_slug; 375 END IF; 376 CALL merchant_do_bump_number_bucket_stat (in_slug, in_timestamp, in_delta); 377 CALL merchant_do_bump_number_interval_stat (in_slug, in_timestamp, in_delta); 378 END $$; 379 COMMENT ON PROCEDURE merchant_do_bump_number_stat 380 IS 'Updates a numeric statistic (bucket or interval)'; 381 382 383 DROP PROCEDURE IF EXISTS merchant_do_bump_amount_stat; 384 CREATE OR REPLACE PROCEDURE merchant_do_bump_amount_stat( 385 in_slug TEXT, 386 in_timestamp TIMESTAMP, 387 in_delta merchant.taler_amount_currency 388 ) 389 LANGUAGE plpgsql 390 AS $$ 391 BEGIN 392 -- Complain loudly about statistics that nobody ever registered: both 393 -- subroutines below silently do nothing for an unknown slug, so a typo 394 -- in a trigger would otherwise never be noticed. 395 PERFORM 396 FROM (SELECT 1 397 FROM merchant_statistic_bucket_meta 398 WHERE slug=in_slug 399 AND stype='amount' 400 UNION ALL 401 SELECT 1 402 FROM merchant_statistic_interval_meta 403 WHERE slug=in_slug 404 AND stype='amount') AS known; 405 IF NOT FOUND 406 THEN 407 RAISE WARNING 'Amount statistic "%" is not registered, event dropped', in_slug; 408 END IF; 409 CALL merchant_do_bump_amount_bucket_stat (in_slug, in_timestamp, in_delta); 410 CALL merchant_do_bump_amount_interval_stat (in_slug, in_timestamp, in_delta); 411 END $$; 412 COMMENT ON PROCEDURE merchant_do_bump_amount_stat 413 IS 'Updates an amount statistic (bucket or interval)'; 414 415 416 417 DROP PROCEDURE IF EXISTS merchant_statistic_counter_gc; 418 CREATE OR REPLACE PROCEDURE merchant_statistic_counter_gc () 419 LANGUAGE plpgsql 420 AS $$ 421 DECLARE 422 my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000; 423 my_rec RECORD; 424 my_sum RECORD; 425 my_meta INT8; 426 my_ranges INT8[]; 427 my_precisions INT8[]; 428 my_precision INT4; 429 my_i INT4; 430 min_slot INT8; 431 max_slot INT8; 432 end_slot INT8; 433 my_total INT8; 434 BEGIN 435 -- Do combination work for all numeric statistic events 436 FOR my_rec IN 437 SELECT imeta_serial_id 438 ,ranges 439 ,precisions 440 ,slug 441 FROM merchant_statistic_interval_meta 442 LOOP 443 PERFORM FROM merchant_statistic_interval_number_get (my_rec.slug); 444 445 my_meta = my_rec.imeta_serial_id; 446 my_ranges = my_rec.ranges; 447 my_precisions = my_rec.precisions; 448 449 FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0) 450 LOOP 451 my_precision = my_precisions[my_i]; 452 IF 1 >= my_precision 453 THEN 454 -- Cannot coarsen in this case 455 CONTINUE; 456 END IF; 457 458 IF 1 = my_i 459 THEN 460 min_slot = 0; 461 ELSE 462 min_slot = my_ranges[my_i - 1]; 463 END IF; 464 end_slot = my_ranges[my_i]; 465 RAISE NOTICE 'Coarsening from [%,%) at %', my_time - end_slot, my_time - min_slot, my_precision; 466 467 LOOP 468 EXIT WHEN min_slot >= end_slot; 469 max_slot = min_slot + my_precision; 470 SELECT SUM(delta) AS total, 471 COUNT(*) AS matches, 472 MIN(nevent_serial_id) AS rep_serial_id 473 INTO my_sum 474 FROM merchant_statistic_counter_event 475 WHERE imeta_serial_id=my_meta 476 AND slot >= my_time - max_slot 477 AND slot < my_time - min_slot; 478 479 RAISE NOTICE 'Found % entries between [%,%)', my_sum.matches, my_time - max_slot, my_time - min_slot; 480 -- we only proceed if we had more then one match (optimization) 481 IF FOUND AND my_sum.matches > 1 482 THEN 483 my_total = my_sum.total; 484 485 RAISE NOTICE 'combining % entries to representative % for slots [%-%)', my_sum.matches, my_sum.rep_serial_id, my_time - max_slot, my_time - min_slot; 486 487 -- The representative absorbs the deltas of all entries we are 488 -- about to remove, so any interval counter still using one of 489 -- them as its watermark must be moved onto the representative. 490 -- Without this, event_delimiter's ON DELETE RESTRICT aborts the 491 -- entire garbage collection run. 492 UPDATE merchant_statistic_interval_counter 493 SET event_delimiter = my_sum.rep_serial_id 494 WHERE imeta_serial_id = my_meta 495 AND event_delimiter IN 496 (SELECT nevent_serial_id 497 FROM merchant_statistic_counter_event 498 WHERE imeta_serial_id=my_meta 499 AND slot >= my_time - max_slot 500 AND slot < my_time - min_slot 501 AND nevent_serial_id > my_sum.rep_serial_id); 502 503 -- combine entries 504 DELETE FROM merchant_statistic_counter_event 505 WHERE imeta_serial_id=my_meta 506 AND slot >= my_time - max_slot 507 AND slot < my_time - min_slot 508 AND nevent_serial_id > my_sum.rep_serial_id; 509 -- Now update the representative to the sum 510 UPDATE merchant_statistic_counter_event SET 511 delta = my_total 512 WHERE imeta_serial_id = my_meta 513 AND nevent_serial_id = my_sum.rep_serial_id; 514 END IF; 515 min_slot = min_slot + my_precision; 516 END LOOP; -- min_slot to end_slot by precision loop 517 END LOOP; -- my_i loop 518 -- Finally, delete all events beyond the range we care about 519 520 RAISE NOTICE 'deleting entries of % before % - % = %', my_meta, my_time, my_ranges[array_length(my_ranges,1)], my_time - my_ranges[array_length(my_ranges,1)]; 521 DELETE FROM merchant_statistic_counter_event 522 WHERE imeta_serial_id=my_meta 523 AND slot < my_time - my_ranges[array_length(my_ranges,1)]; 524 END LOOP; -- my_rec loop 525 END $$; 526 COMMENT ON PROCEDURE merchant_statistic_counter_gc 527 IS 'Performs garbage collection and compaction of the merchant_statistic_counter_event table'; 528 529 530 531 DROP PROCEDURE IF EXISTS merchant_statistic_amount_gc; 532 CREATE OR REPLACE PROCEDURE merchant_statistic_amount_gc () 533 LANGUAGE plpgsql 534 AS $$ 535 DECLARE 536 my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000; 537 my_rec RECORD; 538 my_sum RECORD; 539 my_meta INT8; 540 my_ranges INT8[]; 541 my_precisions INT8[]; 542 my_precision INT4; 543 my_currency TEXT; 544 my_i INT4; 545 min_slot INT8; 546 max_slot INT8; 547 end_slot INT8; 548 my_total_val INT8; 549 my_total_frac INT8; 550 BEGIN 551 -- Do combination work for all numeric statistic events 552 FOR my_rec IN 553 SELECT imeta_serial_id 554 ,ranges 555 ,precisions 556 ,slug 557 FROM merchant_statistic_interval_meta 558 LOOP 559 560 PERFORM FROM merchant_statistic_interval_amount_get (my_rec.slug); 561 562 my_meta = my_rec.imeta_serial_id; 563 my_ranges = my_rec.ranges; 564 my_precisions = my_rec.precisions; 565 FOR my_currency IN 566 SELECT DISTINCT delta_curr 567 FROM merchant_statistic_amount_event 568 WHERE imeta_serial_id = my_meta 569 LOOP 570 571 FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0) 572 LOOP 573 my_precision = my_precisions[my_i]; 574 IF 1 >= my_precision 575 THEN 576 -- Cannot coarsen in this case 577 CONTINUE; 578 END IF; 579 580 IF 1 = my_i 581 THEN 582 min_slot = 0; 583 ELSE 584 min_slot = my_ranges[my_i - 1]; 585 END IF; 586 end_slot = my_ranges[my_i]; 587 588 RAISE NOTICE 'Coarsening from [%,%) at %', my_time - end_slot, my_time - min_slot, my_precision; 589 LOOP 590 EXIT WHEN min_slot >= end_slot; 591 max_slot = min_slot + my_precision; 592 SELECT SUM(delta_value) AS total_val, 593 SUM(delta_frac) AS total_frac, 594 COUNT(*) AS matches, 595 MIN(aevent_serial_id) AS rep_serial_id 596 INTO my_sum 597 FROM merchant_statistic_amount_event 598 WHERE imeta_serial_id=my_meta 599 AND delta_curr = my_currency 600 AND slot >= my_time - max_slot 601 AND slot < my_time - min_slot; 602 -- we only proceed if we had more then one match (optimization) 603 IF FOUND AND my_sum.matches > 1 604 THEN 605 -- normalize new total 606 my_total_frac = my_sum.total_frac % 100000000; 607 my_total_val = my_sum.total_val + my_sum.total_frac / 100000000; 608 609 -- The representative absorbs the deltas of all entries we are 610 -- about to remove, so any interval amount still using one of 611 -- them as its watermark must be moved onto the representative. 612 -- Without this, event_delimiter's ON DELETE RESTRICT aborts the 613 -- entire garbage collection run. 614 UPDATE merchant_statistic_interval_amount 615 SET event_delimiter = my_sum.rep_serial_id 616 WHERE imeta_serial_id = my_meta 617 AND event_delimiter IN 618 (SELECT aevent_serial_id 619 FROM merchant_statistic_amount_event 620 WHERE imeta_serial_id=my_meta 621 AND delta_curr = my_currency 622 AND slot >= my_time - max_slot 623 AND slot < my_time - min_slot 624 AND aevent_serial_id > my_sum.rep_serial_id); 625 626 -- combine entries 627 DELETE FROM merchant_statistic_amount_event 628 WHERE imeta_serial_id=my_meta 629 AND delta_curr = my_currency 630 AND slot >= my_time - max_slot 631 AND slot < my_time - min_slot 632 AND aevent_serial_id > my_sum.rep_serial_id; 633 -- Now update the representative to the sum 634 UPDATE merchant_statistic_amount_event SET 635 delta_value = my_total_val 636 ,delta_frac = my_total_frac 637 WHERE imeta_serial_id = my_meta 638 AND delta_curr = my_currency 639 AND aevent_serial_id = my_sum.rep_serial_id; 640 END IF; 641 min_slot = min_slot + my_precision; 642 END LOOP; -- min_slot to end_slot by precision loop 643 END LOOP; -- my_i loop 644 END LOOP; -- my_currency loop 645 -- Finally, delete all events beyond the range we care about 646 647 RAISE NOTICE 'deleting entries of % before % - % = %', my_meta, my_time, my_ranges[array_length(my_ranges,1)], my_time - my_ranges[array_length(my_ranges,1)]; 648 DELETE FROM merchant_statistic_amount_event 649 WHERE imeta_serial_id=my_meta 650 AND slot < my_time - my_ranges[array_length(my_ranges,1)]; 651 END LOOP; -- my_rec loop 652 END $$; 653 COMMENT ON PROCEDURE merchant_statistic_amount_gc 654 IS 'Performs garbage collection and compaction of the merchant_statistic_amount_event table'; 655 656 657 658 DROP PROCEDURE IF EXISTS merchant_statistic_bucket_gc; 659 CREATE OR REPLACE PROCEDURE merchant_statistic_bucket_gc () 660 LANGUAGE plpgsql 661 AS $$ 662 DECLARE 663 my_rec RECORD; 664 my_range INTERVAL; 665 my_now INT8; 666 my_end INT8; 667 BEGIN 668 my_now = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)::TIMESTAMP); -- seconds since epoch 669 FOR my_rec IN 670 SELECT bmeta_serial_id 671 ,stype 672 ,ranges[array_length(ranges,1)] AS range 673 ,ages[array_length(ages,1)] AS age 674 FROM merchant_statistic_bucket_meta 675 LOOP 676 IF my_rec.range = 'quarter' 677 THEN 678 -- '1 quarter' is not a valid PostgreSQL interval 679 my_range = INTERVAL '3 months'; 680 ELSE 681 my_range = ('1 ' || my_rec.range::TEXT)::INTERVAL; 682 END IF; 683 -- age is given in multiples of the range; EPOCH (and not SECONDS, 684 -- which is merely the seconds *field* of the interval and thus zero 685 -- for all of our ranges) converts that range into seconds 686 my_end = my_now - my_rec.age * EXTRACT(EPOCH FROM my_range); 687 IF my_rec.stype = 'amount' 688 THEN 689 DELETE 690 FROM merchant_statistic_bucket_amount 691 WHERE bmeta_serial_id = my_rec.bmeta_serial_id 692 AND bucket_start < my_end; 693 ELSE 694 DELETE 695 FROM merchant_statistic_bucket_counter 696 WHERE bmeta_serial_id = my_rec.bmeta_serial_id 697 AND bucket_start < my_end; 698 END IF; 699 END LOOP; 700 END $$; 701 COMMENT ON PROCEDURE merchant_statistic_bucket_gc 702 IS 'Performs garbage collection of the merchant_statistic_bucket_counter and merchant_statistic_bucket_amount tables'; 703 704 705 706 -- The date_trunc may not be necessary if we assume it is already truncated 707 DROP FUNCTION IF EXISTS merchant_statistics_bucket_end; 708 CREATE FUNCTION merchant_statistics_bucket_end ( 709 IN in_bucket_start INT8, 710 IN in_range merchant.statistic_range, 711 OUT out_bucket_end INT8 712 ) 713 LANGUAGE plpgsql 714 AS $$ 715 BEGIN 716 IF in_range='quarter' 717 THEN 718 out_bucket_end = EXTRACT(EPOCH FROM CAST(date_trunc('quarter', to_timestamp(in_bucket_start)::date) + interval '3 months' AS date)); 719 ELSE 720 out_bucket_end = EXTRACT(EPOCH FROM CAST(to_timestamp(in_bucket_start)::date + ('1 ' || in_range)::interval AS date)); 721 END IF; 722 END $$; 723 COMMENT ON FUNCTION merchant_statistics_bucket_end 724 IS 'computes the end time of the bucket for an event at the current time given the desired bucket range';