Re: Implementing a change log

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?