How to handle empty values produced by NLog's all-event-properties layout renderer?

Question

I'm configuring NLog's database target for PostgreSQL and I want to use hstore column to store all event properties. I'm using all-event-properties layout renderer for that.

Here's my current target configuration:

<target name="database"
        xsi:type="Database"
        dbProvider="Npgsql.NpgsqlConnection, Npgsql"
        connectionString="Server=localhost;Port=5432;Database=db;User Id=postgres">
  <install-command>
    <text>
      CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;
      CREATE TABLE IF NOT EXISTS logs (
      id bigserial PRIMARY KEY,
      moment timestamp NOT NULL,
      level text NOT NULL,
      message text NOT NULL,
      args hstore NOT NULL
      );
    </text>
  </install-command>
  <commandText>
    INSERT INTO logs (moment, level, message, args)
    VALUES (@moment::timestamp, @level, @message, @args::hstore);
  </commandText>
  <parameter name="@moment" layout="${longdate:universalTime=true}" />
  <parameter name="@level" layout="${level}" />
  <parameter name="@message" layout="${message}" />

  <!-- (!) Here's where the format of event properties adopted for hstore syntax (!)-->
  <parameter name="@args" layout="${all-event-properties:format=[key]=>[value]" />

</target>

It works for logs without parameters, but with parameters the following SQL is generated:

INSERT INTO logs (moment, level, message, args) VALUES (
'2019-05-24 18:44:49.7494'::timestamp,
'Info',
'Message text here',
'a=>1, b=>2, c=>3, EventId_Id=>555, EventId_Name=>, EventId=>555'::hstore);
--                           no value here--------^

Which is invalid syntax, because PostgreSQL syntax needs either NULL keyword for value or not to include the key at all:

Here's the error:

ERROR:  Syntax error near 'E' at position 51

Which exactly reproduces when I'm executing this by hand and goes away when I remove EventId_Name=>, key. So, I'm pretty sure I need somehow to skip/handle those empty key to be happy.

I'm also OK with any solution in SQL, but can't find a simple and robust approach to handle those values.

Answer 1

I don't fully understand what the syntax should be in this case, but you can fix this anyway with a custom layout renderer

create:

// register ${all-properties-postgresql}
LayoutRenderer.Register("all-properties-postgresql", 
                       (logEvent) => SerializeProps(logEvent.Properties));

You need to write SerializeProps to serialize the LogEventInfo.Properties - see API docs