Can QuestDB automatically track/timestamp when an existing row is modified

Community
1

I would like to track when an existing row is updated with a new column value.

For example, table:

CREATE TABLE mytable (
	ts_event TIMESTAMP,
	v_1 LONG,
	v_2 INT,
	ts_created TIMESTAMP,
	ts_modified TIMESTAMP
	)
		timestamp(ts_event)
  PARTITION BY WEEK
  DEDUP UPSERT KEYS(ts_event);

contains row:

ts_event             | v_1 | v_2 | ts_created           | ts_modified
2022-01-01T12:00:00Z | 3   | 15  | 2023-02-01T08:00:00Z | NULL

Can I configure the table so that if an existing ‘ts_event’ row is updated with any new values, QuestDB automatically fills in the ‘ts_modified’ column with the now() timestamp (e.g.: 2024-03-02T09:00:00Z), like so:

ts_event             | v_1 | v_2 | ts_created           | ts_modified
2022-01-01T12:00:00Z | 4   | 15  | 2023-02-01T08:00:00Z | 2024-03-02T09:00:00Z

Note, do not update the ‘ts_modified’ column if the row is unchanged.
I am using the Python ingress client, e.g.: questdb.Sender() dataframe() method to ingest data into the table.

Thanks in advance,
-Lars

2

Hi @Lars_m ,

This is not possible in the way you’d like. You would need to use the updated timestamp as the designated timestamp, and use the ServerTimestamp feature in the Python client to generate this field.

This will make queries less efficient since the table will be clustered by a different timestamp to your current one.

However, work is in progress to bring materialised views. It may be that auditing columns and/or feedback on row updates could be part of a future iteration of this.

So in summary - you’d need to manage this on the application side for now.

3

Thanks @nwoolmer for the reply & feedback. Per the docs it looks like ServerTimestamp is on its way to being deprecated, so I’ll avoid that route.
I assumed I’d probably need to implement this on the application side, but though I would ask just in case Qdb has (or road-mapped) the ability to handle logic like this in the db engine.