Re: Implementing a change log

On 9/20/05, Berend Tober <btober(at)seaworthysys(dot)com> wrote:

[snip]

> 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.
>

I can see your point. You could use a UNION ALL view to combine the
main table with the audit table, though.

> Any other significant distinquishing features of INHERIT verses LIKE for
> this kind of use that you (or others) can think of?
>

I would personally still go with LIKE simply for the CONSTRAINT
stripping since the audit table will, by definition, have duplicates
for the main table's primary key. In fact after looking at the
documentation more closely it seems that all child table must contain
all CHECK constraints from the base table when using INHERITS. If you
decided to add a CHECK constraint to the base table at some future
time then you might have to modify the data in the audit table to
match it (big no-no!).

Read the INHERITS and LIKE sections of this* closely to see what I'm
talking about.

However, INHERITS may be useful for the audit table. If the base
table is updated very frequently you could set up RULE based
partitioning for the audit table. Then you would have the option of
archiving and dropping older sections of the audit table without
affecting the on-disk layout of the rest of the audit data by creating
holes in the audit table (or having to CLUSTER the table).

* http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org