I found the reason the postgre code is/was failing the build testing (it was running a dying scheduled task), but this is not very important, more important was the reason why MySQL was not giving that error, so I dug a bit into the code and discovered something... strange.
The root cause is (now, obviously) the query that retrieves the scheduled tasks:
https://github.com/emanuele45/Dialogo/blob/master/sources/subs/ScheduledTasks.subs.php#L421
Using postgre and removing the LIMIT 1, I get this sequence:
1: approval_notification
2: auto_optimize
3: daily_maintenance
5: daily_digest
6: weekly_digest
7: fetchFiles
9: weekly_maintenance
11: remove_temp_attachments
12: remove_topic_redirect
13: remove_old_drafts
14: remove_old_followups
15: maillist_fetch_IMAP
During the tests the scheduled code is run three times, in theory it should pick the first three:
1: approval_notification
2: auto_optimize
3: daily_maintenance
Though! The real sequence observed in postgre is:
1: approval_notification
2: auto_optimize
3: maillist_fetch_IMAP
And no, at the third run, the sequence returned by the "unlimited" query is correct:
3: daily_maintenance
5: daily_digest
6: weekly_digest
7: fetchFiles
9: weekly_maintenance
11: remove_temp_attachments
12: remove_topic_redirect
13: remove_old_drafts
14: remove_old_followups
15: maillist_fetch_IMAP
That means, the third time the query is run, instead of picking the "correct" task (daily_maintenance) it picks the last.
Since the "strange" behaviour was appearing with phpPgAdmin too, I tried updating the indexes of the table after the first two runs, and... YAY! The expected result come out!
Sooo, apparently something may mess with postgre indexes.
Now, this wouldn't be so terrible, but if the same problem occurs in other cases as well, that could be an issue I think, there are several places we rely on LIMIT 1 to do something.
I started searching a bit, and discovered:
http://dba.stackexchange.com/questions/15067/reindexing-a-large-database
reading the word "transaction" I decided to try something: always begin a transaction before any query:
$this->db_transaction('begin', $connection);
$this->_db_last_result = @pg_query($connection, $db_string);
$this->db_transaction('commit', $connection);
And just to be sure to avoid loops, I changed db_transaction to:
if ($type == 'begin' && $this->_transaction_on_going === true)
return;
// Decide which connection to use
$connection = $connection === null ? $this->_connection : $connection;
if ($type == 'begin')
{
$this->_transaction_on_going = true;
return @pg_query($connection, 'BEGIN');
}
elseif ($type == 'rollback')
{
$this->_transaction_on_going = false;
return @pg_query($connection, 'ROLLBACK');
}
elseif ($type == 'commit')
{
$this->_transaction_on_going = false;
return @pg_query($connection, 'COMMIT');
}
return false;
Apparently that works quite well!!
The table is properly ordered, the index is not bloated and the scheduled tasks are run in the expected order!
Probably this is a bit "extreme", but I think we should use the transaction at least for the UPDATE, otherwise I feel we are going to have messy indexes all around the install...
Any opinion? I'm going to push the code live "ASAP", but if someone that knows PSQL better than me has any idea/concern I'm happy to hear!
P.S.
Most likely this is an issue with SMF too, but since report bugs may hurt devs feelings, I'll refrain from opening an issue (yes, this is intended to be snarky ).