Unit Testing Postgres with pg TAP BY LLOYD

Unit Testing Postgres with pg. TAP BY: LLOYD ALBIN 10/1/2013

pg. TAP is a suite of database functions that make it easy to write TAP-emitting unit tests in psql scripts or x. Unit-style test functions. The TAP output is suitable for harvesting, analysis, and reporting by a TAP harness, such as those used in Perl applications. http: //www. pgtap. org/

Installing the extension CREATE EXTENSION pgtap; The extension may be installed by the database owner or a Postgres superuser aka DBA. The extensions may also be installed via the TAP script, so that it’s functions are not left as part of the database.

A simple tap script -- Load the TAP functions. BEGIN; CREATE EXTENSION pgtap; -- Plan the tests. SELECT plan(1); --SELECT no_plan(); -- Run the tests SELECT pass('Passed one test'); -- Finish the tests and clean up. SELECT * FROM finish(); ROLLBACK; All TAP scripts must be run inside of a transaction and then get rolled back at the end so that any changes you may have made to the data get reversed, except for SERIAL sequences that may have been increased.

psql % psql -h sqltest -d postgres -Xf test. sql (With Plan) 1. . 1 ok 1 - Passed one test (Without Plan) ok 1 - Passed one test 1. . 1 We will create a basic table with just a primary key. We don’t need any other fields for this example.

pg_prove – single file or directory % /usr/local/apps/perl-current/bin/pg_prove -h sqltest -d postgres -v test. sql. . 1 ok 1 - Passed one test ok All tests successful. Files=1, Tests=1, 0 wallclock secs ( 0. 02 usr + 0. 01 sys = 0. 03 CPU) Result: PASS pg_prove is a Perl script that can be used to wrap around TAP scripts. You may also give a directory/*. sql to use every sql file in alphabetical order within the specified directory. Failed commands will show in red within pg_prove.

pg_prove - recursive % /usr/local/apps/perl-current/bin/pg_prove -h sqltest -d postgres --recurse -v --ext sql directory/testing/test. sql. . 1 ok 1 - Passed one test ok All tests successful. Files=1, Tests=1, 0 wallclock secs ( 0. 04 usr + 0. 01 sys = 0. 05 CPU) Result: PASS Using the --recurse option pg_prove will look in directory and all sub directories for. pg files even if you specify *. sql. To fix this use the --ext sql to change the default extension to the sql extension. I also prefer this so that editors like Eclipse will color code the sql files.

Setting Role for xapps owned databases – part 1 -- Load the TAP functions. BEGIN; -- Inline function to set the role for extension installation DO $BODY$ DECLARE db_owner record; BEGIN SELECT pg_user. usename INTO db_owner FROM pg_database LEFT JOIN pg_catalog. pg_user ON pg_database. datdba = pg_user. usesysid WHERE datname = current_database(); IF db_owner. usename <> current_user THEN EXECUTE 'SET ROLE ' || db_owner. usename; END IF; END $BODY$ LANGUAGE plpgsql; If xapps or df_mirror owns the database then, the extension needs to be installed by that user. But also many of the developer databases are owned by the individual developer. This inline function checks to see if the current user is the owner and if not tries to do a SET ROLE to the owner of the database.

Setting Role for xapps owned databases – part 2 -- Install the Extension CREATE EXTENSION pgtap; SET ROLE xapps; -- Run Tests Now you will be able to install the pg. TAP extension. Once that is done, you need to set the user to run the tests as. As long as you are a member of the role/group you with to SET ROLE, you may do this.

Setting Role for non-xapps owned databases – part 1 -- Load the TAP functions. BEGIN; -- Inline function to set the role for extension installation DO $BODY$ BEGIN IF current_database() = 'main' THEN SET ROLE dba; END IF; END $BODY$ LANGUAGE plpgsql; -- Install the Extension CREATE EXTENSION pgtap; The problem is for db. main, sqltest. main, atlassql. cpas, etc. Your devel copies will be owned by the developer and so we only need to do the SET ROLE for production servers. This means that these scripts will only be able to be run by a DBA on production databases.

Setting Role for non-xapps owned databases – part 2 … CREATE EXTENSION pgtap; DO $BODY$ BEGIN IF current_database() = 'main' THEN SET ROLE xapps; END IF; END $BODY$ LANGUAGE plpgsql; For databases such as main, the developer owns their own copy. With this inline function, if the database is a production version, then we want to SET ROLE as the application otherwise continue to run as the database owner. You may wish to add more logic for staging and testing databases.

Setting Role for non-xapps owned databases – part 3 … CREATE EXTENSION pgtap; CREATE TEMP TABLE db_server (server text); INSERT INTO db_server (server) VALUES (: 'HOST'); DO $BODY$ DECLARE server_name record; BEGIN SELECT server INTO server_name FROM db_server; IF server_name. server = 'sqltest' THEN SET ROLE xapps; END IF; END $BODY$ LANGUAGE plpgsql; If you need to write even more complex logic using the host name, you must set the host name into a temporary table because the : ’HOST’ variable is not accessible within inline functions.

Configuration Data -- Configuration Data SELECT diag('Configuration'); SELECT diag('=============='); SELECT diag('Postgres Version: ' || current_setting( 'server_version')); SELECT diag('pg. TAP Version: ' || pgtap_version()); SELECT diag('pg. TAP Postgres Version: ' || pg_version()); SELECT diag('Current Server: ' || : 'HOST'); SELECT diag('Current Database: ' || current_database()); SELECT diag('Current Session User: ' || session_user); SELECT diag('Current User: ' || current_user); SELECT diag(''); SELECT diag('Tests'); SELECT diag('=============='); Here is a sample set of configuration data that could be output at the start of a TAP script. This information could be useful to us/Quality to know which server/database the tests were run against.

Configuration Data - Output # Configuration # ================ # Postgres Version: 9. 2. 4 # pg. TAP Version: 0. 93 # pg. TAP Postgres Version: 9. 2. 4 # Current Server: sqltest # Current Database: postgres # Current Session User: postgres # Current User: dba # # Tests # ================ Here is the output of the configuration data code. It lets us know the Postgres version that we are executing against and the version of pg. TAP. The pg. TAP Postgres Version is also important as that is the version of Postgres that pg. TAP was compiled against. This should always be the same as the Postgres version but could be different.

Testing to make sure the correct Postgres Version SELECT ok((SELECT CASE WHEN current_setting( 'server_version_num') = pg_version_num(): : text THEN TRUE ELSE FALSE END), 'pg. TAP is compiled against the correct Postgres Version'); This allows us to have a test to check the Postgres Version against the Postgres Version that pg. TAP was compiled against.

What happens when you don’t update plan ok 1 - Passed one test ok 2 - pg. TAP is compiled against the correct Postgres Version # Looks like you planned 1 test but ran 2 All 1 subtests passed Test Summary Report ---------test. sql (Wstat: 0 Tests: 2 Failed: 1) Failed test: 2 Parse errors: Bad plan. You planned 1 tests but ran 2. Even though all your tests passed, you will still over all have a failure if you did not increase your plan to 2, pg_prove will show the extra line(s) in red and then complain about the number of test run vers the number of tests planed.

Testing for extensions SELECT is( (SELECT extname FROM pg_catalog. pg_extension WHERE extname = 'plpgsql') , 'plpgsql', 'Verifying extension plpgsql is installed'); SELECT is( (SELECT extname FROM pg_catalog. pg_extension WHERE extname = 'plperl') , 'plperl', 'Verifying extension plperl is installed'); SELECT is( (SELECT extname FROM pg_catalog. pg_extension WHERE extname = 'pgtap') , 'pgtap', 'Verifying extension pgtap is installed'); SELECT is( (SELECT count(*): : int FROM pg_catalog. pg_extension) , 3, 'Verifying no extra extensions are installed'); If your application requires an extension installed, you can test to make sure that the extension is installed. Here are some examples for the template_restore database. In this example I am also checking to make sure that no extra extensions are installed. Remember the pg. TAP extension will be automatically uninstalled at the end of the testing.

Testing for languages SELECT has_language( 'c' ); SELECT has_language( 'internal' ); SELECT has_language( 'sql' ); SELECT has_language( 'plpgsql' ); SELECT has_language( 'plperl' ); SELECT hasnt_language( 'plperlu' ); SELECT is( (SELECT count(*): : int FROM pg_catalog. pg_language) , 5, 'Verifying no extra languages are installed'); The test to make sure that plperlu is not installed is redundant because we are testing the number of languages installed. But when testing the number of languages, it does not tell you the extra languages.

Many possible tests SELECT db_owner_is( current_database(), 'postgres' ); SELECT has_table( 'dbreview' ); SELECT has_pk( 'dbreview' ); SELECT has_column('dbreview', 'datname', 'Verifying that table has field datname'); SELECT col_is_pk('dbreview', 'datname', 'Verifying that field datname is the primary key'); SELECT col_type_is('dbreview', 'datname', 'Verifying that field datname is of type NAME'); SELECT CASE WHEN : 'HOST' = 'atlassql' THEN skip('Skipping xapps tests', 2) WHEN : 'HOST' = 'atlassql-test' THEN skip('Skipping xapps tests', 2) ELSE collect_tap( has_user('xapps'), table_privs_are ('dbreview', 'xapps', ARRAY['SELECT'], 'Verifying xapps has SELECT privilages on dbreview') ) END; SELECT has_function('update_dboid'); This shows testing database ownership, table exists, table structure, etc. In some cases you may want to skip over some number of tests. In this case user xapps does not exist on the atlas servers. collect_tap may be used to bundle more than one tap command together.

Testing Functions / Prepared Queries PREPARE test_delete AS DELETE FROM dbreview WHERE datname = 'postgres'; SELECT lives_ok('test_delete', 'Testing manual DELETE from dbreview'); PREPARE fast_query AS SELECT update_dboid(); SELECT performs_ok('fast_query', 25, 'Making sure update_dboid() runs in under 25 ms'); You may test queries / functions as prepared queries. You may check to see if it returns ok or if it performs within a specified amount of time.

Testing single value query or function. SELECT is( (SELECT count(*): : int FROM pg_catalog. pg_language) , 5, 'Verifying no extra languages are installed'); SELECT is( (SELECT dumped FROM dbreview WHERE datname = 'postgres') , TRUE, 'Verifying is dumped for inserted database postgres'); SELECT isnt( (SELECT comments FROM dbreview WHERE datname = 'postgres') , 'None', 'Verifying inserted comment for database postgres'); is and isnt can used to test single values. These values may be number, boolean, string, etc.

Testing multiple row/columns results -- compare two queries with dynamic results SELECT results_eq( 'SELECT oid, datname, comments FROM dbreview WHERE deleted IS FALSE ORDER BY oid', 'SELECT a. oid, a. datname, b. description AS comments FROM pg_catalog. pg_database a LEFT JOIN pg_catalog. pg_shdescription b ON a. oid = b. objoid ORDER BY a. oid', 'Verifying that all current databases are listed in dbreview'); -- Comparing single column query to static results SELECT results_eq( 'SELECT datname FROM dbreview WHERE deleted IS FALSE ORDER BY datname', ARRAY['clinical_grade', 'df_mirror', 'df_repository', …], 'Verifying that all current databases are listed in dbreview'); -- Comparing multi column query to static results SELECT results_eq( 'SELECT oid, datname FROM dbreview WHERE deleted IS FALSE ORDER BY oid', $$VALUES (1, 'template 1'), (11866, 'template 0'), … $$, 'Verifying that all current databases are listed in dbreview'); results_eq allows you to test one query against a second query or against a set of static values. Using the array, each value is a row of data. Using the VALUES, each (x, y, …) is one row of data and can contain multiple columns of data.

Demo Time Show demo of tests that we have written.