From: | "Okano, Naoki" <okano(dot)naoki(at)jp(dot)fujitsu(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Keep ECPG comment for log_min_duration_statement |
Date: | 2017-02-13 04:12:50 |
Message-ID: | 0B4917A40C80E34BBEC4BE1A7A9AB7E27A7189@g01jpexmbkw05 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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) DBA and 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 than those 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 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.
Regards,
Okano Naoki
Fujitsu
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2017-02-13 05:10:34 | Re: Potential data loss of 2PC files |
Previous Message | Jim Nasby | 2017-02-13 03:25:09 | Re: Commit fest 2017-01 will begin soon! |