bin/refresh_matviews.pl

   1 #!/usr/bin/perl
   2
   3 =head1 NAME
   4
   5 refresh_matviews.pl - run PL/pgSQL functions to do a basic or concurrent refresh of all database materialized views
   6
   7 =head1 DESCRIPTION
   8
   9 refresh_matviews.pl -H [database handle] -D [database name]  -c [to run concurrent refresh] -m [materialized view select]
  10
  11 Options:
  12
  13  -H the database host
  14  -D the database name
  15  -U username
  16  -P password
  17  -c flag; if present, run concurrent refresh
  18  -m materialized view select. can be either 'fullview' or 'stockprop' or 'phenotypes'
  19  -t test mode
  20
  21 All materialized views that are included in the refresh function will be refreshed
  22 If -c is used, the refresh will be done concurrently, a process that takes longer than a standard refresh but that is completed without locking the views.
  23
  24 =head1 AUTHOR
  25
  26 Bryan Ellerbrock <bje24@cornell.edu>
  27 Naama Menda <nm249@cornell.edu>
  28
  29 =cut
  30
  31 use strict;
  32 use warnings;
  33 use DBI;
  34 use Try::Tiny;
  35 use Getopt::Long;
  36
  37 my ( $dbhost, $dbname, $username, $password, $mode, $concurrent, $test);
  38 GetOptions(
  39     'm=s'        => \$mode,
  40     'c'          => \$concurrent,
  41     'P=s'        => \$password,
  42     'U=s'        => \$username,
  43     't'          => \$test,
  44     'dbname|D=s' => \$dbname,
  45     'dbhost|H=s' => \$dbhost,
  46 );
  47
  48
  49 unless ($mode =~ m/^(fullview|stockprop|phenotypes|all_but_genoview)$/ ) { die "Option -m must be fullview, stockprop, phenotypes, or all_but_genoview. -m  = $mode\n"; }
  50
  51 print STDERR "Connecting to database...\n";
  52 my $dsn = 'dbi:Pg:database='.$dbname.";host=".$dbhost.";port=5432";
  53 my $dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1, AutoCommit=>0 });
  54
  55 my $cur_refreshing_q =  "UPDATE public.matviews SET currently_refreshing=?";
  56 if ($mode eq 'stockprop'){
  57     $cur_refreshing_q .= " WHERE mv_name = 'materialized_stockprop'";
  58 }
  59 if ($mode eq 'phenotypes') {
  60     $cur_refreshing_q .= " WHERE mv_name = 'materialized_phenotype_jsonb_table' or mv_name = 'materialized_phenoview' ";
  61 }
  62 if ($mode eq 'all_but_genoview') {
  63     $cur_refreshing_q .= " WHERE mv_name = 'materialized_stockprop' or mv_name = 'materialized_phenoview' or mv_name= 'materialized_phenotype_jsonb_table' ";
  64 }
  65
  66 #set TRUE before the transaction begins
  67 my $state = 'TRUE';
  68 print STDERR "*Setting currently_refreshing = TRUE\n";
  69 my $cur_refreshing_h = $dbh->prepare($cur_refreshing_q);
  70 $cur_refreshing_h->execute($state);
  71 $dbh->commit();
  72
  73 try {
  74     print STDERR "Refreshing materialized views . . ." . localtime() . "\n";
  75     my @mv_names = ();
  76
  77     if ($mode eq 'fullview') {
  78         @mv_names = ('materialized_phenoview','materialized_genoview');
  79     }
  80     if ($mode eq 'stockprop'){
  81        @mv_names = ('materialized_stockprop');
  82     }
  83     if ($mode eq 'phenotypes') {
  84        @mv_names = ("materialized_phenoview", "materialized_phenotype_jsonb_table");
  85     }
  86     if ($mode eq 'all_but_genoview') {
  87        @mv_names = ("materialized_stockprop", "materialized_phenoview", "materialized_phenotype_jsonb_table");
  88     }
  89
  90     my $status = refresh_mvs($dbh, \@mv_names, $concurrent);
  91
  92     #rollback if running in test mode
  93     if ($test) { die ; }
  94 }
  95 catch {
  96     warn "Refresh failed: @_";
  97     if ($test ) { print STDERR "TEST MODE\n" ; }
  98     $dbh->rollback()
  99 }
 100 finally {
 101     if (@_) {
 102         print "The try block died. Rolling back.\n";
 103     } else {
 104         print STDERR "COMMITTING\n";
 105         $dbh->commit();
 106     }
 107     #always set the refreshing status to FALSE at the end
 108     $state = 'FALSE';
 109     my $done_h = $dbh->prepare($cur_refreshing_q);
 110     print STDERR "*Setting currently_refreshing = FALSE \n";
 111     $done_h->execute($state);
 112     $dbh->commit();
 113 };
 114
 115 sub refresh_mvs {
 116     my $dbh = shift;
 117     my $mv_names_ref = shift;
 118     $concurrent = shift;
 119     my $start_q = "UPDATE matviews SET refresh_start = statement_timestamp() where mv_name = ?";
 120     my $end_q =   "UPDATE matviews SET  last_refresh = statement_timestamp() where mv_name = ? ";
 121     my $refresh_q = "REFRESH MATERIALIZED VIEW ";
 122     if ($concurrent) { $refresh_q .= " CONCURRENTLY "; }
 123     my $status;
 124
 125     foreach my $name ( @$mv_names_ref ) {
 126         print STDERR "**Refreshing view $name ". localtime() . " \n";
 127         my $start_h = $dbh->prepare($start_q);
 128         $start_h->execute($name);
 129         print STDERR "**QUERY = " . $refresh_q . $name . "\n";
 130         my $refresh_h = $dbh->prepare($refresh_q . $name) ;
 131         $status = $refresh_h->execute();
 132
 133         print STDERR "Materialized view $name refreshed! Status: $status " . localtime() . "\n\n";
 134
 135         my $end_h = $dbh->prepare($end_q);
 136         $end_h->execute($name);
 137     }
 138     return $status;
 139 }