Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

DIB:Pg returns select plan on execute

by solfier (Initiate)
on Aug 17, 2012 at 14:57 UTC ( #987989=perlquestion: print w/replies, xml ) Need Help??
solfier has asked for the wisdom of the Perl Monks concerning the following question:

hi! I am using DBI to connect to postgresql. Every thing works good, but on $sth->execute the select plan is returned to the console. How can I redirect its output, or simply dont get it? see ya!

Replies are listed 'Best First'.
Re: DIB:Pg returns select plan on execute
by kennethk (Abbot) on Aug 17, 2012 at 15:05 UTC

    What's your code look like? My guess is that adding

    { PrintError => 0, }
    to your connector will likely help.

    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Re: DBD::Pg returns select plan on execute
by erix (Parson) on Aug 17, 2012 at 15:42 UTC

    Postgres will return the plan when you prefix the actual SQL statement with 'EXPLAIN' or 'EXPLAIN ANALYZE'.

    EXPLAIN will get the plan without executing it, EXPLAIN ANALYZE will also execute it, then returns the plan and some detail of the result (like the number of rows); both output a table-like structure with 1 column: 'QUERY PLAN'.

    (Here is an plan-analyze site that is sometimes handy:

Re: DIB:Pg returns select plan on execute
by chacham (Prior) on Aug 17, 2012 at 15:44 UTC

    For example:

    eval{$db = DBI->connect("DBI:ODBC:$config{db_odbc}", $config{db_user}, + $config{db_pass}, {PrintError=>0, RaiseError=>1, AutoCommit=>0})}; error("Could not connect to database:", map{" DBI: $_"} split /\n/, $D +BI::errstr) if $@;

    In this example, error() is a custom function to print/log errors.

Re: DIB:Pg returns select plan on execute
by Anonymous Monk on Aug 17, 2012 at 16:14 UTC

    Are you getting the same behaviour on the psql command-line tool?

    (I'm thinking of some strange server option that outputs query plans on every query... I can't find it in the docs at least.)

      A postgres session (or postgresql.conf file) accepts the parameter 'debug_print_plan' [1]: if set to 'on' (or 1) postgresql will write planner details to the logfile:

      $ psql psql (9.3devel) Type "help" for help. testdb=# set debug_print_plan=1; SET testdb=# select 42; ?column? ---------- 42 (1 row) testdb=# \q 2012-10-30 23:57:03.757 CET 21837 LOG: plan: 2012-10-30 23:57:03.757 CET 21837 DETAIL: {PLANNEDSTMT :commandType 1 :queryId 0 :hasReturning false :hasModifyingCTE false :canSetTag true :transientPlan false :planTree {RESULT :startup_cost 0.00 :total_cost 0.01 :plan_rows 1 :plan_width 0 :targetlist ( {TARGETENTRY :expr {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 7 :constvalue 4 [ 42 0 0 0 0 0 0 0 ] } :resno 1 :resname ?column? :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :qual <> :lefttree <> :righttree <> :initPlan <> :extParam (b) :allParam (b) :resconstantqual <> } :rtable <> :resultRelations <> :utilityStmt <> :subplans <> :rewindPlanIDs (b) :rowMarks <> :relationOids <> :invalItems <> :nParamExec 0 } 2012-10-30 23:57:03.757 CET 21837 STATEMENT: select 42;

      This is all written to the logfile so to me it seems not very likely the case for the OP. He (or some SQL-generator) probably just forgot to take an 'EXPLAIN'-prefix from an SQL statement.

      [1] runtime-config-logging.html (postgres manual)

      update (2012.10.30): show use in session setting (not postgresql.conf setting).

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://987989]
Approved by chacham
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2018-03-17 04:38 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (223 votes). Check out past polls.