I'm looking for a tool which can connect to a PostgreSQL database, run a series of queries potentially in multiple threads, then roll all those changes back.
I've done some searching for methods and tools that can do this, and found DbUnit as well as several articles on the topic.
Every tool I've seen so far for integration testing to set up a database to a known state does it one of two basic ways:
- Delete all the data, then re-insert it (or a subset of it), or
- Update a subset of the data to a known state, and leave all the other data as-is with the hope that nothing left as-is is critical.
Both of these approaches rely on having a small set of data being in a known state and don't really work with a large, pre-populated database, ie a copy of the production database.
I know that PostgreSQL has capabilities to work in transactions, with save states, and can roll back all changes made within that transaction. For our database unit tests, this works fine by ensuring every DAO method is run in one transaction and rolled back instead of committed. But it does not work when one thread/connection needs to access data changed by another, because each would try to run in its own transaction, and changes aren't available between transactions.
So, is there a tool which can:
- wrap or connect to a PostgreSQL database
- hand out connections to threads which on the back end share the same transaction
- correctly simulate transactions within that outer transaction
- work with multithreading such that every query accesses the data with changes from other threads
- successfully + reliably roll back to the start state periodically, ie between two tests (this can kill/break any open connections and actively-running queries)
- handle errors and invalid queries without forcing a rollback
(Question brought over from StackOverflow due to being off-topic for SE.)
While continuing my search, I've found Amnesia for the .NET ecosystem which is meant to fulfill this need. Is there a comparable tool for the Java ecosystem? Or did I misunderstand Amnesia and it's already usable in the Java ecosystem?