Foreign keys to hypertables are not supported (except when they are)

[jonfreedman]

Describe the bug
It's possible to create a table which has a foreign key into a hypertable provided the foreign key is defined when the table is created

To Reproduce

CREATE TABLE "default".test_parent (
    time TIMESTAMPTZ NOT NULL,
    timeseriesId INTEGER NOT NULL,
    value NUMERIC(30, 14) NOT NULL,
    insertTime TIMESTAMPTZ NOT NULL DEFAULT (NOW() at time zone 'UTC'),

    CONSTRAINT PK_test_parent  PRIMARY KEY (time, timeseriesId, insertTime)
);

SELECT public.create_hypertable('test_parent', 'time');

CREATE TABLE "default".test (
    time TIMESTAMPTZ NOT NULL,
    timeseriesId INTEGER NOT NULL,
	insertTime TIMESTAMPTZ NOT NULL,
    key VARCHAR(50) NOT NULL,
    value VARCHAR(50) NOT NULL,

    CONSTRAINT PK_test PRIMARY KEY (time, timeseriesId, insertTime, key),
    CONSTRAINT FK_test_time_timeseriesId_insertTime FOREIGN KEY (time, timeseriesId, insertTime) REFERENCES "default".test_parent(time, timeseriesId, insertTime)
);

ALTER TABLE "default".test DROP CONSTRAINT FK_test_time_timeseriesId_insertTime, ADD CONSTRAINT FK_test_time_timeseriesId_insertTime
FOREIGN KEY (time, timeseriesId, insertTime) REFERENCES "default".test_parent(time, timeseriesId, insertTime)

Expected behavior
Either second table cannot be created, or dropping and re-creating the foreign key is valid.

Actual behavior
Tables are created but FK cannot be dropped and re-created

[dianasaur323]

@jonfreedman Thanks for the bug report - will try to reproduce + triage.

[taavit21]

Having the same issue. Any updates when it will be fixed?

[iosifnicolae2]

Having the same issue. Any updates when it will be fixed?

Same here..

[smpshehan]

I have the same issue still when I use hibernate. Got the error: org.postgresql.util.PSQLException: ERROR: foreign keys to hypertables are not supported

[erimatnor]

@taavit21 @iosifnicolae2 @smpshehan Thanks for letting us know that this is also an issue for you. This will certainly affect our prioritizations. We also welcome community contributions if someone is interested in creating a PR.

[seksity]

I also getting the same issue when I using NodeJS and Sequelize to associate table for many-to-many relations.
The error message as below:-
SequelizeDatabaseError: foreign keys to hypertables are not supported

[jecf7]

Is there any new development to deal with the error of this topic or with the next related issue with hypertables using foreign key constraints in PostgreSQL?

#To Reproduce, there are next tables:

CREATE TABLE ids ( 
    measurement_id int DEFAULT 0,        
    description text DEFAULT 0,       
    m_id bigserial NOT NULL,
    service_id int  DEFAULT NULL,     
    time bigint NOT NULL DEFAULT cast((EXTRACT(EPOCH FROM now() AT TIME ZONE 'UTC') * 1000) as bigint),
    user_id int  DEFAULT NULL,
    end_time DOUBLE PRECISION DEFAULT 0,     
    start_time int NOT NULL DEFAULT 0
);

CREATE INDEX ON ids (time DESC, user_id);
CREATE INDEX ON ids (time DESC, service_id);

SELECT create_hypertable('ids', 'start_time', chunk_time_interval => 604800016);

---------

CREATE TABLE IF NOT EXISTS metrics (
  id bigserial NOT NULL, 
  duration real DEFAULT NULL, 
  metric integer DEFAULT 0,   
  m_id bigint NOT NULL,
  time bigint NOT NULL DEFAULT 0   
);

ALTER TABLE metrics ADD PRIMARY KEY (time, m_id);

CREATE INDEX ON metrics (time DESC);
CREATE INDEX ON metrics (time DESC, measurement );
CREATE INDEX ON metrics (time DESC, m_id );

grant all privileges on ids, metrics to your_db_user;

SELECT create_hypertable('metrics', 'time' , chunk_time_interval => 604800016);

SELECT table_catalog, table_schema, table_name, privilege_type FROM  information_schema.table_privileges WHERE  grantee = 'your_db_user';

---------

DROP TABLE IF EXISTS resource;
CREATE TABLE resource(
id          int NOT NULL,
cpu         text DEFAULT 0,
storing     text DEFAULT 0,
memory      text DEFAULT 0
); 

ALTER TABLE resource ADD PRIMARY KEY (id);

CREATE SEQUENCE resource_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE resource_id_seq
  OWNER TO your_db_user;

ALTER TABLE resource ALTER COLUMN id SET DEFAULT nextval('resource_id_seq'::regclass);

---------

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS ns;

CREATE TABLE ns(
id                    bigint NOT NULL,
uuid                  uuid NOT NULL DEFAULT uuid_generate_v4 (),
availability          double precision,
faultTolerance        boolean,
activated             boolean,     
UNIQUE (id, uuid),
PRIMARY KEY(id),
  CONSTRAINT fk_resource
     FOREIGN KEY(id)
         REFERENCES resource(id)
         ON DELETE CASCADE
);

CREATE SEQUENCE ns_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE ns_id_seq
  OWNER TO your_db_user;

ALTER TABLE ns ALTER COLUMN id SET DEFAULT nextval('ns_id_seq'::regclass);

---------

DROP TABLE IF EXISTS authentication;

CREATE TABLE authentication(
id                    integer NOT NULL,
username              character varying(255) NOT NULL,
password              character varying(255) NOT NULL,
host                  character varying(255) NOT NULL,
port                  character varying(10) NOT NULL,    
PRIMARY KEY(id)
);

CREATE SEQUENCE auth_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE auth_id_seq
  OWNER TO your_db_user;

ALTER TABLE authentication ALTER COLUMN id SET DEFAULT nextval('auth_id_seq'::regclass);

---------

DROP TABLE IF EXISTS job;

CREATE TABLE job(
id                    int NOT NULL,
interval              integer NOT NULL,
auth_id               integer REFERENCES authentication (id),
ns_id                 integer REFERENCES ns (id),  
UNIQUE (auth_id, ns_id),
PRIMARY KEY(id)
);

ALTER TABLE job
ADD CONSTRAINT fk_auth_id
FOREIGN KEY (id) REFERENCES authentication (id)
ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE job
ADD CONSTRAINT fk_ns_id
FOREIGN KEY (id) REFERENCES ns (id)
ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED;

CREATE SEQUENCE job_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE job_id_seq
  OWNER TO your_db_user;

ALTER TABLE job ALTER COLUMN id SET DEFAULT nextval('job_id_seq'::regclass);

---------

DROP TABLE IF EXISTS job_metric;

-- NEW TABLE AT QOSIUM_11 database to validate interference with Qosium Measurements
CREATE TABLE job_metric (
  id                       int NOT NULL,       
  j_id                     int NOT NULL REFERENCES job (id), 
  mj_id                   bigint  NOT NULL,
  jm_time                 bigint NOT NULL 
);
CREATE INDEX ON job_metric (jm_time DESC);
CREATE INDEX ON job_metric (jm_time DESC, id);
CREATE INDEX ON job_metric (jm_time DESC, mj_id);

ALTER TABLE job_metric ADD PRIMARY KEY (jm_time, id);

grant all privileges on job_metric to your_db_user;

SELECT create_hypertable('job_metric', 'jm_time' , chunk_time_interval => 604800016);

CREATE SEQUENCE mjob_metric_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE mjob_metric_id_seq
  OWNER TO your_db_user;

ALTER TABLE job_metric ALTER COLUMN id SET DEFAULT nextval('mjob_metric_id_seq'::regclass);
---------

After creating the tables, I have used the solution proposed by @Laurenz Albe in a database with PostgreSQL 12.6 using the extension of timescaledb 1.7.5 as follows:

#To fill the table with the appropriate values:

UPDATE job_metric AS jm_point
SET jm_time = qm.time
FROM metrics AS qm
WHERE qm.m_id = jm_point.mj_id;

#Then set it NOT NULL:
ALTER TABLE job_metric ALTER jm_time SET NOT NULL;

#To define the foreign key:

ALTER TABLE job_metric
   ADD FOREIGN KEY (mj_id, jm_time)
   REFERENCES metrics (time, m_id) MATCH FULL;

#Response of the last reference table to enable foreign key:
Query returned successfully in 40 msec.

Expected behavior:

• The main reason to use a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table.
• For example, if the row for a job is deleted from the Job table, and the job's ID is used for job metrics in the Job_Metric table, the relational integrity between the two tables is broken; the deleted job's job metrics will be orphaned in the Job_Metric table without a link to the data in the Job table.
• The idea is to have the table job_metric in an even many-to-many relationship to access the information of job and metrics tables.

Actual behavior and error:
Tables are created and FKs were created but cannot be used when data is inserted at job_metric table as is detailed in the following:

INSERT INTO job_metric (j_id, mj_id, jm_time) 
VALUES(13, 185063, 1621957192266);

ERROR: foreign keys to hypertables are not supported CONTEXT: SQL statement " ALTER TABLE _timescaledb_internal._hyper_5_5_chunk ADD CONSTRAINT "5_13_job_metric_j_id_mj_id_jm_time_fkey" FOREIGN KEY (j_id, mj_id, jm_time) REFERENCES qmetrics("time", m_id) MATCH FULL " PL/pgSQL function _timescaledb_internal.chunk_constraint_add_table_constraint(_timescaledb_catalog.chunk_constraint) line 42 at EXECUTE SQL state: 0A000

*According to https://docs.timescale.com/timescaledb/latest/overview/limitations/##distributed-hypertable-limitations, it looks like the above error is part of the hypertable limitations:

Foreign key constraints referencing a hypertable are not supported.

#Request:
Given the above information and errors, does anyone know any solution at the DB level to establish the relationships (many-to-many or one-to-many) using timescaledb extension and mainly hypertables?

---

Actually, I have obtained similar of above error when I have attempted to create many-to-many relation among the tables metrics and job_metric specifically using the Django Rest Framework:

class Job_Metrics(models.Model):
job = models.OneToOneField(Job, on_delete=models.CASCADE)
**metrics = models.ManyToManyField(Metrics)**
time = models.IntegerField(default=0)

Running the application metrics pointing out directly metrics_db

$ python3 manage.py migrate metrics --database=metrics_db
Operations to perform:
Apply all migrations: metrics
Running migrations:
Applying metrics.0002_job...Traceback (most recent call last):
File "/var/myproject/myprojectenv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.FeatureNotSupported: foreign keys to hypertables are not supported

Please share, if someone knows a solution or have an idea to deal with the above error at the REST API level to access data associated tables (metrics and jobs) given the timescaledb extension seems do not a viable solution.

[erimatnor]

@jonfreedman Tried to reproduce the initial example on the most recent version of TimescaleDB (2.4.2) and it fails on creating the second test table with the error: "ERROR: foreign keys to hypertables are not supported".

So, it seems this is the expected behavior.

Closing the issue. Please reopen if this turns out to still be a problem.

[iosifnicolae2]

Where we can track where this functionality will be implemented?

Thank you!

[smpshehan]

I don't understand why do you close this issue? I understand that it is not a bug. But we need that feature. Having a foreign key to another table is a fundamental feature of a relational database. Without that, how can we create the relations?

[smpshehan]

@jonfreedman Tried to reproduce the initial example on the most recent version of TimescaleDB (2.4.2) and it fails on creating the second test table with the error: "ERROR: foreign keys to hypertables are not supported".

So, it seems this is the expected behavior.

Closing the issue. Please reopen if this turns out to still be a problem.

What is your solution to create a reference to a hypertable from another table?

[erimatnor]

This issue was filed as a bug, which now cannot be reproduced. So the current behavior is as intended.

For the feature request of supporting foreign keys to hypertables, please refer to the following issue: #498