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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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