Foreign Key to hypertable

[jvujcic]

Allow creation of foreign keys (that contain timestamp) to hypertables.

[erimatnor]

@jvujcic Yes, this is a known issue, but a bit tricky to support since the data in the hypertable is actually split up across a number of subtables (chunks). In theory, you should be able to have a foreign key into a specific chunk.

[mfreed]

A little more context: I discussed this issue with @jvujcic at length in Slack, and he understands limitation of handling all types of foreign keys.

However, I think he is especially interested in FKs to existing UNIQUE keys in a hypertable (e.g., whether timestamp is UNIQUE, or a unique composite key on timestamp and some other partitioning key), which should be more architecturally feasible.

[njern]

Is this something you guys are planning to tackle? I just noticed this for one of my use cases where I would like to do what the previous poster mentions; refer to existing UNIQUE keys in the hypertable with a foreign key from another hypertable.

[jvujcic]

@erimatnor Any news on this limitation?

[cercata]

I'm having the same issue while trying to port my DB to make it compatible with timescale.
I understand the limitation, I wonder if there are some "desing practices" on how to link to a hypertable.

I have a table for alarms, and another for alarm ack, and i would like to link to what alarm an ack is referenced.

Can I do the link after I remove the FK ? I mean, the FK is just for ensuring integrity, isn't it ?

[mashaalmemon]

Now that timescaledb is finally available on Azure, we are evaluating it for use with our product. This one is a deal breaker. We want to have data integrity and have the benefits of timescale.

Is dealing with this on the roadmap?

[Norbo11]

What I've found strange, is that if you have some tables referencing a timestamp column Y in table X and then call create_hypertable('X', 'Y'), the call succeeds without any error messages and the foreign keys remain.

Then if you try to add a new foreign key constraint on a hypertable, it fails.

Is there a bug here?

[kancsuki]

Seems like Postgres 12 will be able to reference partitioned tables by FKs.

Does this help in any way to implement the same features for hypertables?

[jvujcic]

Somebody on slack just posted that it is now possible to have FK to unique keys in hypertable. Is this true?

[utx0]

If so how?

[jvujcic]

Here is a link to slack conversation:
https://timescaledb.slack.com/archives/C4GT3N90X/p1572502891281000?thread_ts=1572500608.279900&cid=C4GT3N90X

[nestormh]

Here is a link to slack conversation: https://timescaledb.slack.com/archives/C4GT3N90X/p1572502891281000?thread_ts=1572500608.279900&cid=C4GT3N90X

Hi, I tried that example in the current latest release (v2.5.0 over Postgres14 using the docker image timescale/timescaledb:2.5.0-pg14), and it is not working:

demo=# create table logs ( evtime timestamp without time zone not null, device_id int not null, something int not null, primary key(evtime, device_id));
CREATE TABLE
demo=# select create_hypertable('logs', 'evtime');
 create_hypertable 
-------------------
 (1,public,logs,t)
(1 row)

demo=# create table logs_extra (  id bigserial,  evtime timestamp without time zone not null, device_id int not null, "data" jsonb not null , FOREIGN KEY (evtime,device_id) REFERENCES logs(evtime, device_id) ON DELETE RESTRICT );
ERROR:  foreign keys to hypertables are not supported

Version checking, for reference:

demo=# select extname, extversion from pg_extension where extname='timescaledb';
   extname   | extversion 
-------------+------------
 timescaledb | 2.5.0
(1 row)

demo=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.0 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit
(1 row)

• I know it is possible to use foreign keys to reference partitioned tables from other tables in Postgres. What is the technical limitation that prevents getting the same behavior in TimescaleDB? I can see that this is now tagged as tech-debt, but I'm curious about the reason.
• Is this issue planned to be addressed in the short term?

Thank you in advance.

[jvanns]

I just wanted to add a little +1 to this feature request. I've been able to create two hypertables, one with an fkey to the other. This appears to succeed at schema-creation time. But then an insert fails with this message;

"foreign keys to hypertables are not supported"

So I guess they're not really permitted despite the seeming success of the initial schema creation. The above error is thrown from the 'hidden' query that performs an 'ALTER TABLE _timescaledb_internal._hyper_1_1_chunk ADD CONSTRAINT ....'

[jomatt]

+1 on this

I'm a big fan of TimescaleDB so thanks a lot to the whole team for creating such a great product. Is there any comment from the team about this feature request? I (and probably everyone else in this thread) would appreciate a short update @erimatnor

[erimatnor]

@jomatt and others, this is something we're currently considering supporting as part of generalizing hypertables to more workloads where foreign keys into a hypertable is required. Can't promise any timeline at this point, but it is definitely on our list of things we'd like to support. Right now it is just a matter of priority compared to other things. Sorry about not being able to say more right now.

[jvanns]

Awesome! Just to reiterate what @jomatt said - I'm a massive fan of TimescaleDB too and it is such a welcome and needed enhancement to PostgreSQL so the idea of hypertable generalisation sounds fantastic!

[adriangb]

I ran into this and worked around it by creating a custom type instead of a table linked by a FK. It works, but custom types can be a pain to work with.

[jflambert]

I ran into this and worked around it by creating a custom type instead of a table linked by a FK. It works, but custom types can be a pain to work with.

curious what you mean by custom type. I just ended up creating "fake" foreign keys (just unreferenced) and I use a before delete trigger to clear all required targets (either NULL or DELETE) my understanding is that it's not worse performance wise because individual constraint triggers would still need to be executed, and in my case I'm doing three-four triggers in a single one.

[adriangb]

I mean I did CREATE TYPE ... and then have foo my_type[] in my hypertable.

[Huluti]

does this is fixed by #6989 and timescaledb 2.16.0?

[LukasJerabek]

Thanks for this feature! However, retention policy stops working in case you reference rows that need to be deleted. Is adding "delete cascade" option to retention already planned or am I the first one to notice?

here in my example rows in table celery.task_destination_map reference celery.events table rows with celery_task_destination_map_fkey foreign key.

2024-11-14 12:38:52.181 CET [513510] LOG:  job 1131 threw an error
2024-11-14 12:38:52.182 CET [513510] ERROR:  cannot drop constraint 92_994_events_pkey on table _timescaledb_internal._hyper_1_92_chunk because other objects depend on it
2024-11-14 12:38:52.182 CET [513510] DETAIL:  constraint celery_task_destination_map_fkey on table celery.task_destination_map depends on index _timescaledb_internal."92_994_events_pkey"
2024-11-14 12:38:52.182 CET [513510] HINT:  Use DROP ... to drop the dependent objects.
2024-11-14 12:38:52.185 CET [278411] LOG:  background worker "Retention Policy [1131]" (PID 513510) exited with exit code 1