PostgreSQL unit tests

Michael Glaesemann wrote:

During Josh Berkus' presentation at the JPUG PostgreSQL Conference, I
was particularly struck by the fact that 8.1 had a shorter beta period
than previous releases, rolled out on time, and enjoyed a longer period
before the first post-release bug was reported. The PostgreSQL Build
Farm played a key role in making these possible. (Thanks again, Andrew!)

Something that has been briefly discussed in the past wrt PostgreSQL has
been unit testing to complement the current regression tests. I've taken
a very quick google to see what's out there for C unit testing
frameworks. The ones I found are:

Check (GPL)
GNU Autounit (GPL)
CuTest (GPL? zlib/libpng?)
CUnit (GPL)

I do not know the scope of current testing, but I think the testing
should probably also including collecting and monitoring performance
metrics. This might be a thing to keep in mind when choosing a
particular unit testing framework.

regards,
Lukas

Lukas Smith wrote:

Michael Glaesemann wrote:

During Josh Berkus' presentation at the JPUG PostgreSQL Conference, I
was particularly struck by the fact that 8.1 had a shorter beta
period than previous releases, rolled out on time, and enjoyed a
longer period before the first post-release bug was reported. The
PostgreSQL Build Farm played a key role in making these possible.
(Thanks again, Andrew!)

Something that has been briefly discussed in the past wrt PostgreSQL
has been unit testing to complement the current regression tests.
I've taken a very quick google to see what's out there for C unit
testing frameworks. The ones I found are:

Check (GPL)
GNU Autounit (GPL)
CuTest (GPL? zlib/libpng?)
CUnit (GPL)

I do not know the scope of current testing, but I think the testing
should probably also including collecting and monitoring performance
metrics. This might be a thing to keep in mind when choosing a
particular unit testing framework.

No, performance testing should be kept separate. Unit and regression
testing are basically pass/fail tests. Performance testing is about
positions on a continuous scale. That has a major effect on the design
and use of a test harness.

I'm all in favor of a distributed performance testing regime, but in
setting it up we need to start if not from scratch then pretty close to it.

cheers

andrew

On Wednesday 22 February 2006 01:27, Michael Glaesemann wrote:

During Josh Berkus' presentation at the JPUG PostgreSQL Conference, I
was particularly struck by the fact that 8.1 had a shorter beta
period than previous releases, rolled out on time, and enjoyed a
longer period before the first post-release bug was reported. The
PostgreSQL Build Farm played a key role in making these possible.
(Thanks again, Andrew!)

Something that has been briefly discussed in the past wrt PostgreSQL
has been unit testing to complement the current regression tests.
I've taken a very quick google to see what's out there for C unit
testing frameworks. The ones I found are:

Check (GPL)
GNU Autounit (GPL)
CuTest (GPL? zlib/libpng?)
CUnit (GPL)

There also appears to be a separate cUnit framework, but the web page
was unavailable when I checked.

Of these, Check and GNU Autounit set aside a separate, protected
address space for running tests.

(Almost?) all of these frameworks are GPL. I'm assuming this is
probably going to be an issue with distribution, presumably a non-
starter. I wonder if it would be feasible to maintain the unit
testing framework on Pgfoundry, available for those who wish to run
the tests? If the unit tests were to be run on the build farm
machines, that would mean additional dependencies, but perhaps that
can be configured to be relatively painless (and perhaps completely
automated).

On a related note, Neil brought up QuickCheck in a blog entry[1], and
mentioned in particular "try[ing] to define a domain-specific
language that would allow you to construct random SQL queries that
satisfy certain high-level constraints, and then verify that the
backend executes those queries correctly".

I'm interested in hearing others' thoughts on this. What other

You could check into what spikesource has been doing. I believe they mostly
just piggyback off of our regression tests for postgresql core, but there
might still be something that could be built upon. If you look at this url
http://developer.spikesource.com/spikewatch/index.jsp?show=component-results&comp-id=22074
the actual success information isnt terribly exciting but the "code coverage"
url shows something of more interest. There is more stuff if you dig around a
bit.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Michael Glaesemann <grzm@myrealbox.com> writes:

Something that has been briefly discussed in the past wrt PostgreSQL
has been unit testing to complement the current regression tests.

I was and remain pretty dubious of this idea. In the backend in
particular, there is very little code that could usefully be tested
in a stand-alone fashion. For instance, almost everything assumes
the presence of palloc and elog and related facilities, and most
things depend at some level on having catalog data, and there are
lots of complicated, frequently-changed data structures involved in
the APIs. I think you'd spend vastly more time constructing and
maintaining unit-test cases than would ever be repaid :-(

regards, tom lane

Robert Treat wrote:

You could check into what spikesource has been doing. I believe they mostly
just piggyback off of our regression tests for postgresql core, but there
might still be something that could be built upon. If you look at this url
http://developer.spikesource.com/spikewatch/index.jsp?show=component-results&amp;comp-id=22074
the actual success information isnt terribly exciting but the "code coverage"
url shows something of more interest. There is more stuff if you dig around a
bit.

This can't be right. The report for function coverage shows 100% for
all utf8_and_*.c files, at the end of the listing. Notice how "C/D
coverage" (I don't know what it means but I assume it's somehow computed
per lines of code or something) is 0, which is probably the correct
result, because our regression tests do not test charset conversions at
all.

I think the bug may be that they use function names to see what is
actually tested ...

IIRC Gavin Sherry gave a URL to a test coverage result some centuries
ago. The only thing that I remember about the result was that it was
surprinsingly low (IMHO at least).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, 22 Feb 2006, Alvaro Herrera wrote:

Robert Treat wrote:

You could check into what spikesource has been doing. I believe they mostly
just piggyback off of our regression tests for postgresql core, but there
might still be something that could be built upon. If you look at this url
http://developer.spikesource.com/spikewatch/index.jsp?show=component-results&amp;comp-id=22074
the actual success information isnt terribly exciting but the "code coverage"
url shows something of more interest. There is more stuff if you dig around a
bit.

This can't be right. The report for function coverage shows 100% for
all utf8_and_*.c files, at the end of the listing. Notice how "C/D
coverage" (I don't know what it means but I assume it's somehow computed
per lines of code or something) is 0, which is probably the correct
result, because our regression tests do not test charset conversions at
all.

I think the bug may be that they use function names to see what is
actually tested ...

IIRC Gavin Sherry gave a URL to a test coverage result some centuries
ago. The only thing that I remember about the result was that it was
surprinsingly low (IMHO at least).

Yes. Coverage was about 50% from memory. This was coverage resulting from
regression tests.

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.

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?

Thanks,

Gavin

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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

[I neglected to cc the list in my reply earlier. Apologies to Gavin
for the double-post.]

On Feb 23, 2006, at 11:40 , Gavin Sherry wrote:

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.

This is the area I specifically had in mind when thinking of unit
tests. I am looking to do more work on the date/time code in
particular, and having a unit testing framework and tests to verify
expected behavior would definitely give me a greater sense of
security that I wasn't mucking stuff up.

Your earlier proposal was probably the one I remembered. Could you
comment on your experience with any of the existing unit testing
frameworks? In particular, did any stand out in applicability to
Postgres? What's your take on possible licensing issues?

Michael Glaesemann
grzm myrealbox com

On Fri, 24 Feb 2006, Michael Glaesemann wrote:

[I neglected to cc the list in my reply earlier. Apologies to Gavin
for the double-post.]

On Feb 23, 2006, at 11:40 , Gavin Sherry wrote:

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.

This is the area I specifically had in mind when thinking of unit
tests. I am looking to do more work on the date/time code in
particular, and having a unit testing framework and tests to verify
expected behavior would definitely give me a greater sense of
security that I wasn't mucking stuff up.

Yes. You sound like the perfect person to implement this :-).

Your earlier proposal was probably the one I remembered. Could you
comment on your experience with any of the existing unit testing
frameworks? In particular, did any stand out in applicability to
Postgres? What's your take on possible licensing issues?

I looked at Check and CuTest from memory. The former was more
sophisticated, if memory serves me correctly, because it had the ability
to fork and run the code from a child to see if it segfaulted, for
example.

The licensing issue is more of a pain. Obviously we cannot incorporate GPL
stuff into to the code base. CuTest seems to have a pretty BSD compatible
license, though.

That said, the actual implementation is very straight forward: 1) Queue
test functions which assert something about the result of a function 2)
Run the tests 3) capture pass/fails. We have some special requirements
with our code because it can ereport()/elog(). As such, it is quite
attractive to just write our own, if unit testing is to proceed.

Thanks,

Gavin

On Feb 24, 2006, at 13:25 , Gavin Sherry wrote:

On Feb 23, 2006, at 11:40 , Gavin Sherry wrote:

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.

This is the area I specifically had in mind when thinking of unit
tests. I am looking to do more work on the date/time code in
particular, and having a unit testing framework and tests to verify
expected behavior would definitely give me a greater sense of
security that I wasn't mucking stuff up.

Yes. You sound like the perfect person to implement this :-).

:) I'm willing to help out with it. I'd hope to get guidance both in
specifications and implementation. I'd probably need to write unit
tests to test the unit test framework as well, given my lack of C
experience. ;)

I looked at Check and CuTest from memory. The former was more
sophisticated, if memory serves me correctly, because it had the
ability
to fork and run the code from a child to see if it segfaulted, for
example.

I thought the forking bit was a definite plus for Check (and GNU
Autounit). I think this would be something we'd like to include.

The licensing issue is more of a pain. Obviously we cannot
incorporate GPL
stuff into to the code base. CuTest seems to have a pretty BSD
compatible
license, though.

My understanding of the current licensing policy is that if it's not
BSD (even if it's BSD-like), it doesn't go into the distribution. I
don't want to start a huge licensing debate, but that would preclude
using CuTest, which is zlib/libpng according to its homepage, and GPL
according to its Sourceforge site.

We have some special requirements
with our code because it can ereport()/elog(). As such, it is quite
attractive to just write our own, if unit testing is to proceed.

Rolling our own would definitely get around the licensing issues as
well.

I think it would be worth it to me to have unit tests covering the
date/time datatypes, and I would think hackers working on other
datatypes would benefit from the framework as well. However, this
estimation of "worth" is based from a position of relevant ignorance
on the time investment necessary to get this off the ground. I also
recognize that probably a majority of the backend code would *not* be
easily testable using such a framework. Do others see value in moving
ahead on this? What's the likelihood that a good implementation would
be accepted? Are there others that would be willing to work with me
on this?

Michael Glaesemann
grzm myrealbox com