Thanks for the suggestion
I thought of another solution inspired by a suggestion I received off-list
I know that with both Pg and MySQL I can first of all do a check with
$expl = $dbh->selectall_arrayref("EXPLAIN $user_sql_query");
However, I would need seperate piece of code to interpret MySQL and Pg
output. In fact, I'm not 100% sure how to go about interpreting the result
correctly myself. Is this worth exploring? Would this be a useful DBIx
module to add to the canon? Others would have to contribute bindings for
other DBMSs.
Perhaps DBMS-specific admin is the way to go here.. but this does seem
fairly limited, at least for MySQL
On Tue, 6 Apr 2004, Henri Asseily wrote:
> Sorry I can't answer your question, and doubly sorry to raise another
> problem that you'll have, which I did encounter:
> If you do not restrict the # of rows returned, you could easily crash
> older machines.
> I did build a general CGI interface to databases, with
> database/table/column displays, query saving, etc... (which you're
> welcome to), and found out that some of my users were complaining that
> their machines were crashing.
>
> It turns out that upon investigation, it is not a good thing to let
> Internet Explorer retrieve a 100,000-row table to display on an
> under-1GHz machine. :-)
>
> The obvious solution is that before every SQL call you call "SET
> ROWCOUNT $MAXROWCOUNT" (under Sybase) where I suggest $MAXROWCOUNT <
> 20000.
>
> H.
>
> On Apr 5, 2004, at 11:11 AM, Chris Mungall wrote:
>
> >
> > I would like to provide power-users with a CGI interface to a database;
> > this interface would allow arbitrary SQL. I would also like to be able
> > to
> > throttle queries that take too long to stop the db server getting
> > backed
> > up with killer queries.
> >
> > Right now I am doing this:
> >
> > my $tag = "kill$$"."TAG";
> > my $tagf = "/tmp/$tag";
> > system("touch $tagf && chmod 777 $tagf && sleep 15 &&
> > test -f $tagf && kill -9 $$ && rm $tagf &");
> >
> > # execute query
> > $results = $dbh->do(...)
> >
> > # inactivate killer
> > system("rm $tagf &");
> >
> > # print results....
> >
> > However, this isn't much good as it just kills the CGI process, the
> > killer
> > query carries on running on the db server.
> >
> > I have thought about extending my hacky shell kill pipe to also run
> > mysqladmin -kill and whatever the postgresql equivalent is, but then
> > thought:
> >
> > - way too messy and hacky
> > - someone must have solved this one already
> >
> > However, I couldn't find any module for dealing with this. Any
> > suggestions?
> >
> > Cheers
> > Chris
>
>
