Solution for rolling back many transactions?

From: Phillip Tornroth <ptornroth(at)intellidot(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Solution for rolling back many transactions?
Date: 2006-09-26 17:10:23
Message-ID: 9EA71211-8F13-437E-840C-B197DC4352FA@intellidot.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The most cumbersome part of our many (hundreds) of unit tests that
verify our data access is working, is creating and then deleting all
of the test data. Currently, we're doing it at a pretty high level
using java and our ORM (hibernate). It occurred to me that I can
probably speed the deletion up by implementing a stored procedure to
help do the cleanup. This way we don't pay the penalty for a lot of
object construction and individually-issued delete statements.. This
might help, but half way through writing it I wondered if there's an
even easier solution...

What I want is to be able to roll the state of the database back to
the way it was when the test began. Essentially, I want the same
behavior I'd get if I backed the database up and then re-imported it
afterward.. Only I need the process to add very little overhead (less
than a second).

Are there any creative ways to accomplish this? I figured I'd ask the
list before trudging forward with my still-not-optimal stored procedure.

In case it's useful information, the test database is pretty small...
Maybe 15 or 20 megs. The unit tests are individually pretty small..
Less than 100 inserts each, for sure... So there's not that much to
'roll back'.. As far as using transactions to undo the state of the
database, let me know if that's possible.. All of our tests have
their own transaction boundaries that they're applying. I don't want
to undo that, but certainly if I could nest each test in a longer-
running tx and then roll it back.. that could work.

Thanks in advance.

Phill

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Harry Hehl 2006-09-26 17:21:01 Re: Timestamp with timezone query
Previous Message Jeff Davis 2006-09-26 17:02:36 Re: Replication and PITR