Timescaledb: Timescale hypertables don't support ON CONFLICT ON CONSTRAINT upsert statement (breaking Hasura compatibility)

[waterdrop01]

Relevant system information:

• OS: Ubuntu 18.04
• PostgreSQL version: PostgreSQL 11.1
• TimescaleDB version: 1.2.1
• Installation method: docker timescale/timescaledb:1.2.1-pg11

Describe the bug
Here is my schema:

CREATE TABLE data (
    "timestamp" timestamp with time zone NOT NULL,
    "uuid" uuid NOT NULL
);

ALTER TABLE ONLY data
    ADD CONSTRAINT data_pkey PRIMARY KEY ("uuid", "timestamp");

SELECT create_hypertable('data', 'timestamp');

To Reproduce
Run this SQL

INSERT INTO "data" ( "timestamp", "uuid" ) VALUES ( "2019-03-06T16:46:46.988Z", "9db6dbe1-cd14-1d17-38c0-4d4a36679fd6" ) ON CONFLICT ON CONSTRAINT "data_pkey" DO NOTHING RETURNING *

Expected behavior
Expected to correctly insert/upsert data without error, as it is doing with PostgreSQL 10.6 and timescale 1.2.1 (docker timescale/timescaledb:1.2.1-pg10).

Actual behavior
Postgres throws the following error:

ERROR:  hypertables do not support ON CONFLICT statements that reference constraints
HINT:  Use column names to infer indexes instead.

Note: mention of my error in this file: https://github.com/timescale/timescaledb/blob/master/test/expected/upsert.out

[waterdrop01]

Note: Just had a feedback from the folks over at Hasura: https://github.com/hasura/graphql-engine/issues/1711#issuecomment-470429671

[waterdrop01]

note: It may not actually come from PG version, but only from Timescaledb version.

[waterdrop01]

Not being able to reference a constraint by its name is problematic. Is it long term design, or short term workaround?

having a feedback from Timescaledb team on this issue would be very valuable,

Thank you! :)

[waterdrop01]

Hi Timescaledb team,

I understand you may have other priorities, but any chance to get some feedback on this?

Thank you!

[j0h]

@waterdrop01 When using Postgres, it is preferred that you infer indexes. Please see the TIP section: https://www.postgresql.org/docs/11/sql-insert.html#SQL-ON-CONFLICT

It is often preferable to use unique index inference rather than naming a constraint directly using ON CONFLICT ON CONSTRAINT constraint_name. Inference will continue to work correctly when the underlying index is replaced by another more or less equivalent index in an overlapping way, for example when using CREATE UNIQUE INDEX ... CONCURRENTLY before dropping the index being replaced.

My suggestion would be that you use ... ON CONFLICT ("uuid", "timestamp")... instead.

[waterdrop01]

Thanks @j0h for your input. Hi @dianasaur323 :)

Would that make sense one of you go on this thread and share your opinion with the hasura team on how to fix this issue? Bringing back compatibility between Hasura and Timescale is a big thing for both projects...

and It's been broken for almost 5 months :(

[dianasaur323]

Hi @waterdrop01 :)

Thanks for bringing this up. We know the Hasura guys. Let me ping the person on our team who has the best relationship with them to see what's going on here.

[waterdrop01]

Thanks a lot @dianasaur323, much appreciated. Looking to move forward on this question, and let me know if I can be of any help (sorry, just mixed pro and personal github account ;)

[bboule]

hey @waterdrop01 thanks for sticking with this, I need to have a few conversations with the team, but I hope to get back with you and the hasura team as quickly as possible around how we proceed!!

[waterdrop01]

thanks @bboule. Please keep me updated, I'd love to see this issue fixed one way or another and I could help with testing if needed.

[waterdrop01]

Hi @bboule, did you have the chance to talk to the team over at Hasura about this issue, and agree on a fix? Thanks

[bboule]

hey @waterdrop01 apologies for the delayed response, we have looked at this and agree this is something we could fix, we are in the process of slotting this to a release (via our normal process). Will need a little more time before we can discuss the when, but please stay tuned I will update you as soon as possible.

[waterdrop01]

sounds great @bboule! Can't wait to have this fixed ;) Again, would be happy to test if needed

[waterdrop01]

Hi @bboule, any progress on a fix for this issue?

[waterdrop01]

Hi @bboule , any update on this issue? thanks

[waterdrop01]

Hi @bboule and @dianasaur323, hope you are well. Do you still plan to fix for this issue?

[waterdrop01]

timescale/timescaledb:1.7.1-pg12 and asura/graphql-engine:v1.2.1 are still not working correctly together :(

2020-05-26 20:39:36.663 UTC [1589] ERROR:  hypertables do not support ON CONFLICT statements that reference constraints
2020-05-26 20:39:36.663 UTC [1589] HINT:  Use column names to infer indexes instead.

[waterdrop01]

Hi @bboule and @dianasaur323, hope you are well. Do you have a fix in the pipe for this issue?

As per this pull request, @erimatnor wrote:

Being able to support upserts using ON CONFLICT ON CONSTRAINT would bring back compatibility with Hasura and make timescaledb friendlier to use.

This issue has been opened for 18 months, with little to no visibility on the community side :disappointed:

[erimatnor]

Hi @waterdrop01. Thanks for your continued interest in this issue. To be quite honest, we aren't actively working on this limitation right now, due to other priorities set mostly be level of interest in particular user requests. FWIW, we are happy to accept community contributions to remove limitations like this. In the meantime, let me research how much of an effort it would take to fix this.

[waterdrop01]

Thanks @erimatnor for your feedback, appreciated.

I'd love to contribute this fix, but my understanding of SQL and C are too limited I fear :(

I rephrased the issue's title. In fact It's more than just Hasura, all the other Graphql/pg middlewares (Prisma, Postgraphile, etc...) relying on constraints names won't work properly with Timescaledb because of this limitation.

[sulliwane]

Very much interested in having issue this fixed as well! Thanks @erimatnor @bboule @RobAtticus for your input :)

[ondrejspilkaABB]

Hello, any resolution to this issue?

[waterdrop01]

Hello, @erimatnor @dianasaur323 , did you guys plan to release a fix to this bug?

It's soon going to be 2 years this issue is open. And the official documentation page mentions:

Your help would be much appreciated <3 <3 <3

[erimatnor]

@waterdrop01 thank you for the interest in this feature. This is still on our list of limitations that we'd like to address. However, we need to weigh this against other priorities and features that people are asking for.

Now that 2.0 is out, with major new functionality, we will hopefully be able to start looking at the long tail of things like this. Community contributions are of course always welcome

[iosifnicolae2]

I'm having also this problem, any workaround to fix the problem temporarily?