Skip site navigation (1) Skip section navigation (2)

Triggers / Procedures

From: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Triggers / Procedures
Date: 2004-08-24 12:21:02
Message-ID: 1093350065.1099.47.camel@retsol1 (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Martin AtukundaDate: 2004-08-24 12:27:34
Subject: Re: Installing PostgreSQL in a Linux Environment
Previous:From: Gaetano MendolaDate: 2004-08-24 11:49:24
Subject: Re: MySQL vs PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group