General events

In case an event with some custom structure generated by video analytics, plugin or some client application needs to be stored, general events mechanism should be used. It’s intended for storing the events of free structure. Overall size of the general event shouldn’t exceed limit of 2 MB.

Since general events have a free-form structure, they have to be separable by type, which should be specified when saving the event. When saving an general event, some common info should also be provided (e.g., the time the event was created, the time the event ended, the account the event owned by). Despite the fact the general event structure is free, it may contain a certain set of platform-specific fields (e.g. location, stream ID, track ID), the search for which can be performed more efficiently due to the optimization of storing this data in the database. See create general events for details.

The user is provided with:

• the ability to save event of free structure;

• the ability to get the saved event;

• the ability to filter by common info of general event (e.g., event_type, event_create_time, event_end_time, account_id);

• the ability to filter by optional platform-specific fields of general event (e.g., location, stream_id, track_id);

• the ability to filter by custom structure content fields.

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 partial indexes according to event types;

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

• mind out for custom structure 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 general events. There are several conventions for specifying filters:

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

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

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

Index building

For efficient lookups indexes should be built. Indexes have to be partial, that is, built for a specific general event type. Custom event 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 partial B-tree index will be useful:

CREATE INDEX ix_general_event_user_status ON general_event USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'status'))) WHERE event_type = 'my-shiny-event';

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 partial B-tree indexes will be useful:

CREATE INDEX ix_general_event_user_temperature ON general_event USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'temperature')::int)) WHERE event_type = 'my-shiny-event';
CREATE INDEX ix_general_event_user_temperature ON general_event USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'temperature')::float)) WHERE event_type = 'my-shiny-event';

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 general_event_test (id serial primary key, event_type varchar, event jsonb);
   CREATE INDEX IF NOT EXISTS ix_geneal_event_test_status ON general_event_test USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'status')::smallint)) WHERE event_type = 'my-shiny-event';
   

   Then trying to insert the following data into the table

   INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"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 event custom data 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 general_event_test (id serial primary key, event_type varchar, event jsonb);
   INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"user_info": {"status": 0, "temperature": 36}}');
   INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"user_info": {"status": 5, "temperature": "normal"}}');
   

   Then trying to build the following index on the table

   CREATE INDEX IF NOT EXISTS ix_general_event_test_temperature ON general_event_test USING BTREE ((jsonb_extract_path_text(event, 'user_info', 'temperature')::int4)) WHERE event_type = 'my-shiny-event';
   

   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 general_event_test (id serial primary key, event_type varchar, event jsonb);
   INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"user_info": {"group": "aliens"}}');
   INSERT INTO general_event_test (event_type, event) VALUES ('my-shiny-event', '{"user_info": {"group": 1}}');
   

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

   SELECT * FROM general_event_test WHERE jsonb_extract_path_text(event, '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. event.user_info.group=1.