Add a trigger for updated_on
in the catalog database
#4520
Labels
💻 aspect: code
Concerns the software code in the repository
🧰 goal: internal improvement
Improvement that benefits maintainers, not users
🟨 priority: medium
Not blocking but should be addressed soon
🧱 stack: catalog
Related to the catalog and Airflow DAGs
Problem
Unless explicitly set, the
updated_on
column for the primary media tables in the catalog is modified when a record is updated via ingestion, but is not modified when any other update (e.g. a `batched_update) is performed. This can create situations where a record is inaccurately assumed to have not been updated even it has been by some other process.Description
We should add a trigger for the following columns which automatically sets the value to the current timestamp:
openverse/docker/upstream_db/0003_openledger_image_schema.sql
Line 19 in 4798866
openverse/docker/upstream_db/0006_openledger_audio_schema.sql
Line 20 in 4798866
This can be done using the
moddatetime
extension (which I've verified is available on the catalog database). Here's an example from StackExchange for how it might look:The documntation for the
updated_on
column will also need to be updated with this change:openverse/catalog/utilities/media_props_gen/docs/media_props.md
Lines 23 to 30 in efc2a0b
Note
Once this is added, we'll need to remove the code that explicitly sets the
updated_on
column.This may involve changes to the following areas:
TimestampColumn
update strategy definitionAlternatives
We could rely on adding a step to update
updated_on
for every source of updates (see #4460 as an example). This would require more maintenance and touch points than modifying the tables themselves to perform this update for us.Additional context
This has been discussed a few previous times, see: #4460, #4366 (comment), #4429 (comment).
The text was updated successfully, but these errors were encountered: