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

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?

On Wed, Feb 6, 2013 at 11:10 AM, Greg Donald <gdonald(at)gmail(dot)com> wrote:

> On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver <wellsoliver(at)gmail(dot)com>
> wrote:
> > I have a wide-ish table with 60 columns. I want to make a copy of data
> > whenever a record is updated or deleted.
> >
> > Right now I have a table that's almost identical but with a 'created'
> column
> > (timestamp) and an 'action' column (which gets TG_OP for UPDATE or
> DELETE).
> >
> > My idea would be to sort on the created column to see the historical
> record
> > by comparing the columns. My other thought is to create two columns for
> each
> > column in the master table (old_column, new_column, etc), storing the old
> > values and the new values, and see what's changed that way.
> >
> > The other idea, probably a terrible idea, was to use hstore to create a
> list
> > of the old values and new values, and have this history table just be the
> > timestamp, action, and two hstore columns.
> >
> > Surely this has been done thousands of times. What are the thoughts
> > regarding best practices in PG?
>
> I handle this using middleware outside the db. Past revisions of a
> record (from any table I want to track) are serialized into a JSON
> format and stored in a single table. Postgres speaks JSON now, so..
>
>
> --
> Greg Donald
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Wells Oliver
wellsoliver(at)gmail(dot)com