Re: Change the csv log to 'key:value' to facilitate the user to understanding and processing of logs

On 3/15/22 10:12, Andrew Dunstan wrote:
> 
> On 3/15/22 09:30, hubert depesz lubaczewski wrote:
>> On Tue, Mar 15, 2022 at 09:31:19AM +0800, lupeng wrote:
>>> Dear Hackers
>>> When I audit the Postgresql database recently, I found that after
>>> configuring the log type as csv, the output log content is as follows:
>>> "database ""lp_db1"" does not exist",,,,,"DROP DATABASE
>>> lp_db1;",,"dropdb, dbcommands.c:841","","client backend",,0 It is very
>>> inconvenient to understand the real meaning of each field. And in the
>>> log content，" is escaped as "", which is not friendly to regular
>>> expression matching. Therefore, I want to modify the csv log function,
>>> change its format to key:value, assign the content of the non-existing
>>> field to NULL, and at the same time, " will be escaped as  \" in the
>>> log content. After the modification, the above log format is as
>>> follows: Log_time:"2022-03-15 09:17:55.289
>>>
CST",User_name:"postgres",Database_name:"lp_db",Process_id:"17995",Remote_host:"192.168.88.130",Remote_port:"38402",Line_number:
>>> "622fe941.464b",PS_display:"DROP
>>> DATABASE",Session_start_timestamp:"2022-03-15 09:17:53
>>> CST",Virtual_transaction_id:"3/2",Transaction_id:"NULL",Error_severity:"ERROR",SQL_state_code
>>> :"3D000",Errmessage:"database \"lp_db1\" does not
>>> exist",Errdetail:"NULL",Errhint:"NULL",Internal_query:"NULL",Internal_pos:"0",Errcontext:"NULL",User_query
>>> :"DROP DATABASE lp_db1;",Cursorpos:"NULL",File_location:"dropdb,
>>> dbcommands.c:841",Application_name:"NULL",Backend_type:"client
>>> backend",Leader_PID:"0",Query_id:"0"
>> CSV format is well documented
>> (https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG).
>>
>> If you want named fields you can wait for pg15 and its jsonlog
>> (https://www.depesz.com/2022/01/17/waiting-for-postgresql-15-introduce-log_destinationjsonlog/).
>>
>> I, for one, wouldn't want to have to deal with field names repeated in
>> every single record.
>>
> 
> Indeed. And even if this were a good idea, which it's not, it would be
> 15 years too late.

Also, the CSV format, while human readable to a degree, wasn't meant for 
direct, human consumption. It was meant to be read by programs and at 
the time, CSV made the most sense.


Regards, Jan