make test fails "myType"

[Robert L. Kirby]

On a fresh install of PostgreSQL 11.18 with pgTAP 1.2.0 installed in schema tap of role tap:

  make test PGUSER=postgres # a superuser

produces an error:

pg_prove --pset tuples_only=1 \
        test/sql/coltap.sql test/sql/hastap.sql
test/sql/coltap.sql .. psql:test/sql/coltap.sql:33: ERROR:  type "myType" does not exist
LINE 19:     camel   "myType"
                     ^
test/sql/coltap.sql .. Dubious, test returned 3 (wstat 768, 0x300)
Failed 243/243 subtests

If the error line is changed to

  camel   public."myType"

the syntax error goes away but 72+43 (of 243+1004) subsequent failed tests still remain.

Bob Kirby

[David E. Wheeler]

On Dec 19, 2022, at 00:10, Robert L. Kirby <kirb...@gmail.com> wrote:

> On a fresh install of PostgreSQL 11.18 with pgTAP 1.2.0 installed in schema tap of role tap:
> make test PGUSER=postgres # a superuser
> produces an error:
> pg_prove --pset tuples_only=1 \
> test/sql/coltap.sql test/sql/hastap.sql
> test/sql/coltap.sql .. psql:test/sql/coltap.sql:33: ERROR: type "myType" does not exist
> LINE 19: camel "myType"

Looks like `make test` expects pgTAP to be installed in the search path, probably in public. If you put it in a different schema, you need to be sure the search path includes it and also that public is in the search path.

Demo:

``
❯ createdb try

❯ psql -d try -c 'create schema tap' -c 'create extension pgtap with schema tap' CREATE SCHEMA
CREATE EXTENSION

❯ pg_prove --pset tuples_only=1 -d try \ test/sql/coltap.sql test/sql/hastap.sql

test/sql/coltap.sql .. psql:test/sql/coltap.sql:4: ERROR: function plan(integer) does not exist
LINE 1: SELECT plan(243);
```

Use PGOPTIONS to set the path to include both tap and public:

```
❯ PGOPTIONS=--search_path=tap,public pg_prove --pset tuples_only=1 -d try \
test/sql/coltap.sql test/sql/hastap.sql

test/sql/coltap.sql .. ok test/sql/hastap.sql .. ok All tests successful.
Files=2, Tests=1247, 1 wallclock secs ( 0.03 usr 0.01 sys + 0.01 cusr 0.01 csys = 0.06 CPU)
Result: PASS
```

If `public` is not included in the path, you get the error you saw:

```
❯ PGOPTIONS=--search_path=tap pg_prove --pset tuples_only=1 -d try \ test/sql/coltap.sql test/sql/hastap.sql

test/sql/coltap.sql .. psql:test/sql/coltap.sql:33: ERROR: type "myType" does not exist
LINE 19: camel "myType"
^

```

So you should be fine as long as both the schema with the pgTAP functions and public are in the search path.

Best,

David

[Robert L. Kirby]

Indeed for running pgTAP "make test", the search_path didn't include the public schema, only a tap schema of a tap role.

PostgreSQL 11.18 manual subsection 5.8.6 Usage Patterns of subsection 5.8 Schemas of section 5 Data Definition suggests removing the public schema as "unsafe or unreliable." Although the public schema was not removed, not putting the public schema on the current search_path, which is sometimes different from the default search_path of the current role, reduces risk.

In a more secure development environment with pgTAP segregated from other code, a non-superuser role could run pgTAP "make test" as long as it included a tap schema somewhere on its current search_path. Such a run would ensure that nothing from pgTAP collided with code being developed. To make such testing possible, "make test" could create its own temporary schema instead of the public schema. This would only require CREATE privileges on the first schema of the search_path. As a best practice, creating a schema might be wrapped in a lives_ok call.

Bob Kirby

[David E. Wheeler]

On Dec 21, 2022, at 13:28, Robert L. Kirby <kirb...@gmail.com> wrote:

> In a more secure development environment with pgTAP segregated from other code, a non-superuser role could run pgTAP "make test" as long as it included a tap schema somewhere on its current search_path. Such a run would ensure that nothing from pgTAP collided with code being developed. To make such testing possible, "make test" could create its own temporary schema instead of the public schema. This would only require CREATE privileges on the first schema of the search_path. As a best practice, creating a schema might be wrapped in a lives_ok call.

I’m confused: Why would such a developer need to run `make test` at all? Are they working on pgTAP itself? Because if they’re just *using* pgTAP installed in the database to test their own code, they don’t need the pgTAP source code or to run its tests.

Best,

David

[Robert L. Kirby]

> I’m confused: Why would such a developer need to run `make test` at all? Are they working on pgTAP itself? Because if they’re just *using* pgTAP installed in the database to test their own code, they don’t need the pgTAP source code or to run its tests.

I'm surprised that you, a developer of test software, would not see the value of running tests. Perhaps your comment was more rhetorical than direct.

- You mention "make test" on your documentation web page. If it was only part of the installation infrastructure, then I would expect it to be less prominent.

- pgTAP may run in many different environments, including minor version releases. PostgreSQL (and Yugabyte and others) are open source software without necessarily strong quality control. It would seem up to the pgTAP user to ensure that they have a properly working version.

- Developers break working code creating regressions. Although unlikely, it's possible to inadvertently create naming conflicts or other breakage, particularly if any PostgreSQL infrastructure, like catalogs or system views, are involved. For example, as I mentioned earlier, PostgreSQL documentation suggests removing the public schema, which, at a minimum, breaks the "make test" part of pgTAP.

- When debugging, eliminating all potential sources of the unexpected may be needed, including the infrastructure, which should be testable in situ.

Bob Kirby

[David E. Wheeler]

On Dec 25, 2022, at 13:20, Robert L. Kirby <kirb...@gmail.com> wrote:

> I'm surprised that you, a developer of test software, would not see the value of running tests. Perhaps your comment was more rhetorical than direct.

No, I think we misunderstand each other.

> - You mention "make test" on your documentation web page. If it was only part of the installation infrastructure, then I would expect it to be less prominent.

When I write tests in Go, I don’t first run all the tests that come with the package I depend on. When I run tests imperil, I don’t run all the tests for the test frameworks. Instead, I run those tests when I I install the modules/packages, but not thereafter. I only run the tests for my current project. I haven’t changed anything about those other frameworks, so their test outcomes would not change.

> - pgTAP may run in many different environments, including minor version releases. PostgreSQL (and Yugabyte and others) are open source software without necessarily strong quality control. It would seem up to the pgTAP user to ensure that they have a properly working version.

Yes, you should totally run them when you install pgTAP, but you don’t need to run the again until you upgrade.

> - Developers break working code creating regressions. Although unlikely, it's possible to inadvertently create naming conflicts or other breakage, particularly if any PostgreSQL infrastructure, like catalogs or system views, are involved. For example, as I mentioned earlier, PostgreSQL documentation suggests removing the public schema, which, at a minimum, breaks the "make test" part of pgTAP.

I expect developers (like myself) to test the code they’re working on, not some other code. Unless they’re hunting down an issue with that other code and trying to modify it, or to demonstrate a regression or bug.

Now, it may be that the pgTAP tests should not assume the public schema is present and that’s where it’s installed. You running `make && make installcheck` where there is no public schema would demonstrate that issue. Perhaps we should change that. If so, please open an issue:

https://github.com/theory/pgtap/issues

> - When debugging, eliminating all potential sources of the unexpected may be needed, including the infrastructure, which should be testable in situ.

But nothing about pgTAP should have been changed since installation. And it will work fine with no public schema (aside from the tests) as long as the schema in which it was installed is in the search path, or else by schema-qualifying calls to its functions.

Best,

David