Обсуждение: how to amend SQL standard to add comments?

Hi.  Does anybody here know how to add comments to the SQL standard?
I believe this would benefit lots of people.

Situation:  When a system administrator or database administrator looks at
a gnarly SQL query chewing up system resources, there is no way to tell
by looking at the query server-side which application it came from, what its
purpose is, and who the author or responsible party is.

Data: in ANSI SQL standard, you can put single-line comments by preceeding
the line with a double-hyphen.  These comments will be thrown away by the
database client and the server will never see them.  Hence the metadata
(the data about the query itself) is lost.

I propose it'd be a benefit, in today's day of distributed and inter-dependent
systems, to pass that data along with the query so that it could be used
in troubleshooting if needed.

An SQL comment may look something like

SELECT STUDENT_ID from STUDENTS
  WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe'
  COMMENT 'Query Author: Bob Programmer.   Purpose: Pull the student ID
                      number, we'll need it to enroll the student for classes.';

or

SELECT STUDENT_ID FROM STUDENTS
  WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe'
  COMMENT 'Get the Student ID.  Bob Programmer, 9 April 2012.
registration.py, line 612';

In the second example, the program that queries the DB can dynamically
identify where in the program the query-generating code is located.

I'd like to propose such capability be added to the SQL standard...  is anybody
on this list involved with the SQL standard?   What do you think about the
idea?

Best regards,
Aleksey Tsalolikhin

Aleksey Tsalolikhin wrote on 06.05.2012 19:24:
> Situation:  When a system administrator or database administrator looks at
> a gnarly SQL query chewing up system resources, there is no way to tell
> by looking at the query server-side which application it came from, what its
> purpose is, and who the author or responsible party is.
>
> Data: in ANSI SQL standard, you can put single-line comments by preceeding
> the line with a double-hyphen.  These comments will be thrown away by the
> database client and the server will never see them.  Hence the metadata
> (the data about the query itself) is lost.
>
> I propose it'd be a benefit, in today's day of distributed and inter-dependent
> systems, to pass that data along with the query so that it could be used
> in troubleshooting if needed.
>
> An SQL comment may look something like
>
> SELECT STUDENT_ID from STUDENTS
>    WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe'
>    COMMENT 'Query Author: Bob Programmer.   Purpose: Pull the student ID
>                        number, we'll need it to enroll the student for classes.';
>

You can use multi-line comments with /* .. */ to send this information to the server:

SELECT /* Query Author: Bob Programmer.
           Purpose: Pull the student ID number, we'll need it to enroll the student for classes */
      STUDENT_ID
from STUDENTS
WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe';

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> Aleksey Tsalolikhin wrote on 06.05.2012 19:24:
>> Situation:  When a system administrator or database administrator looks at
>> a gnarly SQL query chewing up system resources, there is no way to tell
>> by looking at the query server-side which application it came from, what its
>> purpose is, and who the author or responsible party is.
>>
>> Data: in ANSI SQL standard, you can put single-line comments by preceeding
>> the line with a double-hyphen.  These comments will be thrown away by the
>> database client and the server will never see them.  Hence the metadata
>> (the data about the query itself) is lost.
>>
>> I propose it'd be a benefit, in today's day of distributed and inter-dependent
>> systems, to pass that data along with the query so that it could be used
>> in troubleshooting if needed.
>>
>> An SQL comment may look something like
>>
>> SELECT STUDENT_ID from STUDENTS
>> WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe'
>> COMMENT 'Query Author: Bob Programmer.   Purpose: Pull the student ID
>> number, we'll need it to enroll the student for classes.';

> You can use multi-line comments with /* .. */ to send this information to the server:

Quite aside from the fact that the standard already has two perfectly
good comment syntaxes, ISTM that most of the gripe here is about
behaviors that are outside the standard's scope.

(1) "there is no way to tell by looking at the query server-side which
application it came from" ... Really?  PG has application_name, and
I suspect other SQL databases make more info available than that about
where a query came from.  The text of the query isn't the only available
communication channel, nor necessarily the best one.

(2) "These comments will be thrown away by the database client" ... how
many cases have you checked?  psql does discard single-line comments,
which is something maybe we should change; but I'm not aware of any
other PG-related clients that do that, and it doesn't seem very likely
that clients of other SQL databases do it either.  Parsing out comments
is a complex, expensive thing and there's seldom a good reason to do it
client-side.

            regards, tom lane

On Sun, May 6, 2012 at 10:49 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>
> You can use multi-line comments with /* .. */ to send this information to
> the server:
>
> SELECT /* Query Author: Bob Programmer.
>          Purpose: Pull the student ID number, we'll need it to enroll the
> student for classes */
>     STUDENT_ID
> from STUDENTS
> WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe';

Thank you!!!!  Perfect.  Documenting queries is going to improve our system
tremendously.  Thank you so much, Thomas!


On Sun, May 6, 2012 at 12:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Quite aside from the fact that the standard already has two perfectly
> good comment syntaxes

My bad.  I tested using psql and --, and when I saw that the comment was
not even sent to the database (using tcpdump), I assumed /* */ would behave
the same.

Thank you for letting me know about %a in  log_line_prefix to log the
application_name.  It's not available in 8.4 but I look forward to using it
after we upgrade to 9 later this year.

> (2) "These comments will be thrown away by the database client" ... how
> many cases have you checked?

Only psql.

Sorry for the arrogance of my original post.  I was a bit tired and agitated.

Thank you very much for the help!  Made my day.  What a great group.

Yours fondly,
Aleksey