Thread: Triggers / Procedures

PostGreSql 7.4.3

I have a function that 'audits' inserts / updates on other tables - I have put timetraps in the code to see why it takes so long. The problem is that the timetrap shows a common time although the lapse in the display of the time is 5 to 6 seconds. How can I see what is actually going on?

update carpool set regNo = regNo where recNo = 1;
NOTICE:  (2004-08-24 12:37:54.52781) In Audit Tables
NOTICE:  (2004-08-24 12:37:54.52781) Got Level (L)
NOTICE:  (2004-08-24 12:37:54.52781) Deleted (carpool) (U)
NOTICE:  (2004-08-24 12:37:54.52781) Getting Seq For (tableactionaudit_recno_seq)
CONTEXT:  PL/pgSQL function "audit_tables" line 32 at SQL statement
NOTICE:  (2004-08-24 12:37:54.52781) Getting Seq For (tableactionaudit_recno_seq)
CONTEXT:  PL/pgSQL function "audit_tables" line 32 at SQL statement
NOTICE:  (2004-08-24 12:37:54.52781) INserted (carpool) (U)
UPDATE 1


Code is
BEGIN 
       SELECT CURRENT_TIMESTAMP INTO l_timeStamp;
         RAISE NOTICE '(%) In Audit Tables',l_timeStamp;
     
         SELECT auditLevel 
             INTO m_auditLevel 
             FROM auditLevel 
             WHERE foreignTableName = TG_ARGV[1]; 
      
         IF NOT FOUND THEN 
             m_auditLevel  :=  'L'; 
         END IF; 
      
       SELECT CURRENT_TIMESTAMP INTO l_timeStamp;
          RAISE NOTICE '(%) Got Level (%)',l_timeStamp,m_auditLevel; 
      
         IF m_auditLevel    = 'L' THEN 
             DELETE FROM tableActionAudit 
                 WHERE foreignTableName     = TG_ARGV[1] 
                 AND   foreignRecNo         = NEW.recNo 
                 AND   actionType           = TG_ARGV[0]; 
         END IF; 
       SELECT CURRENT_TIMESTAMP INTO l_timeStamp;
          RAISE NOTICE '(%) Deleted (%) (%)',l_timeStamp,TG_ARGV[1],TG_ARGV[0]; 
      
         IF m_auditLevel = 'L' OR
            m_auditLevel = 'F' THEN
             INSERT INTO tableActionAudit
               VALUES ( 
                       getNextSerialNo('tableactionaudit'),
                       TG_ARGV[0], 
                       TG_ARGV[1], 
                       NEW.recNo, 
                       current_date, 
                       current_timestamp, 
                       user 
                       );
         END IF; 
       SELECT CURRENT_TIMESTAMP INTO l_timeStamp;
          RAISE NOTICE '(%) INserted (%) (%)',l_timeStamp,TG_ARGV[1],TG_ARGV[0]; 
    
         RETURN NEW;
    

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769

On Aug 24, 2004, at 9:21 PM, Steve Tucknott wrote:

>  PostGreSql 7.4.3
>
>  I have a function that 'audits' inserts / updates on other tables - I
> have put timetraps in the code to see why it takes so long. The
> problem is that the timetrap shows a common time although the lapse in
> the display of the time is 5 to 6 seconds. How can I see what is
> actually going on?

<snip />

>         SELECT CURRENT_TIMESTAMP INTO l_timeStamp;
>            RAISE NOTICE '(%) INserted (%)
> (%)',l_timeStamp,TG_ARGV[1],TG_ARGV[0]; 

current_timestamp is evaluated once for the transaction. To see "wall
clock" time, use timeofday() (which, FYI, returns a text string rather
than a timestamp value). For more information,
<http://www.postgresql.org/docs/current/static/functions-datetime.html>

Michael Glaesemann
grzm myrealbox com