Re: Frontend/Backend protocol changes?

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(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Jason E. Stewart" <jason(at)openinformatics(dot)com> writes:
> > "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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.