Re: PostgreSQL unit tests

On Thu, Feb 23, 2006 at 01:40:06PM +1100, Gavin Sherry wrote:
> I previously proposed integrating a unit test framework into PostgreSQL.
> Getting started wasn't much fun and I gave up. This is because unit
> testing is really suited to a functional programming model, IMHO. Testing
> the most complex parts of the postgres backend requires a *lot* of state
> to be initialised and we'd really have to stomp all over the backend. I do
> think that unit testing of areas such as data types would be useful,
> particularly the date/time code and arrays as I consider that area of the
> code quite fragile. I wouldn't expect the unit tests to find any bugs.
> Rather, it would make it easier, I think, for people (particularly new
> comers) to hack on that part of the code with more confidence.

Seems like a good place to start, and once the framework is built
opportunities to expand the unit testing might present themselves.

> The areas of the backend which do not suit unit testing are usually
> associated with lots of state or lots of concurrency - like WAL, buffer
> manager and so on. The approaches we have at the moment -- regression
> tests, user test, load generators (benchmarks et al) -- do an okay job but
> they are a brute force approach. They test the common code path, not the
> uncommon one. Approaches used by other projects include sophisticated
> static analysis (both of language semantics and appliation semantics, such
> as 'function bar should never be call unless function foo has been called
> first'); model checking (difficult to implement with limited results) and
> once of of patches that introduce disasterous conditions in the code
> (like low level functions randomly failing, to check that the callers of
> such functions deal with such conditions correctly).
>
> Some basic static analysis has been applied to the backend (Stanford
> Checker and coverity, etc), no model checking (as far as I am aware) and
> Tom had some good results with the last approach easlier in the year, but
> I cannot remember what area of the code he was focusing on.
>
> The problem is, these approaches are either of limited use or time
> consuming and niche enough that they cannot be done often or across the
> whole tree.
>
> Any other thoughts on this?

ISTM that unit testing on the backend suffers the same roadblocks that
unit testing in a database application does: it maintains state between
tests. But this is something that can be overcome. At a previous job we
were using Oracle and I created an API to the database via
packages/procedures. We were also using a tool they no longer ship that
would take a set of packages and create java interface code. This
provided a trivial means to unit test via JUnit.

So what we ended up with was a set of JUnit tests that would execute in
a certain order. This allowed us to build things up in the database that
would then be used by later tests. So first we'd test functions that
inserted data, then stuff that would retrieve data, update, and then
finally delete. Presumably something similar could be setup for the
backend.

The one thing that would have made things much nicer was the concept of
dependencies between the unit tests, ie: this select test depends on
insert test xyz. That would allow for running an arbitrary set of unit
tests; without this you're pretty much stuck running the complete suite.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461