Postgres Pro
Downloads
Home   >   mailing lists

Thread: log_destination = csvlog, line breaks interfere in analysis

log_destination = csvlog, line breaks interfere in analysis

From
Edson Richter
Date:
Dear all,

I've started collecting log information in CSV format, but I need a way
to customize it.
Problem is that I'm collecting slow statements, and the statements have
\n (newline) character, and the piece of

2012-09-17 14:25:33.150
BRT,"thedb","thedb",15660,"10.10.20.1:33485",5054d3ab.3d2c,1,"SELECT",2012-09-15
16:14:51 BRT,10/204822,0,LOG,00000,"duração: 505.494 ms  executar
<unnamed>: select A, B
     from C join D
          on C.id = D.c_id

And so on.

It's not possible to change the application (hours of testing after
changes will not be feasible for 2 million LOC).

How can I tell PostgreSQL that it should remove the line breaks (and
change them into whitespace character) from SQL statements? Or there is
an "regexp" I can use for this purpose?


Thanks,

Edson

Re: log_destination = csvlog, line breaks interfere in analysis

From
Edson Richter
Date:
...
>
Sorry, by mistake I've duplicated the mail.

Re: log_destination = csvlog, line breaks interfere in analysis

From
Craig Ringer
Date:
On 09/18/2012 03:21 AM, Edson Richter wrote:

> 2012-09-17 14:25:33.150
> BRT,"thedb","thedb",15660,"10.10.20.1:33485",5054d3ab.3d2c,1,"SELECT",2012-09-15
> 16:14:51 BRT,10/204822,0,LOG,00000,"duração: 505.494 ms  executar
> <unnamed>: select A, B
>      from C join D
>           on C.id = D.c_id
>
> And so on.

Yick. That doesn't seem very CSV-like. I'm very surprised Pg isn't
stripping newlines.

> It's not possible to change the application (hours of testing after
> changes will not be feasible for 2 million LOC).

No automated testing is in place?

It's not perfect, but it goes a long way toward improving confidence in
changes to big (or small) codebases.

--
Craig Ringer

Re: log_destination = csvlog, line breaks interfere in analysis

From
"Albe Laurenz"
Date:
Craig Ringer wrote:
> On 09/18/2012 03:21 AM, Edson Richter wrote:
>> 2012-09-17 14:25:33.150
>> BRT,"thedb","thedb",15660,"10.10.20.1:33485",5054d3ab.3d2c,1,"SELECT",2012-09-15
>> 16:14:51 BRT,10/204822,0,LOG,00000,"duração: 505.494 ms  executar
>> <unnamed>: select A, B
>>      from C join D
>>           on C.id = D.c_id
>>
>> And so on.

> Yick. That doesn't seem very CSV-like. I'm very surprised Pg isn't
> stripping newlines.

That's perfectly CVS-like.
The string is escaped with double quotes.
Double quotes in the query will be replaced with double double quotes.

So no CVS parser should have a problem with that.
I have tried it :^)

Yours,
Laurenz Albe

Re: log_destination = csvlog, line breaks interfere in analysis

From
Edson Richter
Date:
Em 18/09/2012 04:13, Albe Laurenz escreveu:
> Craig Ringer wrote:
>> On 09/18/2012 03:21 AM, Edson Richter wrote:
>>> 2012-09-17 14:25:33.150
>>> BRT,"thedb","thedb",15660,"10.10.20.1:33485",5054d3ab.3d2c,1,"SELECT",2012-09-15
>>> 16:14:51 BRT,10/204822,0,LOG,00000,"duração: 505.494 ms  executar
>>> <unnamed>: select A, B
>>>       from C join D
>>>            on C.id = D.c_id
>>>
>>> And so on.
>> Yick. That doesn't seem very CSV-like. I'm very surprised Pg isn't
>> stripping newlines.
> That's perfectly CVS-like.
> The string is escaped with double quotes.
> Double quotes in the query will be replaced with double double quotes.
>
> So no CVS parser should have a problem with that.
> I have tried it :^)
Yes, after testing with Apache OpenOffice 3.4.1, I see that it has been
correctly interpreted.
I'll post a bug request at Microsoft so they may (or may not) fix Excel
csv importer.

Regards,

Edson.
>
> Yours,
> Laurenz Albe
>
>