Comments on End Point Blog: PostgreSQL template databases to restore to a know...

Andres: I've used exactly that strategy myself...

2010-05-18T09:28:43.443-04:00

Andres: I've used exactly that strategy myself in an attempt to apply the test-driven development methodology to Postgres-specific work. It ended up working out nicely, and you end up with a DDL script that will back itself out automatically if it fails in foreseeable ways when applied in production, which is pretty nice.

There's another approach that might work depen...

2010-05-17T19:55:55.556-04:00

There's another approach that might work depending on what you do. I run a lot of unit tests against my stored procedures. To be able to roll back to a known good state after each run, I throw and catch an exception inside my test stored procedure. Something like this:

--- RUN MY TESTS HERE ---
RAISE EXCEPTION 'ROLLBACK';
EXCEPTION WHEN raise_exception THEN
RAISE NOTICE 'ROLLED BACK';

This causes any changes to be rolled back, and it's incredibly fast! There's functions I wrote to facilitate this type of testing: PLUnit

This can also be done using save points, if operating outside of a stored procedure. Something like this:

SAVEPOINT my_savepoint;
--- RUN MY TESTS HERE ---
ROLLBACK TO SAVEPOINT my_savepoint;

This solution is obviously not workable for the manual testing of a full application, but more for automated (unit) testing

Philippe: Thanks for the links, looks interesting....

2010-05-13T10:04:13.435-04:00

Philippe: Thanks for the links, looks interesting.

I think that all objects should be stored in sourc...

2010-05-13T08:07:25.226-04:00

I think that all objects should be stored in source control just like any code should be. From there you can have labels and branches and a simple build script to build a pristine database from source passing in a label or branch that you want to build from.

Hi, The use of the template database is a nice tri...

2010-05-13T04:17:34.916-04:00

Hi,
The use of the template database is a nice trick.
For cases when a relatively small part of the database is updated and no DDL operation is performed, I am developing a tool named E-Maj (a first version being available on pgfoundry) with a presentation. The concept is an extension of table_log contrib. Some supplied functions allow to create group of tables on which it is possible to set marks at predefined point in time and then, if needed, rollback all row insertions, updates and deletions up to one of these marks.
I intend to improve this tool. But if it can already help someone... Of course, any feedback is welcome.