operation not supported on hypertables that have compression enabled

[johntdyer]

Homeassistant 2023.06.01
LTSS 2.1.0

Error at startup

2023-06-13 14:01:59.553 WARNING (LTSS) [custom_components.ltss.migrations] Migrating you LTSS table to the latest schema, this might take a couple of minutes!
2023-06-13 14:01:59.559 ERROR (LTSS) [custom_components.ltss] Error during connection setup: (psycopg2.errors.FeatureNotSupported) operation not supported on hypertables that have compression enabled

[SQL: ALTER TABLE ltss
                    DROP CONSTRAINT ltss_pkey CASCADE,
                    ADD PRIMARY KEY(time,entity_id);]
(Background on this error at: https://sqlalche.me/e/20/tw8g) (retrying in 3 seconds)

[nwerter]

@johntdyer compressed chunks in Timescale can't be updated or deleted, so updating to 2.1.0, which modifies the structure of the hypertable, can only be done on an uncompressed database. Afterwards you can enable compression again, which should also be much more effective without the id column.

See https://docs.timescale.com/api/latest/compression/decompress_chunk/ for details on decompressing your database.

[antoonhuiskens]

got caught out by this one too. Though infact at first glance it seems unrelated to compression (it's disabled).

[nwerter]

got caught out by this one too. Though infact at first glance it seems unrelated to compression (it's disabled).

@antoonhuiskens Could you share some more details? What error message did you get, since the error that @johntdyer got is related to having compression enabled as indicated by the title of this issue?

[shakin89]

Hi, i'm in the same situation.
Home Assistant 2023.6.3
LTSS 2.1.0

I have decompressed all the tables, as you can see and i have disabled compression.

chunk_schema	chunk_name	compression_status	before_compression_table_bytes	before_compression_index_bytes	before_compression_toast_bytes	before_compression_total_bytes	after_compression_table_bytes	after_compression_index_bytes	after_compression_toast_bytes	after_compression_total_bytes	node_name
_timescaledb_internal	_hyper_1_13_chunk	Uncompressed
_timescaledb_internal	_hyper_1_1_chunk	Uncompressed
_timescaledb_internal	_hyper_1_23_chunk	Uncompressed
_timescaledb_internal	_hyper_1_25_chunk	Uncompressed
_timescaledb_internal	_hyper_1_27_chunk	Uncompressed
_timescaledb_internal	_hyper_1_29_chunk	Uncompressed
_timescaledb_internal	_hyper_1_2_chunk	Uncompressed
_timescaledb_internal	_hyper_1_31_chunk	Uncompressed

This is the error i have, after restarting HA.

Logger: custom_components.ltss
Source: custom_components/ltss/init.py:133
Integration: ltss (documentation)
First occurred: 19:42:22 (10 occurrences)
Last logged: 19:42:53

Error during connection setup: (psycopg2.errors.FeatureNotSupported) operation not supported on hypertables that have compression enabled [SQL: ALTER TABLE ltss DROP CONSTRAINT ltss_pkey CASCADE, ADD PRIMARY KEY(time,entity_id);] (Background on this error at: https://sqlalche.me/e/20/tw8g) (retrying in 3 seconds)

[shakin89]

SOLUTION for everyone having this problem

In pgAdmin give this command.

alter table ltss set (timescaledb.compress=false);

Restart HomeAssistant and wait for the migration to complete.

Then you have to re-enable the compression.

[nwerter]

@shakin89 Thanks for sorting this out. I didn't get around to looking into this further, but it looks like TimescaleDB only allows modifications to the schema when all chunks are decompressed and compression is disabled on the hypertable.

@freol35241 Is there a way to include this as a note in the readme on hacs without bumping the version number? I don't think we should include this as a command in the migration, since it will force decompression of the database which might result in disk space issues upon migration and alters modifications that were made manually by users.

[freol35241]

@shakin89 Thanks for sorting this out. I didn't get around to looking into this further, but it looks like TimescaleDB only allows modifications to the schema when all chunks are decompressed and compression is disabled on the hypertable.

@freol35241 Is there a way to include this as a note in the readme on hacs without bumping the version number? I don't think we should include this as a command in the migration, since it will force decompression of the database which might result in disk space issues upon migration and alters modifications that were made manually by users.

@nwerter No, not that I am aware of and I agree regarding not adding this solution to the migration code. But, I do think that we should add a try-catch in the migration code for this specific error with an added explanation on how to solve it. That can be considered as a patch version bump, which together with an updated readme should help people to get through the version upgrade.

What do you think?

[nwerter]

Sounds good, I'll try to have a look at this over the weekend

@shakin89 Thanks for sorting this out. I didn't get around to looking into this further, but it looks like TimescaleDB only allows modifications to the schema when all chunks are decompressed and compression is disabled on the hypertable.
@freol35241 Is there a way to include this as a note in the readme on hacs without bumping the version number? I don't think we should include this as a command in the migration, since it will force decompression of the database which might result in disk space issues upon migration and alters modifications that were made manually by users.

@nwerter No, not that I am aware of and I agree regarding not adding this solution to the migration code. But, I do think that we should add a try-catch in the migration code for this specific error with an added explanation on how to solve it. That can be considered as a patch version bump, which together with an updated readme should help people to get through the version upgrade.

What do you think?

Sounds good, I'll try to have a look at this over the weekend.

[Coffee0297]

Have a issue after decompressing with the

alter table ltss set (timescaledb.compress=false)

And when listing Constraint, there is no ltss_pkey in my setup?

2023-07-04 22:21:21.361 ERROR (LTSS) [custom_components.ltss] Error during connection setup: (psycopg2.errors.UndefinedObject) constraint "ltss_pkey" of relation "ltss" does not exist
[SQL: ALTER TABLE ltss
DROP CONSTRAINT ltss_pkey CASCADE,
ADD PRIMARY KEY(time,entity_id);]
(Background on this error at: https://sqlalche.me/e/20/f405) (retrying in 3 seconds)

[nwerter]

Have a issue after decompressing with the

alter table ltss set (timescaledb.compress=false)

And when listing Constraint, there is no ltss_pkey in my setup?

2023-07-04 22:21:21.361 ERROR (LTSS) [custom_components.ltss] Error during connection setup: (psycopg2.errors.UndefinedObject) constraint "ltss_pkey" of relation "ltss" does not exist
[SQL: ALTER TABLE ltss
DROP CONSTRAINT ltss_pkey CASCADE,
ADD PRIMARY KEY(time,entity_id);]
(Background on this error at: https://sqlalche.me/e/20/f405) (retrying in 3 seconds)

Did you make any manual modifications to the primary keys of the database yourself? In setting up the database, the primary keys are automatically created normally so should be present as constraints in the database. The command DROP CONSTRAINT is meant to drop the original primary keys such that a new set of primary keys without the id column can be created.

[Coffee0297]

I have not, Im not great at SQL so I try to not mess arround in it

[freol35241]

@Coffee0297
I am a bit confused here. In the screenshot you posted I cannot find the id column of the ltss table? I expect that column to be there as otherwise the migration code (that appears to give you problem) should not be executed at all. The main point of the migration code is to remove that column.

Can you please give some more details on you current ltss table setup?

• all columns
• all constraints
• all indexes

[Coffee0297]

Can you give some example SQL commands to lidt those? Not good at SQL and deff not the setup there is in timescale/postgresql

[freol35241]

Can you give some example SQL commands to lidt those? Not good at SQL and deff not the setup there is in timescale/postgresql

I think the easiest is to make use of the built-in \d+ command in psql. Assuming you are running timescaledb in a docker container you would do something like (untested, no possibility to test it right now):

docker exec -it <container_id> psql -U <postgres_username> -W -d <db_name> \d+ ltss

[expaso]

@freol35241 Haha I was working on a new version of the TimescaleDB addon, and I also bumped into this issue.. I thought it was my own doing...

I will decompress... no worry :)

[antoonhuiskens]

Ok. Just had another go at upgrading:
I'm getting this:

Error during connection setup: (psycopg2.errors.UndefinedObject) constraint "ltss_pkey" of relation "ltss" does not exist [SQL: ALTER TABLE ltss DROP CONSTRAINT ltss_pkey CASCADE, ADD PRIMARY KEY(time,entity_id);] (Background on this error at: https://sqlalche.me/e/20/f405) (retrying in 3 seconds)

Checking on compression:

# SELECT hypertable_name,chunk_name,is_compressed FROM timescaledb_information.chunks LIMIT 1;
 hypertable_name |    chunk_name    | is_compressed
-----------------+------------------+---------------
 ltss            | _hyper_1_1_chunk | f

I'm not sure why the ltss_constraint doesn't exist.

I'd say that adding IF EXISTS to the ALTER TABLE statement would fix this.
(1 row)

Doesn't look to be related to role permissions either:

# \z ltss
                                 Access privileges
 Schema | Name | Type  |      Access privileges      | Column privileges | Policies
--------+------+-------+-----------------------------+-------------------+----------
 public | ltss | table | ltssadmin=arwdDxt/ltssadmin+|                   |
        |      |       | grafanareader=r/ltssadmin   |                   |

raised issue #111 for this.

[nwerter]

@antoonhuiskens Agreed, in hindsight using DROP CONSTRAINT IF EXISTS would have been a better solution, which I see you already created a pull request for, thanks.

I'm still planning to include a proper try/catch statement for the complete migration to 2.1.0, but am in the middle of moving houses, so haven't got around to that yet.