graphql-engine
graphql-engine copied to clipboard
hasura
On-conflict support for partial unique indexes
I was trying to add Hasura to an existing project and noticed that my Postgresql Unique Indices weren't showing up in the Hasura Console.
These Postgresql Unique Indices are also missing in the on_conflict constraint enum in the GraphQL
NOTE: I am not using Hasura to manage my db migrations.
Docker Image: hasura/graphql-engine:v1.0.0.cli-migrations
GraphQL query:
mutation {
insert_MyTable(
on_conflict: {
constraint: MyTable_column1_column2_key,
update_columns: [ column3, column4 ]
},
objects: {
column1: "value1",
column2: "value2",
column3: "value3",
column4: "value4"
}
) {
returning {
id
}
}
}
Responds with the following error:
{
"errors": [
{
"extensions": {
"path": "$.selectionSet.insert_MyTable.args.on_conflict.constraint",
"code": "validation-failed"
},
"message": "unexpected value \"MyTable_column1_column2_key\" for enum: 'MyTable_constraint'"
}
]
}
Postgres's ON CONFLICT clause requires you to specify the name of the unique constraint, you cannot use the name of the unique index. You can create a unique constraint as follows:
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> UNIQUE (<column1>, <column2> );
This will automatically create a unique b-tree index on the specified group of columns.
cc @marionschleifer We'll need to add this to the docs where we talk about upserts.
OK one case where I've found no solution is where I need to add a partial index. https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns
In my case I have composite index of three columns:
{
"routes": {
"connection_airport_id": "1",
"connection_airport_id": null,
"destination_airport_id": "1"
}
}
The connection_airport_id may or may not be null. A composite unique constraint allows multiple rows as per the above example. If a value is present for connection_airport_id, the constraint works fine. I found the solution to be a partial index as described here: https://www.enterprisedb.com/postgres-tutorials/postgresql-unique-constraint-null-allowing-only-one-null
However, this doesn't allow me to use on conflict.
Is there any workaround here?
Another use case is creating unique indexes using expressions like
CREATE UNIQUE INDEX example_index ON example_table ((some_json_field->>'field1'), (some_json_field->>'field2'));
Unfortunately not possible with a constraint.
Bumping this - working on a feature where we want to upsert with on_conflict being on a partial unique index on a table; currently this isn't possible. I'm getting an error:
{
"errors": [
{
"extensions": {
"path": "$.selectionSet.insert_<tablename>.args.on_conflict.constraint",
"code": "validation-failed"
},
"message": "unexpected value \"unique_idx_name\" for enum: '<tablename>_constraint'"
}
]
}
Any chance you'll add support for this? It's possible in Postgres; I've successfully upsert-ed on a unique index before.
I've experienced this as well. Even from the GraphQL explorer, the UI suggests the constraint name, but it errors when you execute.
On-conflict support for partial unique indexes (or exclusion constraints) would be great.
For anyone that runs into this in the future: I was also having the problem where it wouldn't pick up uniqueness constraints, even ones that were added via hasura migrations, that existed in the DB. It turns out I needed to restart the hasura application server for it to recognize the constraints.
Just ran into this as well. This could be implemented by adding an extra field to the on_conflict parameter, named columns which could be a set of exposed columns for the given schema.
Then you could use this to generate the corresponding SQL with the already existing where parameter:
{columns: [a, b], where: {b: {_is_null: true}}}
=>
insert into ...
on conflict (a, b) where b is null
do update ...
I am interested in contributing this feature.
I started actually looking into this, it seems the internal Postgres DML types already support mapping columns:
data SQLConflictTarget
= SQLColumn ![PGCol]
| SQLConstraint !ConstraintName
deriving (Show, Eq)
instance ToSQL SQLConflictTarget where
toSQL (SQLColumn cols) = "("
<~> ("," <+> cols)
<~> ")"
toSQL (SQLConstraint cons) = "ON CONSTRAINT" <~> toSQL cons
data SQLConflict
= DoNothing !(Maybe SQLConflictTarget)
| Update !SQLConflictTarget !SetExp !(Maybe WhereFrag)
deriving (Show, Eq)
instance ToSQL SQLConflict where
toSQL (DoNothing Nothing) = "ON CONFLICT DO NOTHING"
toSQL (DoNothing (Just ct)) = "ON CONFLICT"
<~> toSQL ct
<~> "DO NOTHING"
toSQL (Update ct set whr) = "ON CONFLICT"
<~> toSQL ct <~> "DO UPDATE"
<~> toSQL set <~> toSQL whr
Seems to be more of a feature exposition issue through GQL.
Any updates on this? To work around this i would need to add an extra column referencing a foreign key so that I can create a unique constraint on 2 columns. Ideally the table does not need to know about the entity its referencing but this would be necessary to create a unique constraint on 2 columns that basically does what a unique partial index would do.
@tirumaraiselvan or someone else from Hasura: would you welcome a PR implementing on_conflict support for partial unique indexes?
This would be a great add-on. Anyone found a workaround for preventing multiple Null values on a column with a unique constraint that doesn't involve partial unique indexes?
I think this ticket was unintentionally closed. The referred commit deals with mssql…
Would love to add a check constraint against a partial unique index as well
watching. hasura suggests using rails migrations and yet it doesn't support the strategy of uniqueness of rails migrations.
@tirumaraiselvan Bumping this as closed incorrectly - on-conflict support for partial unique indexes still very much desired.
@tirumaraiselvan is this on the roadmap? This has come up several times in the past year with my team :)
