[Bug]: pgjsonb get_fun picks wrong "latest return" when jid format is not lexicographically sortable

[co-cy]

What happened?

Description

salt/returners/pgjsonb.py:get_fun chooses the latest return per minion with:

sql = """SELECT s.id, s.jid, s.full_ret
        FROM salt_returns s
        JOIN (SELECT MAX(jid) AS jid
              FROM salt_returns GROUP BY fun, id) max
        ON s.jid = max.jid
        WHERE s.fun = %s
        """

MAX(jid) is the lexicographic maximum of the jid column, not the time-latest return. The two coincide for Salt's default jid format YYYYMMDDHHMMSSffffff and the nano variant — both are timestamp-formatted strings of equal length, so lexicographic ordering matches chronological. The algorithm silently breaks for any deployment where that assumption does not hold:

• An operator overrides master_job_cache.gen_jid with a custom scheme — random UUIDs, snowflake ids, hash-based identifiers — none of which sort lexicographically as timestamps.
• External publishers (salt-api, salt-ssh, runners, orchestrate) call prep_jid(passed_jid="...") with a custom jid string. master.py:_prep_jid forwards clear_load["jid"] if the caller provided one.
• A master spans a jid_format config change. salt_returns then holds rows under both old and new formats; comparing them lexicographically gives meaningless results.

In all of those cases MAX(jid) returns a row that is not the time-latest, and get_fun returns the wrong full_ret per minion. The function appears to work — no exception, no warning — and the operator just gets the wrong answer.

Setup

• on-prem machine
• classic packaging
• onedir packaging

Any deployment that uses master_job_cache: pgjsonb and either overrides the jid format or accepts custom-jid publishes from external tools.

Steps to Reproduce the behavior

1. Configure master_job_cache: pgjsonb.
2. Publish a job for fun = test.ping on minion minion-1 with a custom jid: salt --jid="abc-001" minion-1 test.ping.
3. A while later, publish another test.ping on the same minion with another custom jid that sorts lexicographically smaller: salt --jid="000-002" minion-1 test.ping.
4. Call pgjsonb.get_fun("test.ping") from a runner or salt-call.
5. The function returns the row for jid abc-001 (lexicographically larger) even though 000-002 was the more recent execution.

Expected behavior

get_fun returns the most recently inserted return per minion, regardless of how the jid was formatted.

Additional context

The accompanying PR replaces the lexicographic-max algorithm with explicit ordering by alter_time DESC (which Postgres populates from DEFAULT NOW() and therefore reflects insertion order regardless of jid format), picking one row per minion with DISTINCT ON:

SELECT DISTINCT ON (id)
       id, jid, full_ret
FROM salt_returns
WHERE fun = %s
ORDER BY id, alter_time DESC

Performance note: there is no index on salt_returns.alter_time in the documented schema, so this remains a sequential scan — same as the previous MAX(jid) GROUP BY form. The lack of an alter_time index, along with several other schema observations, will be tracked as a separate follow-up.

Builds on #69063 (which removed the MySQL-style backtick quoting from this function) and should be merged after it.

Type of salt install

Official deb

Major version

3006.x, 3007.x

What supported OS are you seeing the problem on? Can select multiple. (If bug appears on an unsupported OS, please open a GitHub Discussion instead)

debian-11, debian-12

salt --versions-report output

salt --versions-report
Salt Version:
          Salt: 3007.13

Python Version:
        Python: 3.10.19 (main, Feb  5 2026, 07:05:38) [GCC 11.2.0]

Dependency Versions:
          cffi: 2.0.0
      cherrypy: unknown
  cryptography: 42.0.5
      dateutil: 2.8.2
     docker-py: Not Installed
         gitdb: Not Installed
     gitpython: Not Installed
        Jinja2: 3.1.6
       libgit2: 1.9.1
  looseversion: 1.3.0
      M2Crypto: Not Installed
          Mako: Not Installed
       msgpack: 1.0.7
  msgpack-pure: Not Installed
  mysql-python: Not Installed
     packaging: 24.0
     pycparser: 2.21
      pycrypto: Not Installed
  pycryptodome: 3.19.1
        pygit2: 1.18.2
  python-gnupg: 0.5.2
        PyYAML: 6.0.1
         PyZMQ: 25.1.2
        relenv: 0.22.3
         smmap: Not Installed
       timelib: 0.3.0
       Tornado: 6.5.4
           ZMQ: 4.3.4

Salt Extensions:
 saltext.vault: 1.5.0

Salt Package Information:
  Package Type: onedir

System Versions:
          dist: debian 12.13 bookworm
        locale: utf-8
       machine: x86_64
       release: 6.12.73+deb12-amd64
        system: Linux
       version: Debian GNU/Linux 12.13 bookworm