Update rows in compressed data that have foreign keys

1

I’ve defined a hypertable where i have applied compression and defined segment_by on two specific columns which are Foreign Keys.

Here is the structure of the table.

CREATE TABLE public.measurements (
	"time" timestamptz NOT NULL,
	"waterMeterDevEUI" varchar(255) NOT NULL,
	"waterSupplyId" int4 NULL,
	measurement int4 NOT NULL,
	delta int4 NOT NULL,
	CONSTRAINT "measurements_waterMeterDevEUI_fkey" FOREIGN KEY ("waterMeterDevEUI") REFERENCES public."waterMeters"("devEUI"),
	CONSTRAINT "measurements_waterSupplyId_fkey" FOREIGN KEY ("waterSupplyId") REFERENCES public."waterSupplies"(id)
);

Conversion of table to hypertable based on 1 month interval

SELECT create_hypertable('measurements', 'time', chunk_time_interval => INTERVAL '1 month', create_default_indexes => true);

Compression based on 1 month interval

      ALTER TABLE "measurements" SET (
        timescaledb.compress,
        timescaledb.compress_segmentby = '"waterSupplyId", "waterMeterDevEUI"'
      );
    `
`SELECT add_compression_policy('"measurements"', INTERVAL '1month');`

However, i cannot update rows which have been compressed, whereas rows which haven’t yet been compressed can be updated.

If I remove the FKs (and remove the segment_by section of the code), update operation of the rows which have been compressed works.

Does Timescale supports updates on rows in this case?
Should I change anything in the definition of the compression?

Thank you in advance,
Nady

2

Which version of TimescaleDB and PostgreSQL are you using this should work with most recent TimescaleDB versions and PostgreSQL 14+.