Right now there isn't really a single wonderful option for this.
You need to make sure people use revision control properly and consistently.
Some after-the-fact checks can be used to query for unexpected procedure changes and alert if the pg_proc.prosrc
column doesn't match what you have in revision control.
If necessary you can force all changes through a single person's approval. Alternately you can deny access to create and update procedures to all accounts except one - and have that account automatically apply changes that're committed to git via a hook. I don't recommend this though - it'll make operations very restrictive and make it hard to fix unexpected issues.
Why I would not change the application
I do not like the answers which propose to change the application to fulfill testing needs. To me this is the most wrong way to go. The implementation should only be driven by concepts, requirements and design decisions of the application, not of any helper tools used while implementing.
Use dynamic fixtures instead
It is a very basic concept of all tests to set up a testing environment, also called test fixtures. This concept found its way into the unit testing as well, see for example fixtures in Rails, Python or Java. All propose to use fixtures for preparing specifically crafted databases:
Loading a database with a specific, known set of data
https://github.com/junit-team/junit/wiki/Test-fixtures
Fixtures allow you to populate your testing database with predefined data before your tests run.
http://guides.rubyonrails.org/testing.html#the-low-down-on-fixtures
This may involve, for example, creating temporary or proxy databases […]
https://docs.python.org/2/library/unittest.html
Nothing stops you from populating the test database dependent on the current date!
Actually this is pretty common anyway: For example every Rails object one creates gets the current timestamp as created_at
and updated_at
. Since they are created as part of preparing the fixture, all Rails objects are created with the date of the current test and thrown away afterwards.
I do not see a reason to verify the test fixture. If something is wrong concerning e.g. consistency, your application should throw an exception. To make sure that the tests cover all situations is your responsibility and no automatic verification will answer this question anyway.
Best Answer
pgTAP
pgTAP is the only I know that is ever even talked about.