Warning: Can't synchronize with repository "(default)" (/home/git/ome.git does not appear to be a Git repository.). Look in the Trac log for more information.
Notice: In order to edit this ticket you need to be either: a Product Owner, The owner or the reporter of the ticket, or, in case of a Task not yet assigned, a team_member"

Task #5862 (closed)

Opened 13 years ago

Closed 12 years ago

Last modified 12 years ago

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

Reported by: jamoore Owned by: jamoore
Priority: critical Milestone: OMERO-4.4
Component: ORM Version: n.a.
Keywords: n.a. Cc: cxallan
Resources: n.a. Referenced By: n.a.
References: n.a. Remaining Time: 0.0d
Sprint: n.a.

Description

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)

Change History (7)

comment:1 Changed 13 years ago by jburel

  • Milestone changed from OMERO-Beta4.3 to OMERO-Beta4.3.1
  • Sprint changed from 2011-06-16 (14) to 2011-06-30 (1)

Moved from sprint 2011-06-16 (14)

comment:2 Changed 13 years ago by jmoore

  • Milestone changed from OMERO-Beta4.3.1 to OMERO-Beta4.3.2
  • Sprint 2011-06-30 (1) deleted

comment:3 Changed 13 years ago by jmoore

  • Remaining Time set to 1.0
  • Sprint set to 2011-08-04 (2)
  • Status changed from new to accepted

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

comment:4 Changed 13 years ago by jmoore

  • Milestone changed from OMERO-Beta4.3.2 to OME-5.0
  • Sprint 2011-08-04 (2) deleted
  • Summary changed from Bug: now() in functions show time of db upgrade/install to LIM: now() in functions show time of db upgrade/install

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.

comment:5 Changed 13 years ago by jmoore

Referencing ticket #4891 has changed sprint.

comment:6 Changed 12 years ago by jmoore

  • Remaining Time changed from 1.0 to 0
  • Resolution set to fixed
  • Status changed from accepted to closed

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.

comment:7 Changed 12 years ago by 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.

Note: See TracTickets for help on using tickets. You may also have a look at Agilo extensions to the ticket.

1.3.13-PRO © 2008-2011 Agilo Software all rights reserved (this page was served in: 0.69234 sec.)

We're Hiring!