Rigorous testing is the best way to ensure that your carefully crafted constraints, triggers, rules, views and stored procedures do exactly what they were intended to do (without unintended side-effects). Elaborate tests, when they run successfully, enable you to say with some confidence that, indeed, each of your pgsql schemas behaves according to specification.
In this tutorial, I propose some simple guidelines to make unit testing your PostgreSQL database as smooth as possible. They work for me, so who knows…
During the development of a complex PostgreSQL database for my employer, the need arose to test the correctness of complicated (trigger) functions. Although we were using Rails for the project infrastructure, we never wanted to depend on an application framework for work that so clearly belongs in the DB layer. Also, when trying to do unit tests the Rails way, we noticed that ActiveRecord is only an annoying get-in-the-way if you're doing things which really relate only to SQL.
The basic test unit which I use are simple functions written in one of PostgreSQL's procedural languages. So far, PL/pgSQL has proven to be particular well-suited to this task (although, sadly, setting explicit savepoints within PL/pgSQL isn't possible).
The following is a trigger function which, when used in a trigger, will set a created_at
timestamp
>
BEGIN
v_exception_raised := false;
INSERT INTO test.test (created_at) VALUES ('2000-01-01');
EXCEPTION WHEN raise_exception THEN v_exception_raised := true; END;
PERFORM test.assert(v_exception_raised, 'Inserting a wrong created_at timestamp should not be possible.');
<>
BEGIN
v_exception_raised := false;
UPDATE test.test SET created_at = '2000-01-01';
EXCEPTION WHEN raise_exception THEN v_exception_raised := true; END;
PERFORM test.assert(v_exception_raised, 'Changing a created_at timstamp during an update should not be possible.');
END;
$$ LANGUAGE plpgsql;]]>
Like all our test functions, this function return nothing and does nothing except raise an exception when an assertion fails.
help_global.pgsql
Isolation is provided by calling the test functions each from within a separate transaction that is rolled back after the call to test function. Later, we'll see a Rakefile which already does that for us. Of course, when a test assertion raises an exception, a rollback will also occur.
It would be nice if, within a PL/pgSQL function, explicit savepoints could be used to more cleanly separate individual assertions, but apparently I'm the only one in the PostgreSQL community who thinks so…
test
schemaI create all the test functions in the test
schema. When I create mock tables, triggers and views, I tend to also create those there. Sticking everything in a separate schema gives me another level of isolation.
I've decidedly chosen not to depend on a separate test database to run our tests in. (Rails uses three environments with separate databases: testing, development and production.) I wanted to remain flexible and be able to run tests in my development environment during the development of schema changes without having to clone the whole development DB to my test DB all the time. (Different from Rails' default, I also regularly clone our complete production DB to my development DB periodically which makes my schema changes more likely not to fail during deployment.)
I've also decided against sticking the test functions in the schemas where they apply, because, when you want to access your test functions directly from your database (think pgAdmin or psql) instead of from your Framework
's command-line, this way it's much easier to get a summary of which tests there are.
The test functions are named according to test_[schema]_[what_to_test]()
, where [schema]
is the schema where the tested entity lives, and [what_to_test]
is the entity (a name of a relation, function or constraint) to test. When necessary, [what_to_test]
can also be the name of a behaviour that crosses, for example, multiple tables, triggers and views.
Each of these test functions is then put into a separate file following the function name. The above test lives in a file called test_common_set_and_protect_created_at_timestamp.pgsql
.
Sometimes, you might have a chunk of functionality (such as setting up some test data) which you don't want to repeat for each test function. You can define helper functions for this functionality. These functions should go in files named help_*.pgsql
, where *
should match some part of the filenames of the tests for which you want the helpers to be loaded.
The advantage of the simple test_[schema]_[entity]
naming convention becomes clear once you start running tests. The following simple Rakefile, for example, lets you run a batch of tests very easily by letting your run rake
followed by the filenames of the tests. Then, thanks to your shell's completion and wildcard expansion, you can easily identify and group tests. Simply issuing
in my test directory will (re)load and run the tests for everything in the schema called rake test_common_*
common
.
Rake is a very powerful Ruby alternative to Make. I've made this Rakefile to integrate in a Rails application environment, but by modifying the db_env
task, it can easilly be made to work with any environment. Its only dependency that's not already distributed with Ruby's standard library is ruby-postgres.
[:pg_conn, :clear_test_schema, :load_global_helper] do
print "#{$yellow_begin}Loading helper #{help_fname}: #{$color_end}"
begin
$db_conn.exec $search_path
$db_conn.exec File.open(help_fname).read
rescue PGError
puts $failed
raise
else
puts $succeeded
end
end
end
# Traverse all filenames prefixed with 'test_'
FileList['test_*.pgsql'].each do |test_fname|
# Find files with helper functions
test_basename = test_fname.gsub(/\..*sql$/, '').gsub(/^test_/, '')
test_basename.count('_').times do
test_basename = test_basename.gsub(/_[^_]+$/, '')
help_fname = "help_#{test_basename}.pgsql"
if File.exists? help_fname
file test_fname => help_fname do
end
end
end
file test_fname => [:pg_conn, :clear_test_schema, :load_global_helper] do
file_basename = File.basename(test_fname, '.pgsql')
print "#{$yellow_begin}Loading test #{test_fname}: #{$color_end}"
# Only show me bad things. I don't want to drown in NOTICEs
$db_conn.exec "SET client_min_messages TO 'WARNING'"
begin
# (Re)load test function
$db_conn.exec $search_path
$db_conn.exec File.open(test_fname).read
rescue PGError
puts $failed
raise
else
puts $succeeded
end
print "#{$yellow_begin}Running test #{file_basename}(): #{$color_end}"
begin
# Execute the test function, and rollback any changes it might have made
$db_conn.exec "BEGIN"
$db_conn.exec "SELECT test.#{test_fname.gsub(/\..*sql$/, '')}()"
$db_conn.exec "ROLLBACK"
rescue PGError
puts $failed
raise
else
puts $succeeded
end
end
end
task :load_global_helper => :clear_test_schema do
print "#{$yellow_begin}Loading global helper: #{$color_end}"
begin
$db_conn.exec $search_path
$db_conn.exec File.open("help_global.pgsql").read
rescue PGError
puts $failed
raise
else
puts $succeeded
end
end
task :clear_test_schema => :pg_conn do
print "#{$yellow_begin}Clearing test schema: #{$color_end}"
begin
$db_conn.exec "SET client_min_messages TO 'WARNING'"
$db_conn.exec "BEGIN"
$db_conn.exec "DROP SCHEMA test CASCADE"
$db_conn.exec "CREATE SCHEMA test"
$db_conn.exec "COMMIT"
rescue PGError
puts $failed
raise
else
puts $succeeded
end
end
task :pg_conn => :pg_env do
require 'postgres'
puts "Connecting to #{ENV['PGDATABASE']} on #{ENV['PGHOST']}:#{ENV['PGPORT']}."
$db_conn = PGconn.open(
ENV['PGHOST'], ENV['PGPORT'].to_i, "", "", ENV['PGDATABASE'],
ENV['PGUSER'], ENV['PGPASSWORD']
)
end
# Change this task to fit your particular environment
task :pg_env do
require 'yaml'
require 'erb'
RAILS_ROOT = File.dirname(__FILE__) + '/../../'
db_cfg = YAML.load(
ERB.new(
File.open(File.dirname(__FILE__) + '/../../config/database.yml').read
).result
)[ ENV['RAILS_ENV'] || 'development' ]
ENV['PGHOST'] = db_cfg['host']
ENV['PGPORT'] = db_cfg['port'].to_s
ENV['PGUSER'] = db_cfg['username']
ENV['PGPASSWORD'] = db_cfg['password']
ENV['PGDATABASE'] = db_cfg['database']
end
# vim: set filetype=ruby shiftwidth=2 tabstop=2 expandtab:
]]>
30 okt. 2007 – Too long a time ago already, Halfgaar fixed a glaring bug in the test function which I use as an example in this text. I was catching an error generated by a trigger (raise_exception
). If such an error was caught, I'd raise an error from within the BEGIN...END
block, which, of course, would cause a loop of errors being raised by the block which was catching them. Because there's only one type of fatal exception which you can raise yourself, the solution was as simple as setting a boolean which lasts untill after the BEGIN...END
block. Today, I finally updated the text with his fixes (and some other of his improvements), hoping that not too many people have blindly copied the previous verion (although it would work fine when handling most other types of exception).
Let me know if anything is unclear or missing.