Pgtap (unit testing)

Terminology

• Unit testing: A software testing method that tests individual components or modules of a system to verify their correctness.

• Test Anything Protocol (TAP): A language-agnostic protocol for communicating test results between test harnesses. Originally developed for Perl, it simplifies error reporting during testing.

Usage

• Install the extension

  CREATE EXTENSION pgtap;

• Uninstall the extension

  DROP EXTENSION pgtap;

Examples

The pgtap extension provides a wide range of test functions for database objects, including tablespaces, schemas, tables, columns, views, sequences, indexes, triggers, functions, policies, users, languages, rules, types, operators, and extensions.

The following examples show how to test tables, policies, columns, and functions with the pgtap extension.

• Test tables, indexes, and views

  To check if a table, index, or view exists, use the following example:

  BEGIN;
  SELECT plan(6);
  
  -- Check if the "tap_table" table exists
  SELECT has_table('tap_table');
  
  -- Check if the "tap_table_non_exist" table does not exist
  SELECT hasnt_table('tap_table_non_exist');
  
  -- Check if the "tap_view" view exists
  SELECT has_view('tap_view');
  
  -- Check if the "materialized_tap_view" materialized view exists
  SELECT has_materialized_view('materialized_tap_view');
  
  -- Check if the "tap_table" table has an index named "tap_table_index"
  SELECT has_index('tap_table', 'tap_table_index');
  
  -- Check if "tap_table" is a relation
  SELECT has_relation('tap_table');
  
  SELECT * FROM finish();
  ROLLBACK;

  Note

  Function descriptions:

  • has_table checks if a table exists, while hasnt_table checks if it does not.

  • has_view checks if a specified view exists.

  • has_materialized_view checks if a specified materialized view exists.

  • has_index checks if a table contains a specific index.

  • has_relation checks if a specified relation, such as a table, index, or sequence, exists.

  Running the preceding commands returns the following output. The tests fail because the specified objects do not exist.

                      has_table
  -------------------------------------------------
   not ok 1 - Table tap_table should exist        +
   # Failed test 1: "Table tap_table should exist"
  (1 row)
                      hasnt_table
  ---------------------------------------------------
   ok 2 - Table tap_table_non_exist should not exist
  (1 row)
                     has_view
  -----------------------------------------------
   not ok 3 - View tap_view should exist        +
   # Failed test 3: "View tap_view should exist"
  (1 row)
                            has_materialized_view
  -------------------------------------------------------------------------
   not ok 4 - Materialized view materialized_tap_view should exist        +
   # Failed test 4: "Materialized view materialized_tap_view should exist"
  (1 row)
                         has_index
  -------------------------------------------------------
   not ok 5 - Index tap_table_index should exist        +
   # Failed test 5: "Index tap_table_index should exist"
  (1 row)
                      has_relation
  ----------------------------------------------------
   not ok 6 - Relation tap_table should exist        +
   # Failed test 6: "Relation tap_table should exist"
  (1 row)
                  finish
  --------------------------------------
   # Looks like you failed 5 tests of 6
  (1 row)

  Run the following commands to create the required table, index, and views:

  CREATE TABLE tap_table(col INT PRIMARY KEY, tap_desc TEXT);
  CREATE INDEX tap_table_index on tap_table(col);
  CREATE VIEW tap_view AS SELECT * FROM tap_table;
  CREATE MATERIALIZED VIEW materialized_tap_view AS SELECT * FROM tap_table;

  After creating the objects, run the TAP test again. The following output is returned:

               has_table
  -------------------------------------
   ok 1 - Table tap_table should exist
  (1 row)
                      hasnt_table
  ---------------------------------------------------
   ok 2 - Table tap_table_non_exist should not exist
  (1 row)
               has_view
  -----------------------------------
   ok 3 - View tap_view should exist
  (1 row)
                      has_materialized_view
  -------------------------------------------------------------
   ok 4 - Materialized view materialized_tap_view should exist
  (1 row)
                   has_index
  -------------------------------------------
   ok 5 - Index tap_table_index should exist
  (1 row)
                has_relation
  ----------------------------------------
   ok 6 - Relation tap_table should exist
  (1 row)
   finish
  --------
  (0 rows)

• Test RLS policies

  To check if a table has a specific row-level security (RLS) policy, use the following example:

  CREATE USER tap_user_1;
  CREATE USER tap_user_2;
  CREATE TABLE tap_table(col INT PRIMARY KEY, tap_desc TEXT);
  CREATE POLICY tap_policy ON tap_table FOR select TO tap_user_1, tap_user_2;
  
  BEGIN;
  SELECT plan(5);
  SELECT policy_cmd_is(
      'public',
      'tap_table',
      'tap_policy'::NAME,
      'select'
  );
  SELECT policy_roles_are(
    'public',
    'tap_table',
    'tap_policy',
    ARRAY [
      'tap_user_1', -- Check if the "tap_policy" RLS policy applies to the user "tap_user_1"
      'tap_user_2' -- Check if the "tap_policy" RLS policy applies to the user "tap_user_2"
    ]
  );
  SELECT policies_are(
    'public',
    'tap_table',
    ARRAY [
      'tap_policy' -- Check if the "tap_table" table has the "tap_policy" RLS policy
    ]
  );
  SELECT * FROM check_test(
      policy_roles_are(
        'public',
        'tap_table',
        'tap_policy',
        ARRAY [
          'tap_user_1' -- Test for an expected failure by checking an incomplete list of roles
        ]),
      false,
      'check policy roles',
      'Policy tap_policy for table public.tap_table should have the correct roles');
  SELECT * FROM finish();
  ROLLBACK;
  
  DROP POLICY tap_policy ON tap_table;
  DROP TABLE tap_table;
  DROP USER tap_user_1;
  DROP USER tap_user_2;

  Note

  Function descriptions:

  • policy_cmd_is checks the command type to which the RLS policy applies.

  • policy_roles_are is used to check whether an RLS policy is applied to all of its specified users. The function returns TRUE if and only if all users to whom the RLS policy applies are specified.

  • policies_are checks if a table has a specific set of RLS policies.

  To test for an expected failure, you can use the check_test function and set the expected result to TRUE or FALSE. The test returns the following results:

                                     policy_cmd_is
  ------------------------------------------------------------------------------------
   ok 1 - Policy tap_policy for table public.tap_table should apply to SELECT command
  (1 row)
                                   policy_roles_are
  -----------------------------------------------------------------------------------
   ok 2 - Policy tap_policy for table public.tap_table should have the correct roles
  (1 row)
                            policies_are
  ----------------------------------------------------------------
   ok 3 - Table public.tap_table should have the correct policies
  (1 row)
                            check_test
  --------------------------------------------------------------
   ok 4 - check policy roles should fail
   ok 5 - check policy roles should have the proper description
  (2 rows)
   finish
  --------
  (0 rows)

• Test columns

  To check if a column exists in a table or if it is a primary key or foreign key, use the following example:

  CREATE TABLE tap_table(col INT PRIMARY KEY, tap_desc TEXT);
  CREATE INDEX tap_table_index ON tap_table(col);
  CREATE UNIQUE INDEX tap_table_unique_index ON tap_table(col);
  
  BEGIN;
  SELECT plan(7);
  -- Test if the "col" column is the primary key of the "tap_table" table
  SELECT col_is_pk('tap_table', 'col');
  -- Test if the "tap_desc" column is not the primary key of the "tap_table" table
  SELECT col_isnt_pk('tap_table', 'tap_desc');
  -- Test if the "col" column is a foreign key of the "tap_table" table
  SELECT * FROM check_test(
      col_is_fk('tap_table', 'col'),
      false,
      'check foreign key of table',
      'Column tap_table(col) should be a foreign key');
  -- Test if the "col" column is not a foreign key of the "tap_table" table
  SELECT col_isnt_fk('tap_table', 'col');
  -- Test if the "col" column exists in the "tap_table" table
  SELECT has_column('tap_table', 'col');
  -- Test if the "non_col" column does not exist in the "tap_table" table
  SELECT hasnt_column('tap_table', 'non_col');
  SELECT * FROM finish();
  ROLLBACK;
  
  DROP TABLE tap_table;

  Note

  Function descriptions:

  • col_is_pk checks if a column is the primary key of a table.

  • col_isnt_pk checks if a column is not the primary key of a table.

  • col_isnt_fk checks if a column is not a foreign key.

  • has_column checks if a table contains a specific column.

  • hasnt_column checks if a table does not contain a specific column.

  The test returns the following results:

                        col_is_pk
  ------------------------------------------------------
   ok 1 - Column tap_table(col) should be a primary key
  (1 row)
                            col_isnt_pk
  ---------------------------------------------------------------
   ok 2 - Column tap_table(tap_desc) should not be a primary key
  (1 row)
                                check_test
  ----------------------------------------------------------------------
   ok 3 - check foreign key of table should fail
   ok 4 - check foreign key of table should have the proper description
  (2 rows)
                         col_isnt_fk
  ----------------------------------------------------------
   ok 5 - Column tap_table(col) should not be a foreign key
  (1 row)
                  has_column
  ------------------------------------------
   ok 6 - Column tap_table.col should exist
  (1 row)
                     hasnt_column
  --------------------------------------------------
   ok 7 - Column tap_table.non_col should not exist
  (1 row)
   finish
  --------
  (0 rows)

• Test functions

  To check a function's return type or verify if it is a SECURITY DEFINER function, use the following example:

  CREATE OR REPLACE FUNCTION tap_function()
  RETURNS text
  AS $$
  BEGIN
      RETURN 'This is tap test function';
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER;
  
  CREATE OR REPLACE FUNCTION tap_function_bool(arg1 integer, arg2 boolean, arg3 text)
  RETURNS boolean
  AS $$
  BEGIN
      RETURN true;
  END;
  $$ LANGUAGE plpgsql;
  
  BEGIN;
  SELECT plan(6);
  -- Check if the "tap_function" function returns the "text" type
  SELECT function_returns('tap_function', 'text');
  -- Check if the "tap_function_bool" function with the argument list ('integer', 'boolean', 'text') returns the "boolean" type
  SELECT function_returns('tap_function_bool', ARRAY['integer', 'boolean', 'text'], 'boolean');
  -- Check if the "tap_function" function is a SECURITY DEFINER
  SELECT is_definer('tap_function');
  -- Check if the "tap_function_bool" function is not a SECURITY DEFINER
  SELECT isnt_definer('tap_function_bool');
  -- Check if the "tap_function_bool" function is a SECURITY DEFINER
  SELECT * FROM check_test(
      is_definer('tap_function_bool'),
      false,
      'check function security definer',
      'Function tap_function_bool() should be security definer');
  SELECT * FROM finish();
  ROLLBACK;
  
  DROP FUNCTION tap_function;
  DROP FUNCTION tap_function_bool;

  Note

  Function descriptions:

  • function_returns checks if a function, with an optionally specified argument list, returns a specific type.

  • is_definer checks if a specified function is a SECURITY DEFINER function.

  • isnt_definer checks if a specified function is not a SECURITY DEFINER function.

  The test returns the following results:

   function_returns
  ---------------------------------------------------
   ok 1 - Function tap_function() should return text
  (1 row)
                                  function_returns
  ---------------------------------------------------------------------------------
   ok 2 - Function tap_function_bool(integer, boolean, text) should return boolean
  (1 row)
                          is_definer
  -----------------------------------------------------------
   ok 3 - Function tap_function() should be security definer
  (1 row)
                              isnt_definer
  --------------------------------------------------------------------
   ok 4 - Function tap_function_bool() should not be security definer
  (1 row)
                                  check_test
  ---------------------------------------------------------------------------
   ok 5 - check function security definer should fail
   ok 6 - check function security definer should have the proper description
  (2 rows)
   finish
  --------
  (0 rows)

• Others

  The pgtap extension includes many test functions not covered in these examples. While the functions themselves differ, they follow a similar usage pattern. For a complete list of functions, see the pgtap official documentation.