Thread: Keeping historical record changes w/ triggers - best practices?
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? Thanks everyone. -- Wells Oliver wellsoliver@gmail.com
On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver <wellsoliver@gmail.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
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@gmail.com> wrote: > On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver <wellsoliver@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Wells Oliver wellsoliver@gmail.com
On Wed, Feb 6, 2013 at 11:19 AM, Wells Oliver <wellsoliver@gmail.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