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'";
  61 }
  62
  63 #set TRUE before the transaction begins
  64 my $state = 'TRUE';
  65 print STDERR "*Setting currently_refreshing = TRUE\n";
  66 my $cur_refreshing_h = $dbh->prepare($cur_refreshing_q);
  67 $cur_refreshing_h->execute($state);
  68 $dbh->commit();
  69
  70 try {
  71     print STDERR "Refreshing materialized views . . ." . localtime() . "\n";
  72     my @mv_names = ();
  73
  74     if ($mode eq 'fullview') {
  75         @mv_names = ('materialized_phenoview','materialized_genoview');
  76     }
  77     if ($mode eq 'stockprop'){
  78        @mv_names = ('materialized_stockprop');
  79     }
  80     if ($mode eq 'phenotypes') {
  81        @mv_names = ("materialized_phenoview", "materialized_phenotype_jsonb_table");
  82     }
  83     if ($mode eq 'all_but_genoview') {
  84        @mv_names = ("materialized_stockprop", "materialized_phenoview", "materialized_phenotype_jsonb_table");
  85     }
  86
  87     my $status = refresh_mvs($dbh, \@mv_names, $concurrent);
  88
  89     #rollback if running in test mode
  90     if ($test) { die ; }
  91 }
  92 catch {
  93     warn "Refresh failed: @_";
  94     if ($test ) { print STDERR "TEST MODE\n" ; }
  95     $dbh->rollback()
  96 }
  97 finally {
  98     if (@_) {
  99         print "The try block died. Rolling back.\n";
 100     } else {
 101         print STDERR "COMMITTING\n";
 102         $dbh->commit();
 103     }
 104     #always set the refreshing status to FALSE at the end
 105     $state = 'FALSE';
 106     my $done_h = $dbh->prepare($cur_refreshing_q);
 107     print STDERR "*Setting currently_refreshing = FALSE \n";
 108     $done_h->execute($state);
 109     $dbh->commit();
 110 };
 111
 112 sub refresh_mvs {
 113     my $dbh = shift;
 114     my $mv_names_ref = shift;
 115     $concurrent = shift;
 116     my $start_q = "UPDATE matviews SET refresh_start = statement_timestamp() where mv_name = ?";
 117     my $end_q =   "UPDATE matviews SET  last_refresh = statement_timestamp() where mv_name = ? ";
 118     my $refresh_q = "REFRESH MATERIALIZED VIEW ";
 119     if ($concurrent) { $refresh_q .= " CONCURRENTLY "; }
 120     my $status;
 121
 122     foreach my $name ( @$mv_names_ref ) {
 123         print STDERR "**Refreshing view $name ". localtime() . " \n";
 124         my $start_h = $dbh->prepare($start_q);
 125         $start_h->execute($name);
 126         print STDERR "**QUERY = " . $refresh_q . $name . "\n";
 127         my $refresh_h = $dbh->prepare($refresh_q . $name) ;
 128         $status = $refresh_h->execute();
 129
 130         print STDERR "Materialized view $name refreshed! Status: $status " . localtime() . "\n\n";
 131
 132         my $end_h = $dbh->prepare($end_q);
 133         $end_h->execute($name);
 134     }
 135     return $status;
 136 }