pgtap

Prerequisites

SHOW polar_version;

Terms

• Unit testing: allows you to test the correctness of different modules of the system. It is also known as module testing.

• Test anything protocol (TAP): provides a mechanism for communicating test results to a test harness, while maintains language independence and simplifies error reporting during testing. It was originally developed for the Perl language.

Usage

ALTER DATABASE <dbname> SET polar_enable_empty_string_is_null=off;

• Create the extension.

  CREATE EXTENSION pgtap;

• Remove the extension.

  DROP EXTENSION pgtap;

Test examples

The pgTAP extension provides a wide range of test methods for tablespaces, schemas, tables, columns, views, sequences, indexes, triggers, functions, policies, users, languages, rules, classes, operators, and extensions.

The following examples show how to use the pgTAP extension for tables, policies, columns, and functions.

• Table, index, or view test

  To check whether a table, an index, and a view exist, use the following sample script:

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

  Note

  • has_table and hasnt_table are used to check whether the specified table exists.

  • has_view is used to check whether the specified view exists.

  • has_materialized_view is used to check whether the specified materialized view exists.

  • has_index is used to check whether a table has the specified index.

  • The has_relation is used to check whether the specified relation exists, which can be a table, an index, or a sequence.

  The following sample results of the preceding script indicate that the specified objects such as the table and index 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)

  Execute the following statements to create the table, index, and view:

  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 the table, index, and view are created, perform the preceding TAP test again. The following sample results are displayed:

               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)

• RLS policy test

  To check whether a row-level security (RSL) policy exists in a table, use the following sample script:

  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 [
      'tab_user_1', -- Check whether the tab_user_1 user is restricted by the RLS policy tab_policy.
      'tab_user_2' -- Check whether thetab_user_2 user is restricted by the RLS policy tab_policy.
    ]
  );
  SELECT policies_are(
    'public',
    'tap_table',
    ARRAY [
      'TAP_POLICE' -- Check whether the tap_policy table contains the RLS policy tab_policy.
    ]
  );
  SELECT * FROM check_test(
      policy_roles_are(
        'public',
        'tap_table',
        'tap_policy',
        ARRAY [
          'tape_user_1' -- Check whether the tab_user_1 user is the only user restricted by the RLS policy tab_policy.
        ]),
      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

  • policy_cmd_is is used to check the specified RLS policy exists.

  • policy_roles_are is used to check whether the RLS policy applies to all users. The function returns TRUE if and only if all users to which the RLS policy applies are specified.

  • policies_are is used to check whether a table contains an RLS policy.

  To test expected error results, you can use the check_test method to specify the expected results as TRUE or FALSE. Sample 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)

• Column test

  To check whether a column in a table exists and whether the column is a primary key or a foreign key, use the following sample script:

  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);
  -- Check whether the col column is the primary key of the tap_table table.
  SELECT col_is_pk('tap_table', 'col');
  -- Check whether the tap_desc column is not a primary key of the tap_table table.
  SELECT col_isnt_pk('tap_table', 'tap_desc');
  -- Check whether 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');
  -- Check whether the col column is not a foreign key of the tap_table table.
  SELECT col_isnt_fk('tap_table', 'col');
  -- Check whether the tap_table table contains the col column.
  SELECT has_column('tap_table', 'col');
  -- Check whether the tap_table table does not contain the col column.
  SELECT hasnt_column('tap_table', 'non_col');
  SELECT * FROM finish();
  ROLLBACK;
  
  DROP TABLE tap_table;

  Note

  • col_is_pk is used to check whether a column is a primary key column of a table.

  • col_isnt_pk is used to check whether a column is not a primary key of a table.

  • col_isnt_fk is used to check whether a column is not a foreign key of a table.

  • has_column is used to check whether a table contains a column.

  • hasnt_column is used to check whether a table does not contain a column.

  Sample 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)

• Function test

  To check the return value type of a function and whether the function is SECURITY DEFINER, use the following sample script:

  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 whether the return value type of the tap_function function is TEXT.
  SELECT function_returns('tap_function', 'text');
  -- Check whether the return value type of the tap_function_bool function whose parameter list is INTEGER, BOOLEAN, and TEXT is BOOLEAN.
  SELECT function_returns('tap_function_bool', ARRAY['integer', 'boolean', 'text'], 'boolean');
  -- Check whether the tap_function function is SECURITY DEFINER.
  SELECT is_definer('tap_function');
  -- Check whether the tap_function_bool function is not SECURITY DEFINER.
  SELECT isnt_definer('tap_function_bool');
  -- Check whether the tap_function_bool function is 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_returns is used to check whether the return value type of a function is a specified type or specify the parameter list of a function.

  • is_definer is used to check whether the specified function is SECURITY DEFINER.

  • isnt_definer is used to detect whether the specified function is not SECURITY DEFINER.

  Sample 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)

• Other tests

  The pgTAP extension provides other test methods in addition to the preceding tests. Although the test items are different, the overall usage is the same as the preceding examples. For more information, see pgTAP documentation.