github.com/cloudberrydb/gpbackup@v1.0.3-0.20240118031043-5410fd45eed6/ci/scale/sql/refdb_ddl.sql (about)

     1  DROP SCHEMA IF EXISTS prod CASCADE;
     2  CREATE SCHEMA prod;
     3  
     4  CREATE TABLE IF NOT EXISTS prod.test_names (
     5      test_id INTEGER,
     6      test_name TEXT,
     7  
     8      CONSTRAINT unique_test_names UNIQUE (test_id, test_name)
     9  );
    10  
    11  INSERT INTO prod.test_names(test_id, test_name)
    12  VALUES
    13      (1, 'gpb_single_data_file_copy_q8'),
    14      (2, 'gpr_single_data_file_copy_q8'),
    15      (3, 'gpb_scale_multi_data_file'),
    16      (4, 'gpr_scale_multi_data_file'),
    17      (5, 'gpb_scale_multi_data_file_zstd'),
    18      (6, 'gpr_scale_multi_data_file_zstd'),
    19      (7, 'gpb_scale_single_data_file'),
    20      (8, 'gpr_scale_single_data_file'),
    21      (9, 'gpb_scale_single_data_file_zstd'),
    22      (10, 'gpr_scale_single_data_file_zstd'),
    23      (11, 'gpb_scale_metadata'),
    24      (12, 'gpr_scale_metadata'),
    25      (13, 'gpb_distr_snap_edit_data'),
    26      (14, 'gpr_distr_snap_edit_data'),
    27      (15, 'gpb_distr_snap_high_conc'),
    28      (16, 'gpr_distr_snap_high_conc')
    29  ;
    30  
    31  CREATE TABLE IF NOT EXISTS prod.test_stats (
    32      test_id integer,
    33      test_name text,
    34      test_runs_included integer,
    35      test_runtime_avg bigint,
    36      test_runtime_var bigint,
    37      test_limit_report bigint,
    38      test_limit_fail bigint,
    39  
    40      CONSTRAINT unique_test_stats UNIQUE (test_id)
    41  );
    42  
    43  CREATE TABLE IF NOT EXISTS prod.test_runs (
    44      test_id integer,
    45      run_timestamp text,
    46      test_runtime bigint,
    47      gpbackup_version text,
    48      gpdb_version text,
    49      was_reported boolean,
    50      was_failed boolean
    51  );
    52  
    53  CREATE OR REPLACE function
    54      prod.summarize_runs(_testname text)
    55  RETURNS
    56      VOID
    57  AS 
    58  $$
    59  BEGIN
    60      DELETE FROM
    61          prod.test_stats ts
    62      WHERE 
    63          ts.test_id in (
    64              SELECT DISTINCT
    65                  tn.test_id 
    66              FROM 
    67                  prod.test_names tn
    68              WHERE
    69                  tn.test_name = _testname
    70          )
    71      ;
    72  
    73      CREATE TEMPORARY TABLE temp_stats AS
    74      SELECT
    75          tn.test_id,
    76          tn.test_name,
    77          count(tr.test_runtime) AS test_runs_included,
    78          avg(tr.test_runtime) AS test_runtime_avg,
    79          variance(tr.test_runtime) AS test_runtime_var
    80      FROM
    81          prod.test_names tn
    82          LEFT JOIN prod.test_runs tr
    83              ON tn.test_id = tr.test_id
    84      WHERE
    85          tr.was_failed = false
    86          AND tr.was_reported = false
    87          AND tn.test_name = _testname
    88          AND tr.test_runtime > 0
    89      GROUP BY
    90          tn.test_id,
    91          tn.test_name
    92      ORDER BY
    93          tn.test_id,
    94          tn.test_name
    95      ;
    96  
    97      INSERT INTO prod.test_stats
    98      SELECT
    99          tmp.test_id,
   100          tmp.test_name,
   101          tmp.test_runs_included,
   102          tmp.test_runtime_avg,
   103          tmp.test_runtime_var,
   104          tmp.test_runtime_avg * 1.1 AS test_limit_report,
   105          tmp.test_runtime_avg * 1.2 AS test_limit_fail
   106      FROM
   107          temp_stats tmp
   108      ;
   109  
   110  END;
   111  $$ 
   112  LANGUAGE 
   113      plpgsql;