Timescaledb: Occasional timescaledb extension load errors when using C# Npgsql client lib.

[gumshoes]

C# client using Npgsql 3.2.7
PostgreSQL 9.6.8 on Debian 8.9
Timescaledb 0.9.1

Initial connections and queries work fine but sometimes subsequent queries (the app is threaded and uses Npgsql connection pool) fail.

PostgreSQL log:
```2018-04-05 21:09:10 BST [98917-1] FATAL: extension "timescaledb" must be preloaded
2018-04-05 21:09:10 BST [98917-2] HINT: Please preload the timescaledb library via shared_preload_libraries.

    This can be done by editing the postgres config file
    and adding 'timescaledb' to the list in the shared_preload_libraries config.
            # Modify postgresql.conf:
            shared_preload_libraries = 'timescaledb'

```

Setting NoResetOnClose=true
http://www.npgsql.org/api/Npgsql.NpgsqlConnectionStringBuilder.html#Npgsql_NpgsqlConnectionStringBuilder_NoResetOnClose

or Pooling=false is a workaround.

[cevian]

We can't reproduce. If anyone has any more info/way to reproduce please post here.

[manigandham]

We see this too. Npgsql uses its own connection pooling by default and sends a DISCARD ALL statement prepended to the next query to reset the session: http://www.npgsql.org/doc/performance.html#pooled-connection-reset

It seems that discard command is affecting the extension settings...

[cevian]

@manigandham If you have any suggestions on how to reproduce please post it here. I haven't been able to reproduce even with Npgsql running. Can you provide a program and/or dataset (or any other clues)?

[sanpa1977]

@cevian : I am able to replicate the issue when i run a time_bucket based query for 1 hour grouping for a dataset containing data for a year with a timestamp interval of 1 minute. If i select a lesser period then there is no error. If i restart postgresql service and run the large dataset query the same error occurs again. NoResetOnClose seems to resolve the problem and it does not reoccur after that.

[manigandham]

Here's a test project: https://github.com/manigandham/timescale-npgsqltest

That includes a connection string to a public test database running in aiven you can use. SQL used to setup the table is in the readme file.

The error is caused after doing multiple queries on the same connection. In this sample code, its first using entityframework to run a count over the table then using dapper with a connection directly to run a raw sql statement. Both ops are sent over a pooled npgsql connection. Error goes away when setting NoResetOnClose to true or only doing one operation on the connection before closing fully.

[wallies]

I dont know if this helps or not, but we are running timescaledb 0.9.1 and we were seeing this issue.
We had shared_preload_libraries = 'timescaledb' line in our postgresql.conf file. I ran
echo "shared_preload_libraries = 'timescaledb'" >> /var/lib/postgresql/data/postgresql.conf and rebooted the databases and it started working for all queries for only a small time. Also tried adding shared_preload_libraries = 'timescaledb, timescaledb-0.9.1' instead to postgresql.conf and this worked as well for a while, but we noticed a query like this would bring the issue back:

SELECT time_bucket($1, d_time) + 90 AS "time", MAX(speed)/$5 AS "speedMin", MIN(spped)/$5 AS "speedMin", AVG(speed)/$5 AS "speedAvg", MAX(veloc)/$5 AS "velocMax", MIN(veloc)/$5 AS "velocMin", AVG(veloc)/$5 AS "velocAvg" FROM datad, treasure WHERE datad.p_id = treasure.id AND treasure.d_id = $4 AND d_time >= $2 AND d_time <= $3 GROUP BY time ORDER BY time ASC

We also didnt see this issue on an instance we have that is timescaledb 0.8.0, it is only when we upgraded to 0.9.1 that we saw this issue.

Update: I downgraded to 0.9.0 and I havent seen this error since, so I was thinking it may have something to do with this change https://github.com/timescale/timescaledb/commit/fc056377923c3e827472e64abbf17245733b4a7c

[gumshoes]

Just encountered this with Microsoft Power BI Version: 2.57.5068.721 64-bit (April 2018)
PostgreSQL 9.6.8 on Debian 8.9
Timescaledb 0.9.1
DataSource.Error: PostgreSQL: XX000: extension "timescaledb" must be preloaded

With timescaledb 0.8.0 this did not happen.

[cjrh]

@gumshoes Would you be able to try timescaledb 0.9.0? If that is also fine, it would suggest that the culprit might be in 0.9.1 only (perhaps fc056377923c3e827472e64abbf17245733b4a7c ?)

[Artimi]

I've encountered this error too. I'm using python client asyncpg 0.15.0 that use connection pool too. We are running dockerized Timescaledb 0.9.1-pg10.

  File "/home/artimis/.virtualenvs/mondrian/lib/python3.6/site-packages/asyncpg/pool.py", line 405, in fetch
    return await con.fetch(query, *args, timeout=timeout)
  File "/home/artimis/.virtualenvs/mondrian/lib/python3.6/site-packages/asyncpg/connection.py", line 359, in fetch
    return await self._execute(query, args, 0, timeout)
  File "/home/artimis/.virtualenvs/mondrian/lib/python3.6/site-packages/asyncpg/connection.py", line 1303, in _execute
    query, args, limit, timeout, return_status=return_status)
  File "/home/artimis/.virtualenvs/mondrian/lib/python3.6/site-packages/asyncpg/connection.py", line 1311, in __execute
    return await self._do_execute(query, executor, timeout)
  File "/home/artimis/.virtualenvs/mondrian/lib/python3.6/site-packages/asyncpg/connection.py", line 1333, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 203, in bind_execute
asyncpg.exceptions.InternalServerError: extension "timescaledb" must be preloaded
HINT:  Please preload the timescaledb library via shared_preload_libraries.

This can be done by editing the postgres config file 
and adding 'timescaledb' to the list in the shared_preload_libraries config.
    # Modify postgresql.conf:
    shared_preload_libraries = 'timescaledb'

Another way to do this, if not preloading other libraries, is with the command:
    echo "shared_preload_libraries = 'timescaledb'" >> /path/to/config/file 

(Will require a database restart.)

If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with: 
    SET timescaledb.allow_install_without_preload = 'on';

[gumshoes]

@cjrh sadly I won't be able to try with 0.9.0.
At the moment things we care about are working for us when setting NoResetOnClose=true with npgsql.

[cevian]

@manigandham @gumshoes @wallies @cjrh Thank you, thank you, thank you. I was finally able to reproduce this problem locally (it related to parallel workers). A PR to fix this should be coming shortly.