Обсуждение: How can I include sql file in pgTAP unittest?
Hi,
context: I would like to write UnitTest to test pgSQL triggers which use postgres_fdw extension.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;
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'
);
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
Running tests: /test/*.sql -v
/test/init.sql ................... No subtests run
But I have this error:
ERROR: user mapping not found for "db2"
ERROR: user mapping not found for "db2"
Question: where is my mistake? How can I include some sql file in my test?
Best regards,
Stéphane
--
--
Stéphane Klein <contact@stephane-klein.info>
blog: http://stephane-klein.info
cv : http://cv.stephane-klein.info
Twitter: http://twitter.com/klein_stephane
blog: http://stephane-klein.info
cv : http://cv.stephane-klein.info
Twitter: http://twitter.com/klein_stephane
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/> to write this UnitTest (I use this > Docker environment poc-postgresql-pgTAP > <https://github.com/harobed/poc-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. > > 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/> to write this UnitTest (I use this Docker environment poc-postgresql-pgTAP <https://github.com/harobed/poc-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;
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