Hello,
Could someone tell me a single character that may never be included in a
SQL query except between single quotes?
I have written a script that will help me optimize the database
performance. It scans the postgresql log and transforms it into a
sequence like
123|"select foo from bar where id= or t='';"
...
The first field is the amount of time that the query on the right used.
The second field is a manipulation of the query, consisting of taking
all numbers out of the query, and all character constants like 'foo'
converted to ''.
I still have some little bugs, like 'foo''bar' being converted to ''''
instead of '', but that is not too annoying anyway for the purpose.
The purpose is sorting that file by the second field and adding up all
times on the first column, by request, I will get the times that took to
calculate every different type of request. That way, I can find which
requests are eating up most of my CPU times in the production
environment, in real conditions.
It helped me discover the lack of an index on a table that made a
specific request last for a lot more than it should, and thus loading
the system. It helped me discover that a specific request I make really
often is completely absurd and does eat 10 times more CPU than any other
type. That request type can easily be avoided by creating a table that
will hold the results, denormalizing the database, but improving
performance 1 to 1000, probably. I will check.
However, I have a specific request type like
select 'foo' || bar from foobar;
which gets translated into
123|"select '' || bar from foobar;"
which, when I add the first field, assuming that the request is on the
second one, gets converted into
12345|"select ''
as I am using, for the sake of simplicity, "cut", "sort", and "awk" to
post-process my results, taking | as my field separator.
Ouch... sorry for being so long...
So the QUESTION is:
Which character may I use for my field separator?
Thank you very much for your patience!!
Antonio Fiol
P.S. As soon as this script is finished, it might go to contrib,
maybe... Some people will find it useful. In the meantime, in the
unlikely case that you read this post, and that you are interested on
the script, please tell me and I will send it to you.