How to use timescale hypertables with foreign keys and keep a one-to-many relation?

Question

I am trying to create a database with minimum redundancy in mind. We would like to use the timescaledb hypertables (I run postgreSQL v. 12 and timescaledb v. 1.7.4). The postgreSQL code to create the tables are as follows - you can see the dbdiagram here https://dbdiagram.io/d/5f992f0e3a78976d7b797ca2 or view the tables here Image of database

CREATE TABLE "datapoints" (
  "id" bigserial UNIQUE NOT NULL,
  "tstz" timestamptz NOT NULL,
  "entity_id" bigint NOT NULL,
  "value" real NOT NULL,
  PRIMARY KEY ("tstz", "entity_id")
);

CREATE TABLE "datapoint_quality" (
  "tstz" timestamptz NOT NULL,
  "datapoint_id" bigint NOT NULL,
  "flag_id" bigint NOT NULL,
  PRIMARY KEY ("tstz", "datapoint_id", "flag_id")
);

CREATE TABLE "quality_flags" (
  "id" bigserial PRIMARY KEY,
  "value" text
);

CREATE TABLE "sensor_types" (
  "id" bigserial PRIMARY KEY,
  "name" text UNIQUE NOT NULL
);

CREATE TABLE "sensors" (
  "tstz" timestamptz NOT NULL DEFAULT (now()),
  "id" bigserial UNIQUE NOT NULL,
  "name" text NOT NULL,
  "parent" bigint NOT NULL,
  "type" bigint NOT NULL,
  PRIMARY KEY ("tstz", "id")
);

CREATE TABLE "datapoint_annotation" (
  "tstz" timestamptz NOT NULL,
  "datapoint_id" bigint NOT NULL,
  "annotation_id" bigint NOT NULL,
  PRIMARY KEY ("tstz", "datapoint_id", "annotation_id")
);

CREATE TABLE "annotations" (
  "id" bigserial PRIMARY KEY NOT NULL,
  "value" text NOT NULL
);

ALTER TABLE "datapoints" ADD FOREIGN KEY ("entity_id") REFERENCES "sensors" ("id");

ALTER TABLE "datapoint_quality" ADD FOREIGN KEY ("datapoint_id") REFERENCES "datapoints" ("id");

ALTER TABLE "datapoint_quality" ADD FOREIGN KEY ("flag_id") REFERENCES "quality_flags" ("id");

ALTER TABLE "sensors" ADD FOREIGN KEY ("parent") REFERENCES "sensors" ("id");

ALTER TABLE "sensors" ADD FOREIGN KEY ("type") REFERENCES "sensor_types" ("id");

ALTER TABLE "datapoint_annotation" ADD FOREIGN KEY ("datapoint_id") REFERENCES "datapoints" ("id");

ALTER TABLE "datapoint_annotation" ADD FOREIGN KEY ("annotation_id") REFERENCES "annotations" ("id");

CREATE UNIQUE INDEX ON "quality_flags" ("value");

CREATE UNIQUE INDEX ON "annotations" ("value");

So far so good - next I want to create the hypertables, which I do as:

SELECT create_hypertable('datapoint_annotation', 'tstz');
SELECT create_hypertable('datapoint_quality', 'tstz');
SELECT create_hypertable('datapoints', 'tstz');
SELECT create_hypertable('sensors', 'tstz');

This works well for the first two lines, but for the latter two I get the following error:

ERROR:  cannot create a unique index without the column "tstz" (used in partitioning)
SQL state: TS103

I can include the tstz in the primary key as ("id", "tstz") and use that as foreign key, but this gives me a one-to-one relation, and for minimum redundancy I would like to have a one-to-many relation.

I am sure there should be some way to do this - so what am I missing?

score 2 · Answer 1 · answered Oct 28 '20 at 11:26

I'll take the foreign key constraint from datapoint_quality to datapoints as an example.

To make that work with a partitioned table, you need a unique constraint on datapoint. As the error message tell you, such a constraint must contain the partitioning key. So you end up with

ALTER TABLE datapoints ADD UNIQUE (id, tstz);

To reference that unique constraint from datapoint_quality, you need to have the timestamp there too:

ALTER TABLE datapoint_quality ADD datapoints_tstz timestamp with time zone;

You have to fill it with the appropriate values:

UPDATE datapoint_quality AS dq
SET datapoints_tstz = d.tstz
FROM datapoints AS d
WHERE d.id = dq.datapoint_id;

Then set it NOT NULL:

ALTER TABLE datapoint_quality ALTER datapoints_tstz SET NOT NULL;

Now you can define your foreign key:

ALTER TABLE datapoint_quality
   ADD FOREIGN KEY (datapoint_id, datapoints_tstz)
   REFERENCES datapoints (id, tstz) MATCH FULL;

There is no other way to have foreign key constraints with partitioned tables.

I finally got around to test this out, but ran in to the same issue as Jean Fernandez and it does not seem like it is possible to use foreign keys with hypertables (https://docs.timescale.com/timescaledb/latest/overview/limitations/) — Tonie Davidsen Brinch, Dec 14 '21 at 08:53

score 2 · Answer 2 · answered May 28 '21 at 18:45

After testing the proposed solution by Laurenz in a database I have and also after replicating the original database of this case. I use PostgreSQL 12.6 and timescaledb 1.7.5.

Basically, I arrived well until defining the Foreign Key for Table datapoint_quality:

ALTER TABLE datapoint_quality
   ADD FOREIGN KEY (datapoint_id, datapoints_tstz)
   REFERENCES datapoints (id, tstz) MATCH FULL;

The next error is present in both databases I've tested after several attempts (included above one) to define the foreign key to a hypertable:

ERROR: foreign keys to hypertables are not supported Blockquote 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.

Considering this, does anyone know any solution at the DB level to establish the relationships (1..* or ...) among a table without hypertables to other tables with hypertables behind?

Maybe could be a solution to deal with this at even a REST API level (e.g. Django or Flask) given at timescaledb or PostgreSQL I have not found much more solutions.

How to use timescale hypertables with foreign keys and keep a one-to-many relation?

2 Answers2

Linked