Re: Frontend/Backend protocol changes?

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Jason E. Stewart" <jason(at)openinformatics(dot)com> writes:
> > Are all the RI_ConstraintTrigger_XXXXX triggers on _other_ tables that
> > affect this table in some way??? Because Audit shouldn't have any.
>
> You should check, but I think those are all going to be ON UPDATE or ON
> DELETE triggers, if they all appear to be in pairs. You probably have a
> bunch of FK's referencing Audit from other tables? If so, they are not
> hurting your insert performance. But an update or delete in Audit would
> be expensive.

Right, I removed all the fkey constraints and they all
disappeared. The audit table is never updated, only inserted into.

BTW, is there some other way to have all rows in a table automatically
timestamped? That would satisfy 99% of what I need. Maintaining the
trail of audits could then be done for those rare cases where data is
updated.

> (BTW, I'm having a hard time visualizing why an audit-trail table would
> be referenced by FKs from other tables.)

Maybe it's a stupid design, but each table that needs on audit trail
on it's rows has an audit_fk foreign key column. When I insert a row
into that table, the trigger creates a new audit in the audit table
and sets the audit_fk for the table.

I suppose I could do it the other way around, put the fkey column in
the Audit table, and either reference that rows oid, or the pkey value
of the table entry (all the pkey values are drawn from the same
sequence so they are unique across tables).

But that would mean searching the Audit table to find a given entry,
I can make an index for that column which would speed it up, but it
would never be as fast as holding the audit_fk in the other table.

Is there some advantage I don't see to having the column in the Audit
table?

Actually searching the Audit table is a very rare occurrence (only
needed if somebody messed up some data and you want to find the
culprit).

Cheers,
jas.