Graphql-engine: hasura + timescaledb on pg11 > Upserts not working anymore

[waterdrop01]

Hi there,

I recently switched Hasura backend db from timescale/timescaledb:1.2.1-pg10 to timescale/timescaledb:1.2.1-pg11.

As described in this issue on Timescaledb github, when doing an UPSERT operation on an hypertable (through Graphql-engine), I'm now faced with this error: ERROR: hypertables do not support ON CONFLICT statements that reference constraints.

To workaround this, Guys at Timescaledb advised me to instead of mentioning the constraint name (like does graphql-engine statement) :

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 *

To explicitly mention column names (involved in the constraint):

Even though I believe the "design issue" is more on Timescaledb side, I'd be interested to hear your input on this > would that make sense for Hasura to change the way it's working now, If not, why?

Many thanks!

[0x777]

would that make sense for Hasura to change the way it's working now, If not, why?

This wouldn't be a right way to solve this problem. What if some other extension only supports using constraint names? Hasura should ideally switch between flavors of postgres (and their versions) and work around their limitations. However this looks slightly more complicated as we also need to check if a table is a hypertable, and then switch based on that.

[waterdrop01]

Hello @0x777 , thank you for answer. I just got feedback from the Timescale team on this issue:

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.

and also:

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

This issue is actually not related to PG version, but instead to the latest TimescaleDB version (it comes as a pg extension).

Since 2 months, Hasura GQL engine doesn't work with timescaledb anymore (for UPSERT operations). It's too bad as these two projects are both gaining lot of traction. Any take from the Hasura team on how to solve this issue?

FYI, here is the dedicated thread on TimescaleDB github.

Thank you!

[0x777]

@waterdrop01 We'll try to add a workaround.

[waterdrop01]

@0x777 wow that would be great! really looking forward a solution to this, thanks.

[waterdrop01]

Hi @0x777 , congrats on the 1.0 beta0.1 release.

I'd love to have timescale support in CA :)

I noticed this project has the exact same issue: https://github.com/JasperFx/marten/issues/1262

As mentioned: "According to the Postgres docs: It is often preferable to use unique index inference rather than naming a constraint directly using ON CONFLICT ON CONSTRAINT constraint_name."

Thanks, have a nice day

[waterdrop01]

Hello @0x777 , any chance to have that added to the roadmap?

[waterdrop01]

@marionschleifer thanks for your help on this. @dsandip please ask any question, I'd be happy to assist you on this issue!

[waterdrop01]

Hi Hasura team, would that make sense that one of you @0x777 @shahidhk @dsandip @marionschleifer go on the Timescale github issue, and share with them your opinion on how to fix this?

That would potentially bring back compatibility between Timescale and Hasura projects! (it's been 5 months it's broken).

[waterdrop01]

Hi @marionschleifer , any update on this issue? Thanks!

[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 @tirumaraiselvan, any visibility regarding this issue? Would be greatly appreciated

[haf]

https://github.com/hasura/graphql-engine/issues/1711#issuecomment-489552148 @0x777 What was the workaround?

[sulliwane]

Very much interested in the workaround as well! Thanks @0x777 @tirumaraiselvan for your input