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

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.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com