Events can't be added to invocation logs due to missing ID

[sf-vio]

No events can added to the invocation logs. This means that all events are re-triggered every time the graphql engine is restarted. Error log shows that the events cannot be added to the invocation logs due to a null ID. Example log
{"type":"event-trigger","timestamp":"2020-02-25T09:10:25.557+0000","level":"error","detail":{"internal":{"statement":"\n INSERT INTO hdb_catalog.event_invocation_logs (event_id, status, request, response)\n VALUES ($1, $2, $3, $4)\n ","prepared":true,"error":{"exec_status":"FatalError","hint":null,"message":"null value in column "id" violates not-null constraint","status_code":"23502","description":"Failing row contains (null, c8fbcfca-e3a6-40d4-9d5b-524b1f642c8b, 200

[tirumaraiselvan]

@sf-vio What version of Postgres are you on? Is it hosted on Heroku?

Can you run the result of the following SQL:

SELECT column_name, column_default
FROM information_schema.columns
WHERE (table_schema, table_name) = ('hdb_catalog', 'event_invocation_logs')
ORDER BY ordinal_position;

[tiagob]

Same issue, this is what I see

postgres version

psql (12.2 (Debian 12.2-1.pgdg100+1))

column_name	column_default
id	NULL
event_id	NULL
status	NULL
request	NULL
response	NULL
created_at	now()

[tirumaraiselvan]

It appears that the column default is getting dropped. I am not sure how this happens.

The fix, till root cause is found, is to add the column default back:

ALTER TABLE hdb_catalog.event_invocation_logs ALTER COLUMN id SET DEFAULT gen_random_uuid()

[tiagob]

Thank you @tirumaraiselvan ! That fixes it.

I see what happened. I ran

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Then brought the container back up and the DEFAULT was gone. Is there a better way to wipe all the data? Is there a way to bring back these defaults after I've wiped the data like this? I'm concerned I may have missed something else.

Tested this with the latest docker image https://github.com/hasura/graphql-engine/tree/stable/install-manifests/docker-compose

[tirumaraiselvan]

@tiagob If you run DROP SCHEMA public , it will throw errors that gen_random_uuid() (a function that comes from pgcrypto extension) is being used in hdb_catalog tables. With cascade, the dependencies are dropped and the columns have no default.

Maybe instead of dropping schema, you can just drop all tables (function, views, etc) : https://stackoverflow.com/a/3327326/1911889

Also, created this issue to track a longer-term solution: #4217

[tiagob]

Very helpful. Thank you @tirumaraiselvan ! I love this product

[tirumaraiselvan]

A quick script to get the defaults back (on v1.3.2) :

BEGIN;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
ALTER TABLE hdb_catalog.hdb_version
  ALTER COLUMN hasura_uuid
  SET DEFAULT gen_random_uuid();
ALTER TABLE hdb_catalog.event_log
  ALTER COLUMN id
  SET DEFAULT gen_random_uuid();
ALTER TABLE hdb_catalog.event_invocation_logs
  ALTER COLUMN id
  SET DEFAULT gen_random_uuid();
ALTER TABLE hdb_catalog.hdb_action_log
  ALTER COLUMN id
  SET DEFAULT gen_random_uuid();
ALTER TABLE hdb_catalog.hdb_cron_events
  ALTER COLUMN id
  SET DEFAULT gen_random_uuid();
ALTER TABLE hdb_catalog.hdb_cron_event_invocation_logs
  ALTER COLUMN id
  SET DEFAULT gen_random_uuid();
ALTER TABLE hdb_catalog.hdb_scheduled_events
  ALTER COLUMN id
  SET DEFAULT gen_random_uuid();
ALTER TABLE hdb_catalog.hdb_scheduled_event_invocation_logs
  ALTER COLUMN id
  SET DEFAULT gen_random_uuid();
COMMIT;

[tirumaraiselvan]

Closed in #6085