[jdbc] TimescaleDB persistence didn’t create correct tables

[dankr3]

Hi guys,

the jdbc persistance service for the TimescaleDB didn't create the correct named data tables. The restart of openHAB leads to errors.

Expected Behavior

The jdbc service was setup via the web UI:

tableUseRealItemNames -> active
tableCaseSensitiveItemNames -> active

I would expect that an Item (e.g. “Home_Power_Input”) will be stored in a table named “Home_Power_Input”.

Current Behavior

But the system created a table called “home_power_input”. The funny thing is that the “wrong named” table is filled with the current values.

The real problem occurs after a restart of the system. The values will not be stored in the lower case table “home_power_input” any longer with the following log entry:

2023-03-05 16:59:29.258 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store: Unable to store item
org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: Error in SQL query!!!; ERROR: table "home_power_input" is already a hypertable Query: SELECT created from create_hypertable('Home_Power_Input', 'time') Parameters: []; Pool Name= yank-default; SQL= SELECT created from create_hypertable('Home_Power_Input', 'time')
    at org.openhab.persistence.jdbc.internal.db.JdbcTimescaledbDAO.doCreateItemTable(JdbcTimescaledbDAO.java:58) ~[?:?]
    at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:202) ~[?:?]
    at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:392) ~[?:?]
    at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:217) ~[?:?]
    at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.internalStore(JdbcPersistenceService.java:162) ~[?:?]
    at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:140) ~[?:?]
    at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) ~[?:?]
    at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) ~[?:?]
    at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
    at java.lang.Thread.run(Thread.java:829) [?:?]

The current workaround is to delete the whole database and recreate it. After that openHAB can recreate the "wrong named" tables again in logs all the values.

Your Environment

• Version used: openHAB is 3.4.2 in Docker
• Persistence service is jdbc with a connection to a TimescaleDB (based on PostgreSQL 14.7) in Docker

[JonathanvdGHU]

I have encountered the same bug while working on my internship assignment. A bug fix will be appreciated :)

[lsiepel]

Also occurs in 4.2.1?

[JonathanvdGHU]

Also occurs in 4.2.1?

Yeah, I upgraded from v4.1.3 to v4.2.1 to make sure.

[JonathanvdGHU]

Also occurs in 4.2.1?

It's not meant in a mean way but when do you think the bug is fixed? A part of the internship assignment is to transfer data from the old database (RRD4j/Round-robin database) to the new one, what's in this case TimescaleDB. This bug can make it a little bit annoying to make this data migration.