I have a mixed love/hate relationship with tests.

I hate writing them. I hate remembering to add them when I'm in the zone, and application code is flowing freely from the tips of my fingers.

But when I do add them, I absolutely love the ability to twist and replace the most core innards of application, and be able to tell that at least the sanity check of the code passes.

I love them even more when they prevent me for repeating some mistake/bug – i.e. when there is a bug, and I have tests (which clearly failed, as they didn't catch the bug), I add test for this specific bug, so I know that in future it will not happen again.

For a long time I've been fan of stored procedures (or functions) – of course not for everything, but where it makes sense.

For quite some time now (first version that I see is from 2008-06-17 – 2 years ago), we can use pgTAP.

What it is? First, let's start with name. pg is obviously from PostgreSQL. TAP is acronym and means ‘Test Anything Protocol'. In case you're not familiar with it – basically it is just a standardized way of reporting success/failure of test program(s).

So. First of all – should you use it?

Answer is complex, but generally – if you use any stored procedures/functions, and or triggers – I would at least consider using them.

Let's assume you have trigger which changes full name of person to properly capitalized version. That is – it will change ‘hubert LUBACZEWSKI' into ‘Hubert Lubaczewski'.

Code is trivial:

CREATE OR REPLACE FUNCTION capitalize_fullname() RETURNS TRIGGER AS $BODY$
BEGIN
NEW.full_name := INITCAP( NEW.full_name );
RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER capitalize_fullname
BEFORE INSERT OR UPDATE ON people
FOR EACH ROW EXECUTE PROCEDURE capitalize_fullname();

And it works fairly well:

# INSERT INTO people (full_name) VALUES ('hubert LUBACZEWSKI');
INSERT 0 1
 
# SELECT * FROM people;
id |     full_name
----+--------------------
1 | Hubert Lubaczewski
(1 ROW)

Now. Let's assume you've got complaint, that your code incorrectly capitalized name of “Louis d'Orléans" – it made his name into “Louis D'Orléans". Which is clearly wrong.

Before I'll go to fix it, let's add test suite (simple test suite for now) for my database.

First – let's get pgTap. On it's site I go to download page, and download newest tar.bz2, unpacked, and did make && make install (you might need to use sudo make install in case Pg is installed somewhere where you don't have write access to).

After installation I got 5 new files:

• /home/pgdba/work/share/postgresql/contrib/pgtap.sql
• /home/pgdba/work/share/postgresql/contribuninstall_pgtap.sql
• /home/pgdba/work/share/doc/postgresql/contrib/README.pgtap
• /home/pgdba/work/bin/pg_prove
• /home/pgdba/work/bin/pg_tapgen

(of course in another situation, the paths might be different).

Now. For to use pgtap, we need to load it's functions to database. At this moment quick notice – I could have used make TAPSCHEMA=something, but for this simple test, I just don't care.

So, let's load pgTap to my test database:

# psql -f `pg_config --sharedir`/contrib/pgtap.sql
<a lot OF "CREATE FUNCTION" messages>

With this, I'm ready to actually make some tests.

In app directory, I create “tests" subdirectory, and store there first test file:

=$ cat tests/t-01.sql
BEGIN;
SELECT plan(1);
 
SELECT lives_ok(
'INSERT INTO people (full_name) VALUES ($$abc$$)',
'Inserting to people should work, and not raise exception.'
);
 
SELECT finish();
ROLLBACK;

Please notice that the whole test is within transaction that gets rolled back. Reason is simple – tests shouldn't pollute database with test data.

Now, I can run this simple test “suite":

=$ pg_prove tests/*sql
tests/t-01.sql .. ok
<b>All tests successful.</b>
Files=1, Tests=1,  0 wallclock secs ( 0.02 usr +  0.00 sys =  0.02 CPU)
Result: PASS

Nice. This test only shows that I can insert data and the trigger doesn't break. Now, let's add tests for actual capitalization. These can be in the same file (t-01.sql), but I'll add them in 2nd test file:

=$ cat tests/t-02.sql
BEGIN;
SELECT plan(3);
 
INSERT INTO people (full_name) VALUES ( 'abc' );
SELECT IS( full_name, 'Abc', 'Capitalization of >Abc<' ) FROM people WHERE id = currval( 'people_id_seq' );
 
INSERT INTO people (full_name) VALUES ( 'DEPESZ' );
SELECT IS( full_name, 'Depesz', 'Capitalization of >Depesz<' ) FROM people WHERE id = currval( 'people_id_seq' );
 
INSERT INTO people (full_name) VALUES ( 'HuBeRt dEPesZ LubaCZEWski' );
SELECT IS( full_name, 'Hubert Depesz Lubaczewski', 'Capitalization of >Hubert Depesz Lubaczewski<' ) FROM people WHERE id = currval( 'people_id_seq' );
 
SELECT finish();
ROLLBACK;

Please notice that I had to bump plan() to be 3.

Now, I can run it, and it shows that we're fine:

=$ pg_prove tests/*sql
tests/t-01.sql .. ok
tests/t-02.sql .. ok
All tests successful.
Files=2, Tests=4,  0 wallclock secs ( 0.02 usr +  0.00 sys =  0.02 CPU)
Result: PASS

So, now I can add test that will actually fail – with Louis d'Orléans:

=$ cat tests/t-03.sql
BEGIN;
    SELECT plan(1);
 
    INSERT INTO people (full_name) VALUES ( 'Louis d''Orléans' );
    SELECT IS( full_name, 'Louis d''Orléans', 'Capitalization of >Louis d''Orléans<' ) FROM people WHERE id = currval( 'people_id_seq' );
 
    SELECT finish();
ROLLBACK;

And test run:

=$ pg_prove tests/*sql
tests/t-01.sql .. ok
tests/t-02.sql .. ok
tests/t-03.sql .. 1/1
not ok 1 - Capitalization of >Louis d'Orléans<
# Failed test 1: "Capitalization of >Louis d'Orléans<"
#         have: Louis D'Orléans
#         want: Louis d'Orléans
# Looks like you failed 1 test of 1
tests/t-03.sql .. Failed 1/1 subtests
 
Test Summary Report
-------------------
tests/t-03.sql (Wstat: 0 Tests: 1 Failed: 1)
  Failed test:  1
Files=3, Tests=5,  0 wallclock secs ( 0.01 usr  0.02 sys +  0.00 cusr  0.01 csys =  0.04 CPU)
Result: FAIL

As you can see the test clearly failed, it showed there it failed (tests/t-03.sql, not ok 1 – failed test #1), and it showed nicely what it got, and what it should get to pass.

Now, I can fix the trigger, make sure the test pass, and release the code.

The great thing is that if I'll ever want to switch from (for example) plpgsql trigger to plperl one – I got cover that checks whatever errors we previously had so we will not hit the same issue twice. And that's a great thing.

One final note: regardless of how big your test suite is, how many thousands of cases it checks, how many edge cases, how many previous errors – it can never be said: “my code is correct because tests pass".

It can be said: “my code is not correct because tests fail", or “I don't know of any more bugs in the code, because all tests have passed".

But tests will never prove that your code is fully correct, because it is technically not possible to test all of the possible cases.

With this in mind – use tests. Play with it, and see what functionality David put in there (the is() and lives_ok() functions are just the beginning). It is really astonishing piece of work. And will make your life, as DBA simpler. After some getting used to it 🙂