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:

<snip />

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend