l10n_update.check.inc (starting at line 112) uses the following SQL statement to check local update files:

SELECT p.name, l.language AS lang, f.* FROM {l10n_update_project} p INNER JOIN {languages} l
LEFT JOIN {l10n_update_file} f ON p.name = f.project AND 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

That throws an error in Postgres.

The following equivalent SQL code works on Postgres (but likely not in Mysql, due to the subselect):

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

Comments

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.

As far as I understand it, the idea behind the inner join was to produce a carthesian product of the two tables, with no restriction: any project, any language.

Status:Reviewed & tested by the community» Active

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.

Status:Active» Reviewed & tested by the community

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";

Status:Active» Fixed

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 :)

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Issue tags:+PostgreSQL