note
erix
<p>Vacuum writes to STDERR so you can redirect & capture that. Here is a program based on an [node://291303|old node] by [davido] (thanks! o/) (which was based on an example in perldoc [http://perldoc.perl.org/perlfunc.html |perlfunc]).
<p>It drops/creates a dummy table t, and vacuums it, silently capturing the output (then dumping it in STDOUT):
<c>
use strict;
use DBI;
main();
exit;
sub main {
my $dbh = DBI->connect("dbi:Pg:", undef,undef, {RaiseError=>1,PrintError=>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;
}</c>
<p>Running that gives:
<c>
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 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: analyzing "public.t"
INFO: "t": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
----------------------------------------------------------------------</c>
<p>(PostgreSQL 9.3devel, perl 5.16.2)
1009186
1009186