I need to export data from the database to external file. The difficulty is that only data modified or added since previous export should be written to the file.
I consider adding "modification_time" timestamp field to all the tables that should be exported. Then I can set this field to now() within ON UPDATE OR INSERT trigger.
During export I can select modified data with 'WHERE modification_time > last_export_time' clause.
It seems to be the solution but...
What if the concurrent (and not yet committed) transaction modified some data before export transaction begins? These modifications would not be visible to export transaction and modified data would not be included to export file. Also it won't be included to the next export because it's modification time is less than current export start time (the new value of last_export_time).
Thus some data could be lost from export files sequence. And that is not good at all.
I will appreciate any suggestions on how to solve this problem. I.e. how can I (within the export transaction) select all the data that was updated since the last export?
Thanks.
