postgresql中使用时间函数需要注意的一个问题

时间：2010-08-30 来源：osdba

项目中有段ESQL代码是轮询方式查数据表中，是否有符合时间限制的数据，限制条件是当前2小时前的数据，如果没有，就sleep固定时间后，再轮询。示例代码如下：




            int hasAvailableData(String t

项目中有段ESQL代码是轮询方式查数据表中，是否有符合时间限制的数据，限制条件是当前2小时前的数据，如果没有，就sleep固定时间后，再轮询。示例代码如下：

int hasAvailableData(String timeBefore)
{
EXEC SQL BEGIN DECLARE SECTION;
    int recs=0;
    char * timeStr = timeBefore
EXEC SQL END DECLARE SECTION;

    EXEC SQL SELECT count(*) into :recs from datatable where flag=0 and(occurtime<localtimestamp - interval :timeStr);
    if ( 0 > sqlca.sqlcode )
    {
        return -1;
    }
    return recs;
}

int processData()
{
    while(1)
    {
        if (hasAvailableData("2 hours") == -1) return -1;
        if (recs > 0) break;

        sleep(interval);
    }

    doit();

    return 0;
}

这段代码使用了postgres的日期函数localtimestamp取当前时间，减去interval `2 hours'作为查询条件，查看是否有合适的数据。在第一次进processData函数时，数据库有数据的情况下，处理过程正常。但是当进入processData循环时，如果开始没有合适数据，但是sleep一段时间以后，表中数据符合条件时，hasAvailableData却并不能查出。查了postgresql的文档，文档9.9.4. 当前日期/时间中有这么一段话：

还有一件事提醒大家，那就是 CURRENT_TIMESTAMP 和相关的函数把时间当做当前事务的开始返回；在事务运行的时候，它们的数值并不改变。我们认为这是一个特性：目的是为了允许一个事务在"当前" 时间上有连贯的概念，这样在同一个事务离得多个修改可以有同样的时间戳。

所以上面代码的问题猜测是由于事务引起的。修改hasAvailableData函数如下：

int hasAvailableData(String timeBefore)
{
EXEC SQL BEGIN DECLARE SECTION;
    int recs=0;
    char * timeStr = timeBefore
EXEC SQL END DECLARE SECTION;

    EXEC SQL SELECT count(*) into :recs from datatable where flag=0 and(occurtime<localtimestamp - interval :timeStr);
    if ( 0 > sqlca.sqlcode )
    {
        return -1;
    }
    //避免下次查询使用同样的时刻，需要重新开始另外一个事务
    EXEC SQL COMMIT;
    return recs;
}