_job_start_time precision differs by backend: datetime(3) on MySQL, microsecond timestamp on PostgreSQL

[dimitri-yatsenko]

_job_start_time is declared with millisecond precision on MySQL and default (microsecond) precision on PostgreSQL. The same logical attribute has a different type on each backend, and the spec documents only the MySQL one.

Observed

Declared on a dj.Computed table with config.jobs.add_job_metadata on, read back from the server:

	declared type	original_type
MySQL 8.0	datetime(3)	None
postgres:15	timestamp without time zone	None

PostgreSQL's unqualified timestamp carries six fractional digits, so this is an inconsistency rather than data loss — but reference/specs/job-metadata.md states "datetime(3) provides millisecond precision" as a property of the feature, and that is true on only one backend.

Cause

adapters/postgres.py hand-writes the column:

'"_job_start_time" timestamp DEFAULT NULL'

where adapters/mysql.py hand-writes datetime(3). Neither goes through the type system, so the two strings are maintained independently and have drifted.

The mapping that would keep them aligned already exists. core_type_to_sql("datetime(3)") yields datetime(3) on MySQL and timestamp(3) on PostgreSQL:

format_column_definition(name="_job_start_time",
                         sql_type=adapter.core_type_to_sql("datetime(3)"), ...)

MySQL       ->  `_job_start_time` datetime(3) DEFAULT NULL COMMENT ":datetime(3):"
PostgreSQL  ->  "_job_start_time" timestamp(3) DEFAULT NULL

So the correct PostgreSQL type is already derivable; the hand-written string is what loses it.

original_type is also lost

None on both backends for every job-metadata column, because the hand-written SQL carries no :type: comment.

This is cosmetic, not functional — correcting an earlier version of this issue. Nothing reads original_type for a hidden attribute: jobs.py:216 iterates target_pk, and hidden attributes are never in the primary key; table.py:1400 sits inside describe(), which excludes them. Decoding derives json, uuid, numeric and dtype from the SQL type (heading.py:499-503), so these attributes behave correctly as they are.

Worth restoring only because it comes free with the fix below. The precision discrepancy above is the actual defect.

Fix

Declare the three job-metadata columns through core_type_to_sql + format_column_definition, as _singleton already does (declare.py:538-551) and as _prov does after #1565. That corrects the precision and restores original_type in one change.

This is subsumed by the uniform declaration proposed in #1567, but it is a live inconsistency and can be fixed on its own without waiting for that.

Note on existing tables

Changing the declaration affects newly declared tables only. Tables already created keep whatever their backend gave them; migrate.add_job_metadata_columns would need the same treatment if existing deployments are to converge.