Support deleting the database on start

[mmaslowskicc]

What action do you want to perform

I use postgresql_noproc and my tests crash with a segmentation fault in the code that I'm testing.

I attempt to rerun them to see if they fail again or to debug them.

What are the results

The tests database from previous run already exists and the fixture fails to create it:

>       with DatabaseJanitor(
                pg_user, pg_host, pg_port, pg_db, proc_fixture.version
        ):

../../.local/share/virtualenvs/venvname/lib/python3.8/site-packages/pytest_postgresql/factories.py:281: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
../../.local/share/virtualenvs/venvname/lib/python3.8/site-packages/pytest_postgresql/janitor.py:89: in __enter__
    self.init()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <pytest_postgresql.janitor.DatabaseJanitor object at 0x7ffff25220a0>

    def init(self) -> None:
        """Create database in postgresql."""
        with self.cursor() as cur:
>           cur.execute('CREATE DATABASE "{}";'.format(self.db_name))
E           psycopg2.errors.DuplicateDatabase: database "tests" already exists

../../.local/share/virtualenvs/venvname/lib/python3.8/site-packages/pytest_postgresql/janitor.py:55: DuplicateDatabase

What are the expected results

I somehow configure pytest-postgresql that it's not a mistake and it drops the test database before creating it, just like it would drop it at the end of the test.

Not dropping the database by default is good, since I could have accidentally configured it to use a more useful database with some data I want to keep.

[fizyk]

@mmaslowskicc Why do you have the database already existing? For the case on noproc especially, I'd be vary of this configuration, since it might be dangerous. Imagine running the test suite accidentally against production database.

[mmaslowskicc]

I have it already existing, since the Python interpreter crashed in my previous test run, not running the fixture teardown.

But in most situations when I expect noproc to be used, we either have database containers which can be easily restarted, or we need more care and shouldn't delete the database.

This issue can remain a closed remainder of why the database is dropped only on test teardown.

[vlcinsky]

I had the same issue when using multiple noproc fixtures with the same name sharing the same dbname.

My first pg_proc was defined in tests(conftest.py which is btw used by tests/test_model.py

The second pg_proc was defined within tests/test_model_populate.py.

The dbname was configured only in pytest.ini and set to tests.

Running only single test file went well, but if I run the whole test suite, I got random errors complaining about DuplicateDatabase.

The cause is quite obvious: while each pg_proc runs during whole testing session, it attempts to hold the template database open the whole time but as they compete about the same database name, then we have the conflict.

Developer shall take care to use for each session scope (no)proc fixture the databases named independently.

The problem got resolved by using different dbname values for each instance of pg_proc fixture.

Note, that with pytest, it is legal and common to reuse fixture names across test modules.

[brendan-morin]

I also ran into this issue when something happened and my python interpreter must have crashed for some reason before tearing down. Unlike @vlcinsky, I only had one fixture in my case. This appears to have nothing to do with how anything was set up and seems to be purely a random bug that happened after running the test suite 1000s of times successfully, and a product of incomplete teardown.

The fix was a both manual and temporary: I added this line in the DatabaseJanitor.init() method (pytest_postgresql/janitor.py):

    def init(self) -> None:
        """Create database in postgresql."""
        template_name = f"{self.dbname}_tmpl"
        with self.cursor() as cur:
            if self.dbname.endswith("_tmpl"):
                result = False
            else:
                cur.execute(
                    "SELECT EXISTS "
                    "(SELECT datname FROM pg_catalog.pg_database WHERE datname= %s);",
                    (template_name,),
                )
                row = cur.fetchone()
                result = (row is not None) and row[0]
            if not result:
                cur.execute(f'CREATE DATABASE "{self.dbname}";')
            else:
                # All template database does not allow connection:
                self._dont_datallowconn(cur, template_name)
                # And make sure no-one is left connected to the template database.
                # Otherwise Creating database from template will fail
                self._terminate_connection(cur, template_name)
                ################################################
                ##### TEMP FIX - REMOVE AFTER A SINGLE RUN #####
                self.drop()
                ################# END TEMP FIX ################# 
                ################################################
                cur.execute(f'CREATE DATABASE "{self.dbname}" TEMPLATE "{template_name}";')

I ran it once, that cleared the test db out (and failed, as expected, with a different error: psycopg.errors.InvalidCatalogName: database "test" does not exist). After, I removed that bit of code, and everything went back to working as normal.

[fizyk]

🤔 a flag to clean databases on start? Cli flag?

[brendan-morin]

Yes, I’ve noticed I’m running into this quite a bit lately, maybe another half a dozen times since I last commented. Probably because I’m actively running tests frequently as part of TDD for this particular dev cycle.

I often find myself needing to cancel or interrupt long running test suites, which usually is fine but occasionally (20% of the time?) results in corrupted test db state.

I’ve similarly seen database "test_tmpl" does not exist as well, which is slightly different but similar fix actions of injecting a drop in the right place.

A CLI flag could be a good option, as long as it is robust to the database being either in a valid or invalid state (e.g. it would be great to have a command that will work consistently no matter what). Maybe it is worth some consideration if this corrupted state is detectable on startup and just clearing it as the default behavior. Automated dropping of databases can feel a little risky, but this script drops the same DB anyways on cleanup, so it seems reasonable to also check and resolve on init as well.

[fizyk]

Okay,
one thing would be the cli switch to clean
other would be to fix behaviour and let the fixture clean after itself on cancelled or interrupted runs, and see where the breaks happen.

Could you possibly create new issues for both cases?

[astoff]

How about naming the test database tests-<some random chars>? It wouldn't solve the issue of garbage potentially being left behind in the DB after an interruption, but at least wouldn't break subsequent tests.

[JoaquimEsteves]

Can we possibly re-open this issue?

I also faced this issue because I embarrassingly mashed Ctrl-C while investigating a test with with the debugger.

If PRs are welcome I could create an optional

delete_if_tmpl_exists flag

[Dfred]

we also use containers for our dev environment. Every now and then someone interrupts the tests with SIGKILL/INT .. a native feature to run the janitor on start would always be better than our custom workaround.

[nahuakang]

Like other recent comments I'd like this issue to be re-opened.

I run a pytest job on Gitlab CI with pytest-postgresql with a Postgres from Gitlab service and every now and then, the test job would fail with psycopg.errors.DuplicateDatabase: database "tests" already exists. Usually a re-run of the job would fix the issue but this renders the job "flaky".

I see the same error message running pytest locally with a Postgres container but the test usually didn't fail and there seemed to be retries.

Would appreciate either a flag to prevent the failing or randomized DB naming for each test.