How can I include sql file in pgTAP unittest?

On 03/02/2018 01:17 AM, Stéphane Klein wrote:

Hi,

context: I would like to write UnitTest to test pgSQL triggers which use
postgres_fdw extension.
I use pgTAP <http://pgtap.org/&gt; to write this UnitTest (I use this
Docker environment poc-postgresql-pgTAP
<https://github.com/harobed/poc-postgresql-pgTAP&gt;).

All works perfectly with this test file:

BEGIN;
  SELECT plan(1);

  CREATE EXTENSION IF NOT EXISTS postgres_fdw;
  DROP SERVER IF EXISTS db2 CASCADE;
  CREATE SERVER kea FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'db2', dbname 'db2');
  CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password
'password');

  CREATE SCHEMA IF NOT EXISTS db2;

  IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;

  SELECT ok(
    (SELECT COUNT(host_id) FROM db2.hosts) = 1,
    'foobar'
  );

  -- ;
ROLLBACK;

Now, I would like to extract db2 initialization in separate file
"/test/init.sql" with this content:

  CREATE EXTENSION IF NOT EXISTS postgres_fdw;
  DROP SERVER IF EXISTS db2 CASCADE;
  CREATE SERVER kea FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'db2', dbname 'db2');
  CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password
'password');

  CREATE SCHEMA IF NOT EXISTS db2;

  IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;

Now, my test file is:

BEGIN;
  SELECT plan(1);

  \i /test/init.sql

  SELECT ok(
    (SELECT COUNT(host_id) FROM db2.hosts) = 1,
    'foobar'
  );

In log I see that "init.sql" file is loaded with success:

Running tests: /test/*.sql -v
/test/init.sql ................... No subtests run

But I have this error:

ERROR:  user mapping not found for "db2"

Question: where is my mistake? How can I include some sql file in my test?

It is early morning here and I may be missing something, but I do not
see where you actually create server db2. I do see you creating server kea.

Best regards,
Stéphane
--
Stéphane Klein <contact@stephane-klein.info
<mailto:contact@stephane-klein.info>>
blog: http://stephane-klein.info
cv : http://cv.stephane-klein.info
Twitter: http://twitter.com/klein_stephane

--
Adrian Klaver
adrian.klaver@aklaver.com

2018-03-02 14:52 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 03/02/2018 01:17 AM, Stéphane Klein wrote:

Hi,

context: I would like to write UnitTest to test pgSQL triggers which use
postgres_fdw extension.
I use pgTAP <http://pgtap.org/&gt; to write this UnitTest (I use this
Docker environment poc-postgresql-pgTAP <https://github.com/harobed/po
c-postgresql-pgTAP>).

All works perfectly with this test file:

BEGIN;
SELECT plan(1);

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
DROP SERVER IF EXISTS db2 CASCADE;
CREATE SERVER kea FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'db2', dbname 'db2');
CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password
'password');

CREATE SCHEMA IF NOT EXISTS db2;

IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;

SELECT ok(
(SELECT COUNT(host_id) FROM db2.hosts) = 1,
'foobar'
);

-- ;
ROLLBACK;

Now, I would like to extract db2 initialization in separate file
"/test/init.sql" with this content:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
DROP SERVER IF EXISTS db2 CASCADE;
CREATE SERVER kea FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'db2', dbname 'db2');
CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password
'password');

CREATE SCHEMA IF NOT EXISTS db2;

IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;

Now, my test file is:

BEGIN;
SELECT plan(1);

\i /test/init.sql

SELECT ok(
(SELECT COUNT(host_id) FROM db2.hosts) = 1,
'foobar'
);

In log I see that "init.sql" file is loaded with success:

Running tests: /test/*.sql -v
/test/init.sql ................... No subtests run

But I have this error:

ERROR: user mapping not found for "db2"

Question: where is my mistake? How can I include some sql file in my test?

It is early morning here and I may be missing something, but I do not see
where you actually create server db2. I do see you creating server kea.

Sorry, it is not kea but db2 in my example. I did a mistake when I replace
the db name in my example.

You need to read:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
DROP SERVER IF EXISTS db2 CASCADE;
CREATE SERVER db2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2',
dbname 'db2');
CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password
'password');

CREATE SCHEMA IF NOT EXISTS db2;

IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;

On 03/02/2018 06:46 AM, Stéphane Klein wrote:

Sorry, it is not kea but db2 in my example. I did a mistake when I
replace the db name in my example.

You need to read:

  CREATE EXTENSION IF NOT EXISTS postgres_fdw;
  DROP SERVER IF EXISTS db2 CASCADE;
  CREATE SERVER db2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'db2', dbname 'db2');
  CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password
'password');

  CREATE SCHEMA IF NOT EXISTS db2;

  IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;

I don't know much about pgTap, but my first instinct would be to change:

BEGIN;
SELECT plan(1);

\i /test/init.sql

SELECT ok(
(SELECT COUNT(host_id) FROM db2.hosts) = 1,
'foobar'
);

to:

BEGIN;

\i /test/init.sql

SELECT plan(1);

SELECT ok(
(SELECT COUNT(host_id) FROM db2.hosts) = 1,
'foobar'
);

--
Adrian Klaver
adrian.klaver@aklaver.com