Handle multiple constraint violations on upsert

[ARMATAV]

graphql-engine: hasura/graphql-engine:v1.1.0.cli-migrations

How can I go about handling multiple constraint violations?

Say I have two constraints, [my_first_constraint, my_second_constraint]

on_conflict: {
    constraint: my_first_constraint,
    update_columns: [updated_at]
}

Works for my_first_constraint violations, but then errors out when my_second_constraint conflicts during an upsert.

I know Postgres doesn't handle multiple constraints, but is there a way to do something like this;

on_conflict: {
    constraint: null,
    update_columns: [updated_at]
}

And specify that regardless of the violation, just update updated_at? I'm updating the column because I want the row to be returned.

How do I specify an update action regardless of constraint target?

[0x777]

Looks like this is not straightforward in Postgres currently.

[LucasBSC]

I'd like also to know if it is possible to handle this behaviour

[larsblumberg]

My current, fully functional workaround: Execute the mutation with the first constraint. If there was a constraint violation with any of both constraints (check both error code and error message), re-run the mutation with the second constraint. Repeat if you have more than 2 constraints.

Here's part of the core part of the implementation (Python):

from json import loads

from gql import Client, gql
from gql.transport.requests import RequestsHTTPTransport


def post_query(
    query,
    variables={},
    retry_constraints=None,  # <------ This argument carries the constraints to retry on failure
    client=None,
):
    assert as_admin or getattr(_request_ctx_stack.top, 'access_token', None), (
        "You are trying to make a non-admin graphql request but the request "
        "context has no access_token set"
    )
    transport = None if client else RequestsHTTPTransport(...)
    client = client or Client(transport=transport, fetch_schema_from_transport=False)
    if retry_constraints:
        # When executing a mutation with an `on_conflict` setting, only one single
        # constraint can be specified. By passing a list of `retry_constraints` each
        # constraint is used subsequently in place of `$CONSTRAINT` (placeholder in the
        # query). For each constraint it is checked if one of the possible
        # `retry_constraints` is violated. If that's the case, the mutation is re-run.
        for constraint in retry_constraints:
            try:
                return client.execute(
                    gql(query.replace('$CONSTRAINT', constraint)),
                    variable_values=variables,
                )
            except Exception as e:
                try:
                    error = loads(str(e).replace('"', '\\"').replace("'", '"'))
                    if not (
                        (error['extensions']['code'] == 'constraint-violation')
                        and any(c in error['message'] for c in retry_constraints)
                    ):
                        raise e
                except:
                    raise e
        raise Exception(f"All constraints ({', '.join(retry_constraints)}) violated")
    else:
        return client.execute(gql(query), variable_values=variables)

And here's an example call to this function, using retry constraints:

post_query(
    '''mutation(...) {
        updateSomething(
            objects: ...
            on_conflict: {
                constraint: $CONSTRAINT,
                update_columns: [updated_at]
            }
        ) {affected_rows}
    }
    ''',
    retry_constraints=('my_first_constraint', 'my_second_constraint')
)

[cakemountain]

My team would find this extremely useful!

[quentin-decre]

Would love to have that !