Mike Rylander wrote:
>On 9/20/05, Berend Tober <btober@seaworthysys.com> wrote:
>
>
>>/*
>>The following is based on suggestion by Mike Rylander posted on
>>Postgresql-General
>>Sun, 18 Sep 2005 23:29:51 +0000
>>
>>Rylander's original suggestion employed a trigger and tracked
>>only row updates. My implementation makes use of rules and
>>handles both updates and deletions.
>>*/
>>
>>
>
>I'm glad that was inspirational ...
>
>
That was indeed pretty cool.
>...did in fact track deletions:
>
>
Guess I was too excited to actually read the whole thing more closely
once I grasped the direction you were going!!
>You may want to consider using the LIKE style of table copying, as it
>strips all constraints from the new table. It's safer IMHO, as this
>way you wouldn't have to worry about the primary key being propagated
>to the new table (and accidentally forgetting to remove it).
>
>
I'm glad you pointed that out because you reminded me that when I tried
the original idea from Greg Patnude in Mar 2005 using inheritance, I did
indeed run into a problem with constraints. The problem there I think
was that I had a check constraint on the table for which I created the
audit log table, but the check constraint was defined in a different
schema than the original table. Something about the way inheritance
table creation works found this a problematic situation. I'll have to
revisit that and see if using LIKE overcomes that problem.
I guess I originally thought using INHERIT rather than LIKE was that,
having the audit history, I might at some point present a select view
across both the base and descendant tables or something ("...if you
record it, they (PHB's) will eventually ask for a report on it..."), but
I haven't actually had an implementation where such an audit history
table was actually required in production -- I'm just exercising the
functionality and exploring the quirks in order to be prepared for when
such a requirement is actually promulgated.
Any other significant distinquishing features of INHERIT verses LIKE for
this kind of use that you (or others) can think of?