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]
  10
  11 Options:
  12
  13  -H the database host
  14  -D the database name
  15  -c flag; if present, run concurrent refresh
  16
  17 All materialized views that are included in the refresh function will be refreshed
  18 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.
  19
  20 =head1 AUTHOR
  21
  22 Bryan Ellerbrock <bje24@cornell.edu>
  23
  24 =cut
  25
  26 use strict;
  27 use warnings;
  28 use Getopt::Std;
  29 use DBI;
  30 #use CXGN::DB::InsertDBH;
  31
  32 our ($opt_H, $opt_D, $opt_U, $opt_P, $opt_c, $refresh);
  33 getopts('H:D:U:P:c');
  34
  35 print STDERR "Connecting to database...\n";
  36 my $dsn = 'dbi:Pg:database='.$opt_D.";host=".$opt_H.";port=5432";
  37 my $dbh = DBI->connect($dsn, $opt_U, $opt_P);
  38
  39 eval {
  40   my $q = "UPDATE public.matviews SET currently_refreshing=?";
  41   my $state = 'TRUE';
  42   my $h = $dbh->prepare($q);
  43   $h->execute($state);
  44
  45   print STDERR "Refreshing materialized views . . ." . localtime() . "\n";
  46
  47   if ($opt_c) {
  48     $refresh = 'SELECT refresh_materialized_views_concurrently()';
  49   } else {
  50     $refresh = 'SELECT refresh_materialized_views()';
  51   }
  52
  53   $h = $dbh->prepare($refresh);
  54   my $status = $h->execute();
  55
  56   print STDERR "Materialized views refreshed! Status: $status" . localtime() . "\n";
  57
  58   $q = "UPDATE public.matviews SET currently_refreshing=?";
  59   $state = 'FALSE';
  60   $h = $dbh->prepare($q);
  61   $h->execute($state);
  62 };
  63
  64 if ($@) {
  65   $dbh->rollback();
  66   print STDERR $@;
  67 } else {
  68   print STDERR "Done, exiting refresh_matviews.pl \n";
  69 }