This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: dbi-users@perl.org
From: Jeff Boes <jboes@nexcerpt.com>
Subject: Capturing EXPLAIN output in DBD::Pg
Date: Thu, 30 Aug 2001 09:54:33 -0400
Does anyone have a slick way to capture the output of an EXPLAIN command
issued via DBI (specifically, DBD::Pg)?
I'm attempting to extend a query generator so that it can intelligently
select columns to build a WHERE clause based on a record (hashref)
received. What I want to do is something like this:
foreach (@column_names) {
$dbh->do(qq!EXPLAIN SELECT * FROM emp WHERE $_ = 0!);
last if (...it's an index scan...);
}
The EXPLAIN SELECT statement will produce, under DBD::Pg, output that
looks like
NOTICE: QUERY PLAN:
Index Scan using emp_pkey on emp (cost=0.00..6.07 rows=5 width=238)
but it does so to STDOUT, not as the return value of the 'do' method.
===
To: Jeff Boes <jboes@nexcerpt.com>
From: Alex Pilosov <alex@pilosoft.com>
Subject: Re: Capturing EXPLAIN output in DBD::Pg
Date: Thu, 30 Aug 2001 10:07:00 -0400 (EDT)
On Thu, 30 Aug 2001, Jeff Boes wrote:
> Does anyone have a slick way to capture the output of an EXPLAIN command
> issued via DBI (specifically, DBD::Pg)?
>
> I'm attempting to extend a query generator so that it can intelligently
> select columns to build a WHERE clause based on a record (hashref)
> received. What I want to do is something like this:
>
> foreach (@column_names) {
> $dbh->do(qq!EXPLAIN SELECT * FROM emp WHERE $_ = 0!);
> last if (...it's an index scan...);
> }
>
> The EXPLAIN SELECT statement will produce, under DBD::Pg, output that
> looks like
>
> NOTICE: QUERY PLAN:
>
> Index Scan using emp_pkey on emp (cost=0.00..6.07 rows=5 width=238)
>
> but it does so to STDOUT, not as the return value of the 'do' method.
Not yet. Patches are welcome. Maybe if I get a free hour or two, I could
do it.
Here's my idea for implementation of this: libpq has a 'notice processor'
concept, a function that will be called when a NOTICE/WARNING/ERROR is
received from backend. DBD::Pg could conceivably allow for a perl function
to be used as notice processor, providing some glue to make it
work.
Shouldn't take more than a few hours, maybe next week.
someone pays me for it :)
My tiny annoyance with DBI is (or maybe I don't understand the way DBI
works) is that the only way I can add driver-specific functions to DBI
handle is by using $dbh->func('funcname',args), which is pretty ugly. Is
there a better way to be able to write directly $dbh->funcname(args)?
===
To: dbi-users@perl.org, alex@pilosoft.com (Alex Pilosov)
From: merlyn@stonehenge.com (Randal L. Schwartz)
Subject: Re: Capturing EXPLAIN output in DBD::Pg
Date: 30 Aug 2001 07:15:41 -0700
>>>>> "Alex" == Alex Pilosov <alex@pilosoft.com> writes:
Alex> My tiny annoyance with DBI is (or maybe I don't understand the
Alex> way DBI works) is that the only way I can add driver-specific
Alex> functions to DBI handle is by using $dbh->func('funcname',args),
Alex> which is pretty ugly. Is there a better way to be able to write
Alex> directly $dbh->funcname(args)?
I think you would have to subclass DBI to create database handles that
understand the new methods. But it would be interesting for DBI's
dbhandles to have an AUTOLOAD (or equivalent) that first attempts a
$dbh->func call with that name. I don't know if that's worse or
better than a controlled subclass. :)
Tim?
===
To: dbi-users@perl.org
From: Jeff Boes <jboes@nexcerpt.com>
Subject: Re: Capturing EXPLAIN output in DBD::Pg
Date: Thu, 30 Aug 2001 10:27:19 -0400
On Thu, 30 Aug 2001 10:07:00 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com> wrote:
> On Thu, 30 Aug 2001, Jeff Boes wrote:
>
> > The EXPLAIN SELECT statement will produce, under DBD::Pg, output that
> > looks like
> >
> > NOTICE: QUERY PLAN:
> >
> > Index Scan using emp_pkey on emp (cost=0.00..6.07 rows=5
width=238)
> >
> > but it does so to STDOUT, not as the return value of the 'do' method.
>
> Not yet. Patches are welcome. Maybe if I get a free hour or two, I could
> do it.
Perhaps related, perhaps not: what is the purpose of the 'tty=' option in
the driver? I looked at that with an increasingly puzzled expression on my
face (8-), in hopes that I could use that to capture the output.
===
To: "Randal L. Schwartz" <merlyn@stonehenge.com>
From: Tim Bunce <Tim.Bunce@pobox.com>
Subject: Re: Capturing EXPLAIN output in DBD::Pg
Date: Thu, 30 Aug 2001 16:21:45 +0100
On Thu, Aug 30, 2001 at 07:15:41AM -0700, Randal L. Schwartz wrote:
> >>>>> "Alex" == Alex Pilosov <alex@pilosoft.com> writes:
>
> Alex> My tiny annoyance with DBI is (or maybe I don't understand the
> Alex> way DBI works) is that the only way I can add driver-specific
> Alex> functions to DBI handle is by using $dbh->func('funcname',args),
> Alex> which is pretty ugly. Is there a better way to be able to write
> Alex> directly $dbh->funcname(args)?
>
> I think you would have to subclass DBI to create database handles that
> understand the new methods.
That's certainly one way, but it puts the method into the 'outer world'
of the application, not the 'inner world' for the driver. In other words
the DBI dispatcher doesn't see calls to those method so can't log them
of provide RaiseError/PrintError behaviour for them.
> But it would be interesting for DBI's
> dbhandles to have an AUTOLOAD (or equivalent) that first attempts a
> $dbh->func call with that name. I don't know if that's worse or
> better than a controlled subclass. :)
>
> Tim?
It's my intention to allow the DBI's private install_method() method
to be used to install driver-specific methods - so long as the name
of the method begins with the drivers own prefix (ie ora_ for DBD::Oracle).
Patches welcome.
===
To: Jeff Boes <jboes@nexcerpt.com>
From: Alex Pilosov <alex@pilosoft.com>
Subject: Re: Capturing EXPLAIN output in DBD::Pg
Date: Thu, 30 Aug 2001 11:27:31 -0400 (EDT)
On Thu, 30 Aug 2001, Jeff Boes wrote:
> Perhaps related, perhaps not: what is the purpose of the 'tty=' option in
> the driver? I looked at that with an increasingly puzzled expression on my
> face (8-), in hopes that I could use that to capture the output.
Not exactly. This is TTY that backend output will go to _on the server_.
===
To: Tim Bunce <Tim.Bunce@pobox.com>
From: Alex Pilosov <alex@pilosoft.com>
Subject: Re: Capturing EXPLAIN output in DBD::Pg
Date: Thu, 30 Aug 2001 11:30:23 -0400 (EDT)
On Thu, 30 Aug 2001, Tim Bunce wrote:
> It's my intention to allow the DBI's private install_method() method
> to be used to install driver-specific methods - so long as the name
> of the method begins with the drivers own prefix (ie ora_ for DBD::Oracle).
Ah, that's a nice solution. Is it already done or its in future? :)
===
To: Alex Pilosov <alex@pilosoft.com>
From: Tim Bunce <Tim.Bunce@pobox.com>
Subject: Re: Capturing EXPLAIN output in DBD::Pg
Date: Fri, 31 Aug 2001 18:24:03 +0100
On Thu, Aug 30, 2001 at 11:30:23AM -0400, Alex Pilosov wrote:
> On Thu, 30 Aug 2001, Tim Bunce wrote:
>
> > It's my intention to allow the DBI's private install_method() method
> > to be used to install driver-specific methods - so long as the name
> > of the method begins with the drivers own prefix (ie ora_ for DBD::Oracle).
>
> Ah, that's a nice solution. Is it already done or its in future? :)
It's mostly there. The install_method() method works (the DBI uses it itself to
install it's own interface :) but it needs documenting. Plus a hash that
maps driver names to registered prefixes (Oracle=>ora_) is needed.