Date: 22 Nov 2002 14:34:20 -0700
Message-ID: <87ptsx70gz.fsf@openinformatics.com>
From: "Jason E. Stewart" <jason@openinformatics.com>
To: pgsql-interfaces@postgresql.org
Subject: Re: Frontend/Backend protocol changes?
References: <87of8ha7ch.fsf@openinformatics.com>
	<25204.1037984494@sss.pgh.pa.us> <877kf5a5md.fsf@openinformatics.com>
	<26177.1037992260@sss.pgh.pa.us>
In-Reply-To: <26177.1037992260@sss.pgh.pa.us>
Lines: 96
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

Hey Tom,

Thanks for the advice. You've already helped me see some major
weakness in my design. I've only been working with DB's for a couple
of years, and I'm self taught, so I have a lot to learn about good
design.

Also, it seems that your black list doesn't like qwest.net (my ISP) so
all my direct emails bounce...

<more comments inline>

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> "Jason E. Stewart" <jason@openinformatics.com> writes:
> > "Tom Lane" <tgl@sss.pgh.pa.us> writes:
> >> 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?
> 
> >   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;
> 
> These inserts are probably fine.
> 
> >          UPDATE tableadmin SET audit_fk = new_audit
> >                 WHERE UPPER(table_name) = UPPER(text(TG_RELNAME));
> 
> This seems fishy though.  In the first place, why the UPPER() calls?
> TG_RELNAME is already in the correct case; AFAICS the only thing the
> UPPER() will do for you is create confusion if there are both "Foo"
> and "foo" tables.  The UPPER() calls also prevent use of indexes.
> Make it
> 
>          UPDATE tableadmin SET audit_fk = new_audit
>                 WHERE table_name = text(TG_RELNAME);
> 
> and make sure you have an index on tableadmin.table_name.

Tableadmin has a single row for every table in the DB (~50), and yes
it has an index.

I remember having to add the UPPER because I store the tables in
table_name in mixed case, but Postgres loses all case information
unless the table names are surrounded by double quotes in the CREATE
TABLE script. So text(TG_RELNAME) is all lowercase.

So I suppose I could store the name in all lowercase...

> BTW, a secondary possibility for performance problems is that either
> Audit_view or tableadmin might have triggers (such as foreign key
> enforcement triggers) that are slowing things down.

TableAdmin has a fkey constraint on its audit_fk column - I could drop
that since it's always handled by a trigger - the audit table is
pretty huge (since it logs every modification to every table) so
searching it is probably ugly.

Audit may be stupidly designed

                                           Table "audit"
      Column       |            Type             |                    Modifiers                     
-------------------+-----------------------------+--------------------------------------------------
 audit_pk          | bigint                      | not null default nextval('"GENEX_ID_SEQ"'::text)
 audit_fk          | integer                     | 
 modification_date | timestamp without time zone | not null
 modified_by       | name                        | 
Primary key: audit_pkey
Triggers: RI_ConstraintTrigger_2654857,
          RI_ConstraintTrigger_2654859,

[snip 30 other constraints]

          RI_ConstraintTrigger_2655571,
          RI_ConstraintTrigger_2655573

It has an fkey constraint on the audit_fk (which can be removed by the
same logic as TableAdmin), it has a primary key constraint which be
removed by the same logic as can the default value. 

Are all the RI_ConstraintTrigger_XXXXX triggers on _other_ tables that
affect this table in some way??? Because Audit shouldn't have any. 

Thanks,
jas.