Request for review of my pgTAP test

[Logan Grosz]

Hi, all

I am new to pgTAP (and PostgreSQL) and I wanted to see if I was doing things in a reasonable way.

I have a database schema...

```sql

CREATE TABLE areas(
    id SERIAL PRIMARY KEY,
    name TEXT CHECK (name IS NULL OR name <> '')
);

CREATE TABLE area_closures (
    area_id INTEGER PRIMARY KEY REFERENCES areas(id) ON DELETE CASCADE,
    super_area_id INTEGER NOT NULL REFERENCES areas(id) ON DELETE RESTRICT
);

CREATE FUNCTION check_area_closures_cycle() RETURNS trigger AS $$
DECLARE
    v_parent_id INTEGER;
BEGIN
    v_parent_id := NEW.super_area_id;

    WHILE v_parent_id IS NOT NULL LOOP
        IF v_parent_id = NEW.area_id THEN
            RAISE EXCEPTION 'Area closure cycle detected';
        END IF;
        SELECT super_area_id INTO v_parent_id
            FROM area_closures
            WHERE area_id = v_parent_id;
    END LOOP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_area_closures_cycle
    BEFORE INSERT OR UPDATE ON area_closures
    FOR EACH ROW EXECUTE FUNCTION check_area_closures_cycle();

```

The areas table is pretty easy to test, and I think I got that done well...

```sql

BEGIN;
SELECT plan(6);

SELECT has_table( 'areas' );

SELECT has_column( 'areas', 'id' );
SELECT col_is_pk( 'areas', 'id' );

SELECT has_column( 'areas', 'name' );
SELECT col_has_check( 'areas', 'name' );

PREPARE insert_area_with_empty_name AS INSERT INTO areas (name) VALUES ('');
SELECT throws_ok(
    'insert_area_with_empty_name',
    '23514',
    'new row for relation "areas" violates check constraint "areas_name_check"',
    'Cannot insert area with empty name'
);

SELECT * FROM finish();
ROLLBACK;

```

but where I am unsure is when testing the constraints and triggers related to the area_closures table.. this is what I have so far...

```sql

BEGIN;
SELECT plan(14);

SELECT has_table( 'area_closures' );

SELECT has_column( 'area_closures', 'area_id' );
SELECT col_is_pk( 'area_closures', 'area_id' );
SELECT col_is_fk( 'area_closures', 'area_id' );

SELECT has_column( 'area_closures', 'super_area_id' );
SELECT col_is_fk( 'area_closures', 'super_area_id' );
SELECT col_not_null( 'area_closures', 'super_area_id');

SELECT has_function( 'check_area_closures_cycle' );

SELECT has_trigger( 'area_closures', 'prevent_area_closures_cycle' );

-- TODO probably PREPARE the insertions as well
PREPARE get_closure AS SELECT * FROM area_closures WHERE area_id = $1;

CREATE FUNCTION test_area_id_cascade_on_delete()
RETURNS SETOF TEXT
AS $$
DECLARE
    area_a areas%ROWTYPE;
    area_b areas%ROWTYPE;
BEGIN

    INSERT INTO areas DEFAULT VALUES RETURNING * INTO area_a;
    INSERT INTO areas DEFAULT VALUES RETURNING * INTO area_b;

    INSERT INTO area_closures ( area_id, super_area_id )
        SELECT ( area_a.id ), ( area_b.id );

    RETURN NEXT isnt_empty(format('EXECUTE get_closure(%s)', area_a.id),
        'Area closure record exists before cascade deletion');

    DELETE FROM areas WHERE id = area_a.id;

    RETURN NEXT is_empty(format('EXECUTE get_closure(%s)', area_a.id),
        'Area closure exists before cascade deletion');

END;
$$ LANGUAGE plpgsql;

SELECT * FROM test_area_id_cascade_on_delete();

CREATE FUNCTION test_super_area_id_restrict_on_delete()
RETURNS SETOF TEXT
AS $$
DECLARE
    area_a areas%ROWTYPE;
    area_b areas%ROWTYPE;
BEGIN

    INSERT INTO areas DEFAULT VALUES RETURNING * INTO area_a;
    INSERT INTO areas DEFAULT VALUES RETURNING * INTO area_b;

    INSERT INTO area_closures ( area_id, super_area_id )
        SELECT ( area_a.id ), ( area_b.id );

    RETURN NEXT isnt_empty(format('EXECUTE get_closure(%s)', area_a.id),
        'Area closure record exists before restrict deletion');

    PREPARE delete_area AS DELETE FROM areas WHERE id = $1;

    RETURN NEXT throws_ok(format('EXECUTE delete_area(%s)', area_b.id),
        '23503',
        'update or delete on table "areas" violates foreign key constraint "area_closures_super_area_id_fkey" on table "area_closures"',
        'Restrict super-area deletion when closure present');

END;
$$ LANGUAGE plpgsql;

SELECT * FROM test_super_area_id_restrict_on_delete();

CREATE FUNCTION test_prevent_area_closures_cycle()
RETURNS SETOF TEXT
AS $$
DECLARE
    area_a areas%ROWTYPE;
    area_b areas%ROWTYPE;
BEGIN

    INSERT INTO areas DEFAULT VALUES RETURNING * INTO area_a;
    INSERT INTO areas DEFAULT VALUES RETURNING * INTO area_b;

    INSERT INTO area_closures ( area_id, super_area_id )
        SELECT ( area_a.id ), ( area_b.id );

    RETURN NEXT throws_ok(
        format('INSERT INTO area_closures ( area_id, super_area_id )
            SELECT ( %s ), ( %s )', area_b.id, area_a.id),
        'P0001',
        'Area closure cycle detected',
        'Restrict insertion when record would create a cycle'
        );

END;
$$ LANGUAGE plpgsql;

SELECT * FROM test_prevent_area_closures_cycle();

SELECT * FROM finish();
ROLLBACK;

```

This does seem reasonable? Is creating an anonymous pl/pgSQL function and `SELECT`ing the pgTAP checks from it seem reasonable for testing the ON DELETE constraints? Am I overlooking anything really obvious? Maybe I got a bit influenced by the latest thread storing an inserted record as a variable within a test.

Glad to join the group. Looking forward to reading more conversations.

Thank you,

Logan

[David E. Wheeler]

On Oct 22, 2024, at 10:56, Logan Grosz <logan...@gmail.com> wrote:

> SELECT col_is_fk( 'area_closures', 'area_id' );

Dion’t forget `fk_ok()`, so you can test that it references the proper primary key.

https://pgtap.org/documentation.html#fk_ok

> This does seem reasonable? Is creating an anonymous pl/pgSQL function and `SELECT`ing the pgTAP checks from it seem reasonable for testing the ON DELETE constraints? Am I overlooking anything really obvious? Maybe I got a bit influenced by the latest thread storing an inserted record as a variable within a test.

I have followed this pattern many times, yes, including in the pgTAP tests themselves (usually to vary tests by Postgres version).

It might be useful to come up with tests for `ON xxxx` expressions in foreign key constraints, though.

Best,

David

[Logan Grosz]

Hi, David

I appreciate the response!

Dion’t forget `fk_ok()`, so you can test that it references the proper primary key.

I appreciate the heads up! I'll tweak my tests to use this. In hindsight, it seems like this is what I should've been using most of the time anyway.

It might be useful to come up with tests for `ON xxxx` expressions in foreign key constraints, though.

I'd certainly use that. For now, I'll just continue using pl/pgSQL functions to test them.

Thank you,

Logan