Test environment and development environment share the same database server

[seancolsen]

@pavish in the develop branch I'm seeing a failing SQL test, test_get_roles, which you appear to have authored in #3663.

docker exec mathesar_dev_db /bin/bash /sql/run_tests.sh -v -x test_get_roles

# Subtest: public.test_get_roles()
    # Test died: 42710: role "foo" already exists
    #         CONTEXT:
    #             SQL statement "CREATE ROLE foo"
    #             PL/pgSQL function test_get_roles() line 10 at SQL statement
    #             PL/pgSQL function _runner(text[],text[],text[],text[],text[]) line 62 at FOR over EXECUTE statement
    #             SQL function "runtests" statement 1
not ok 1 - public.test_get_roles
# Failed test 1: "public.test_get_roles"

Can you troubleshoot this? I've assigned you.

[seancolsen]

Ok this is actually more interesting than I thought...

I was washing dishes this morning and it occurred to me: if this test was failing for me, why wasn't it failing in CI? 🤔

It turns out that at some point during development and testing, I had created a role within my dev server, coincidentally using the same name as used in this test — foo.

I just verified that when I manually execute DROP ROLE foo; in pqsl, then I can run all the SQL tests successfully. Very interesting!

So it's not a problem with that specific test. (So @pavish I've unassigned you.)

It seems that our test environment is not entirely isolated from our development environment. I haven't researched this, but I imagine pgtap creates a new database either for each test session or for each test. But it appears to be using the same database server as we use for development.

I wonder what the best approach should be to avoiding errors like this in the future.

@mathemancer I'm looping you into this thread since you might have some insight/opinions.

Perhaps we could spin up a new server for each test session? I'm not sure how tricky/slow that would be though.

I imagine we can hand-waive this away for some time, but it might get frustrating during the development of all the permissions-related features we're about to begin building.