Re: get a list of table modifications in a day?

"Ottavio Campana" <ottavio(at)campana(dot)vi(dot)it> writes:

> I need to generate a diff (or something similar) of a table, day by day.
> What is the best way to tack insert/update/delete operations? I have two
> ideas, and I'd like to hear your opinion:
>
> 1) pg_dump each day and run diff

You can't use pg_dump directly as the rows are unordered. An update will
remove the old row in one place and put the new row possibly in a completely
different place. Some operations like CLUSTER or VACUUM FULL could move around
rows which doesn't matter to SQL but would show up in a diff.

You would have to COPY to a file a query which includes an ORDER BY.

> 2) modify some triggers we use and store the information in another table
>
> I am not aware of any functionality offered by postgresql. Does it exists?

Well alternatively you could do the same as 1) but do it in SQL. Something
like

CREATE TABLE copy_table AS (SELECT * FROM original_table);
... wait a day
SELECT * FROM copy_table EXCEPT SELECT * FROM original_table

It's not going to be fast though. Probably the triggers are the best option
really. They give you more information than a diff in that they tell you when
the change occurred, what user made the change, and if multiple changes to the
same record occurred you get a record of each.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com