Re: Performance question: Commit or rollback?

On Sat, Dec 24, 2011 at 11:34 AM, Darren Duncan <darren(at)darrenduncan(dot)net> wrote:

>
>
> You can do it correctly while reusing all of your code; you just have
> different arguments at connect time and otherwise your code uses the
> connection handle in the same way afterwards.  Its fine to have flags in the
> app so the app just tries acceptable things, but privileges in the database
> are the only way to actually be safe and resilient against accidental
> changes. -- Darren Duncan

Agreed. Roles in the db are important (we use them extensively in
LedgerSMB). However here is a case where it doesn't work so well:

Suppose I want to run read-write test cases against a production
database to look for things that are wrong but we want to be
mathematically sure that the test cases do not commit data to the
database.

So I don't entirely know the best way to do this in other languages,
but here is what we did in Perl:

1) Our application normally uses DBI (and DBD::Pg).
2) We have a special module (LedgerSMB::DBTest) which basically
exposes the portions of the DBI/DBD::Pg interface we are using, but
lies to the higher levels about committing. Basically it returns
true, but does nothing database-wise, providing the appearance of a
consistent set of changes but in fact the changes are still to be
rolled back.
3) In our test case scripts, we switch out the DBI database handles
with instances of LedgerSMB::DBTest.

This works well because it is fails safely. If we omit something from
the DBTest module, our code will error. If we have test cases that
depend on some transactions committing and others rolling back, we
have to order the test cases appropriately or the test cases fail. In
other words, the worst we can get are test case failures, not spurious
commits, and the changes necessary to make this happen are in the test
case files themselves. This makes it easy to verify.

Best Wishes,
Chris Travers