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