On Mar 12, 2013, at 8:41 AM, Perry Smith <pedzsan@gmail.com> wrote:
>
>
> One choice would be to create the database, use it, and then drop it for each test. I would create the database from
atemplate that already has data taken from the production database (and probably trimmed down to a small subset of it).
This requires 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.
I do this. It's not blindingly fast, but plenty fast enough for automated testing as long as your tests aren't too
fine-grainedand your test database isn't too big. It takes no more than two or three seconds on my (slow, IO-starved)
QAVMs.
By parameterizing the database name you can parallelize tests - each test creates it's own copy of the template
database,runs whatever it needs to run, then drops the database. That lets you hide a lot of the setup/teardown
latency.
>
> The other choice would be to somehow copy the data to temporary tables before the test run and then copy it back.
Theadvantage to this is it is not very PostgreSQL specific. Indeed, if the template database is already set up, then
onlyone copy would be needed at the start of the test.
You'd also need to undo any other state that was changed. Sequences, for instance, if they can affect the meaning of
yourtest or expected results in any way.
>
> 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.
That - or anything else involving rolling back transactions - would only work if you were testing an app that didn't
usetransactions.
> The other thought is perhaps there is a "snap shot" type concept. I don't see it in the list of SQL commands. A
"snapshot" would do exactly what it sounds like. It would take a snap shot and save it somehow. Then a "restore to
snapshot" would restore the DB back to that state.
That's pretty much what creating a database from a template does, other than the need to have everybody disconnect from
thedatabase before doing the drop+create.
Dump / restore will do that too - somewhat slower, but doesn't require disconnecting from the DB.
File-system level snapshots are another option, but I'm pretty sure you'd need to shut down and restart the database
server,which'd cost far more than you'd save.
Cheers,
Steve