Re: Keeping historical record changes w/ triggers - best practices?

On Wed, Feb 6, 2013 at 11:19 AM, Wells Oliver <wellsoliver(at)gmail(dot)com> wrote:
> I don't mean to hog my own thread, but the more I look at the hstore type,
> the more reasonable it seems. The table is just a serial, a timestamp, and
> two columns 'old' and 'new'. The trigger function inserts these values using
> hstore(OLD) and hstore(NEW).
>
> Then, you can select old, new, and new - old, which returns an hstore of
> what changed, or you could store this as a third column called 'delta' or
> whatever. The hstore of course can be cast to a record, or any other
> suitable object.
>
> Of course, you could not store old and new, and only the delta if you
> preferred, but it's nice to have both records.
>
> Will anyone tell me there's some terrible side effect of this approach that
> I am not realizing?

If you want hstore-based history logs, you should read through this,
either to implement it, or for ideas on how to do your own:

http://wiki.postgresql.org/wiki/Audit_trigger_91plus

Cheers,

Jeff