Thread: modification time & transaction synchronisation problem
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:2.0cm 42.5pt 2.0cm 3.0cm; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> Hi. 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. Eugene.
Ostrovsky Eugene wrote: > Hi. > 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. About the only solid solution I can think of right now is to LOCK TABLE the table you want to dump. You can use a lockmode that permits SELECT, but just blocks UPDATE/INSERT/DELETE from other threads. That way your modification time approach works. (I strongly suggest leaving the modification time field without an index, so that HOT can do in-place replacement of the rows and you avoid a whole lot of bloat). There might be another possible approach that uses the system "xmin/xmax" fields of each tuple. That'd permit your incremental dumps to be done read-only, saving you a whole lot of expensive I/O and bloat. I'm just not sure what I'm thinking of will work yet. I'll check back in once I've had a play and written a test script or two. ( If it will, then surely there'd be "pg_dump --incremental-as-of" by now ...) -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
On 19/04/2010 4:51 PM, Craig Ringer wrote: > There might be another possible approach that uses the system > "xmin/xmax" fields of each tuple. That'd permit your incremental dumps > to be done read-only, saving you a whole lot of expensive I/O and bloat. > I'm just not sure what I'm thinking of will work yet. Yeah. You can use a SERIALIZABLE transaction and txid_current_snapshot(), which almost magically solves your problem. In a single call it provides all the details about active and committed transactions at the time of snapshot creation that you need. It even gives you a list of transaction IDs for uncommitted transactions between those ranges so old uncommitted transactions don't force you to repeatedly dump data. That's all you need to know to do intelligent incremental backup of a table. I haven't written the actual test code, but what you should need to do (according to my probably flawed understanding) is: - Begin a read only SERIALIZABLE transaction - Record txid_current_snapshot(), which you will need for the next run. We'll call the value of the last run's txid_current_snapshot() call 'txprev'. - SELECT all rows that have: tablename.xmin > current_snapshot_xmax(txprev) OR tablename.xmin IN (txid_snapshot_xip(txprev)) ( The above doesn't consider deletion. Deletion is never a fun thing to handle in incremental backups/dumps. I'm not presently sure how it should be handled or if it *can* be handled without help from VACCUM and/or an ON DELETE trigger ). If I get a chance, I'll play with this and see if it works in practice. See: http://www.postgresql.org/docs/current/interactive/functions-info.html Table 9-52. Transaction IDs and snapshots http://www.postgresql.org/docs/current/interactive/storage-page-layout.html \df pg_catalog.tx* -- Craig Ringer
Craig Ringer wrote: > On 19/04/2010 4:51 PM, Craig Ringer wrote: > >> There might be another possible approach that uses the system >> "xmin/xmax" fields of each tuple. That'd permit your incremental dumps >> to be done read-only, saving you a whole lot of expensive I/O and bloat. >> I'm just not sure what I'm thinking of will work yet. > > Yeah. You can use a SERIALIZABLE transaction and > txid_current_snapshot(), which almost magically solves your problem. In > a single call it provides all the details about active and committed > transactions at the time of snapshot creation that you need. It even > gives you a list of transaction IDs for uncommitted transactions between > those ranges so old uncommitted transactions don't force you to > repeatedly dump data. That's all you need to know to do intelligent > incremental backup of a table. > > I haven't written the actual test code, but what you should need to do > (according to my probably flawed understanding) is: > > - Begin a read only SERIALIZABLE transaction Actually, it looks like READ COMMITTED is preferable, as it gives you information about any concurrently running transactions in txid_current_snapshot(), and you can do the actual dump in one statement anyway. > - Record txid_current_snapshot(), which you will need > for the next run. We'll call the value of the last > run's txid_current_snapshot() call 'txprev'. > - SELECT all rows that have: > tablename.xmin > current_snapshot_xmax(txprev) OR > tablename.xmin IN (txid_snapshot_xip(txprev)) I've tested this approach and it appears to work fine, *but* only for append-only tables. I don't seem to be smart enough to figure out how to correctly handle UPDATEs and DELETEs - I know the xmax (for last transaction in which the row is visible) field is key, but can't figure out a way to effectively use it to record deletions. I guess I don't understand MVCC or at least Pg's implementation of it even as well as I thought I did (which isn't very). Unless someone smarter steps in or I have the time to learn more of the details about this, I probably can't offer a pre-formed solution to your problem. You can always use a trigger-maintained change history table to track inserts/updates/deletes, and DELETE FROM ... RETURNING it. That's simple and easy, but generates plenty of extra I/O to do your progressive backup/copy. I'm going to stop talking to myself now. -- Craig Ringer