It turns out merging this is actually non-trivial. The new getBlock
query works only on Oracle (due to its use of native Oracle
date-isms). I wrote a revised PG query using EXTRACT, which works,
except that the PDL parser doesn't actually parse the query correctly,
because EXTRACT uses the 'from' reserved SQL keyword, and the parser
doesn't handle that case. For the record, here's the PG query (only
been tested in psql, not in the CMS due to the PDL parser issue):

query getBlock {
   QueueEntry queueEntry;
   do {
       select
           id, item_id, parent_id, task, destination,
           time_queued, time_last_failed, fail_count,
           sort_order, in_process
       from
           publish_to_fs_queue
       where
           host_id = :hostId
       and ( fail_count = 0
             or extract(epoch from time_last_failed) < (extract(epoch
from now()) - :queueEntryRetryDelay) )
       and ( :maximumFailCount < 0
              or fail_count < :maximumFailCount )
       } map {
               queueEntry.id = id;
               queueEntry.itemId = item_id;
               queueEntry.parentId = parent_id;
               queueEntry.task = task;
               queueEntry.destination = destination;
               queueEntry.timeQueued = time_queued;
               queueEntry.timeLastFailed = time_last_failed;
               queueEntry.failCount = fail_count;
               queueEntry.sortOrder = sort_order;
               queueEntry.inProcess = in_process;
           }
}

SQLParser.jj updated @40602 to address 'from' issue.

PG & Oracle versions checked in @40604. There was also some minor Java
porting work required for Rickshaw to avoid using deprecated API
(Web.getHosts() is now deprecated in Rickshaw).

I should have mentioned that 40604 includes integrations of 40509 and
40564, the PG fixes detailed above, and the Java updates.

In general, applying a function to an indexed column should be avoided
in where clauses. Unless the optimizer is able to rewrite the
condition to have nothing but the indexed column on one side of the
condition, the index cannot be used.

Instead of using EXTRACT, the condition could be written like this in
PostgreSQL:

time_last_failed < now() - (:queueEntryRetryDelay)::interval

For the above query, it makes no difference, because we have no index
on time_last_failed.

Hm. I can't get that syntax to work:

postgres=# select time_last_failed < now() - (10)::interval from
publish_to_fs_queue;
ERROR:  Cannot cast type integer to interval
postgres=# select time_last_failed < now() - '10 seconds'::interval
from publis\
h_to_fs_queue;
 ?column?
----------
(0 rows)

It appears you have to treat the queueEntryRetryDelay as a literal?

'10' would also work, as "seconds" is the default. I only tried it in
pgsql, so I did not notice that 10 and not '10' would be passed to the
database.

So QueueManager does:

     query.setParameter("queueEntryRetryDelay", s_retryDelay);

where s_retryDelay is an Integer. This doesn't work for PG, because
the interval cast requires a JDBC type of a string. So, two options
that I can see:

1. Stick with the functions in the where clause.
2. Do something like:

if (DbHelper.getDB() = DB_POSTGRES ) {
 query.setParameter("queueEntryRetryDelay", s_retryDelay.toString());
} else {
 query.setParameter("queueEntryRetryDelay", s_retryDelay);
}

Carsten: any opinion as to whether or not the presumable performance
gain of #2 is worth it?

(publish_to_fs_queue seems like it's a pretty small table (?), so
maybe functions are OK?)

We can stick with the functions, since we don't have an index on the
column, and the table is indeed small. My comment was more about
coding style than about this particular query.

danpb figured out a way to do the above (cast first to text, then to
interval). checked in @40627.

re: functions, i agree. i tried to avoid them, but i wasn't as clever
as you and dan in figuring out a way to not use them :).