[Postgres] l10n_update.check.inc uses a SELECT statement that throws an error on Postgres

The inner join in the original query looks wrong to me. Try this:

SELECT p.name, l.language AS lang, f.* 
FROM {l10n_update_project} p 
LEFT JOIN {l10n_update_file} f 
  ON p.name = f.project AND l.language = f.language
INNER JOIN {languages} l
  ON l.language = f.language
WHERE p.status = 1 AND l.enabled = 1 AND (f.status IS NULL OR f.status = 1 AND f.last_checked < %d) 
ORDER BY last_checked

Corrected:

SELECT p.name, l.language AS lang, f.*
FROM {l10n_update_project} p
LEFT JOIN {l10n_update_file} f ON p.name = f.project
INNER JOIN {languages} l ON l.language = f.language
WHERE p.status = 1 AND l.enabled = 1 AND (f.status IS NULL OR f.status = 1 AND f.last_checked < %d)
ORDER BY last_checked

#2 code worked for me. No noticeable errors after cron run now.

Several cron jobs later and this:

pg_query(): Query failed: ERROR: duplicate key value violates unique constraint "l10n_update_file_pkey" in /var/www/includes/database.pgsql.inc on line 139.

query: INSERT INTO l10n_update_file (project, language, type, filename, fileurl, filepath, timestamp, version, status, last_updated, last_checked, import_date) VALUES ('addthis', 'ko', '', 'addthis-6.x-3.x-dev.ko.po', '', '', 0, '6.x-3.x-dev', 1, 0, 1293685227, 0) in /var/www/includes/common.inc on line 3538.

@vividgates, this error is not related. See #952102: Duplicate entry for long time.. and search other issues queue for "duplicate key" and you find many similar errors.

Thank you. I'll search as suggested.

I confirm this is a major problem for PostgreSQL.

Writing:

INNER JOIN {languages} l
LEFT JOIN {l10n_update_file}

is ambiguous, it should be replaced with #2.

Writing:

SELECT p.name, l.language AS lang, f.*
FROM {l10n_update_project} p
LEFT JOIN {l10n_update_file} f ON p.name = f.project
INNER JOIN {languages} l ON l.language = f.language
WHERE p.status = 1 AND l.enabled = 1 AND (f.status IS NULL OR f.status = 1 AND f.last_checked < %d)
ORDER BY last_checked

is not ambiguous.

The database optimizer will run the query several times and rewrite it.
So there is no reason why write ambiguous queries.

The correct line 112 is:

$sql = "SELECT p.name, l.language AS lang, f.*
  FROM {l10n_update_project} p
  LEFT JOIN {l10n_update_file} f ON p.name = f.project
  INNER JOIN {languages} l ON l.language = f.language
  WHERE p.status = 1 AND l.enabled = 1 AND (f.status IS NULL OR f.status = 1 AND f.last_checked < %d)
  ORDER BY last_checked";

Committed #1044652: [PostgreSQL] error in l10n_update_check as the Drupal 7 port of this patch then committed this patch as well. Had some attention issues here, so referenced both commits to this issue. Well, well. It landed at least :)