LIM: now() in functions show time of db upgrade/install

[jamoore]

omero3=> select time, count(time) from event where status = 'TRIGGERED' group by time order by time;
            time            | count 
----------------------------+-------
 2010-10-29 09:09:04.023106 | 34086
 2010-10-29 11:30:24.822505 |   106
 2010-11-28 18:15:24.530647 |    17
 2011-01-21 18:18:06.028203 |    23
 2011-01-21 18:18:25.76283  |     2
 2011-04-27 10:02:08.019238 |   200
(6 rows)

[jburel]

Moved from sprint 2011-06-16 (14)

[jmoore]

Doing some initial investigation. Most likely this should be moved to #4891 (4.4 DB patch)

[jmoore]

I can only find this on nightshade (postgresql 8.2). My guess, then, is that this is follow-on from #5861 in which _prepare_session fails. When that happens _current_event:

CREATE OR REPLACE FUNCTION _current_event() RETURNS int8
    AS '
    DECLARE
        eid int8;
    BEGIN
        IF NOT EXISTS(SELECT table_name FROM information_schema.tables where table_name = ''_current_session'') THEN
            RETURN 0;
        END IF;
        SELECT INTO eid event_id FROM _current_session;
        RETURN eid;

    END;'
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION _current_or_new_event() RETURNS int8
    AS '
    DECLARE
        eid int8;
    BEGIN
        SELECT INTO eid _current_event();
        IF eid = 0 OR eid IS NULL THEN
            SELECT INTO eid ome_nextval(''seq_event'');
            INSERT INTO event (id, permissions, status, time, experimenter, experimentergroup, session, type)
                SELECT eid, -35, ''TRIGGERED'', now(), 0, 0, 0, 0;
        END IF;
        RETURN eid;
    END;'
LANGUAGE plpgsql;

must return a 0 for root, so that _current_or_new_event creates a "TRIGGERED" event. now() in _current_or_new_event() returns the now()` time of the transaction, rather than the actual time. In order to return the ​statement time we'll need to modify this to:

SELECT eid, -35, ''TRIGGERED'', clock_timestamp(), 0, 0, 0, 0;

Moving to 4.4 patch (#4891) so we can review all the now() function usages.

[jmoore]

Referencing ticket #4891 has changed sprint.

[jmoore]

Fixed pushed to my 8696-db-perms branch (OMERO4.4RC1__0):

commit 1aa6024e94a7a0d33012420ee67a450c02512b48
Author: jmoore <josh@glencoesoftware.com>
Date:   Wed Jun 13 10:41:23 2012

    Replace now() calls with clock_timestamp() (Fix #5862)
    
    now() is equivalent to transaction_timestamp which for triggers
    is not giving us the kind of resolution we would like.

[jmoore <josh@…>]

(In [1aa6024e94a7a0d33012420ee67a450c02512b48/ome.git] on branch develop) Replace now() calls with clock_timestamp() (Fix #5862)

now() is equivalent to transaction_timestamp which for triggers
is not giving us the kind of resolution we would like.