Graphql-engine: Warning when deleting an automatically-created "updated_at" trigger when the column is deleted

[bitjson]

Hi there – I have a mutation that looks like this:

mutation ConfirmEmailByTokenAndProject(
  $token: String!,
  $projectId: Int!,
  $time: timestamptz!
) {
  update_email_confirmation(
    where: {
      token: {_eq: $token},
      device:{user: { project_id: {_eq: $projectId} }}
    },
    _set: {
      confirmed_at: $time
    }) {
    returning {
      email_confirmation_id
      email
      created_at
      confirmed_at
    }
  }
}

And in testing, I'm getting this error:

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "statement": "WITH \"public_email_confirmation__mutation_result_alias\" AS (UPDATE \"public\".\"email_confirmation\" SET \"confirmed_at\" = $1  WHERE (('true') AND (((((\"public\".\"email_confirmation\".\"token\") = ($2)) AND ('true')) AND ((EXISTS  (SELECT  1  FROM \"public\".\"device\" AS \"_be_0_public_device\" WHERE ((((\"_be_0_public_device\".\"device_id\") = (\"public\".\"email_confirmation\".\"device_id\")) AND ('true')) AND (((EXISTS  (SELECT  1  FROM \"public\".\"user\" AS \"_be_1_public_user\" WHERE ((((\"_be_1_public_user\".\"user_id\") = (\"_be_0_public_device\".\"user_id\")) AND ('true')) AND (((((\"_be_1_public_user\".\"project_id\") = ($3)) AND ('true')) AND ('true')) AND (('true') AND ('true'))))     )) AND ('true')) AND (('true') AND ('true'))))     )) AND ('true'))) AND ('true'))) RETURNING * ) SELECT  json_build_object('returning', (SELECT  coalesce(json_agg(\"root\" ), '[]' ) AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_1_e\"  FROM  (SELECT  \"_0_root.base\".\"email_confirmation_id\" AS \"email_confirmation_id\", \"_0_root.base\".\"email\" AS \"email\", \"_0_root.base\".\"created_at\" AS \"created_at\", \"_0_root.base\".\"confirmed_at\" AS \"confirmed_at\"       ) AS \"_1_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"public_email_confirmation__mutation_result_alias\" WHERE ('true')     ) AS \"_0_root.base\"      ) AS \"_2_root\"      ) )        ",
          "prepared": true,
          "error": {
            "exec_status": "FatalError",
            "hint": null,
            "message": "record \"_new\" has no field \"updated_at\"",
            "status_code": "42703",
            "description": null
          },
          "arguments": [
            "(Oid 1184,Just (\"\\NUL\\STX8\\192u\\253\\166\\CAN\",Binary))",
            "(Oid 25,Just (\"eba4997c11915d9038f09d25013ec3c3\",Binary))",
            "(Oid 23,Just (\"\\NUL\\NUL\\NUL\\ETX\",Binary))"
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "postgres query error"
    }
  ]
}

For reference, this query is working:

query EmailConfirmationByTokenAndProject(
  $token: String!,
  $projectId: Int!
) {
  email_confirmation(
    where: {
      token: {_eq: $token},
      device: {user: {project_id: {_eq: $projectId}}}
    }) {
    email_confirmation_id
    created_at
    confirmed_at
    token
    device {
      user {
        project_id
      }
    }
  }
}

And returning this result:

{
  "data": {
    "email_confirmation": [
      {
        "email_confirmation_id": 44,
        "created_at": "2019-10-25T18:16:56.159911+00:00",
        "confirmed_at": null,
        "token": "eba4997c11915d9038f09d25013ec3c3",
        "device": {
          "user": {
            "project_id": 3
          }
        }
      }
    ]
  }
}

Is it possible for Hasura's metadata about column names to get out of sync with the actual state of the database? I don't currently have any columns on any tables with the name updated_at, though I had that column on a few tables recently. (I'm also using Hasura's migrations, so those may help to reproduce the issue.)

Please let me know if there is any additional debugging information I can provide.

[leoalves]

You probably have a trigger set in the update_at field.

Hasura show triggers in the console. Select the table you are having issues, click on modify and scroll down to see the triggers sections. I believe there will be one there that updates the update_at field on before insert.

[bitjson]

@leoalves that was it, thanks!

So it would seem Hasura doesn't delete associated triggers when you delete a column?

Looks like I created it automatically using the "Frequently used columns" feature: default: now() + trigger to set value on update. I suppose this is a one-off case for that particular default, but might be worth checking for it on column deletion. (Especially if future versions of Hasura make it easier to add triggers from the console.)

[leoalves]

No problem.

I don't think it's easy to find out the fields a trigger is changing.

In this specific case, since Hasura created the trigger, I guess they could
warn the user when deleting an updated_at field and the table has a trigger
with the same name they create with the frequently used columns.

I guess you can rename this issue to suggest that. Or create a new one.

cheers

On Fri., Oct. 25, 2019, 5:57 p.m. Jason Dreyzehner, <
[email protected]> wrote:

@leoalves https://github.com/leoalves that was it, thanks!

So it would seem Hasura doesn't delete associated triggers when you delete
a column?

Looks like I created it automatically using the "Frequently used columns"
feature: default: now() + trigger to set value on update. I suppose this
is a one-off case for that particular default, but might be worth checking
for it on column deletion. (Especially if future versions of Hasura make it
easier to add triggers from the console.)

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/hasura/graphql-engine/issues/3243?email_source=notifications&email_token=AA5QEBC4IVMVMGEPI5W3LB3QQNTTBA5CNFSM4JFJIUC2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOECJVKQY#issuecomment-546526531,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AA5QEBAKUNRLTGGTRVX6BXDQQNTTBANCNFSM4JFJIUCQ
.

[0x777]

@bitjson As @leoalves pointed out, it is not easy to figure out what columns a trigger is modifying. In fact, Postgres also does not throw an error when the column is dropped for the same reason. The only solution is track the columns involved in a trigger at graphql-engine's layer, when the console is creating the trigger. This is quite complicated as this 'state' has to be kept in sync as columns are renamed or if their types change. While this is a nice to have feature, it is not straightforward to implement this. The least we could do though is document this and make sure that it is searchable in our docs. cc @rikinsk

[pnappa]

Also occurs when changing the name of a column with the trigger attached. A lazy fix is to change back to the old version, then change the GraphQL field name to what you actually want it to be renamed to.

[chrisknu]

Also occurs when changing the name of a column with the trigger attached. A lazy fix is to change back to the old version, then change the GraphQL field name to what you actually want it to be renamed to.

This worked for me.. though it'd be nice to see related triggers updated when changing a field name.