theory / pgtap

PostgreSQL Unit Testing Suite

pgTAP 1.3.4

pgTAP is a unit testing framework for PostgreSQL written in PL/pgSQL and PL/SQL. It includes a comprehensive collection of TAP-emitting assertion functions, as well as the ability to integrate with other TAP-emitting test frameworks. It can also be used in the xUnit testing style. For detailed documentation, see the documentation in doc/pgtap.mmd or online.

pgTAP must be installed on a host with PostgreSQL server running; it cannot be installed remotely.

To build it, just do this:

make
make install
make installcheck

If you encounter an error such as:

"Makefile", line 8: Need an operator

You need to use GNU make, which may well be installed on your system as gmake:

gmake
gmake install
gmake installcheck

If you encounter an error such as:

make: pg_config: Command not found

Or:

Makefile:52: *** pgTAP requires PostgreSQL 9.1 or later. This is .  Stop.

Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:

env PG_CONFIG=/path/to/pg_config make && make install && make installcheck

And finally, if all that fails, copy the entire distribution directory to the contrib/ subdirectory of the PostgreSQL source tree and try it there without pg_config:

env NO_PGXS=1 make && make install && make installcheck

If you encounter an error such as:

ERROR:  must be owner of database regression

You need to run the test suite using a super user, such as the default "postgres" super user:

make installcheck PGUSER=postgres

If you encounter an error such as:

ERROR: Missing extensions required for testing: citext isn ltree

Install the PostgreSQL Additional Supplied Modules, which are required to run the tests. If you used a package management system such as RPM to install PostgreSQL, install the -contrib package.

Once pgTAP is installed, you can add it to a database by connecting as a super user and running:

CREATE EXTENSION pgtap;

If you've upgraded your cluster to PostgreSQL 9.1 and already had pgTAP installed, you can upgrade it to a properly packaged extension with:

CREATE EXTENSION pgtap FROM unpackaged;

If you want to install pgTAP and all of its supporting objects into a specific schema, use the SCHEMA clause to specify the schema, like so:

CREATE EXTENSION pgtap SCHEMA tap;

Dependencies

pgTAP requires PostgreSQL 9.1 or higher.

Copyright and License

Copyright (c) 2008-2023 David E. Wheeler. Some rights reserved.

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL DAVID E. WHEELER BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF DAVID E. WHEELER HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

DAVID E. WHEELER SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND DAVID E. WHEELER HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

pgtap's Issues

download page is out of date

The download page (linked from https://github.com/theory/pgtap/blob/gh-pages/index.html) leads to pgfoundry, where the latest download is version 0.90.0, but the latest release is already 0.93.0.

Operator Test Functions Not Ideal

The has_operator(), has_leftop(), and has_rightop() functions do not allow schema names to be passed for the type parameters. I think it makes sense to just let them be included in the strings, and use pg_format_type() to format the type names.

Once that's done, add operator_owner_is(), leftop_owner_is(), and rightop_owner_is(). Here are some functions to get started on those:

CREATE OR REPLACE FUNCTION _get_operator_owner ( NAME, NAME, NAME, NAME )
RETURNS NAME AS $$
    SELECT pg_catalog.pg_get_userbyid(t.typowner)
      FROM pg_catalog.pg_operator  o
      JOIN pg_catalog.pg_namespace n ON o.oprnamespace = n.oid
      JOIN pg_catalog.pg_type      l ON o.oprleft      = l.oid
      JOIN pg_catalog.pg_type      r ON o.oprright     = r.oid
     WHERE n.nspname = $1
       AND o.oprname = $2
       AND pg_catalog.format_type(l.oid, l.typtypmod) = $3
       AND pg_catalog.format_type(r.oid, 3.typtypmod) = $4
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _get_operator_owner ( NAME, NAME, NAME )
RETURNS NAME AS $$
    SELECT pg_catalog.pg_get_userbyid(t.typowner)
      FROM pg_catalog.pg_operator  o
      JOIN pg_catalog.pg_type      l ON o.oprleft      = l.oid
      JOIN pg_catalog.pg_type      r ON o.oprright     = r.oid
     WHERE o.oprname = $1
       AND pg_catalog.format_type(l.oid, l.typtypmod) = $2
       AND pg_catalog.format_type(r.oid, 3.typtypmod) = $3
       AND pg_catalog.pg_operator_is_visible(o.oid)
$$ LANGUAGE SQL;

postgresql 9.3 create extension operator is not unique error

when i try to install pgtap into my database with CREATE EXTENSION pgtap;
i get the following error:
2013-09-17 17:01:42.933 CEST ERROR: operator is not unique: unknown || numeric at character 69
2013-09-17 17:01:42.933 CEST HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
2013-09-17 17:01:42.933 CEST QUERY:
SELECT performs_ok(
$1, $2, 'Should run in less than ' || $2 || ' ms'
);

2013-09-17 17:01:42.933 CEST STATEMENT: CREATE EXTENSION pgtap;

postgresql: 9.3
pgtap: 0.93

pgxnclient install pgtap

pgxnclient install pgtap generates 4 files

pgtap.control
pgtap-core.sql
pgtap-schema.sql
pgtap.sql (cat of pgtap-core.sql and pgtap-schema.sql)

CREATE EXTENSION pgtap; is looking for extname--version.sql

pgxnclient should instead install
pgtap.control
pgtap--0.93.0.sql (same as pgtap.sql but renamed)

Add useful negation function tests

• isnt_definer()
• isnt_strict()
• isnt_aggregate()

Ignore Internal Triggers

Looks as though triggers_are() needs to ignore internal triggers. The docs say something about how internal triggers are "usually, to enforce the constraint identified by tgconstraint". Not sure what that means exactly, so worth looking into to write a test to replicate such triggers, and then probably apply this patch to ignore them.

--- a/sql/pgtap.sql.in
+++ b/sql/pgtap.sql.in
@@ -7086,6 +7086,7 @@ RETURNS TEXT AS $$
               JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE n.nspname = $1
                AND c.relname = $2
+               AND NOT t.tgisinternal
             EXCEPT
             SELECT $3[i]
               FROM generate_series(1, array_upper($3, 1)) s(i)
@@ -7100,6 +7101,7 @@ RETURNS TEXT AS $$
               JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE n.nspname = $1
                AND c.relname = $2
+               AND NOT t.tgisinternal
         ),
         $4
     );
@@ -7123,6 +7125,7 @@ RETURNS TEXT AS $$
               JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relname = $1
                AND n.nspname NOT IN ('pg_catalog', 'information_schema')
+               AND NOT t.tgisinternal
             EXCEPT
             SELECT $2[i]
               FROM generate_series(1, array_upper($2, 1)) s(i)
@@ -7136,6 +7139,7 @@ RETURNS TEXT AS $$
               JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid
               JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                AND n.nspname NOT IN ('pg_catalog', 'information_schema')
+               AND NOT t.tgisinternal
         ),
         $3
     );

skip grammar

according to the tap documentation the correct skip grammar is:
a hash mark, followed by zero or more spaces, followed by a "skip", followed by a word boundary, zero or more spaces.

https://metacpan.org/module/TAP::Parser::Grammar#TAP-GRAMMAR
https://github.com/Perl-Toolchain-Gang/Test-Harness/blob/master/lib/TAP/Parser/Grammar.pm#L146

pgtap emits 'ok 1 - SKIP: why' without the hash mark
eg: https://github.com/theory/pgtap/blob/master/sql/pgtap.sql.in#L653

is this a bug or i missing something?

thanks

Add has_check() Variants

has_check() is currently quite limited. It should be updated to have the same interface as has_index().

Regression test for OSNAME fails on Cygwin

When running "make installcheck" on Cygwin one test in utils.sql fails.

$ more regression.diffs
*** ./expected/util.out Fri Jul 31 10:25:52 2009
--- ./results/util.out  Fri Jul 31 13:06:04 2009
***************
*** 9,14 ****
  ok 7 - pg_version() should return same as "server_version" setting
  ok 8 - pg_version_num() should return integer
  ok 9 - pg_version_num() should be correct
! ok 10 - os_name() should output something like an OS name
  ok 11 - findfincs() should return distinct values
  ok 12 - pgtap_version() should work
--- 9,18 ----
  ok 7 - pg_version() should return same as "server_version" setting
  ok 8 - pg_version_num() should return integer
  ok 9 - pg_version_num() should be correct
! not ok 10 - os_name() should output something like an OS name
! # Failed test 10: "os_name() should output something like an OS name"
! #                   'cygwin_nt-5.1'
! #    doesn't match: '^[[:alnum:]]+$'
  ok 11 - findfincs() should return distinct values
  ok 12 - pgtap_version() should work
+ # Looks like you failed 1 test of 12

The value of OSNAME is "cygwin_nt-5.1" on this machine. That's because "uname" returns the following string:

$ uname
CYGWIN_NT-5.1

Since it's likely we really just want "cygwin" there you could either:

1. Use "cygwin -o" to get just the OS name (will return "Cygwin" in this case but seems not all Unices support it); or
2. Test for presence of /usr/include/cygwin to set the OSNAME in the Makefile.

Add more Owner Test Functions

Noticed that v0.92.0 does not include schema_owner_is(). Probably could use tests for other objects, too.

Add has_sequence() variant with schema and no description

I use "has_sequence" to test a column with type serial.

The test fails when I omit the description part and returns ok if I add one.

It does NOT however return "If you omit the test description, it will be set to "Sequence :sequence should exist"." as the documentation says.

On PostgreSQL 9.1.3 on i486-slackware-linux-gnu, compiled by gcc (GCC) 4.7.0, 32-bit, tested with pgtap 0.93.0 and 0.94.0

Sync parameter names with those listed in the docs

Add function parameter names matching the docs so parameters can be passed by name on 9.0 and higher.

Use of `display_type()` Sucks

Found that col_type_is() and domain_type_is() are funky when pointing to types not in the search path. Really, display_type() is unnecessarily funky. I think that it should be removed, and pg_catalog.format_type() used, instead. We should prepend the schema name to it only if the expected schema is in the search path, because if it's not in the search path, pg_catalog.format_type() will include the schema.

35cf17e makes a start on this (in its own branch), but still is not quite right. I think domain_type_is() does a better job than col_type_is(), but it could all use more thinking through, frankly.

col_is_default throws an exception for 'point' type

Column definition:
orig_size point NOT NULL DEFAULT '(0,0)'::point

Test code:
select col_default_is('photos', 'orig_size', '(0,0)');

Error:
photos.sql .............. 1/? psql:photos.sql:34: ERROR: operator does not exist: point = point
LINE 1: SELECT NOT $1 IS DISTINCT FROM $2
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT NOT $1 IS DISTINCT FROM $2
CONTEXT: PL/pgSQL function "is" line 7 at assignment
SQL statement "SELECT is('(0,0)'::point::point, '(0,0)'::point, 'Column photos.orig_size should default to ''(0,0)''')"
PL/pgSQL function "_def_is" line 10 at EXECUTE statement
SQL statement "SELECT _def_is(
pg_catalog.pg_get_expr(d.adbin, d.adrelid),
display_type(a.atttypid, a.atttypmod),
$3, $4
)
FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, pg_catalog.pg_attrdef d
WHERE c.oid = a.attrelid
AND pg_table_is_visible(c.oid)
AND a.atthasdef
AND a.attrelid = d.adrelid
AND a.attnum = d.adnum
AND c.relname = $1
AND a.attnum > 0
AND NOT a.attisdropped
AND a.attname = $2"
PL/pgSQL function "_cdi" line 13 at RETURN

col_is_unique not working on PostgreSQL 8.3

I'm using pgtap-0.25 and I'm getting this behaviour:

des=# BEGIN ;
BEGIN
des=# \d tb_g00
           Tabela "some.tb_g00"
       Coluna        |            Tipo             | Modificadores 
---------------------+-----------------------------+---------------
 cod                 | numeric(10,0)               | not null
 typ                 | integer                     | not null
 nro                 | character varying(12)       | not null
Indices:
    "pk_g00" PRIMARY KEY, btree (cod, typ)
    "ui_g00" UNIQUE, btree (cod, nro)
Restricoes de chave estrangeira:
    "fk_g11_g00" FOREIGN KEY (cod) REFERENCES tb_g11(cod) ON UPDATE CASCADE

des=# SELECT plan(1);
 plan 
------
 1..1
(1 registro)

des=# SELECT pgtap.col_is_unique('tb_g00', ARRAY['cod', 'nro']);
                                                        col_is_unique                                                         
------------------------------------------------------------------------------------------------------------------------------
 not ok 2 - Columns tb_g00(cod, nro) should have a unique constraint
 # Failed test 2: "Columns tb_g00(cod, nro) should have a unique constraint"
 #         have: 
 #         want: {cod,nro}
(1 registro)

has_index() without index name requirement

Any chance there can be a version of the has_index() and/or hasnt_index() check that just checks if there's an index on specified columns and doesn't care about the index name? All the variations in the docs have the :index property.

Add rowtype_is()

Useful result testing function to consider adding (but might require C code): rowtype_is(). Could certainly simplify the diagnostic output of results_eq() when the values are the same but the types are different (right now, there is no way to tell what the types are, since they can't be extracted from a refcursor or a record. 037f49a was about the best I could manage.

has_index does not understand array expressions in the column list

For this index:

    CREATE INDEX idx_foo_bar_ids ON foo((bar_ids[1]));

This test will fail:

SELECT has_index(
    'public',
    'foo,
    'idx_foo_bar_ids',
    ARRAY['(bar_ids[1])']
);

So there needs to be a way to test array expressions (and other kinds of expressions?) in the column list for an index.

Allow description to be optional when schema passed to table testing functions

Add schema, table, colname variations of the table-checking functions (table_has_column(), col_type_is(), etc.). That is, allow the description to be optional if the schema is included in the function call.

Add Alternate functions accepting a regprocedure Param

Instead of requiring that the schema, function name, and array of param types be passed separately, consider adding alternate functions for the function-testing assertions that accept a regprocedure parameter. Would be especially useful for function_privs_are(), so it could be called like so:

SELECT function_privs_are(
    'blanket.save_thingy( int, text, text, smallint, smallint, numeric )',
     'betty', 'EXECUTE', 'betty should have EXECUTE on save_thingy()'
);

The existing functions might be updated to use this version under the hood. Suggested by Jim Nasby.

Add other schema testing functions

Useful schema testing functions to consider adding:

• sequence_has_range()
• sequence_increments_by()
• sequence_starts_at()
• sequence_cycles()

how do I run different setup/teardown functions for each test ?

I am trying to use runtests(...) to unit-test my stored procedures. What I basically would like to do is

1. insert/copy some test-data into the database
2. run a test
3. remove the test-data
   and repeat these three steps for each test function.

I thought I could achieve this by creating a setup function for step 1 and a teardown function for step 3. However, there does not seem to be a way to define which setup/teardown function I want to run for which test function; according to the docs, all test functions just run all functions whose names start with setup in alphabetical order. I don't need more than one setup/teardown function per test, I just need a different one each time.

Am I generally misunderstanding something here or does pgTAP assume all tests use the same setup/teardown ?

Using installcheck target in an extension

The docs imply that I can use the standard installcheck target in a PG extension. However, when I use the sample code as my .sql, I get the following:

    % make installcheck
    /usr/local/Cellar/postgresql/9.1.2/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=. --psqldir='/usr/local/Cellar/postgresql/9.1.2/bin'   --dbname=contrib_regression similarity_vector
    (using postmaster on Unix socket, default port)
    ============== dropping database "contrib_regression" ==============
    DROP DATABASE
    ============== creating database "contrib_regression" ==============
    CREATE DATABASE
    ALTER DATABASE
    ============== running regression test queries        ==============
    test similarity_vector        ... diff: /Users/jay/src/tiptap/similarity_vector/expected/similarity_vector.out: No such file or directory
    diff command failed with status 512: diff  "/Users/jay/src/tiptap/similarity_vector/expected/similarity_vector.out" "/Users/jay/src/tiptap/similarity_vector/results/similarity_vector.out" > "/Users/jay/src/tiptap/similarity_vector/results/similarity_vector.out.diff"
    make: *** [installcheck] Error 2

Is there a way to tell installcheck "no, there's no 'expected' output to diff; just display the actual output"?

Add `has_foreign_table()`

Just like has_table(), except check for "f" instead of "r".

Installing Perlbrew in 10.8.2

Hello!

I'm trying to install Perlbrew using this command:

curl -kL http://install.perlbrew.pl | bash

And everything goes smooth. However, when I add "source ~/perl5/perlbrew/etc/bashrc" to my .bash_profile, and restart Terminal, I get "Permission denied" on opening the .bash_profile.

However, when I attempt to open a new shell window, it immediately says "Permission denied," and then it's denied for almost anything I type.

I have Homebrew installed, and Python installed with Homebrew, so currently my .bash_profile looks like this:

export PATH="/usr/local/bin:/usr/local/sbin:~/bin:$PATH"
export PYTHONPATH="/usr/local/lib/python2.7/site-packages:$PYTHONPATH"
[[ -s "$HOME/.rvm/scripts/rvm" ]] && . "$HOME/.rvm/scripts/rvm" # Load RVM function

source ~/perl5/perlbrew/etc/bashrc

(I had to comment out the Perlbrew in order to get things working again.)
Help?

ERROR: relation "__tcache__" does not exist

I am trying to use pgTAP to create some unit tests for stored procedures in my database. What am I am doing is this:

1. run a script to create the database and all the objects in it, everything in a main schema
2. run pgtap.sql to install the test functions in a test schema
3. run a very early version of my SQL test script to
   1. delete some base data inserted during the first step
   2. run a number of \copy commands to import test data from text files
   3. run PREPARE result_have AS SELECT * FROM <my stored proc>
   4. run PREPARE result_want AS VALUES <expected result>
   5. run SELECT results_eq('result_have', 'result_want', '<some description>);

The last step gives me this error output:

ERROR:  relation "__tcache__" does not exist
LINE 1: SELECT ARRAY[ id, value] FROM __tcache__ WHERE label = 'todo...
                                      ^
QUERY:  SELECT ARRAY[ id, value] FROM __tcache__ WHERE label = 'todo' AND id = (SELECT MAX(id) FROM __tcache__ WHERE label = 'todo') LIMIT 1
CONTEXT:  PL/pgSQL function "_get_latest" line 5 at EXECUTE statement
PL/pgSQL function "_todo" line 9 at assignment
PL/pgSQL function "ok" line 9 at assignment
PL/pgSQL function "results_eq" line 33 at RETURN
PL/pgSQL function "results_eq" line 9 at assignment

What is wrong here ? How can I fix this ?

Environment: Latest revision of pgTAP from github (3a53e41) on PostgreSQL 9.1.3 / Mac OSX Lion.

travis link in README goes to wrong repo

Installing 0.90.0 fails on Mountain Lion with perlbrew

Hi,

I tried to install 0.90.0 on Mountain Lion and am using perlbrew.

Makefile line 38 correctly gets the path to perl as "/Users/marcel/perl5/perlbrew/perls/perl-5.16.0/bin/perl" but after the "ifdef NO_PGXS" block, $(PERL) is back to "/usr/bin/perl" - so I guess something in the included Makefiles is changing the path.

So it'd be better to have the "ifndef PERL" block after the "ifdef NO_PGXS" block.

Also I got "make: execvp: ./getos.sh: Permission denied"; this was rectified with "chmod 755 getos.sh"

Thanks,

Marcel

Test to see if a role has no privileges

Think it would be possible to have an opposite test to something like the table_privs_are() test? Extension I'm working on grants and revokes permissions on tables depending on configuration settings, including removing all privileges. Would be handy to have a test that the revoking of all privileges worked. Tried passing an empty array to the table_privs_are() function but that doesn't work.

row_eq doesn't seem like it would ever work

I was trying to test a trigger's impact, and so in looking at the docs, row_eq seemed to be a good choice. In looking at the function, it seems like it would never work, because the 2nd parameter can't be of type record, and the query's results that you pass in as the 1st parameter will always be stored as a record type in the function, and (at least in 8.3), record <> anything but record.

Here's basically what I was doing:

BEGIN;

SELECT plan(1);
CREATE TYPE sometype AS (handle TEXT);
PREPARE query AS SELECT 'testing'::text;
SELECT row_eq('query', ROW('testing')::sometype);
SELECT * FROM finish();

ROLLBACK;

and this produces the error:

psql83:test_row_eq.sql:12: ERROR:  operator does not exist: record = sometype

Also, in the documentation the examples for row_eq are syntactically incorrect:

SELECT row_eq( id, name), ROW(1, 'theory')::sometype ) --one extra parenthesis
  FROM users;

Anyway, am I missing something or using this incorrectly?

col_type_is fails with long non-english table/col type name

I've got the following table:
CREATE TABLE prt."Модульное тестирование секционирования (логи)$"
(
uoid oid,
utid tid,
rv "Модульное тестирование секционирования (логи)"
);

And when I performing the following test
CREATE OR REPLACE FUNCTION pgtap_fail()
RETURNS SETOF TEXT AS $$
BEGIN
RETURN NEXT col_type_is(
'prt', -- schema name
'Модульное тестирование секционирования (логи)$', -- table name
'rv', -- column name
'public', -- type schema
'Модульное тестирование секционирования (логи)', -- type name
'desc' -- description
);
END;
$$ LANGUAGE plpgsql;

  select * from runtests('^pgtap_fail');

I see the following

  # tap.pgtap_phail()
  not ok 1 - desc
  # Failed test 1: "desc"
  #         have: public."Модульное тестирование секционирования (логи)"
  #         want: public." (логи)"(логи)
  1..1
  # Looks like you failed 1 test of 1

Database encoding is CP-1251.

I'll try to fix it later, but now it's just a report.

materialized_views_are

pgtap_version 0.94

There's a views_are and a tables_are but no materialized_views_are?

Misspelling (actually, mis-capitalization) of TAP::Harness

In the Makefile and also in Changes, there are references to Tap::Harness. However, the actual Perl module is named TAP::Harness (TAP is all uppercase).

That means this code from the Makefile should get false negatives:

Is Tap::Harness installed?
ifdef PERL

HAVE_HARNESS := $(shell $(PERL) -le 'eval { require Tap::Harness }; print 1 unless $$@' )

endif

Add Functions with reg* casts

Currently, we have two versions of a bunch of functions, one that takes a schema and an object, and another that just takes the object. We should add a variant that takes a reg* value, where appropriate. The text versions should be changed to try to cats to reg* versions, fail if the cast dies, and otherwise all the function that takes the reg* version.

Odd behavior of results_eq between postgresql 9.2 and 9.3

Been testing my extensions against 9.3 lately and running into a weird issue in 9.2 with the following test in my mimeo extension (https://github.com/omniti-labs/mimeo)

SELECT results_eq('SELECT match FROM validate_rowcount(''mimeo_dest.snap_test_dest'')', ARRAY[true], 'Check validate_rowcount match');

I'm running pgtap 0.94.0 on both 9.2 and 9.3. When I run the test script that has this check (test/test10_check_refresh_data.sql) in 9.2 it runs and passes the test fine. In 9.3 it's throwing the following error

ERROR:  function ok(boolean, text) does not exist at character 8
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT ok( true, $3 )
CONTEXT:  PL/pgSQL function results_eq(refcursor,refcursor,text) line 30 at RETURN
    PL/pgSQL function results_eq(text,anyarray,text) line 10 at assignment
STATEMENT:  SELECT results_eq('SELECT match FROM validate_rowcount(''mimeo_dest.snap_test_dest'')', ARRAY[true], 'Check validate_rowcount match');
psql:test/test10_check_refresh_data.sql:92: ERROR:  function ok(boolean, text) does not exist
LINE 1: SELECT ok( true, $3 )
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT ok( true, $3 )
CONTEXT:  PL/pgSQL function results_eq(refcursor,refcursor,text) line 30 at RETURN
PL/pgSQL function results_eq(text,anyarray,text) line 10 at assignment

Funny thing is, if I go into my 9.2 instance and run this test directly from psql, I get the same error. But for some reason the error isn't raised when run as a full test script. Is 9.3 just catching something better than 9.2 was?

has_table( :schema, :table ) version of the function is missing

The other three versions of this function exist, but the has_table(NAME,NAME) version is missing.

The documentation lists the three versions which do exist, but the example code is for the missing version.

It appears to be a problem for all of the has_* and hasnt_* functions, e.g. has_view and hasnt_table, as well.

Have `has_function()` manage OUT, INOUT, and VARIADIC arguments

getos.sh not executable

$ tar xjf pgtap-0.90.0.tar.bz2
$ cd pgtap-0.90.0
$ make
make: execvp: ./getos.sh: Permission denied

It goes on after that, but it's probably not working as intended.

Either make the file executable, or call it through $(SHELL) in the makefile.

[Feature] throws_matching DETAIL: portion?

Do you have access to the DETAIL: portion of the error message? F'rinstance, I'm porting over contrib/cube's tests to pgtap, and I'd like to check for "Different point dimensions in (1,2) and (1,2,3)", not just "bad cube representation".

pgtap-core needs dependency on plpgsql

Add

requires = 'plpgsql'

to pgtap-core.control.

It's of lower importance because plpgsql is installed by default, but that doesn't mean it's always installed.

throws_ok() & throws_like() not handling long exception messages?

Not sure what the exact problem may be here. Trying to get it to match on an exception message and for some reason it's not. I tried using throws_like() on a shorter version, but giving "%Limit of 99 queue tables%" doesn't seem to want to match anything either. I use the throws_ok() function without any issues in another test for mimeo, in the same manner but with shorter error messages, and it works fine.

Here's a gist with several test examples followed by the error I'm getting.

https://gist.github.com/keithf4/7832088

spclocation

pgtap 0.91 (or older) with PostgreSQL 9.2

I'm getting an error 'column "spclocation" does not exist' because spclocation was removed from pg_tablespace in 9.2 and a pg_tablespace_location() function was added.

http://www.postgresql.org/docs/9.2/static/release-9-2.html

results_eq error message for data type mismatch is misleading

When there is a data type mismatch in a result_eq test, it gives an error message like this:

Columns differ between queries:
   have: (1,2,3)
   want: (1,2,3)

while the actual problem is something like that the first record uses int and the second bigint, say. So this message is totally misleading. (result_ne is the same.)

It looks like you have code that handles this properly in _relcomp and _docomp. Please consider adapting that for result_eq as well.

Allow records to be passed to set comparison functions

Add variants of set_eq(), bag_eq(), and results_eq() that take an array of records as the second argument Probably would require C code.

Unable to run pgtap tests twice in a Postgres session

My usual routine for testing schema is to open a Postgres session and leave it open, wrapping the test runs in a full transaction. However, with pgtap this is impossible; on the second attempt to run the tests, I get an error message generally like this one:

psql82:functions_test.sql:251: ERROR:  could not open relation with OID 226117479
CONTEXT:  PL/pgSQL function "add_result" line 8 at return
PL/pgSQL function "ok" line 18 at assignment
PL/pgSQL function "results_eq" line 16 at return
PL/pgSQL function "results_eq" line 9 at assignment
PL/pgSQL function "test_00_ensure_data" line 2 at return next
PL/pgSQL function "_runner" line 32 at for over execute statement
SQL function "runtests" statement 1
SQL function "runtests" statement 1

Is it possible to run pgtap tests twice in a session? I'm running pgtap version 0.22, if it makes a difference.

enum_has_labels() Sorts Added Labels Incorrectly.

Create an enum:

CREATE TYPE foo AS ('alice', 'barbara', 'cathy');

Insert a new value in the middle:

ALTER TYPE foo ADD VALUE 'brenda' AFTER 'barbara';

This call to enum_has_labels() will fail:

SELECT enum_has_labels('foo', ARRAY['alice', 'barbara', 'brenda', 'cathy']);

Because it will fetch "cathy" before "brenda". Most likely need to sort the values by some other method than currently, and perhaps only on versions of Postgres since ADD VALUE was added.

Enable non-0 exit on test failure

When running outside pg_prove, that is. I believe it will require an exception to be thrown, and even then it won't be fatal unless ON_ERROR_STOP is set. But we ought to do what we can do.

Add `isnt_empty()` to complement `is_empty()`

Add permission-testing functions

Add some sort of tests for permisions. Something like: table_privs_are(:table, :user, :privs[]), and would have variations for database, sequence, function, language, schema, and tablespace.

In 8.1, we have:

• has_database_privilege()
• has_table_privilege()
• has_function_privilege()
• has_language_privilege()
• has_schema_privilege()
• has_tablespace_privilege()

8.4 adds:

• has_any_column_privilege()
• has_column_privilege()
• has_foreign_data_wrapper_privilege()
• has_server_privilege()

In 9.0, we also get:

• has_sequence_privilege()

cleanup of sequences

Function _cleanup() drops the tables, but not the sequences. I suggest you add

DROP SEQUENCE __tcache___id_seq;
DROP SEQUENCE __tresults___numb_seq;

I see you already made the sequences temporary in the master, so this might make this change less necessary, but consistency is nice.