[Bug]: adding a foreign key constraint to a compressed hypertable gives a generic error even if the segmentby column is the foreign key column

[srieding]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Compression

What happened?

If you create a table, make it a hypertable, add a foreign key, then switch on compression you can make a table that you are not able to make when you make the table, make it a hypertable, switch on compression and then try to add the foreign key.

TimescaleDB version affected

2.11.2

PostgreSQL version used

14

What operating system did you use?

windows 10 x64

What installation method did you use?

Docker

What platform did you run on?

Not applicable

Relevant log output and stack trace

No response

How can we reproduce the bug?

-- On an empty database, I create two hypertables
-- On the first I add a foreign key before enabling compression
-- On the second I add a foreign key after enabling compression
-- The first succeeds, the second fails.

-- 2.11.2
SELECT extversion FROM pg_extension where extname = 'timescaledb';

CREATE TABLE quantities (
  id   serial constraint quantities_pkey primary key
);

CREATE TABLE measurements (
  generated_at timestamptz not null,
  value        int4        not null,
  quantity_id  int4        not null
);

SELECT create_hypertable(
         'measurements',
         'generated_at',
         chunk_time_interval => INTERVAL '7 days',
         if_not_exists => TRUE,
         create_default_indexes => FALSE
         );

ALTER TABLE measurements ADD CONSTRAINT measurements_quantity_fkey FOREIGN KEY (quantity_id) references quantities (id);

ALTER TABLE measurements SET (timescaledb.compress, timescaledb.compress_segmentby = 'quantity_id');

CREATE TABLE measurements_2 (
  generated_at timestamptz not null,
  value        int4        not null,
  quantity_id  int4        not null
);

SELECT create_hypertable(
         'measurements_2',
         'generated_at',
         chunk_time_interval => INTERVAL '7 days',
         if_not_exists => TRUE,
         create_default_indexes => FALSE
         );

ALTER TABLE measurements_2 SET (timescaledb.compress, timescaledb.compress_segmentby = 'quantity_id');

-- This gives ERROR: operation not supported on hypertables that have compression enabled.
ALTER TABLE measurements_2 ADD CONSTRAINT measurements_2_quantity_fkey FOREIGN KEY (quantity_id) references quantities (id);
-- instead I expect it to add the foreign key in the case that the segmentby column is the same as the foreign key column.

[jnidzwetzki]

Hello @srieding,

Thanks for the detailed steps to reproduce the problem. I was able to reproduce the behavior with TimescaleDB 2.12.0-dev in my local environment. I was only able to add the constraint only to measurements.

[marcelofernandez]

Hi!

This issue makes migration procedures painful following the current documentation because you end up with restored hypertables without FKs.

Documented procedure is:

1. Restore hypertable(s) pre-data
2. Create hypertable(s)
3. Set hypertable(s) compression parameters
4. Restore data from CSVs
5. Restore hypertable(s) post-data here this fails with errors like below:

pg_restore: from TOC entry 4619; 2606 361444 CONSTRAINT sensor_readings sensor_readings_read_time_device_id_12dfafaa_uniq postgres
pg_restore: error: could not execute query: ERROR:  operation not supported on hypertables that have compression enabled
Command was: ALTER TABLE ONLY public.sensor_readings
    ADD CONSTRAINT sensor_readings_read_time_device_id_12dfafaa_uniq UNIQUE (read_time, device_id);
[...]
pg_restore: creating FK CONSTRAINT "public.sensor_readings sensor_readings_device_id_f120ca42_fk_sensor_devices_id"
pg_restore: from TOC entry 4621; 2606 361463 FK CONSTRAINT sensor_readings sensor_readings_device_id_f120ca42_fk_sensor_devices_id postgres
pg_restore: error: could not execute query: ERROR:  operation not supported on hypertables that have compression enabled
Command was: ALTER TABLE ONLY public.sensor_readings
    ADD CONSTRAINT sensor_readings_device_id_f120ca42_fk_sensor_devices_id FOREIGN KEY (device_id) REFERENCES public.sensor_devices(id) DEFERRABLE INITIALLY DEFERRED;

So, I'm going to slightly change the procedure to:

1. Restore hypertable(s) pre-data
2. Add FK reference and/or UNIQUE constraints with ALTER TABLE on the hypertable(s)
3. Create hypertable(s)
4. Set hypertable(s) compression parameters
5. Restore data from CSVs
6. Restore hypertable(s) post-data

This way I'm still going to have the same errors, but I don't care because the FK Constraint was already in place before setting a compression policy.

Is there any other option? Thank you!

[marcelofernandez]

Hi!

Looking at the 2.16.0 Release Notes, is this issue somehow covered/fixed?

Offer new options for use cases that require foreign keys defined.
You can now add foreign keys from regular tables towards hypertables. We have also removed
some really annoying locks in the reverse direction that blocked access to referenced tables
while compression was running.

And below, in the highlights section:

You can add foreign keys from regular tables to hypertables, with support for all types of cascading options.
This is useful for hypertables that partition using sequential IDs, and need to reference those IDs from other tables.

Thank you!