How to test a constraint trigger with DEFERRABLE INITIALLY DEFERRED?

[Daniel Smith]

Hi,

Some of my business rules are enforced by triggers which I test with pgTAP using functions throws_ok() and lives_ok(). It allows me to verify that a trigger does or does not raise an error.

Is there also a way to check that a constraint trigger with DEFERRABLE INITIALLY DEFERRED does or does not raise an error at commit?

Regards,

Daniel

[David E. Wheeler]

On May 30, 2018, at 09:21, Daniel Smith <daniel...@id.ternata.net> wrote:

> Is there also a way to check that a constraint trigger with DEFERRABLE INITIALLY DEFERRED does or does not raise an error at commit?

Not really, because pgTAP assumes it runs in a transaction and everything rolls back at the end. You’d have to write a test that uses DBLink or something to create a separate connection to the database and trigger the successes or failures on that connection. Use PL/pgSQL exception handling to catch errors from the connection to share with pgTAP. You’ll also have to manually do any cleanup on that same connection to reset things following a test (delete new rows, etc.).

HTH,

David

[Daniel Smith]

Yes, that helps. I tried and it works. Thank you.

The only trouble I have is with the connection string used by dblink_connect(). I would like to avoid hard coding the password in the test file. Is there an elegant way to reuse what I have in my ~/.pgpass file?

[David E. Wheeler]

On Jun 4, 2018, at 05:46, Daniel Smith <daniel...@id.ternata.net> wrote:

> Yes, that helps. I tried and it works. Thank you.
>
> The only trouble I have is with the connection string used by dblink_connect(). I would like to avoid hard coding the password in the test file. Is there an elegant way to reuse what I have in my ~/.pgpass file?

dblink_connect() runs as the database cluster user inside the database, usually “postgres”. You’ll need to create a separate .pgpass file for that user *on your Postgres server*. Some more info here:

https://stackoverflow.com/q/37991825/79202

HTH,

David

[Daniel Smith]

On Monday, June 4, 2018 at 3:28:58 PM UTC+2, David Wheeler wrote:

dblink_connect() runs as the database cluster user inside the database, usually “postgres”. You’ll need to create a separate .pgpass file for that user *on your Postgres server*. Some more info here:

  https://stackoverflow.com/q/37991825/79202

Great, I managed to use a new .pgpass file on the server in combination with the use of dblink_connect_u().

I can now test my DEFERRABLE INITIALLY DEFERRED triggers.

Thank you,

Daniel

[David E. Wheeler]

On Jun 5, 2018, at 03:08, Daniel Smith <daniel...@id.ternata.net> wrote:

> Great, I managed to use a new .pgpass file on the server in combination with the use of dblink_connect_u().
> I can now test my DEFERRABLE INITIALLY DEFERRED triggers.

Sweet! Care to share with us a little howto?

Best,

David

[Daniel Smith]

On 05.06.2018 15:52, David E. Wheeler wrote:

Sweet! Care to share with us a little howto?

Sure, here is a howto.

If you want to test your DEFERRABLE INITIALLY DEFERRED triggers with the help of dblink, your first need to prepare your test database by executing the following commands as a superuser.

SET client_min_messages TO WARNING;
CREATE SCHEMA IF NOT EXISTS pgtap AUTHORIZATION ownerrole;
CREATE EXTENSION IF NOT EXISTS pgtap SCHEMA pgtap;
CREATE EXTENSION IF NOT EXISTS dblink SCHEMA public;
RESET client_min_messages;
SET search_path TO public;

GRANT EXECUTE
ON FUNCTION dblink_connect_u(text, text), dblink_connect_u(text)
TO ownerrole, applicationrole;

Note that I create a pgtap schema and install the pgTAP extension in this schema to avoid repeating this code in all test files.

The use of the function dblink_connect_u instead dblink_connect allows you to make use of a .pgpass file to store passwords in order to connect. You may already be using another .pgpass file on your client machine but this is different. A new .pgpass file is still needed and will have to be stored on your server, in the home folder of the user running PostgreSQL. So the next step is to create this .pgpass file and place it on your server machine, in my case, my server runs on xubuntu and the user running PostgreSQL is postgres whose home folder is: /var/lib/postgresql. So I created /var/lib/postgresql/.pgpass as:

localhost:5432:*:ownerrole:98NQqhMnbMbV6DmraiyBuPosjAnryCALg9gFTunUbFUUnUCCt9sQaaJdzxm3ZJCk
localhost:5432:*:applicationrole:M6XM3vPvPv9d4r2Yp8Ejsfa9VBbTTY2GgNtbWqaFWxf4bErTeUWkGsRNzFNtXf8E

The next step is to write a test case with the help of dblink. Here is an example:

SET search_path TO pgtap, public;

BEGIN;

SELECT plan(2);

--------------------------------------------------------------------------------
-- Test 1: Check that the commit throws an error
--------------------------------------------------------------------------------
CREATE FUNCTION pg_temp.commit_throws(IN in_connstr text)
    RETURNS void
    LANGUAGE plpgsql
    VOLATILE
    RETURNS NULL ON NULL INPUT
    AS $body$
BEGIN

    PERFORM dblink_connect_u('unittest', in_connstr);

    PERFORM dblink_exec('unittest', 'BEGIN;');

    -- Commands that should lead to an inconsistent state
    PERFORM dblink_exec('unittest', 'INSERT INTO …;');

    -- Attempt a commit
    BEGIN
        PERFORM dblink_exec('unittest', 'COMMIT;');
    EXCEPTION
        WHEN SQLSTATE 'U1001' THEN
            RAISE LOG 'The commit failed!';
            PERFORM dblink_disconnect('unittest');
            RAISE;
    END;

    PERFORM dblink_disconnect('unittest');
END;$body$;

PREPARE plan_1_commit_throws AS
    SELECT pg_temp.commit_throws(:'ff_connstr_ownerrole');
SELECT throws_ok(
    $$
    EXECUTE plan_1_commit_throws
    $$,
    'U1001',
    'here the error message supposed to be raised.'
);

--------------------------------------------------------------------------------
-- Test 2: Check that the commit does not throw an error
--------------------------------------------------------------------------------
CREATE FUNCTION pg_temp.commit_lives(IN in_connstr text)
    RETURNS void
    LANGUAGE plpgsql
    VOLATILE
    RETURNS NULL ON NULL INPUT
    AS $body$
BEGIN

    PERFORM dblink_connect_u('unittest', in_connstr);

    PERFORM dblink_exec('unittest', 'BEGIN;');

    -- Commands that should not lead to an inconsistent state
    PERFORM dblink_exec('unittest', 'INSERT INTO …;');

    -- Attempt a commit
    BEGIN
        PERFORM dblink_exec('unittest', 'COMMIT;');
    EXCEPTION
        WHEN SQLSTATE 'U1001' THEN
            RAISE LOG 'The commit failed!';
            PERFORM dblink_disconnect('unittest');
            RAISE;
    END;

    PERFORM dblink_disconnect('unittest');
END;$body$;

PREPARE plan_2_commit_lives AS
    SELECT pg_temp.commit_lives(:'ff_connstr_ownerrole');
SELECT lives_ok(
    $$
    EXECUTE plan_2_commit_lives
    $$
);

--------------------------------------------------------------------------------
-- Cleanup
--------------------------------------------------------------------------------

-- Drop the test functions
DROP FUNCTION pg_temp.commit_throws(text);
DROP FUNCTION pg_temp.commit_lives(text);

-- Cleanup whatever has been committed
DELETE FROM …;

SELECT finish();

-- Here it is important to use COMMIT and not ROLLBACK if you want your cleanup to be applied
COMMIT;

To test a DEFERRABLE INITIALLY DEFERRED triggers, you will create a PL/pgSQL function to create a connection, start a transaction, run SQL commands that will leads or not to an error and then attempt to commit the transaction. In case the commit attempt fails, you can catch the exception to cleanly disconnect before re-raising the exception.

Doing that allows you to use the PL/pgSQL function very easily with pgTAP throws_ok and lives_ok functions.

Note that I build the connection string used by dblink_connect_u before running the test file. I then pass it via psql variables :'ff_connstr_ownerrole'.

Hope this helps!

Daniel

[David E. Wheeler]

On Jun 6, 2018, at 04:44, Daniel Smith <daniel...@id.ternata.net> wrote:

> On 05.06.2018 15:52, David E. Wheeler wrote:
>> Sweet! Care to share with us a little howto?
> Sure, here is a howto.

Nice, thank you!

> Note that I create a pgtap schema and install the pgTAP extension in this schema to avoid repeating this code in all test files.

I put boilerplate like this at the top of all my test scripts.

SET client_min_messages TO warning;
CREATE EXTENSION IF NOT EXISTS pgtap;
RESET client_min_messages;

This way it doesn’t matter whether I ever remember to set things up in advance; if I run tests, all the stuff they need is there.

> So the next step is to create this .pgpass file and place it on your server machine, in my case, my server runs on xubuntu and the user running PostgreSQL is postgres whose home folder is: /var/lib/postgresql.

.pgpass reference: https://www.postgresql.org/docs/current/static/libpq-pgpass.html

> The next step is to write a test case with the help of dblink. Here is an example:
>
> SET search_path TO pgtap, public;
>
> BEGIN;
>
> SELECT plan(2);
>
> --------------------------------------------------------------------------------
> -- Test 1: Check that the commit throws an error
> --------------------------------------------------------------------------------
> CREATE FUNCTION pg_temp.commit_throws(IN in_connstr text)

Huh. Why in pg_temp?

> PREPARE plan_1_commit_throws AS
> SELECT pg_temp.commit_throws(:'ff_connstr_ownerrole');
> SELECT throws_ok(
> $$
> EXECUTE plan_1_commit_throws
> $$,
> 'U1001',
> 'here the error message supposed to be raised.'
> );

I might have made commit_throws return a boolean. Then it’s just

SELECT ok(
pg_temp.commit_throws(:'ff_connstr_ownerrole’),

At this point, assuming the test passes, your database is left with data you inserted into the table. If you want to clean that out, to leave the database in the same state in which you found it, you’ll need to delete the relevant record(s) or truncate the relevant tables. You’d need to do that cleanup over dblink, too.

> -- Here it is important to use COMMIT and not ROLLBACK if you want your cleanup to be applied
> COMMIT;

Applies only to the current database connection, not the one over dblink.

>
> To test a DEFERRABLE INITIALLY DEFERRED triggers, you will create a PL/pgSQL function to create a connection, start a transaction, run SQL commands that will leads or not to an error and then attempt to commit the transaction. In case the commit attempt fails, you can catch the exception to cleanly disconnect before re-raising the exception.
>
> Doing that allows you to use the PL/pgSQL function very easily with pgTAP throws_ok and lives_ok functions.

You can also use DO and skip creating functions.

https://www.postgresql.org/docs/current/static/sql-do.html

> Note that I build the connection string used by dblink_connect_u before running the test file. I then pass it via psql variables :'ff_connstr_ownerrole’.

If it’s the same database, user, etc., you can take advantage of the system info functions, such ass current_database(), current_role(), etc.

https://www.postgresql.org/docs/10/static/functions-info.html

Thanks!

David

[Daniel Smith]

On 06.06.2018 16:08, David E. Wheeler wrote:

I put boilerplate like this at the top of all my test scripts.

    SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;

This way it doesn’t matter whether I ever remember to set things up in advance; if I run tests, all the stuff they need is there.

Yes, and I copied that idea from you. However, since I need to initialize my test database (because I use dblink) with elevated privilege and I run the tests with restricted privilege, I thought it would be the occasion to put that boilerplate next to it. Besides, it may speed up the tests execution a bit.

The next step is to write a test case with the help of dblink. Here is an example:

SET search_path TO pgtap, public;

BEGIN;

SELECT plan(2);

--------------------------------------------------------------------------------
-- Test 1: Check that the commit throws an error
--------------------------------------------------------------------------------
CREATE FUNCTION pg_temp.commit_throws(IN in_connstr text)

Huh. Why in pg_temp?

Why not? Is there a more appropriate place (assuming I do not use a DO block)?

PREPARE plan_1_commit_throws AS
    SELECT pg_temp.commit_throws(:'ff_connstr_ownerrole');
SELECT throws_ok(
    $$
    EXECUTE plan_1_commit_throws
    $$,
    'U1001',
    'here the error message supposed to be raised.'
);

I might have made commit_throws return a boolean. Then it’s just

    SELECT ok(
        pg_temp.commit_throws(:'ff_connstr_ownerrole’),
        'here the error message supposed to be raised.’
    );

I take advantage of throws_ok not only to verify that my commit throws an error, but also to make sure the error code and the error message are the correct ones, hence the use of throws_ok.

That is right, this is what I do in the "Cleanup" section that you did not quote here.

To test a DEFERRABLE INITIALLY DEFERRED triggers, you will create a PL/pgSQL function to create a connection, start a transaction, run SQL commands that will leads or not to an error and then attempt to commit the transaction. In case the commit attempt fails, you can catch the exception to cleanly disconnect before re-raising the exception.

Doing that allows you to use the PL/pgSQL function very easily with pgTAP throws_ok and lives_ok functions.

You can also use DO and skip creating functions.

  https://www.postgresql.org/docs/current/static/sql-do.html

Good idea, I did not think about that.

Note that I build the connection string used by dblink_connect_u before running the test file. I then pass it via psql variables :'ff_connstr_ownerrole’.

If it’s the same database, user, etc., you can take advantage of the system info functions, such ass current_database(), current_role(), etc.

  https://www.postgresql.org/docs/10/static/functions-info.html

Oh nice, I was trying to look for such functions. Thanks.

Thanks!

Thank you David for your lovely tool which I enjoy using in combination with another of your lovely tool: Sqitch.

Daniel

[David E. Wheeler]

On Jun 6, 2018, at 10:36, Daniel Smith <daniel...@id.ternata.net> wrote:

> Yes, and I copied that idea from you. However, since I need to initialize my test database (because I use dblink) with elevated privilege and I run the tests with restricted privilege, I thought it would be the occasion to put that boilerplate next to it. Besides, it may speed up the tests execution a bit.

Sure.

> Why not? Is there a more appropriate place (assuming I do not use a DO block)?

Hrm. I guess not. Never occurred to me, but I guess it means you can skip dropping the functions if you want (or expect them to be dropped automatically if your test fails for some other reason). Though they should be dropped by the ROLLBACK, anyway.

> I take advantage of throws_ok not only to verify that my commit throws an error, but also to make sure the error code and the error message are the correct ones, hence the use of throws_ok.

Oh, makes sense. I might have returned an array with that data, but it amounts to the same thing.

>> At this point, assuming the test passes, your database is left with data you inserted into the table. If you want to clean that out, to leave the database in the same state in which you found it, you’ll need to delete the relevant record(s) or truncate the relevant tables. You’d need to do that cleanup over dblink, too.
> That is right, this is what I do in the "Cleanup" section that you did not quote here.

You mean this?

> --------------------------------------------------------------------------------
> -- Cleanup
> --------------------------------------------------------------------------------
>
> -- Drop the test functions
> DROP FUNCTION pg_temp.commit_throws(text);
> DROP FUNCTION pg_temp.commit_lives(text);
>
> -- Cleanup whatever has been committed
> DELETE FROM …;
>
> SELECT finish();
>

> -- Here it is important to use COMMIT and not ROLLBACK if you want your cleanup to be applied
> COMMIT;

That will not clean out data committed in the dblink connection.

> Thank you David for your lovely tool which I enjoy using in combination with another of your lovely tool: Sqitch.

Oh, you use both? You might also enjoy this hack, then:

https://justatheory.com/2014/01/templating-tests-with-sqitch/

Best,

David

[Daniel Smith]

On 06.06.2018 16:52, David E. Wheeler wrote:

you can skip dropping the functions if you want (or expect them to be dropped automatically if your test fails for some other reason). Though they should be dropped by the ROLLBACK, anyway.

The thing is, I do not use ROLLBACK when testing my DEFERRED triggers, neither in the dblink connection—obviously to test the commit—nor in the test file—because I need to commit the cleanup commands to put things back in order after the mess created with the dblink connection(s).

At this point, assuming the test passes, your database is left with data you inserted into the table. If you want to clean that out, to leave the database in the same state in which you found it, you’ll need to delete the relevant record(s) or truncate the relevant tables. You’d need to do that cleanup over dblink, too.

That is right, this is what I do in the "Cleanup" section that you did not quote here.

You mean this?

--------------------------------------------------------------------------------
-- Cleanup
--------------------------------------------------------------------------------

-- Drop the test functions
DROP FUNCTION pg_temp.commit_throws(text);
DROP FUNCTION pg_temp.commit_lives(text);

-- Cleanup whatever has been committed
DELETE FROM …;

SELECT finish();

-- Here it is important to use COMMIT and not ROLLBACK if you want your cleanup to be applied
COMMIT;

Yes, that is what I meant.

That will not clean out data committed in the dblink connection.

I just double-checked and it does clean up my data. In my actual test file, I first verify that the database is in order to avoid false positives. I use pgTAP for that:

SELECT is(
    EXISTS (SELECT 1 FROM mytable),
    FALSE
);

If I comment out the cleanup part (what is just under the "-- Cleanup whatever has been committed"), then I can run the test file successfully the first time but the second time, it fails thanks to the above check.

Daniel

[David E. Wheeler]

On Jun 6, 2018, at 11:17, Daniel Smith <daniel...@id.ternata.net> wrote:

> The thing is, I do not use ROLLBACK when testing my DEFERRED triggers, neither in the dblink connection—obviously to test the commit—nor in the test file—because I need to commit the cleanup commands to put things back in order after the mess created with the dblink connection(s).

Oh, yeah, if you COMMIT in the test script, you of course have to manually clean stuff up. But if you put stuff in pg_temp, then it will be dropped when you disconnect. So that’s nice, I’d not thought of it before.

> I just double-checked and it does clean up my data. In my actual test file, I first verify that the database is in order to avoid false positives. I use pgTAP for that:
> SELECT is(
> EXISTS (SELECT 1 FROM mytable),
> FALSE
> );
> If I comment out the cleanup part (what is just under the "-- Cleanup whatever has been committed"), then I can run the test file successfully the first time but the second time, it fails thanks to the above check.

I bet it wouldn’t if you used SERIALIZABLE isolation, not without first committing, then deleting, then committing again. I forget that READ COMMITTED is the default.

https://www.postgresql.org/docs/10/static/transaction-iso.html

Anyway, good stuff, thanks!

Best,

David

[Daniel Smith]

On 06.06.2018 17:23, David E. Wheeler wrote:

I just double-checked and it does clean up my data. In my actual test file, I first verify that the database is in order to avoid false positives. I use pgTAP for that:
SELECT is(
    EXISTS (SELECT 1 FROM mytable),
    FALSE
);
If I comment out the cleanup part (what is just under the "-- Cleanup whatever has been committed"), then I can run the test file successfully the first time but the second time, it fails thanks to the above check.

I bet it wouldn’t if you used SERIALIZABLE isolation, not without first committing, then deleting, then committing again. I forget that READ COMMITTED is the default.

  https://www.postgresql.org/docs/10/static/transaction-iso.html

I am using READ COMMITTED isolation level. I tested with SERIALIZABLE (for all transactions) and it did not clean up, indeed.

Daniel