Timescaledb persistence - Need some help to understand

Dibbler42 · March 23, 2022, 2:09pm

I like to use the timescaledb to persist values. timescaledb is an addon for postgresql to add special highload capabilities for time related values.

The setup of an timescaledb is described very well in the internet and is straight forward. The process of creating tables in the database is a two step process. First a standard table needs to be created and then this tabe is converted to a hypertable.

This is the point where openHAB steps in. The documentation shows that the timescaledb uses the postgresql driver. So far so good. If i look into the persistence source code i find timescale that extends the postgresql and i find the part where the table is converted to the hypertable, but and this is my question:

How does the driver see that it should create a Hyper table. If i configure persistencs with jdbc:timescaledb … i get error. If i create persistence with jdbc:postgresql… i get persistence but no hypertables.

Could someone bring some light into the darkness?

Wolfgang_S · March 23, 2022, 3:13pm

What exaclty did you configure ?
Looking into the source code

    public Properties getDatabaseProperties() {
        Properties properties = new Properties(this.databaseProps);

        // Adjust the jdbc url since the service name 'timescaledb' is only used to differentiate the DAOs
        if (properties.containsKey("jdbcUrl")) {
            properties.put("jdbcUrl", properties.getProperty("jdbcUrl").replace("timescaledb", "postgresql"));
        }

        return properties;
    }

it looks like it would work and in the background use a PostgreSQL database.

The error message resp. TRACE/DEBUG enabled log output might help here to do so.

Dibbler42 · March 23, 2022, 3:24pm

In the jdbc configuration (UI) i use the following database url

jdbc:timescaledb://idun.iot.thing.home:5432/openhab_db

and the error message is as follows:

2022-03-23 16:20:21.452 [WARN ] [ence.internal.PersistenceManagerImpl] - bundle org.openhab.core.persistence:3.3.0.202203220859 (208)[org.openhab.core.persistence.internal.PersistenceManagerImpl(227)] : Could not get service from ref {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.id=559, service.bundleid=287, service.scope=bundle, enableLogTime=false, user=openhab, url=jdbc:timescaledb://idun.iot.thing.home:5432/openhab_db, component.name=org.openhab.persistence.jdbc.internal.JdbcPersistenceService, service.config.label=JDBC Persistence Service, component.id=324, service.config.factory=false, password=bycucumber, tableUseRealItemNames=true, rebuildTableNames=false, service.config.category=persistence, service.config.description.uri=persistence:jdbc, service.pid=[org.openhab.jdbc, org.openhab.jdbc]}
2022-03-23 16:20:21.454 [WARN ] [ence.internal.PersistenceManagerImpl] - bundle org.openhab.core.persistence:3.3.0.202203220859 (208)[org.openhab.core.persistence.internal.PersistenceManagerImpl(227)] : DependencyManager : invokeBindMethod : Service not available from service registry for ServiceReference {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.id=559, service.bundleid=287, service.scope=bundle, enableLogTime=false, user=openhab, url=jdbc:timescaledb://idun.iot.thing.home:5432/openhab_db, component.name=org.openhab.persistence.jdbc.internal.JdbcPersistenceService, service.config.label=JDBC Persistence Service, component.id=324, service.config.factory=false, password=bycucumber, tableUseRealItemNames=true, rebuildTableNames=false, service.config.category=persistence, service.config.description.uri=persistence:jdbc, service.pid=[org.openhab.jdbc, org.openhab.jdbc]} for reference PersistenceService
2022-03-23 16:20:21.457 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-03-23 16:20:21.462 [ERROR] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.3.0.202203220412 (287)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(324)] : The activate method has thrown an exception
java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:timescaledb://idun.iot.thing.home:5432/openhab_db
        at com.zaxxer.hikari.util.DriverDataSource.<init>(DriverDataSource.java:84) ~[?:?]
        at com.zaxxer.hikari.pool.PoolBase.initializeDataSource(PoolBase.java:298) ~[?:?]
        at com.zaxxer.hikari.pool.PoolBase.<init>(PoolBase.java:91) ~[?:?]
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:101) ~[?:?]
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71) ~[?:?]
        at org.knowm.yank.YankPoolManager.createPool(YankPoolManager.java:101) ~[?:?]
        at org.knowm.yank.YankPoolManager.addDefaultConnectionPool(YankPoolManager.java:69) ~[?:?]
        at org.knowm.yank.Yank.setupDefaultConnectionPool(Yank.java:705) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.openConnection(JdbcMapper.java:215) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.pingDB(JdbcMapper.java:70) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.checkDBAccessability(JdbcMapper.java:248) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.updateConfig(JdbcPersistenceService.java:231) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.activate(JdbcPersistenceService.java:89) ~[?:?]
        at jdk.internal.reflect.GeneratedMethodAccessor208.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.apache.felix.scr.impl.inject.methods.BaseMethod.invokeMethod(BaseMethod.java:244) ~[bundleFile:?]
        at org.apache.felix.scr.impl.inject.methods.BaseMethod.access$500(BaseMethod.java:41) ~[bundleFile:?]
        at org.apache.felix.scr.impl.inject.methods.BaseMethod$Resolved.invoke(BaseMethod.java:685) ~[bundleFile:?]
        at org.apache.felix.scr.impl.inject.methods.BaseMethod.invoke(BaseMethod.java:529) [bundleFile:?]
        at org.apache.felix.scr.impl.inject.methods.ActivateMethod.invoke(ActivateMethod.java:318) [bundleFile:?]
        at org.apache.felix.scr.impl.inject.methods.ActivateMethod.invoke(ActivateMethod.java:308) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.createImplementationObject(SingleComponentManager.java:354) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.createComponent(SingleComponentManager.java:115) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:1000) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:973) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:918) [bundleFile:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse$1.run(ServiceFactoryUse.java:216) [org.eclipse.osgi-3.16.300.jar:?]
        at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:213) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:114) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:48) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:547) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:533) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:656) [org.eclipse.osgi-3.16.300.jar:?]
        at org.apache.felix.scr.impl.manager.SingleRefPair.getServiceObject(SingleRefPair.java:88) [bundleFile:?]
        at org.apache.felix.scr.impl.inject.methods.BindMethod.getServiceObject(BindMethod.java:675) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.DependencyManager.getServiceObject(DependencyManager.java:2556) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.DependencyManager.doInvokeBindMethod(DependencyManager.java:2075) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.DependencyManager.invokeBindMethod(DependencyManager.java:2058) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.invokeBindMethod(SingleComponentManager.java:443) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.addedService(DependencyManager.java:333) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.addedService(DependencyManager.java:301) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.customizerAdded(ServiceTracker.java:1200) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.customizerAdded(ServiceTracker.java:1121) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.ServiceTracker$AbstractTracked.trackAdding(ServiceTracker.java:928) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.ServiceTracker$AbstractTracked.track(ServiceTracker.java:864) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.serviceChanged(ServiceTracker.java:1152) [bundleFile:?]
        at org.apache.felix.scr.impl.BundleComponentActivator$ListenerInfo.serviceChanged(BundleComponentActivator.java:114) [bundleFile:?]
        at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:120) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:957) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:151) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:936) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:873) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:141) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:261) [org.eclipse.osgi-3.16.300.jar:?]
        at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:496) [org.eclipse.osgi-3.16.300.jar:?]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:929) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:915) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.RegistrationManager.changeRegistration(RegistrationManager.java:133) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager.registerService(AbstractComponentManager.java:984) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager.activateInternal(AbstractComponentManager.java:752) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.reconfigure(SingleComponentManager.java:750) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.reconfigure(SingleComponentManager.java:684) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.ConfigurableComponentHolder.configurationUpdated(ConfigurableComponentHolder.java:437) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.RegionConfigurationSupport.configurationEvent(RegionConfigurationSupport.java:347) [bundleFile:?]
        at org.apache.felix.scr.impl.manager.RegionConfigurationSupport$2.configurationEvent(RegionConfigurationSupport.java:115) [bundleFile:?]
        at org.apache.felix.cm.impl.ConfigurationManager$FireConfigurationEvent.sendEvent(ConfigurationManager.java:1720) [bundleFile:?]
        at org.apache.felix.cm.impl.ConfigurationManager$FireConfigurationEvent.run(ConfigurationManager.java:1662) [bundleFile:?]
        at org.apache.felix.cm.impl.UpdateThread.run0(UpdateThread.java:122) [bundleFile:?]
        at org.apache.felix.cm.impl.UpdateThread.run(UpdateThread.java:84) [bundleFile:?]
        at java.lang.Thread.run(Thread.java:829) [?:?]

From the source code i woul expect that timescaledb is an acceptable database type that is internally replced by postgresql. But i do not finde the point where the timescale extension is loaded. Lack of java knowledge