User defined data (meta)

In case some additional data generated by plugin or some client application needs to be stored along with the event, meta field should be used. It’s intended for storing key-value object of free form. Overall size of the object shouldn’t exceed limit of 2 MB.

The user is provided with:

• the ability to save json object along with the event;

• the ability to get the saved object;

• the ability to filter by values in the object.

The user undertakes:

• keep data consistent with given schemes; in case of a mismatch, PostgreSQL will not allow inserting a row with a type that cannot be added to the existing index (if any);

• migrate data if needed;

• build indexes;

• specify the data type when executing a request (by default, all values are assumed to be strings);

• mind out for field names; fields to be filtered by must not contain reserved keywords like :int, double underscores, special symbols, and so on.

Search

Search filters are passed as query parameters on get events. There are several conventions for specifying filters:

• to navigate through nested objects, use the dot (.), e.g. meta.user_info.temperature;

• to specify the comparison operator, use the suffix with double underscore (__eq, __like, __in, __gt, __lt, etc.), e.g. meta.user_info.temperature__gte;

• to specify the data type, use the suffix with colon (:string, :integer, :numeric), e.g. meta.user_info.temperature__gte:numeric.

Index building

For efficient lookups indexes should be built. User-defined data is stored in PostgreSQL as JSONB that supports GIN, B-tree and Hash indexes. For additional info see https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

If you are going to work with some custom plugins, you can build any indexes you are interested in, which are supported by the Postgresql JSONB.

If you intend to use the regular event search, there are functional restrictions on the indexes that can be used for search.

To filter by string field, the following B-tree index will be useful:

CREATE INDEX ix_event_meta_user_status ON event USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'status')));

Indexes for string fields to be involved must be built using jsonb_extract_path_text, since service queries to the database implement this format of the string field extract.

To filter by integer / float field that takes some range of values, the following B-tree indexes will be useful:

CREATE INDEX ix_event_meta_user_temperature ON event USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'temperature')::int));
CREATE INDEX ix_event_meta_user_temperature ON event USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'temperature')::float));

Indexes for integer / float fields to be involved must be built using jsonb_extract_path_text + cast, since service queries to the database implement this format of the integer / float field extract.

Troubleshooting

1. Why can’t save an event?

   PostgreSQL will not allow inserting a row with a type that cannot be added to the existing index. Suppose such an index is built:

   CREATE TABLE IF NOT EXISTS event_meta_test (id serial primary key, meta jsonb);
   CREATE INDEX IF NOT EXISTS ix_event_meta_test_status ON event_meta_test USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'status')::smallint));
   

   Then trying to insert the following data into the table

   INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"status": "unknown", "temperature": 36}}');
   

   will cause the following error: ERROR: invalid input syntax for type smallint: “unknown”.

   This, attempting to save inconsistent data will result in a 422 (Unprocessable) error from the server if wait_events_saving=1 query parameter is set, or background error otherwise.

   Solution options:

   • check the correctness of setting the meta for saving within the existing scheme;

   • revise the data scheme, apply migrations if needed, and build an appropriate index.

2. Why can’t build an index?

   Suppose the following data is already stored in the database:

   CREATE TABLE IF NOT EXISTS event_meta_test (id serial primary key, meta jsonb);
   INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"status": 0, "temperature": 36}}');
   INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"status": 5, "temperature": "normal"}}');
   

   Then trying to build the following index on the table

   CREATE INDEX IF NOT EXISTS ix_event_meta_test_temperature ON event_meta_test USING BTREE ((jsonb_extract_path_text(meta, 'user_info', 'temperature')::int4));
   

   will cause the following error: ERROR: invalid input syntax for type integer: “normal”.

   Thus, it will not be possible to create an incompatible index.

   Solution options:

   • revise the data scheme, and apply migrations if needed;

   • build another index (jsonb_extract_path_text with no cast).

3. Why can’t search with some filter?

   Suppose the following data is already stored in the database:

   CREATE TABLE IF NOT EXISTS event_meta_test (id serial primary key, meta jsonb);
   INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"group": "aliens"}}');
   INSERT INTO event_meta_test (meta) VALUES ('{"user_info": {"group": 1}}');
   

   Then trying to use meta.user_info.group:integer=1 query parameter that will result in the following database query

   SELECT * FROM event_meta_test WHERE jsonb_extract_path_text(meta, 'user_info', 'group')::int = 1;
   

   will cause the following error: ERROR: invalid input syntax for type integer: “aliens”.

   Thus, attempting to use incompatible filters will result in a 422 (Unprocessable) error from the server.

   Solution options:

   • revise the data scheme, and apply migrations if needed;

   • use another filter type, e.g. meta.user_info.group=1.