pgtap and plpgsql do blocks

[K. Brannen]

Hi, I don't see anything about this from a google search, so I hope this hasn't been discussed to death before.

I'm trying to use pgtap calls insde a plpgsql DO block and it's failing in an interesting way. The docs don't say I can't do this, then again, I don't see any examples of it either, so I can't tell if this should work or not. Here's a simple example, which assumes 2 schemas and 1 table (define any way you want).

---------------------------------------
BEGIN;
SELECT plan(4);
DO $$
DECLARE
v_schema text;
BEGIN
FOREACH v_schema IN ARRAY ARRAY[ 'logging', 'archive' ]
LOOP
PERFORM has_table(v_schema, 'busy_log', 'has busy_log');
/* lots more tests and what I really care about */
END LOOP;
END;
$$ LANGUAGE plpgsql;
/* test that pgtap is working normally with these 2 tests */
select has_table('logging', 'busy_log', 'has busy_log');
select has_table('archive', 'busy_log', 'has busy_log');
SELECT * FROM finish();
ROLLBACK;
---------------------------------------

The reason to do this is because we have multiple pairs of tables that should be the same except that they live in separate schemas, so why have to duplicate a bunch of code when a loop with a variable would do nicely? :) The output shows:

---------------------------------------
$ pg_prove -v busy.pg
busy.pg ..
1..4
ok 3 - has busy_log
ok 4 - has busy_log
Failed 2/4 subtests

Test Summary Report
-------------------
busy.pg (Wstat: 0 Tests: 2 Failed: 0)
Parse errors: Tests out of sequence. Found (3) but expected (1)
Tests out of sequence. Found (4) but expected (2)
Bad plan. You planned 4 tests but ran 2.
Files=1, Tests=2, 1 wallclock secs ( 0.01 usr + 0.00 sys = 0.01 CPU)
Result: FAIL
---------------------------------------

At the top, it's says I ran 4 tests, but at the bottom it says I ran 2 tests … which is it? :)

I also tried changing PERFORM... to do:
SELECT has_table(...) INTO myvar ;
RAISE NOTICE '%', myvar

I could tell has_table() ran successfully, but I still get general failure overall, so that is a no-go.

For more info, while running that, our pg_log shows:

---------------------------------------
2017-10-30 18:09:27 CDT [4609]: [3-1] user=nroot,db=nms,app=psql DEBUG:
building index "pg_toast_364971_index" on table "pg_toast_364971"
2017-10-30 18:09:27 CDT [4609]: [4-1] user=nroot,db=nms,app=psql
CONTEXT: SQL statement "
CREATE TEMP SEQUENCE __tcache___id_seq;
CREATE TEMP TABLE __tcache__ (
id INTEGER NOT NULL DEFAULT nextval('__tcache___id_seq'),
label TEXT NOT NULL,
value INTEGER NOT NULL,
note TEXT NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX __tcache___key ON __tcache__(id);
GRANT ALL ON TABLE __tcache__ TO PUBLIC;
GRANT ALL ON TABLE __tcache___id_seq TO PUBLIC;

CREATE TEMP SEQUENCE __tresults___numb_seq;
GRANT ALL ON TABLE __tresults___numb_seq TO PUBLIC;
"
PL/pgSQL function plan(integer) line 6 at EXECUTE
2017-10-30 18:09:27 CDT [4609]: [5-1] user=nroot,db=nms,app=psql STATEMENT: SELECT plan(4);
2017-10-30 18:09:27 CDT [4609]: [6-1] user=nroot,db=nms,app=psql DEBUG: building index "__tcache___key" on table "__tcache__"
2017-10-30 18:09:27 CDT [4609]: [7-1] user=nroot,db=nms,app=psql CONTEXT: SQL statement "
CREATE TEMP SEQUENCE __tcache___id_seq;
CREATE TEMP TABLE __tcache__ (
id INTEGER NOT NULL DEFAULT nextval('__tcache___id_seq'),
label TEXT NOT NULL,
value INTEGER NOT NULL,
note TEXT NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX __tcache___key ON __tcache__(id);
GRANT ALL ON TABLE __tcache__ TO PUBLIC;
GRANT ALL ON TABLE __tcache___id_seq TO PUBLIC;

CREATE TEMP SEQUENCE __tresults___numb_seq;
GRANT ALL ON TABLE __tresults___numb_seq TO PUBLIC;
"
PL/pgSQL function plan(integer) line 6 at EXECUTE
2017-10-30 18:09:27 CDT [4609]: [8-1] user=nroot,db=nms,app=psql
STATEMENT: SELECT plan(4);
---------------------------------------

So, is what I'm trying to do even possible? If not, is there a way to accomplish the goal, or am I just going to have to duplicate about 40 tests for each pair of tables or maybe do some sort of shell script to control the schema via a variable and dynamically create the test code on the fly?

If it matters:
Pg : 9.5.1
PtTap: 0.95 (upgrade to 0.97 is planned)
OS : Centos 6.7

Thanks,
Kevin

[David E. Wheeler]

On Nov 3, 2017, at 12:03, K. Brannen <kbra...@pwhome.com> wrote:

> Hi, I don't see anything about this from a google search, so I hope this hasn't been discussed to death before.
>
> I'm trying to use pgtap calls insde a plpgsql DO block and it's failing in an interesting way. The docs don't say I can't do this, then again, I don't see any examples of it either, so I can't tell if this should work or not. Here's a simple example, which assumes 2 schemas and 1 table (define any way you want).
>
> ---------------------------------------
> BEGIN;
> SELECT plan(4);
> DO $$
> DECLARE
> v_schema text;
> BEGIN
> FOREACH v_schema IN ARRAY ARRAY[ 'logging', 'archive' ]
> LOOP
> PERFORM has_table(v_schema, 'busy_log', 'has busy_log');
> /* lots more tests and what I really care about */
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
> /* test that pgtap is working normally with these 2 tests */
> select has_table('logging', 'busy_log', 'has busy_log');
> select has_table('archive', 'busy_log', 'has busy_log');
> SELECT * FROM finish();
> ROLLBACK;

> ———————————————————

The problem is that there is no query output from DO statements, so nothing gets output for the harness to read.

>
> The reason to do this is because we have multiple pairs of tables that should be the same except that they live in separate schemas, so why have to duplicate a bunch of code when a loop with a variable would do nicely? :) The output shows:
>
> ---------------------------------------
> $ pg_prove -v busy.pg
> busy.pg ..
> 1..4
> ok 3 - has busy_log
> ok 4 - has busy_log
> Failed 2/4 subtests
>
> Test Summary Report
> -------------------
> busy.pg (Wstat: 0 Tests: 2 Failed: 0)
> Parse errors: Tests out of sequence. Found (3) but expected (1)
> Tests out of sequence. Found (4) but expected (2)
> Bad plan. You planned 4 tests but ran 2.
> Files=1, Tests=2, 1 wallclock secs ( 0.01 usr + 0.00 sys = 0.01 CPU)
> Result: FAIL
> ---------------------------------------
>
> At the top, it's says I ran 4 tests, but at the bottom it says I ran 2 tests … which is it? :)

You ran four, but only 2 went to STDOUT where pg_prove could see them.

Don’t use a DO loop; use a query!

SELECT has_table(sch, 'busy_log', 'Has ' || sch || '.busy_log')
FROM (VALUES('logging'), ('archive')) F(sch);

Best,

David

[K. Brannen]

David Wheeler wrote:

> On Nov 3, 2017, at 12:03, K. Brannen <kbra...@pwhome.com> wrote:
>
> > Hi, I don't see anything about this from a google search, so I hope
> this hasn't been discussed to death before.
> >
> > I'm trying to use pgtap calls insde a plpgsql DO block and it's
> failing in an interesting way. The docs don't say I can't do this, then
> again, I don't see any examples of it either, so I can't tell if this
> should work or not. Here's a simple example, which assumes 2 schemas and
> 1 table (define any way you want).
> >

> > ...example...

>
> The problem is that there is no query output from DO statements, so
> nothing gets output for the harness to read.

Yeah, that was the conclusion I came to, hence why I tried SELECT...INTO and then RAISE.


> ...

> Don’t use a DO loop; use a query!
>
> SELECT has_table(sch, 'busy_log', 'Has ' || sch || '.busy_log')
> FROM (VALUES('logging'), ('archive')) F(sch);

Hmm, OK, I can try that. Interestingly, I did think of something similar, but didn't think I'd need to. :) And it works! Thanks for that! I think it would have taken me awhile to get the syntax correct.

Hopefully this can be found by the next person who has this question. You might want to consider adding this example to the docs, along with the warning not to try a DO loop. :)

Kevin

[David E. Wheeler]

On Nov 3, 2017, at 18:15, K. Brannen <kbra...@pwhome.com> wrote:

> Hmm, OK, I can try that. Interestingly, I did think of something similar, but didn't think I'd need to. :) And it works! Thanks for that! I think it would have taken me awhile to get the syntax correct.

You can also use unnest and an array if it’s more natural-feeling to you:

SELECT has_table(sch, 'busy_log', 'Has ' || sch || '.busy_log')

FROM unnest(ARRAY['logging', 'archive']) F(sch);

> Hopefully this can be found by the next person who has this question. You might want to consider adding this example to the docs, along with the warning not to try a DO loop. :)

Yeah, I thought it was in the docs, but I think I’m remembering an old presentation or something.

Best,

David

[K. Brannen]

David Wheeler wrote:

> On Nov 3, 2017, at 18:15, K. Brannen <kbra...@pwhome.com> wrote:
>
> > Hmm, OK, I can try that. Interestingly, I did think of something
> similar, but didn't think I'd need to. :) And it works! Thanks for that!
> I think it would have taken me awhile to get the syntax correct.
>
> You can also use unnest and an array if it’s more natural-feeling to
> you:
>
>
> SELECT has_table(sch, 'busy_log', 'Has ' || sch || '.busy_log')
> FROM unnest(ARRAY['logging', 'archive']) F(sch);

Thanks, but the VALUES() version works fine. It's just getting all the parts there; I have to look it up every time because I don't use it very often. :)

> > Hopefully this can be found by the next person who has this question.
> > You might want to consider adding this example to the docs, along with
> > the warning not to try a DO loop. :)
>
> Yeah, I thought it was in the docs, but I think I’m remembering an old
> presentation or something.

I didn't see it, but that doesn't mean it's not there. :)
There is something that almost gets close in the "Pursuing Your Query" section, so that might be a good place to add it. If you do change the docs, then you make also want to show something like this:

SELECT col_not_null(sch, 'table1', col, 'not null column ' || sch || '.' || col)
FROM (VALUES('schema1'), ('schema1')) AS stmp (sch)
CROSS JOIN (VALUES('col_pk'),
('col2'),
('col3')) AS ctmp (col);

Here I'm doing a column test on a table that's the same in 2 schemas. That can be adapted to whatever test is needed. The point is that when you have more columns that need the same test, you can easily add them to the list.
The secret (for me) was finding the CROSS JOIN, which gives you the equivalent of a loop in loop to get the "2 level affect". I suppose you can keep adding CROSS JOINs for 'N-level', but I have trouble coming up with an example that actually needs it without be weirdly contrived (or else my imagination is still asleep).

Anyway, thanks for answering my questions!

HTH,
Kevin

[David E. Wheeler]

On Nov 6, 2017, at 10:31, K. Brannen <kbra...@pwhome.com> wrote:

> I didn't see it, but that doesn't mean it's not there. :)
> There is something that almost gets close in the "Pursuing Your Query" section, so that might be a good place to add it. If you do change the docs, then you make also want to show something like this:
>
> SELECT col_not_null(sch, 'table1', col, 'not null column ' || sch || '.' || col)
> FROM (VALUES('schema1'), ('schema1')) AS stmp (sch)
> CROSS JOIN (VALUES('col_pk'),
> ('col2'),
> ('col3')) AS ctmp (col);

Great idea. I’ve added a new section to the docs, “Secrets of the pgTAP Mavens”:


https://github.com/theory/pgtap/commit/1374245

With your example.

What other tips and tricks should we add here? Contributions wanted! Reply to this message with your favorite pgTAP hacks, or better yet, submit a PR!

Best,

David