Thread: [HACKERS] Keep ECPG comment for log_min_duration_statement

[HACKERS] Keep ECPG comment for log_min_duration_statement

From
"Okano, Naoki"
Date:
Hi,

I'd like to make it easier to analyze slow queries in ECPG using 
log_min_duration_statement. Especially when DBA and C application developer 
is different, DBA can feedback slow embedded query to the developer without 
difficulty and mistakes.

When our customers log and look into slower queries in C programs with 
embedded SQL, they use log_min_duration_statement.Using this logging option, 
SQL statement slower than a threshold will be displayed, but comments won't. 
That's because the pre-compiler (ECPG) removes all the comments when the 
pre-compiler converts C with embedded SQL to normal C code.

Without comments, DBA has difficulty with identifying to which C code 
the slow query belongs. And the exact slow query issue cannot be reported 
to the developer.   
So, I'd like to modify ecpg command not to remove some specific comments.

[Use-cases]
Here is a scenario:
1) Writing comments to embedded C file a) Describe the detailed usage of SQL in each comment.  b) Allocating id to each
SQL.   - application developer need to create corresponding table between id       and the detailed description of SQL

2) DBA takes advantage of comments especially when: a) Similar comments are displayed here and there.     In such a
case,each comment plays a role as an identifier and makes it     easier for DBA to identify SQL he/she looking for. b)
DBAand C application developer are different.    DBA can tell an application developer which query is slow without
mistakes.
 
[Interface]
add a new option "--enable-parse-comment" to ecpg command.<usage> ecpg --enable-parse-comment ,..., prog.pgc
This option enables ecpg command to pass on block comments (/* 〜 */) to converted C file.
The conditions to enable processing "block comments" as follows:- a block comment can be used with SELECT, INSERT,
UPDATE,or DELETE- a block comment can be placed right after keywords: SELECT, INSERT, UPDATE, DELETE or With- other
thanthose above error will occur- line comment(--) are ignored, which is same as log output when logging libpq
application
 
[Example]
1)[Correct comment position] this comment position is right after SELECTEXEC SQL SELECT /* qid=3, at line 30 in
yourApp.ecpg*/ * INTO :C1, :C2 FROM T1 WHERE C1=1;
 
2)[Incorrect comment position] this comment position is bad(error will occur)EXEC SQL /* qid=3, at line 30 in
yourApp.ecpg*/ SELECT * INTO :C1, :C2 FROM T1 WHERE C1=1;
 

As far as I searched, there seems no discussion on this topic.
Please let me know if exists.

Regards,
Okano Naoki
Fujitsu



Re: [HACKERS] Keep ECPG comment for log_min_duration_statement

From
"Dr. Michael Meskes"
Date:
Hi,
   
> So, I'd like to modify ecpg command not to remove some specific
> comments.
> ...
> [Interface]
> add a new option "--enable-parse-comment" to ecpg command.
>  
>  <usage> ecpg --enable-parse-comment ,..., prog.pgc
>  
> This option enables ecpg command to pass on block comments (/* 〜 */)
> to converted C file.

The reason for not keeping the comments in the statement was simply to
make the parser simpler. If you added this feature, do we still see a
reason for keeping the old version? Or in other words, shouldn't we
make the "enable-parse-comment" version the default without a new
option?

Michael
-- 
Dr. Michael Meskes, Geschaeftsfuehrer/CEO
Tel.: +49 (0)2166 / 99 01 0
E-Mail: michael.meskes@credativ.com
IM: mme@jabber.credativ.com
PGP: BBBC 58B4 5994 CF9C CC56  BCDA DF23 DA33 9697 8EB3

credativ international GmbH, HRB Moenchengladbach 15543, 
Trompeterallee 108, 41189 Moenchengladbach, Germany
Geschaeftsfuehrung: Dr. Michael Meskes, Joerg Folz

======================================
Global:         http://credativ.com
Germany:        http://credativ.de
Netherlands:    http://credativ.nl
India:          http://credativ.in
UK:             http://credativ.co.uk
USA:            http://credativ.us
======================================



Re: [HACKERS] Keep ECPG comment for log_min_duration_statement

From
"Okano, Naoki"
Date:
Hi, 

Michael wrote:
> The reason for not keeping the comments in the statement was simply to
> make the parser simpler. If you added this feature, do we still see a
> reason for keeping the old version? Or in other words, shouldn't we
> make the "enable-parse-comment" version the default without a new
> option?
Thank you for your feedback! 

As I said in the first e-mail, there are some restrictions of comment position in my implementation. I am concerned
thatan error will occur in .pgc files users made in old version.
 
So, this feature should be a new option.

When the pre-compiler(ECPG) converts C with embedded SQL to normal C code, gram.y is used for syntactic analysis. I
needto change gram.y for comments in SQL statement. 
 
But I do not come up with better idea that gram.y is not affected.
If you are interested in my implementation in detail, please check the [WIP]patch I attached.

I am appreciated if you give me any idea or opinion.

Regards,
Okano Naoki
Fujitsu

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Attachment

Re: [HACKERS] Keep ECPG comment for log_min_duration_statement

From
Michael Meskes
Date:
> As I said in the first e-mail, there are some restrictions of comment
> position in my implementation. I am concerned that an error will
> occur in .pgc files users made in old version.
> So, this feature should be a new option.

I see, this makes sense.

> When the pre-compiler(ECPG) converts C with embedded SQL to normal C
> code, gram.y is used for syntactic analysis. I need to change gram.y
> for comments in SQL statement. 
> But I do not come up with better idea that gram.y is not affected.
> If you are interested in my implementation in detail, please check
> the [WIP]patch I attached.

I'm not sure we would want to change the backend parser for something
only used in ecpg. Actually I'm pretty sure we don't.

I can see two possible solutions. One would be to replace the parser
rules. Please see parse.pl for details. Some rules are already replaced
by ecpg specific ones. However, the more rules we replace the more
manual syncing effort we need for changes in gram.y.

The other option I can see, albeit without looking into details, is
allowing all comments and then testing it for the right syntax after
parsing. This could potentially also solve the above mentioned option
problem.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL



Re: [HACKERS] Keep ECPG comment for log_min_duration_statement

From
"Okano, Naoki"
Date:
Thank you for your kind advise!

Michael Meskes wrote:
> The other option I can see, albeit without looking into details, is
> allowing all comments and then testing it for the right syntax after
> parsing. This could potentially also solve the above mentioned option
> problem.
This idea sounds great. But I am not sure that I can understand it correctly.

I understood the concept of this idea as following. Is it right?
1. The parser ignores comments/*...*/ as usual. That is, parser does not   identify comments as a token. 
2. After parsing, we parse again only to extract comments.
3. comments are put back or forth in SQL statement.

Regards,
Okano Naoki
Fujitsu

Re: [HACKERS] Keep ECPG comment for log_min_duration_statement

From
Michael Meskes
Date:
> Michael Meskes wrote:
> > The other option I can see, albeit without looking into details, is
> > allowing all comments and then testing it for the right syntax
> > after
> > parsing. This could potentially also solve the above mentioned
> > option
> > problem.
> 
> This idea sounds great. But I am not sure that I can understand it
> correctly.
> 
> I understood the concept of this idea as following. Is it right?
> 1. The parser ignores comments/*...*/ as usual. That is, parser does
> not 
>    identify comments as a token. 

I guess it'd be easier to accept each comment as a token and then parse
the token  text afterwards.

> 2. After parsing, we parse again only to extract comments.

Not sure if we can do that without creating a lot of overhead.

Michael

-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL



Re: [HACKERS] Keep ECPG comment for log_min_duration_statement

From
"Okano, Naoki"
Date:
Michael Meskes wrote:
> > Michael Meskes wrote:
> > > The other option I can see, albeit without looking into details, is 
> > > allowing all comments and then testing it for the right syntax after 
> > > parsing. This could potentially also solve the above mentioned 
> > > option problem.
> >
> > This idea sounds great. But I am not sure that I can understand it 
> > correctly.
> >
> > I understood the concept of this idea as following. Is it right?
> > 1. The parser ignores comments/*...*/ as usual. That is, parser does 
> > not
> >   identify comments as a token.
>
> I guess it'd be easier to accept each comment as a token and then parse the token 
> text afterwards.
>
> > 2. After parsing, we parse again only to extract comments.
>
> Not sure if we can do that without creating a lot of overhead.
I see. Based on your advice, I try to make a patch. 
I will attach a patch when I finish it.

Regards,
Okano Naoki
Fujitsu