Resent-To: pgsql-interfaces@postgresql.org
Resent-From: jason@openinformatics.com (Jason E. Stewart)
Resent-Date: 22 Nov 2002 11:01:00 -0700
Resent-Message-Id: <E18FI6u-0006uW-00@amadeus>
Resent-Sender: "Jason E. Stewart" <jason@openinformatics.com>
Date: 22 Nov 2002 10:14:50 -0700
Message-ID: <877kf5a5md.fsf@openinformatics.com>
From: "Jason E. Stewart" <jason@openinformatics.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Frontend/Backend protocol changes?
References: <87of8ha7ch.fsf@openinformatics.com>
	<25204.1037984494@sss.pgh.pa.us>
In-Reply-To: <25204.1037984494@sss.pgh.pa.us>
User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Honest Recruiter)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Lines: 58

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';