INSERT trigger not running

[carlpaten]

I'm using the Docker images hasura/graphql-engine:v1.3.0 and postgres:11 (11.10). Ubuntu 20.10 clean install, containerd 1.3.7-0ubuntu3. I've reproduced this consistently on two separate Ubuntu boxes but I believe my colleague failed to reproduce it on his OS X box.

I have the following trigger configured on a table:

{
  "table": {"schema": "survey_monkey", "name": "webhook_callback"},
  "event_triggers": [
    {
      "name": "on_survey_monkey_webhook",
      "definition": {"enable_manual": true, "insert": {"columns": "*"}},
      "retry_conf": {"num_retries": 0, "interval_sec": 10, "timeout_sec": 60},
      "webhook_from_env": "FUNCTIONS_WEBHOOK_URL",
      "headers": [{"name": "Authorization", "value_from_env": "FUNCTIONS_AUTHORIZATION_HEADER"}]
    }
  ]
}

When inserting a new row in the table, this webhook doesn't run. Nothing shows up under Invocation Logs. However when I trigger it manually from the web console it works perfectly fine.

I apologize, this isn't a lot of detail. Does this ring a bell? If not, do you have any suggestions as to where to start digging?

Thanks in advance.

[tirumaraiselvan]

The manual trigger functionality does not rely on any PG activity and is hence slightly different in implementation (i.e. it skips few steps) so I wouldn't consider that important in resolving this bug.

Can you check for any functions in the hdb_views schema in your database. Via PSQL: df+ hdb_views.*. You should ideally see something like notify_hasura_on_survey_monkey_webhook_INSERT or something.

[carlpaten]

Hi, I've been procrastinating on this bug report, until yesterday it surfaced in far more troubling circumstances. Serves me right I guess.

My webhook was working, until it wasn't. Here's the bit in metadata.json:

    {
      "table": {"schema": "public", "name": "acquired_patient"},
      "object_relationships": [
        // ...
      ],
      "event_triggers": [
        {
          "name": "acquired_patient_sync",
          "definition": {"enable_manual": true, "update": {"columns": ["sync"]}},
          "retry_conf": {"num_retries": 0, "interval_sec": 10, "timeout_sec": 60},
          "webhook_from_env": "FUNCTIONS_WEBHOOK_URL",
          "headers": [{"name": "Authorization", "value_from_env": "FUNCTIONS_AUTHORIZATION_HEADER"}]
        }
      ]
    },

Relevant bit of /df+ output:

                                                                                                                                                                                   List of functions
  Schema   |                        Name                         | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |                                                                                Source code                                                                                | Description 
-----------+-----------------------------------------------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
 hdb_views | notify_hasura_acquired_patient_sync_UPDATE          | trigger          |                     | func | volatile   | unsafe   | postgres | invoker  |                   | plpgsql  |                                                                                                                                                                          +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |   DECLARE                                                                                                                                                                +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     _old record;                                                                                                                                                         +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     _new record;                                                                                                                                                         +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     _data json;                                                                                                                                                          +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |   BEGIN                                                                                                                                                                  +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     IF TG_OP = 'UPDATE' THEN                                                                                                                                             +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       _old := row((SELECT  "e"  FROM  (SELECT  OLD."sync"        ) AS "e"      ) );                                                                                      +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       _new := row((SELECT  "e"  FROM  (SELECT  NEW."sync"        ) AS "e"      ) );                                                                                      +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     ELSE                                                                                                                                                                 +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     /* initialize _old and _new with dummy values for INSERT and UPDATE events*/                                                                                         +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       _old := row((select 1));                                                                                                                                           +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       _new := row((select 1));                                                                                                                                           +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     END IF;                                                                                                                                                              +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     _data := json_build_object(                                                                                                                                          +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       'old', row_to_json(OLD ),                                                                                                                                          +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       'new', row_to_json(NEW )                                                                                                                                           +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     );                                                                                                                                                                   +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     BEGIN                                                                                                                                                                +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       IF (TG_OP <> 'UPDATE') OR (_old <> _new) THEN                                                                                                                      +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |         PERFORM hdb_catalog.insert_event_log(CAST(TG_TABLE_SCHEMA AS text), CAST(TG_TABLE_NAME AS text), CAST('acquired_patient_sync' AS text), TG_OP, _data);           +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       END IF;                                                                                                                                                            +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |       EXCEPTION WHEN undefined_function THEN                                                                                                                             +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |         IF (TG_OP <> 'UPDATE') OR (_old *<> _new) THEN                                                                                                                   +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |           PERFORM hdb_catalog.insert_event_log(CAST(TG_TABLE_SCHEMA AS text), CAST(TG_TABLE_NAME AS text), CAST('acquired_patient_sync' AS text), TG_OP, _data);         +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |         END IF;                                                                                                                                                          +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     END;                                                                                                                                                                 +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |                                                                                                                                                                          +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |     RETURN NULL;                                                                                                                                                         +| 
           |                                                     |                  |                     |      |            |          |          |          |                   |          |   END;                                                                                                                                                                   +| 

[toondaey]

Hi @carlpaten, were you able to find a solution for this?