Thread: Report Generator Proposal

Report Generator Proposal

From
"Gaetano Mendola"
Date:
Hi all,
each time that someone say: this query is slow, this query take forever, bla
bla bla
all our request are:

1) Which version are you running ?
2) May I see your table definition?
3) May I see you configuration file?
4) May I see the explain ?
5) .......

sometimes a beginner is scared about all these questions and even
he/she don't know how do collect these informations and we lose
important data about:  "why that damn query is too slow"?


may be is usefull generate an automatic report that take charge
of collect all these informations and send it to a file or directly to
pgsql-performance@postgresql.org , may be something like:


> BEGIN REPORT TO FILE <file_name>
> SELECT ......
> END REPORT;


or


> BEGIN REPORT TO EMAIL <address>
> SELECT ......
> END REPORT;



Regards
Gaetano Mendola





Re: Report Generator Proposal

From
"Gaetano Mendola"
Date:
Nobody entusiastic ? I'm wrong about my feeling ?
I think that have the psql able to generate a report
will increase the our better response to a user novice
or not ?


Regards
Gaetano Mendola

----- Original Message -----
From: ""Gaetano Mendola"" <mendola@bigfoot.com>
Newsgroups: comp.databases.postgresql.admin
Sent: Wednesday, September 10, 2003 4:10 AM
Subject: Report Generator Proposal


> Hi all,
> each time that someone say: this query is slow, this query take forever,
bla
> bla bla
> all our request are:
>
> 1) Which version are you running ?
> 2) May I see your table definition?
> 3) May I see you configuration file?
> 4) May I see the explain ?
> 5) .......
>
> sometimes a beginner is scared about all these questions and even
> he/she don't know how do collect these informations and we lose
> important data about:  "why that damn query is too slow"?
>
>
> may be is usefull generate an automatic report that take charge
> of collect all these informations and send it to a file or directly to
> pgsql-performance@postgresql.org , may be something like:
>
>
> > BEGIN REPORT TO FILE <file_name>
> > SELECT ......
> > END REPORT;
>
>
> or
>
>
> > BEGIN REPORT TO EMAIL <address>
> > SELECT ......
> > END REPORT;
>
>
>
> Regards
> Gaetano Mendola
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>


Re: Report Generator Proposal

From
"Gaetano Mendola"
Date:
Must I suppose that the idea rejected ?

----- Original Message -----
From: ""Gaetano Mendola"" <mendola@bigfoot.com>
Newsgroups: comp.databases.postgresql.admin
Sent: Thursday, September 11, 2003 11:52 PM
Subject: Re: Report Generator Proposal


> Nobody entusiastic ? I'm wrong about my feeling ?
> I think that have the psql able to generate a report
> will increase the our better response to a user novice
> or not ?
>
>
> Regards
> Gaetano Mendola
>
> ----- Original Message -----
> From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> Newsgroups: comp.databases.postgresql.admin
> Sent: Wednesday, September 10, 2003 4:10 AM
> Subject: Report Generator Proposal
>
>
> > Hi all,
> > each time that someone say: this query is slow, this query take forever,
> bla
> > bla bla
> > all our request are:
> >
> > 1) Which version are you running ?
> > 2) May I see your table definition?
> > 3) May I see you configuration file?
> > 4) May I see the explain ?
> > 5) .......
> >
> > sometimes a beginner is scared about all these questions and even
> > he/she don't know how do collect these informations and we lose
> > important data about:  "why that damn query is too slow"?
> >
> >
> > may be is usefull generate an automatic report that take charge
> > of collect all these informations and send it to a file or directly to
> > pgsql-performance@postgresql.org , may be something like:
> >
> >
> > > BEGIN REPORT TO FILE <file_name>
> > > SELECT ......
> > > END REPORT;
> >
> >
> > or
> >
> >
> > > BEGIN REPORT TO EMAIL <address>
> > > SELECT ......
> > > END REPORT;
> >
> >
> >
> > Regards
> > Gaetano Mendola
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Re: Report Generator Proposal

From
"Matt Clark"
Date:
I rather like it actually.  Cisco equipment has a 'show tech-support' command that does exactly that, dumps all the
config,HW/SW 
versions, current state, you name it.  If you have a problem you run that, attach the output to yr support email, and
99%of the 
time there's enough info there to solve the problem.

It'd be easy to automate all except the explain, a script that did everything else could then emit "If your preoblem
relatesto a 
specific query, then run 'explain analyze <query>' and attach it to your message as well".

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gaetano Mendola
> Sent: 15 September 2003 16:25
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Report Generator Proposal
>
>
> Must I suppose that the idea rejected ?
>
> ----- Original Message -----
> From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> Newsgroups: comp.databases.postgresql.admin
> Sent: Thursday, September 11, 2003 11:52 PM
> Subject: Re: Report Generator Proposal
>
>
> > Nobody entusiastic ? I'm wrong about my feeling ?
> > I think that have the psql able to generate a report
> > will increase the our better response to a user novice
> > or not ?
> >
> >
> > Regards
> > Gaetano Mendola
> >
> > ----- Original Message -----
> > From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> > Newsgroups: comp.databases.postgresql.admin
> > Sent: Wednesday, September 10, 2003 4:10 AM
> > Subject: Report Generator Proposal
> >
> >
> > > Hi all,
> > > each time that someone say: this query is slow, this query take forever,
> > bla
> > > bla bla
> > > all our request are:
> > >
> > > 1) Which version are you running ?
> > > 2) May I see your table definition?
> > > 3) May I see you configuration file?
> > > 4) May I see the explain ?
> > > 5) .......
> > >
> > > sometimes a beginner is scared about all these questions and even
> > > he/she don't know how do collect these informations and we lose
> > > important data about:  "why that damn query is too slow"?
> > >
> > >
> > > may be is usefull generate an automatic report that take charge
> > > of collect all these informations and send it to a file or directly to
> > > pgsql-performance@postgresql.org , may be something like:
> > >
> > >
> > > > BEGIN REPORT TO FILE <file_name>
> > > > SELECT ......
> > > > END REPORT;
> > >
> > >
> > > or
> > >
> > >
> > > > BEGIN REPORT TO EMAIL <address>
> > > > SELECT ......
> > > > END REPORT;
> > >
> > >
> > >
> > > Regards
> > > Gaetano Mendola
> > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 8: explain analyze is your friend
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>



Re: Report Generator Proposal

From
"Gaetano Mendola"
Date:
""Matt Clark"" <matt@ymogen.net> wrote:
> It'd be easy to automate all except the explain, a script that did
everything else could then emit "If your preoblem relates to a
> specific query, then run 'explain analyze <query>' and attach it to your
message as well".

Well, must be charge of psql store somewhere all selects performed
between the BEGIN REPORT and END REPORT and perform
the explain command of that selects with the explain; may be the really
difficult here is that psql
shall know all tables involved in a select and insert in the report the
descriptions of that tables.
Before to work on it I want be sure that is a good idea.

Regards
Gaetano Mendola


> > -----Original Message-----
> > From: pgsql-admin-owner@postgresql.org
> > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gaetano Mendola
> > Sent: 15 September 2003 16:25
> > To: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Report Generator Proposal
> >
> >
> > Must I suppose that the idea rejected ?
> >
> > ----- Original Message -----
> > From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> > Newsgroups: comp.databases.postgresql.admin
> > Sent: Thursday, September 11, 2003 11:52 PM
> > Subject: Re: Report Generator Proposal
> >
> >
> > > Nobody entusiastic ? I'm wrong about my feeling ?
> > > I think that have the psql able to generate a report
> > > will increase the our better response to a user novice
> > > or not ?
> > >
> > >
> > > Regards
> > > Gaetano Mendola
> > >
> > > ----- Original Message -----
> > > From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> > > Newsgroups: comp.databases.postgresql.admin
> > > Sent: Wednesday, September 10, 2003 4:10 AM
> > > Subject: Report Generator Proposal
> > >
> > >
> > > > Hi all,
> > > > each time that someone say: this query is slow, this query take
forever,
> > > bla
> > > > bla bla
> > > > all our request are:
> > > >
> > > > 1) Which version are you running ?
> > > > 2) May I see your table definition?
> > > > 3) May I see you configuration file?
> > > > 4) May I see the explain ?
> > > > 5) .......
> > > >
> > > > sometimes a beginner is scared about all these questions and even
> > > > he/she don't know how do collect these informations and we lose
> > > > important data about:  "why that damn query is too slow"?
> > > >
> > > >
> > > > may be is usefull generate an automatic report that take charge
> > > > of collect all these informations and send it to a file or directly
to
> > > > pgsql-performance@postgresql.org , may be something like:
> > > >
> > > >
> > > > > BEGIN REPORT TO FILE <file_name>
> > > > > SELECT ......
> > > > > END REPORT;
> > > >
> > > >
> > > > or
> > > >
> > > >
> > > > > BEGIN REPORT TO EMAIL <address>
> > > > > SELECT ......
> > > > > END REPORT;
> > > >
> > > >
> > > >
> > > > Regards
> > > > Gaetano Mendola
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
broadcast)---------------------------
> > > > TIP 8: explain analyze is your friend
> > > >
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>


Re: Report Generator Proposal

From
kaolin fire
Date:
On Monday, September 15, 2003, at 02:06  PM, Gaetano Mendola wrote:

> ""Matt Clark"" <matt@ymogen.net> wrote:
>> It'd be easy to automate all except the explain, a script that did
> everything else could then emit "If your preoblem relates to a
>> specific query, then run 'explain analyze <query>' and attach it to
>> your
> message as well".
>
> Well, must be charge of psql store somewhere all selects performed
> between the BEGIN REPORT and END REPORT and perform
> the explain command of that selects with the explain; may be the really
> difficult here is that psql
> shall know all tables involved in a select and insert in the report the
> descriptions of that tables.
> Before to work on it I want be sure that is a good idea.
>
> Regards
> Gaetano Mendola

sounds good.  maybe with the option (enabled by default?) to only show
the columns used in the queries for the tables pulled.  ((wheat from
chaff and all that))

hmm.  though there's still the (some small percentage of) things that
could be going wrong with custom stored procedures.  dumping those
would be (I'm presuming, based on the scope of the task as it's been
described) reasonably simply, but figuring out what they touch could be
prohibitive?

kudos for any attempt, though, all around. :)

-kaolin fire
-postgresql novice
-http://poemranker.com


Re: Report Generator Proposal

From
"Somasekhar Bangalore"
Date:
Sounds good ...it will save lot of emails floating around and time..

Regards,
Somasekhar Bangalore
Principal Software Engineer
ZenSutra Software Technologies Pvt. Ltd.
Suite 601, HM Geneva House
#14, Cunningham Road
Bangalore 560-052, India

Ph:+91-80-235-0481
Fax:+91-80-235-0486
Email: sbangalore@zensutra.com

Weaving the knowledge tapestry'


-----Original Message-----
From: Matt Clark [mailto:matt@ymogen.net]
Sent: Monday, September 15, 2003 11:00 PM
To: Gaetano Mendola; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Report Generator Proposal


I rather like it actually.  Cisco equipment has a 'show tech-support' command that does exactly that, dumps all the
config,HW/SW 
versions, current state, you name it.  If you have a problem you run that, attach the output to yr support email, and
99%of the 
time there's enough info there to solve the problem.

It'd be easy to automate all except the explain, a script that did everything else could then emit "If your preoblem
relatesto a 
specific query, then run 'explain analyze <query>' and attach it to your message as well".

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gaetano Mendola
> Sent: 15 September 2003 16:25
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Report Generator Proposal
>
>
> Must I suppose that the idea rejected ?
>
> ----- Original Message -----
> From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> Newsgroups: comp.databases.postgresql.admin
> Sent: Thursday, September 11, 2003 11:52 PM
> Subject: Re: Report Generator Proposal
>
>
> > Nobody entusiastic ? I'm wrong about my feeling ?
> > I think that have the psql able to generate a report
> > will increase the our better response to a user novice
> > or not ?
> >
> >
> > Regards
> > Gaetano Mendola
> >
> > ----- Original Message -----
> > From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> > Newsgroups: comp.databases.postgresql.admin
> > Sent: Wednesday, September 10, 2003 4:10 AM
> > Subject: Report Generator Proposal
> >
> >
> > > Hi all,
> > > each time that someone say: this query is slow, this query take forever,
> > bla
> > > bla bla
> > > all our request are:
> > >
> > > 1) Which version are you running ?
> > > 2) May I see your table definition?
> > > 3) May I see you configuration file?
> > > 4) May I see the explain ?
> > > 5) .......
> > >
> > > sometimes a beginner is scared about all these questions and even
> > > he/she don't know how do collect these informations and we lose
> > > important data about:  "why that damn query is too slow"?
> > >
> > >
> > > may be is usefull generate an automatic report that take charge
> > > of collect all these informations and send it to a file or directly to
> > > pgsql-performance@postgresql.org , may be something like:
> > >
> > >
> > > > BEGIN REPORT TO FILE <file_name>
> > > > SELECT ......
> > > > END REPORT;
> > >
> > >
> > > or
> > >
> > >
> > > > BEGIN REPORT TO EMAIL <address>
> > > > SELECT ......
> > > > END REPORT;
> > >
> > >
> > >
> > > Regards
> > > Gaetano Mendola
> > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 8: explain analyze is your friend
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match


Re: Report Generator Proposal

From
"skennedy"
Date:
Oracle have something similar: RDA which generates  HTML reports for the hardware, SO, and database.
It doesn't do explains but does show which SQLs are the most expensive, etc.

It could be worth having a look at that for ideas on what and how to report it.

Stephen.

Somasekhar Bangalore wrote:
Sounds good ...it will save lot of emails floating around and time..

Regards, 
Somasekhar Bangalore
Principal Software Engineer 
ZenSutra Software Technologies Pvt. Ltd.
Suite 601, HM Geneva House
#14, Cunningham Road 
Bangalore 560-052, India

Ph:+91-80-235-0481
Fax:+91-80-235-0486
Email: sbangalore@zensutra.com 

Weaving the knowledge tapestry' 


-----Original Message-----
From: Matt Clark [mailto:matt@ymogen.net]
Sent: Monday, September 15, 2003 11:00 PM
To: Gaetano Mendola; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Report Generator Proposal


I rather like it actually.  Cisco equipment has a 'show tech-support' command that does exactly that, dumps all the config, HW/SW
versions, current state, you name it.  If you have a problem you run that, attach the output to yr support email, and 99% of the
time there's enough info there to solve the problem.

It'd be easy to automate all except the explain, a script that did everything else could then emit "If your preoblem relates to a
specific query, then run 'explain analyze <query>' and attach it to your message as well".
 
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gaetano Mendola
Sent: 15 September 2003 16:25
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Report Generator Proposal


Must I suppose that the idea rejected ?

----- Original Message -----
From: ""Gaetano Mendola"" <mendola@bigfoot.com>
Newsgroups: comp.databases.postgresql.admin
Sent: Thursday, September 11, 2003 11:52 PM
Subject: Re: Report Generator Proposal

   
Nobody entusiastic ? I'm wrong about my feeling ?
I think that have the psql able to generate a report
will increase the our better response to a user novice
or not ?


Regards
Gaetano Mendola

----- Original Message -----
From: ""Gaetano Mendola"" <mendola@bigfoot.com>
Newsgroups: comp.databases.postgresql.admin
Sent: Wednesday, September 10, 2003 4:10 AM
Subject: Report Generator Proposal

     
Hi all,
each time that someone say: this query is slow, this query take forever,       
bla     
bla bla
all our request are:

1) Which version are you running ?
2) May I see your table definition?
3) May I see you configuration file?
4) May I see the explain ?
5) .......

sometimes a beginner is scared about all these questions and even
he/she don't know how do collect these informations and we lose
important data about:  "why that damn query is too slow"?


may be is usefull generate an automatic report that take charge
of collect all these informations and send it to a file or directly to
pgsql-performance@postgresql.org , may be something like:

       
BEGIN REPORT TO FILE <file_name>
SELECT ......
END REPORT;         
or

       
BEGIN REPORT TO EMAIL <address>
SELECT ......
END REPORT;         

Regards
Gaetano Mendola





---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
       
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
     
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
   


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your     joining column's datatypes do not match


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org
 

Re: Report Generator Proposal

From
Mauricio de Castro
Date:
Report Generator in PHP-GTK that work with Pear-db. It's very easy for
users...

http://agata.codigolivre.org.br

Creator: Pablo (pablo@php.net)

[]s
Mauricio

On Monday 15 September 2003 18:25, kaolin fire wrote:
> On Monday, September 15, 2003, at 02:06  PM, Gaetano Mendola wrote:
> > ""Matt Clark"" <matt@ymogen.net> wrote:
> >> It'd be easy to automate all except the explain, a script that did
> >
> > everything else could then emit "If your preoblem relates to a
> >
> >> specific query, then run 'explain analyze <query>' and attach it to
> >> your
> >
> > message as well".
> >
> > Well, must be charge of psql store somewhere all selects performed
> > between the BEGIN REPORT and END REPORT and perform
> > the explain command of that selects with the explain; may be the really
> > difficult here is that psql
> > shall know all tables involved in a select and insert in the report the
> > descriptions of that tables.
> > Before to work on it I want be sure that is a good idea.
> >
> > Regards
> > Gaetano Mendola
>
> sounds good.  maybe with the option (enabled by default?) to only show
> the columns used in the queries for the tables pulled.  ((wheat from
> chaff and all that))
>
> hmm.  though there's still the (some small percentage of) things that
> could be going wrong with custom stored procedures.  dumping those
> would be (I'm presuming, based on the scope of the task as it's been
> described) reasonably simply, but figuring out what they touch could be
> prohibitive?
>
> kudos for any attempt, though, all around. :)
>
> -kaolin fire
> -postgresql novice
> -http://poemranker.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Maurício de Castro
Coordenador de Desenvolvimento em Software Livre
SOLIS - Cooperativa de Soluções Livres
http://www.solis.coop.br
51 3011 0610


Re: Report Generator Proposal

From
"Mendola Gaetano"
Date:
"Mauricio de Castro" <mcastro@solis.coop.br> wrote:
> Report Generator in PHP-GTK that work with Pear-db. It's very easy for
> users...

I was thinking to generate the report with the psql, not with an external
program, somethink like this:

Welcome to psql 7.X, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# BEGIN REPORT TO my_report_file;
BEGIN REPORT;
test=# select * from ........
[SNIPPED RESULT]
test=# END REPORT;
END REPORT;


Regards
Gaetano Mendola