Perl DBI postgres question

[Anonymous Monk]

Forgive me if this is slightly off topic.

But has anyone ever ran a Postgres vacuum command from a Perl DBI database handle?

It looks like running the command would work with a 'do' statement but I'd like to capture the output as well ('do' only returns the rowcount)

$dbh->do("VACUUM VERBOSE ANALYZE");

[erix]

Vacuum writes to STDERR so you can redirect & capture that. Here is a program based on an old node by davido (thanks! o/) (which was based on an example in perldoc perlfunc).

It drops/creates a dummy table t, and vacuums it, silently capturing the output (then dumping it in STDOUT):

use strict;
use DBI;
main();
exit;
sub main {
    my $dbh = DBI->connect("dbi:Pg:", undef,undef, {RaiseError=>1,Prin
+tError=>0});
    my $table = "t";
    $dbh->do("drop table $table") or die "die 1 - $!\n";
    $dbh->do("create table t(c text)")      or die "die 2 - $!\n";
    my $routput = vacuum_analyze($dbh, $table);
    print "-- verbose output:\n";
    print "-"x70, "\n";
    print $$routput;
    print "-"x70, "\n";
}
sub vacuum_analyze {
    my ($dbh, $table) = @_;
    my $output;
    open OLDERR, ">&STDERR";
    close STDERR;
    open STDERR, ">", \$output    or die "error opening a stderr (heh)
+\n";
    $dbh->do("vacuum verbose analyze $table");
    close STDERR;
    open STDERR, ">&OLDERR";
    \$output;
}

Running that gives:

2012.12.18 12:47:31 aardvark@bulldog:~/pg_stuff/pg_sql/pgsql.HEAD [0]
$ perl vacuum.pl
-- verbose output:
----------------------------------------------------------------------
INFO:  vacuuming "public.t"
INFO:  "t": found 0 removable, 0 nonremovable row versions in 0 out of
+ 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_28335219"
INFO:  index "pg_toast_28335219_index" now contains 0 row versions in 
+1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_28335219": found 0 removable, 0 nonremovable row vers
+ions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.t"
INFO:  "t": scanned 0 of 0 pages, containing 0 live rows and 0 dead ro
+ws; 0 rows in sample, 0 estimated total rows
----------------------------------------------------------------------

(PostgreSQL 9.3devel, perl 5.16.2)

[mje]

I don't use Postgres to do this but I have done something similar with DBD::ODBC and MS SQL Server. With DBD::ODBC, the messages output are passed to the odbc_err_handler (if you define one). Perhaps DBD::Pg has something similar.

[Anonymous Monk]

Quoth DBD::Pg:

PrintError (boolean, inherited)

Forces database errors to also generate warnings, which can then be filtered with methods such as locally redefining $SIG{__WARN__} or using modules such as CGI::Carp. This attribute is on by default.