Testing Technique when using a DB - Mailing list pgsql-general

I tried posting this from Google Groups but I did not see it come through after an hour so this may be a duplicate
messagefor some. 

The current testing technique for things like Ruby On Rails has three choices but all of the choices will not work in
mycase.  

The first choice is "truncate" which starts a transaction before each test.  If the testing is within the same process,
thisworks and a roll back restores the DB.  But if the testing involves two processes, then the test data entered by
thetest rig can not be seen by the "system under test" (SUT).  With Rails, there are times when this is needed.  The
testrig drives a browser which calls into a Rails application.  There are "dangerous" ways to still use this method but
eachhas various down falls or risks.  

The other two choices are delete and truncate which both end up with an empty database just after each test.  This
preventsany test data that is already in the database from being used after the first test.  Note that a "test run"
willrun through a sequence of tests (usually quite a few).  

All of these are fairly fast with each one being "faster" under different conditions (according to users).

Generally, this pushes the Rails community to have either "fixtures" or "factories".  Both are ok solutions but both
alsohave problems.  In my case, I have a dozen or so tables all with very tight constraints and creating either
fixturesor factories is very troublesome.  Also, I have a real database with real data in production and it seems
foolishnot to take advantage of the knowledge contained within that database.  By "knowledge" I mean the particular
valuesand weirdness within the data that a factory or a fixture might not realize.  

One choice would be to create the database, use it, and then drop it for each test.  I would create the database from a
templatethat already has data taken from the production database (and probably trimmed down to a small subset of it).
Thisrequires some crafty dancing in the Rails set up since it likes to just attach to a database and run but it could
bedone.  From first blush, this sounds like it would be really slow but may be not.  

The other choice would be to somehow copy the data to temporary tables before the test run and then copy it back.  The
advantageto this is it is not very PostgreSQL specific.  Indeed, if the template database is already set up, then only
onecopy would be needed at the start of the test.  

The other thought I had is if there is some type of "leaky" transaction.  A transaction where another process can see
thedata but the roll back would still work and be effective.  Essentially I'm asking if all the protections a database
offerscould be dropped... but I thought I'd see if that was possible.  

The other thought is perhaps there is a "snap shot" type concept.  I don't see it in the list of SQL commands.  A "snap
shot"would do exactly what it sounds like.  It would take a snap shot and save it somehow.  Then a "restore to snap
shot"would restore the DB back to that state.  

I thought this group might suggest other ideas and either nuke the really bad ideas or promote the plausible ideas I've
mentionedabove.  

Sorry for the long post.  I appreciate your thoughts.

Perry