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
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
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
[snip 30 other constraints]
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.
In response to
pgsql-interfaces by date
|Next:||From: Tom Lane||Date: 2002-11-22 22:37:03|
|Subject: Re: Frontend/Backend protocol changes? |
|Previous:||From: Jason E. Stewart||Date: 2002-11-22 17:14:50|
|Subject: Re: Frontend/Backend protocol changes?|