Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Perl DBI postgres question

by Anonymous Monk
on Dec 17, 2012 at 15:21 UTC ( #1009186=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

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");

Comment on Perl DBI postgres question
Download Code
Re: Perl DBI postgres question
by mje (Deacon) on Dec 18, 2012 at 10:01 UTC

    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.

Re: Perl DBI postgres question
by Anonymous Monk on Dec 18, 2012 at 10:43 UTC

    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.

Re: Perl DBI postgres question (capturing STDERR from VACUUM)
by erix (Vicar) on Dec 18, 2012 at 11:36 UTC

    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)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1009186]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2014-09-17 19:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (98 votes), past polls