Mechanism for incremental config updates

[julianbrost]

Periodically fetching the full configuration from the database and searching it for changes would become infeasible quickly. Therefore, we will require a mechanism for getting notified about individual configuration changes in the database.

The exact details of the implementation are up to discussion, some proposals follow (I currently favor option 3).

Option 1: Web notifies the daemon

The backend daemon already needs a listener for events from sources, it could just add a new endpoint that Web could ping every time it updates something.

Pros:

• No added latency as no polling is required.
• No cleanup needed.

Cons:

• Separate from the database means this connection could fail independently adding new failure scenarios.

Option 2: Update information in affected tables

Add columns like changed_at/deleted_at to affected tables similar to system-versioned tables and regularly poll for rows with a higher value than the last seen one.

Pro:

• Tracked in the same row where the change happens, therefore reliable as the change is signaled with the same query that does the actual change.

Cons:

• Polling required, with a query per table that is monitored for changes.
• If timestamp are used for the value, there might be problems with diverging system times, especially if there are multiple hosts serving the web interface and they just always use now(), monotonic values aren't guaranteed. On the other hand using a sequence number would be annoying with MySQL as there seems to be no explicit sequence type and only one auto-increment column seems to be supported.

Option 3: Change queue table in the database

Create a table like config_change (id bigserial, table text, foreign_pk bigint). Each time Web creates/updates/deletes a row, it inserts a row into this table referring the affected row.

Pros:

• Notification happens via the database, thus no extra connection that can fail. Notification can happen inside a transaction with the actual change.
• Only a single table has to be polled.
• Table could be extended with columns time, username, diff and we would have a config change/audit log.
• Compared to the previous suggestion, the incrementing id column would provide reliable sequencing of changes.

Cons:

• Polling required, however with just one query watching for changes in all tables.
• Cleanup of the table needed (unless we actively want this as a log).

[julianbrost]

40 should to the job for the prototype, a later version will need a more sophisticated mechanism though.

[oxzi]

We just discussed this issue again. Since its emerge, the current periodic synchronization was introduced by @julianbrost, commented above. It runs on a secondly basis, fetching the whole configuration every time. Even as it is questionable how big end user's configuration might become, this approach does not scale and introduces unnecessary load.

Regarding the current Go code, a variant of the second suggestion would be the easiest to implement. All configuration tables would require two new columns - changed_at and deleted_at - with an index each. Each change from Icinga Notifications Web would have to alter those columns and Icinga Notifications would then be able to fetch only those rows with a field greater than the last known. In addition, this allows addressing #179.

Another thought we discussed was an audit log or journal for the configuration. As the config lives within the database, both (some) users and developers might want to track its changes. The third option, or a variant thereof, would allow creating a configuration audit log. However, such an audit log would require a full diff to be useful as otherwise it is a half-baked feature and users will complain.

Regardless of which way we are going to choose, a garbage collection will become necessary. The second option is going to pile up deleted_at objects and the third option will collect every change.

@nilmerg: What do you think? I am currently in favor of the second option, as outlined above. This should also be easier to implement in Icinga Notifications Web.

[julianbrost]

Regardless of which way we are going to choose, a garbage collection will become necessary. The second option is going to pile up deleted_at objects and the third option will collect every change.

These rows still serve the purpose of providing additional information for old history entries. So I wouldn't say that garbage collection is immediately necessary with this variant. But that probably also depends a bit on the individual tables and whether these are actually referenced from the history.

One question will then arise: do we want that for every config table or just for the main ones? Like for example, if a user is added to a group, there would be two options: track each membership individually with changed_at/deleted_at columns or simply view it as a change of the group and track the change only there.

[oxzi]

While the OP mentioned temporal tables or system tables, which might look promising, those are not available in PostgreSQL. The SQL:2011 revision specifies system-versioned tables as implemented in MariaDB, but PostgreSQL currently only has external extensions therefore.

[nilmerg]

I don't really care. Or rather, Web doesn't. Whether we'll need to update two additional columns or insert a new row in a new table, isn't a big difference.

[oxzi]

Then we're aiming at a variant of the second version with a changed_at column and a boolean deleted column. In a first naive idea, this should affect all tables listed here: https://github.com/Icinga/icinga-notifications/blob/8ca9efde9db7397ebc0bd74b1116bd05e5727322/internal/config/runtime.go#L57-L66