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