Thread: [HACKERS] Keep ECPG comment for log_min_duration_statement
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
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 ======================================
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
> 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
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
> 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
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