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  -c flag; if present, run concurrent refresh
  16  -m materialized view select. can be either 'fullview' or 'stockprop'
  17
  18 All materialized views that are included in the refresh function will be refreshed
  19 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.
  20
  21 =head1 AUTHOR
  22
  23 Bryan Ellerbrock <bje24@cornell.edu>
  24
  25 =cut
  26
  27 use strict;
  28 use warnings;
  29 use Getopt::Std;
  30 use DBI;
  31 #use CXGN::DB::InsertDBH;
  32
  33 our ($opt_H, $opt_D, $opt_U, $opt_P, $opt_m, $opt_c, $refresh, $status);
  34 getopts('H:D:U:P:m:c');
  35
  36 print STDERR "Connecting to database...\n";
  37 my $dsn = 'dbi:Pg:database='.$opt_D.";host=".$opt_H.";port=5432";
  38 my $dbh = DBI->connect($dsn, $opt_U, $opt_P);
  39
  40 eval {
  41     print STDERR "Refreshing materialized views . . ." . localtime() . "\n";
  42
  43     if ($opt_m eq 'fullview'){
  44         my $q = "UPDATE public.matviews SET currently_refreshing=?";
  45         my $state = 'TRUE';
  46         my $h = $dbh->prepare($q);
  47         $h->execute($state);
  48
  49         if ($opt_c) {
  50             $refresh = 'SELECT refresh_materialized_views_concurrently()';
  51         } else {
  52             $refresh = 'SELECT refresh_materialized_views()';
  53         }
  54
  55         $h = $dbh->prepare($refresh);
  56         $status = $h->execute();
  57
  58         if ($opt_c) {
  59           $refresh = 'SELECT refresh_materialized_stockprop_concurrently()';
  60         } else {
  61           $refresh = 'SELECT refresh_materialized_stockprop()';
  62         }
  63
  64         $h = $dbh->prepare($refresh);
  65         $status = $h->execute();
  66
  67         if ($opt_c) {
  68           $refresh = 'SELECT refresh_materialized_phenotype_jsonb_table_concurrently()';
  69         } else {
  70           $refresh = 'SELECT refresh_materialized_phenotype_jsonb_table()';
  71         }
  72
  73         $h = $dbh->prepare($refresh);
  74         $status = $h->execute();
  75
  76         $q = "UPDATE public.matviews SET currently_refreshing=?";
  77         $state = 'FALSE';
  78         $h = $dbh->prepare($q);
  79         $h->execute($state);
  80     }
  81
  82     if ($opt_m eq 'stockprop'){
  83         if ($opt_c) {
  84           $refresh = 'SELECT refresh_materialized_stockprop_concurrently()';
  85         } else {
  86           $refresh = 'SELECT refresh_materialized_stockprop()';
  87         }
  88
  89         my $h = $dbh->prepare($refresh);
  90         $status = $h->execute();
  91     }
  92
  93     print STDERR "Materialized views refreshed! Status: $status" . localtime() . "\n";
  94 };
  95
  96 if ($@) {
  97   $dbh->rollback();
  98   print STDERR $@;
  99 } else {
 100   print STDERR "Done, exiting refresh_matviews.pl \n";
 101 }