Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
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 wandering the Monastery: (7)
As of 2014-07-29 01:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (211 votes), past polls