Re: Mimicking Oracle SYSDATE - Mailing list pgsql-general

Sameer Thakur <samthakur74@gmail.com> writes:
> We are thinking of building our own version of Oracle's sysdate, in
> the form of PostgreSQL extension.
> Consider the behavior of sysdate for multiple inserts inside a function

> CREATE OR REPLACE FUNCTION fun2
>    RETURN number
>    IS var1 number(10); var2 number (2);
> BEGIN
> insert into t1 select sysdate from dual connect by rownum<=70000;
> FOR var1 IN 0..1000000
> LOOP
>   SELECT 0 iNTO var2 FROM dual;
> END LOOP;
> insert into t1 select sysdate from dual connect by rownum<=70000;
> RETURN var1;
>  END;

> The result of all first 70000 rows are same and result of all second
> 70000 row are same. But there is a difference between the values
> returned by sysdate between the 2 loops.

Do you really need to be bug-compatible with Oracle's SYSDATE at that
level of detail?  Especially seeing that SYSDATE is only precise to
1 second to start with?  In most situations you could not tell the
difference as to whether SYSDATE had advanced within a function or not;
and, very likely, if it did visibly advance that would actually be a bug
so far as behavior of the function was concerned, because it would be
a case that hadn't been considered or tested.  I suspect whoever thinks
they have a requirement here hasn't actually thought very hard about it.

If you think you do need bug-compatibility then the above is far from
a precise specification, eg it doesn't address what should happen in
sub-selects that are executed multiple times by the surrounding query.

Anyway, the approach I'd think about using is to rely on the ability of
C functions to cache query-lifespan data in fn_extra, ie
    (1) if fn_extra is NULL then read current timestamp and
        store it at *fn_extra
    (2) return *fn_extra
This would give you one reading per query execution per textual
occurrence of "sysdate()", which would satisfy the example above.

A possible problem is that once in awhile, something like
    SELECT sysdate(), sysdate()
would give two different answers.  I'm not sure if that's possible
with SYSDATE.

[ thinks for a bit... ]  Actually this might not work so well for
sysdate() appearing in simple expressions in plpgsql.  I think the
expression evaluation tree is cached for the whole transaction in
such cases.  You'd have to test it.

On the whole, much the best advice would be to explicitly read
clock_timestamp() at the points where you'd like time to advance,
eg convert the above to

   timestampvar := date_trunc('second', clock_timestamp());
   insert into t1 select timestampvar from ...

            regards, tom lane