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.

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) DBA and C application developer are different.
     DBA can tell an application developer which query is slow without mistakes.
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 than those above error will occur
 - line comment(--) are ignored, which is same as log output when logging libpq 
1)[Correct comment position] this comment position is right after SELECT
 EXEC 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.

Okano Naoki

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

Reply via email to