Subject: Proposal: More structured logging

From: Ronan Dunklau <[email-masked]>
Date: Fri, 13 Aug 2021 15:23:03 +0200

Hello,

The logging system already captures a lot of information in the ErrorData. But 
at present there is no way for a log message authors to include more metadata 
about the log outside of the log message itself. For example, including the 
extension name which can be useful for filtering / dispatching log messages.
This can be done in the log message itself, but that requires specialized 
parsing in the log output. 

Even though among the available loggers in core, only the csv logger would be 
able to make sense of it, I feel it would be beneficial to add a tagging system 
to logs, by adding different (tag, value) combinations to a log entry, with an 
API similar to what we do for other ErrorData fields:

ereport(NOTICE,
 (errmsg("My log message")),
 (errtag("EMITTER", "MYEXTENSION")),
 (errtag("MSG-ID", "%d", error_message_id))
);

Please find attached a very small POC patch to better demonstrate what I 
propose.  

Third party logging hooks could then exploit those values to output them 
correctly. For example the json loggger written by Michael Paquier here: 
https://github.com/michaelpq/pg_plugins/tree/master/jsonlog, or  the 
seeminlgy-abandonned journald hook here: https://github.com/intgr/pg_journal 
could add those information in a structured way.

I think the pgaudit extension (or something similar) could also make good use 
of such a feature instead of dumping a CSV entry in the log file. 

As for Postgres own log messages, I'm sure we could find practical use cases 
for tagging messages like this.

On a related note, the only structured logger we have in-core is the CSV 
logger. Many users nowadays end up feeding the logs to journald either by 
capturing the stderr output with systemd, or by having syslog implemented by 
journald itself. Would there be any interest in having native journald support 
as a logging_destination ?

Best regards,

-- 
Ronan Dunklau

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 19 Aug 2021 09:04:30 -0400

>  (errtag("MSG-ID", "%d", error_message_id))

Interesting idea.   I agree this would be useful.

> Please find attached a very small POC patch to better demonstrate what I 

Seems like a good start.  I think a further step towards a committable
patch would include a test module that uses the new tagging
functionality.

> as a logging_destination ?

I don't know anything about journald, but I agree that we sorely need
some better way to emit structured log entries.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"The problem with the facetime model is not just that it's demoralizing, but
that the people pretending to work interrupt the ones actually working."
                                                           (Paul Graham)

---

From: Magnus Hagander <[email-masked]>
Date: Thu, 19 Aug 2021 16:14:25 +0200

> some better way to emit structured log entries.

+1. I think having the ability to natively send structured data to
journald would be useful.

Another thing I've noticed in more and more other products is to be
able to log as json, which is then later thrown into a central logging
system somewhere. Basically like csv, but with the schema defined in
each row. Yes, a lot more overhead, but some systems really do like to
consume json.... So when we're on the topic of more structured
logging...

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 19 Aug 2021 10:50:10 -0400

> consume json.... So when we're on the topic of more structured

Yeah, I was thinking in json logging too -- specifically thinking about
Fluentbit and similar tools.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

---

From: Ronan Dunklau <[email-masked]>
Date: Fri, 20 Aug 2021 11:31:21 +0200

> patch would include a test module that uses the new tagging

Please find attached the original patch + a new one adding a test module.
The test module exposes a function for generating logs with tags, and a log 
hook which format the tags in the DETAIL field for easy regression testing.

Next step would be to emit those tags in the CSV logs. I'm not sure what 
representation they should have though: a nested csv in it's own column ? A 
key => value pairs list similar to hstore ? A json object ? 

Also we should probably make this available to the client too, but once again 
the format of the tag field needs to be defined. Any opinion ? 

-- 
Ronan Dunklau

---

From: Ronan Dunklau <[email-masked]>
Date: Fri, 20 Aug 2021 11:35:29 +0200

> Fluentbit and similar tools.

Michael, your jsonlog module already fullfills this need. Is it something that 
should be merged into our tree ?

-- 
Ronan Dunklau

---

From: Michael Paquier <[email-masked]>
Date: Sat, 21 Aug 2021 09:37:26 +0900

> should be merged into our tree ?

Yes, there is nothing technically preventing to have this stuff in
core, of course, and that would even take care of the issues in
detecting if the piping protocol should be used or not.

Now, the last time this was proposed, there was a lot of drawback
because the presence of the JSON keys increases significantly the size
of the logs compared to CSV, and usually users care a lot about the
size of the log files.  I would support the presence of JSON format
for the logs in core, FWIW.

The module is here, for reference:
https://github.com/michaelpq/pg_plugins/tree/master/jsonlog
--
Michael

---

From: Ronan Dunklau <[email-masked]>
Date: Mon, 23 Aug 2021 08:58:50 +0200

> key => value pairs list similar to hstore ? A json object ?

I opted for a JSON representation in the CSV logs, please find attached v3 
which is the same thing as v2 with another patch for CSV log output.

> 
> Also we should probably make this available to the client too, but once
> again the format of the tag field needs to be defined. Any opinion ?

-- 
Ronan Dunklau

---

From: Magnus Hagander <[email-masked]>
Date: Mon, 23 Aug 2021 11:33:09 +0200

> for the logs in core, FWIW.

As long as it's optional, I don't think that drawback holds as an
argument. The same argument could be made against the cvs logs in the
first place -- they add information to every row that a lot of people
don't need. But it's optional. Leaving it up to the administrator to
choose whether they prefer the verbose-and-easier-to-parse-maybe
format or the more compact format seems like the right path for that.
I bet quite a few would actually choose json -- easier to integrate
with other systems (because newer systems love json), and unless
you're actually logging a lot of queries (which many people don't),
the size of the logfile is often not a concern at all.

In short, I would also support the presence of JSON log format in
core. (but as a proper log_destination of course -- or if it's time to
actually split that into a separaet thing, being one parameter for
log_destination and another for log_format)

---

From: Michael Paquier <[email-masked]>
Date: Wed, 25 Aug 2021 08:21:43 +0900

> format or the more compact format seems like the right path for that.

From a code perspective, and while on it, we could split a bit elog.c
and move the log entries generated for each format into their own
file.  That would be cleaner for CSV and JSON.  As a whole I don't
have an objection with moving the JSON format into core.  If one
proposes a patch, feel free to reuse the code from the module I have
released.

> the size of the logfile is often not a concern at all.

The module I publish on github to do this work is the most popular
thing of my plugin repo, FWIW.  It even gets bug reports, sometimes.

> log_destination and another for log_format)

What would you gain with a new parameter here?  I think that this
would be rather confusing and log_destination has been around for
years.
--
Michael

---

From: Sehrope Sarkuni <[email-masked]>
Date: Tue, 31 Aug 2021 10:46:30 -0400

> proposes a patch, feel free to reuse the code from the module I have

I had a patch that does exactly this from a few years ago. It started off
copying a bunch out of your json logging module and adds it as a new
"jsonlog" destination.

It needed some cleanup due to bit rot, but it now builds and works atop
master. I'll post it in its own thread.

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/

---

From: Michael Paquier <[email-masked]>
Date: Wed, 1 Sep 2021 09:28:02 +0900

> master. I'll post it in its own thread.

Thanks.
--
Michael

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 1 Sep 2021 09:35:19 +0200

> log_destination and another for log_format)

It would be useful if there was some kind of standardized schema for 
JSON logging, meaning what the keys should be named etc.  Then we don't 
need to make this all up from scratch, and there could be some 
integration with existing log analysis tools.

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 1 Sep 2021 09:36:50 +0200

>   (errtag("MSG-ID", "%d", error_message_id))

What are some more examples of where this could be used?  The extension 
name could be added more or less automatically to ereport() calls.  I 
don't know what the MSG-ID is supposed to be.  Are there other use cases?

---

From: Ronan Dunklau <[email-masked]>
Date: Wed, 01 Sep 2021 10:00:28 +0200

Le mercredi 1 septembre 2021, 09:36:50 CEST Peter Eisentraut a écrit :
> > The logging system already captures a lot of information in the ErrorData.
> > But at present there is no way for a log message authors to include more
> > metadata about the log outside of the log message itself. For example,
> > including the extension name which can be useful for filtering /
> > dispatching log messages. This can be done in the log message itself, but
> > that requires specialized parsing in the log output.
> > 
> > Even though among the available loggers in core, only the csv logger would
> > be able to make sense of it, I feel it would be beneficial to add a
> > tagging system to logs, by adding different (tag, value) combinations to
> > a log entry, with an API similar to what we do for other ErrorData
> > fields:
> > 
> > ereport(NOTICE,
> > 

Adding it automatically would be nice, but how would you go about that ?

In-core it would open up the possibility to split log messages into different 
fields, for example the different statistics reported in the logs by VACUUM / 
ANALYZE VERBOSE and make it easier to consume the output without having to 
parse the message. Parsing the message also means that any tool parsing it 
needs to either be aware of the locale, or to force the user to use a specific 
one.

Out-of-core, the same things could be done for extensions like pg_audit which 
logs structured information into the message itself, leaving the message 
format to be parsed at a later time.

-- 
Ronan Dunklau

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 8 Sep 2021 11:51:31 +0200

> needs to either be aware of the locale, or to force the user to use a specific

I think those messages would themselves have substructure.  For example, 
the current message

     "I/O timings: read: %.3f ms, write: %.3f ms\n"

might be

     {"I/O timings": {"read": ..., "write": ...}}

and that in turn is already part of a larger message.

So just having a single level of tags wouldn't really work for this.

---

From: Ronan Dunklau <[email-masked]>
Date: Thu, 09 Sep 2021 11:15:36 +0200

Le mercredi 8 septembre 2021, 11:51:31 CEST Peter Eisentraut a écrit :
> > In-core it would open up the possibility to split log messages into
> > different fields, for example the different statistics reported in the
> > logs by VACUUM / ANALYZE VERBOSE and make it easier to consume the output
> > without having to parse the message. Parsing the message also means that
> > any tool parsing it needs to either be aware of the locale, or to force
> > the user to use a specific one.
> 

I agree having a nested structure may feel more natural, but I don't think it 
would matter too much if we standardize on ini-style property names (ie, 
iotimings.read, iotimings.write and so on...)

Regards,

-- 
Ronan Dunklau

---

From: Justin Pryzby <[email-masked]>
Date: Wed, 29 Dec 2021 07:59:16 -0600

> Subject: [PATCH v3 2/3] Add test module for the new tag functionality.
...
> +test_logging(PG_FUNCTION_ARGS)
> +{
...
> +	 (errmsg("%s", message),
> +	  ({
> +		forboth(lk, keys, lv, values)
> +		{
> +			(errtag(lfirst(lk), "%s", (char *) lfirst(lv)));
> +		}})
> +	));

The windows build fails with that syntax.
http://cfbot.cputube.org/ronan-dunklau.html
https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.157923

> Subject: [PATCH v3 3/3] Output error tags in CSV logs
> +++ b/doc/src/sgml/config.sgml
> @@ -7370,6 +7371,7 @@ CREATE TABLE postgres_log
>    backend_type text,
>    leader_pid integer,
>    query_id bigint,
> +  tags jsonb
>    PRIMARY KEY (session_id, session_line_num)
>  );
>  </programlisting>

That's invalid sql due to missing a trailing ",".

You should also update file-fdw.sgml - which I only think of since we forgot in
to update it before e568ed0eb and 0830d21f5.  config.sgml should have a comment
as a reminder to do that.

-- 
Justin

---

From: Michael Paquier <[email-masked]>
Date: Thu, 7 Apr 2022 15:44:26 +0900

On Mon, Jan 31, 2022 at 05:46:29PM -0500, Greg Stark wrote:
> It looks strange to me that the errorTag struct has a "const char
> *tagname" but a "char *tagvalue". I think this is a side effect of the
> code and not actually a sensible way to define the struct. Surely they
> should both be const?

This review has not been answered, and it was around for two months
now.  I am marking this patch as returned with feedback.
--
Michael