Mike Rylander wrote:
On 9/20/05, Berend Tober <btober@xxxxxxxxxxxxxxxx> 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?
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
