Saving snapshots for later use - Mailing list pgsql-hackers

I'd like to be able to save the current snapshot and then at a later date roll the entire database back to that snapshot, essentially erasing everything that happened since the snapshot.  Well, more like making all that stuff invisible.  This would be really useful when testing applications who's databases take a while to setup and who's tests change the database in some way.  It could also provide a way to recover from some horrible user error.  Frankly, I'm more interested in this for testing, but having the ability to recover from errors would be nice.

Once you've saved the snapshot it'd be tempting to expose the ability to run queries against the old snapshot as well.  This seems like more of a bonus in my mind, though.

>From here on out I'm not sure if I have my terms right so please correct me if I'm wrong.

So I'm wondering a few things:

1.  I _think_ all I have to do to store one of these snapshots is to store the current xid and a list of all in progress transactions.  I believe I should be able to reconstitute this with the commit log to determine visibility at that snapshot.

2.  I _think_ I can save that information in a system table.  It feels weird to use a table to store visibility information but part of me thinks that is ok.  I can't put my finger on why I think that is ok though.

3.  I'm not really sure at all what to do with other connections that are doing things during a snapshot restore.  They might just have to get cut.  For my purposes this would be ok but I can immagine that would be a problem.

4.  I think not allowing the user to save a snapshot during a transaction would simplify the book keeping a ton.  I know what I typed in (1) wouldn't cover snapshots inside transactions.  I just don't think that is worth the effort.

5.  On a more personal note I have no idea if I am capable of implementing this.  I'm really not very good with C but I haven't had too much trouble figuring out how to add new parser rules or map them into the tcop layer.  I figured out how to create a new system table without too much trouble but haven't a clue how upgrades work for the new table.  I'm not sure how to read from the system table but I see there is a faq entry for that.

6.  I think it'd be ok if restoring an older snapshot removes a newer snapshot.  Using the snapshot mechanism to jump forward in time just sounds hard to keep strait.

I'd attach a patch of my work so far but it certainly isn't anything mind blowing at this point.

Sorry to ramble on for a solid page.  Thanks for everyone who made it to the end.

Cheers,

Nik