Skip locked may not be working correctly

[saward]

For a separate project, using a similar query, it appeared that a second process trying to pull tasks off would sometimes get none back, instead of the next unlocked one. The following query, similar to queue's query, did not work well (the returning

WITH u AS (
  SELECT
    repository_id AS target_repository_id,
    format(full_path, path) AS final_path
  FROM site.repository
  JOIN site.provider p ON p.provider_id = provider_id_provider
  WHERE last_checked IS NULL or last_checked < Now() - INTERVAL '1 hour'
  ORDER BY last_checked asc
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE site.repository
SET last_checked = Now()
FROM u
WHERE repository_id = target_repository_id
RETURNING repository_id, final_path

Whereas this one worked as expected:

with selected AS (
  UPDATE site.repository
  SET last_checked = Now()
  WHERE repository_id = (
    SELECT
      repository_id
    FROM site.repository
    WHERE last_checked IS NULL or last_checked < Now() - INTERVAL '1 hour'
    ORDER BY last_checked, repository_id asc
    FOR UPDATE SKIP LOCKED
    LIMIT 1
  )
  RETURNING repository_id
)
SELECT
  repository_id AS target_repository_id,
  format(full_path, path) AS final_path
FROM site.repository
JOIN site.provider p ON p.provider_id = provider_id_provider
WHERE repository_id = (select repository_id from selected)