From: Jason E. Stewart <jason@openinformatics.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Frontend/Backend protocol changes?
Date: 22 Nov 2002 10:14:50 -0700
Message-ID: <877kf5a5md.fsf@openinformatics.com> (raw)
In-Reply-To: <25204.1037984494@sss.pgh.pa.us>
References: <87of8ha7ch.fsf@openinformatics.com>
<25204.1037984494@sss.pgh.pa.us>
Hey Tom,
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> I dunno who the heck you got that advice from, but *none* of those
> statements are correct, at least not in recent PG releases.
Well, that's refreshing.
> You definitely want to do lots of inserts per transaction. There's
> probably not much further improvement to be had beyond a thousand or so
> rows per transaction, though.
OK, that's good, too.
> COPY is faster than a series of INSERTs because you bypass the
> parsing and planning overhead needed for each insert.
Ok.
> But my suspicion is that the cycles are actually going into your
> triggers. What triggers have you got on this table, and what are they
> doing?
Just one trigger, it tracks who inserted the row and when, and adds
this as a row to the Audit table. I keep an audit trail, so that if
it's an update (which it isn't in the case I'm writing about) I chain
the most recent audit to the previous one. I'm including the code at
the end.
If there's a better way to do this, I'd happily remove the trigger.
Thanks,
jas.
--
CREATE OR REPLACE FUNCTION audit () RETURNS OPAQUE AS '
DECLARE
ts TIMESTAMP := ''now'';
new_audit INT4 := nextval(''"GENEX_ID_SEQ"''::text);
BEGIN
IF TG_OP = ''INSERT'' THEN
INSERT INTO Audit_view (audit_pk, modification_date, modified_by)
VALUES (new_audit, ts, user());
ELSE
/* for UPDATE we keep a trail of audits */
INSERT INTO Audit_view (audit_pk,audit_fk,modification_date,modified_by)
VALUES (new_audit,OLD.audit_fk,ts,user());
END IF;
UPDATE tableadmin SET audit_fk = new_audit
WHERE UPPER(table_name) = UPPER(text(TG_RELNAME));
NEW.audit_fk := new_audit;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: jason@openinformatics.com, tgl@sss.pgh.pa.us
Subject: Re: Frontend/Backend protocol changes?
In-Reply-To: <877kf5a5md.fsf@openinformatics.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox