Extensions that require preloading in postgresql.conf

[mathiasose]

I'm trying to set up some tests for a service that uses the TimescaleDB extension. pytest-pgsql looks promising, but I can't find a way to set the shared_preloading_libraries setting in postgresql.conf that TimescaleDB seems to require.

Simple failing example:

def test_connection(transacted_postgresql_db):
    r = transacted_postgresql_db.connection.execute('SELECT 1+1;')
    assert next(r) == (2,)

$ pytest tests --pg-extensions=timescaledb

E       sqlalchemy.exc.InternalError: (psycopg2.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: /tmp/tmpc2d4u9wn/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'" >> /tmp/tmpc2d4u9wn/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.
E        [SQL: 'BEGIN TRANSACTION; CREATE EXTENSION IF NOT EXISTS "timescaledb"; COMMIT;'] (Background on this error at: http://sqlalche.me/e/2j85)

The error message suggests editing the config file at: /tmp/tmpc2d4u9wn/data/postgresql.conf, but since that config file is ephemeral that doesn't help. It also suggests SET timescaledb.allow_install_without_preload = 'on'; might help. I tried creating a fixture to run that expression before using the extension, but that just gave me a different error message (sqlalchemy.exc.InternalError: (psycopg2.InternalError) cannot create PGC_POSTMASTER variables after startup).

[dargueta]

Okay, this makes sense. It's possible to add this without too much trouble, if you'd like to open a pull request. (You can also wait for me to get around to it in like six years. 🙂)

To do so, you can modify pytest_addoption here to add another argument, something like --pg-conf-opt where you can specify a key-value pair like so:

pytest tests --pg-conf-opt="shared_preload_libraries='timescaledb'"

# or multiple ones
pytest tests --pg-conf-opt="key1='value1'" --pg-conf-opt="key2=value2"

You'd add a call that'd look something like this:

parser.addoption(
    '--pg-conf-opt',
    action='append',
    help='useful text')

Then here you'd add another part to postgres_args like:

conf_opts = request.config.getoption('--pg-conf-opt')
if conf_opts:
    conf_opts_string = ' -c ' + ' -c '.join(conf_opts)
else:
    conf_opts_string = ''

with testing.postgresql.Postgresql(
    postgres_args=...
                  '-c bgwriter_delay=10000ms'
                  + conf_opts_string) as pgdb:
    yield pgdb.url()

I think this will work. Please feel free to open a pull request with these changes if you like.

[jsnb]

This feature is available as of version 1.1.2