4

I want to test a Timescale database using Python and pytest.

I have discovered, that pytest-pqsql provides a database fixture to be used in testing. However, I am struggling to enable timescaledb support for it.

I have gone through the following steps to get it working:

  • enable extension using --pg-extension="timescaledb" -> Error: extension "timescaledb" must be preloaded
  • preload extension using --pg-conf-opt="shared_preload_libraries='timescaledb'" -> Error: could not access file "'timescaledb'": No such file or directory
    • Timescale Docs suggest to update extension, which doesn't apply here, as it hasn't been updated previously.

Here is my example test file and the (truncated) output:

# test_database.py
import pytest

@pytest.fixture
def DB(transacted_postgresql_db):
    assert transacted_postgresql_db.is_extension_available('timescaledb')
    yield transacted_postgresql_db


def test_add_schema(DB):
    assert not DB.has_schema('foo')
    DB.session.execute("CREATE TABLE foo (bar VARCHAR(40));")
    assert DB.has_schema('foo')

$ pytest --pg-extensions="timescaledb" test_database.py
================================== test session starts ===================================
platform darwin -- Python 3.8.3, pytest-5.4.3, py-1.8.1, pluggy-0.13.1
rootdir: /Users/mkj/Developer, inifile: setup.cfg
plugins: pgsql-1.1.2
collected 1 item                                                                         

tests/test_database.py E                                                           [100%]

========================================= ERRORS =========================================
___________________________ ERROR at setup of test_add_schema ____________________________
...
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x102854a00>
cursor = <cursor object at 0x1026407c0; closed: 0>
statement = 'BEGIN TRANSACTION; CREATE EXTENSION IF NOT EXISTS "timescaledb"; COMMIT;'
parameters = {}
context = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x1028830a0>

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       psycopg2.errors.InternalError_: extension "timescaledb" must be preloaded
E       HINT:  Please preload the timescaledb library via shared_preload_libraries.
E       
E       This can be done by editing the config file at: /var/folders/1g/hcdk5d553g334m2nd7_thy6m5y1jp8/T/tmpo9ut8s1l/data/postgresql.conf
E       and adding 'timescaledb' to the list in the shared_preload_libraries config.
E               # Modify postgresql.conf:
E               shared_preload_libraries = 'timescaledb'
E       
E       Another way to do this, if not preloading other libraries, is with the command:
E               echo "shared_preload_libraries = 'timescaledb'" >> /var/folders/1g/hcdk5d553g334m2nd7_thy6m5y1jp8/T/tmpo9ut8s1l/data/postgresql.conf 
E       
E       (Will require a database restart.)
E       
E       If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with: 
E               SET timescaledb.allow_install_without_preload = 'on';
E       server closed the connection unexpectedly
E               This probably means the server terminated abnormally
E               before or while processing the request.

preloading the extension yields another error:

$ pytest --pg-conf-opt="shared_preload_libraries='timescaledb'" --pg-extensions="timescaledb" tests/test_database.py 
================================== test session starts ===================================
platform darwin -- Python 3.8.3, pytest-5.4.3, py-1.8.1, pluggy-0.13.1
rootdir: /Users/mkj/Developer, inifile: setup.cfg
plugins: pgsql-1.1.2
collected 1 item                                                                         

tests/test_database.py E                                                           [100%]

========================================= ERRORS =========================================
___________________________ ERROR at setup of test_add_schema ____________________________

...
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <testing.postgresql.Postgresql object at 0x1102f5460>

    def wait_booting(self):
        boot_timeout = self.settings.get('boot_timeout', self.DEFAULT_BOOT_TIMEOUT)
        exec_at = datetime.now()
        while True:
            if self.child_process.poll() is not None:
>               raise RuntimeError("*** failed to launch %s ***\n" % self.name +
                                   self.read_bootlog())
E               RuntimeError: *** failed to launch Postgresql ***
E               2020-06-15 07:57:03.052 CEST [91515] FATAL:  could not access file "'timescaledb'": No such file or directory
E               2020-06-15 07:57:03.052 CEST [91515] LOG:  database system is shut down

env/lib/python3.8/site-packages/testing/common/database.py:170: RuntimeError
================================ short test summary info =================================
ERROR tests/test_database.py::test_add_schema - RuntimeError: *** failed to launch Post...
==================================== 1 error in 1.80s ====================================
Jérôme
  • 13,328
  • 7
  • 56
  • 106
jmm
  • 384
  • 2
  • 12

1 Answers1

2

I managed to do it with pytest-postgresql.

I'm using SQLAlchemy so my use case is a bit different, but simplified (and untested), it could look like this:

from pytest_postgresql import factories as ppf


postgresql_proc = ppf.postgresql_proc(
    postgres_options="-c shared_preload_libraries='timescaledb'"
)
postgresql = ppf.postgresql('postgresql_proc')


@pytest.fixture
def database(postgresql):

    cur = postgresql.cursor()
    cur.execute("CREATE EXTENSION IF NOT EXISTS timescaledb;")
    postgresql.commit()
    cur.close()

    yield postgresql

This provides a database fixture to use in the tests.

Jérôme
  • 13,328
  • 7
  • 56
  • 106