Graphql-engine: "Error function gen_random_uuid() does not exist"

[andretorres00123]

Getting "Error function gen_random_uuid() does not exist" when an event trigger on a table of another shema than the "public" is trigger.

[shakedlokits]

Just got it on initialization as well. It seems it doesn't relate to event-triggers only :(

"error":{
    "exec_status":"FatalError",
    "hint":"No function matches the given name and argument types. You might need to add explicit type casts.",
    "message":"function gen_random_uuid() does not exist",
    "status_code":"42883",
    "description":null
}

Running latest version of engine on docker-compose.

[tirumaraiselvan]

@andretorres00123 I am not able to reproduce this. Can you share more details about your database setup?

• Does it have a public schema?
• What is the default search_path? Run select current_schemas(true) from the SQL section in Hasura console.
• Can you confirm pgcrypto is installed? Run select * from pg_available_extensions where installed_version is not null

[shakedlokits]

@tirumaraiselvan This might not be relevant to @andretorres00123 but here's what I got;
Hasura keeps failing in initialization so I can't get to the Hasura console so I'm running everything through pdAdmin container.

1. There is not public schema
2. returns {pg_catalog}
3. returns only plpgsql, v1.0

EDIT
Just solved it by clearing all containers, removing all orphans, deleted images, removed volumes and then re-ran everything, this must've been a caching issue, should probably give the DB a good purge if you get fatal errors in the future, and for sure - not crash 🚀

TL;DR - I ran this and solved it..

docker-compose down --remove-orphans -v
docker-compose rm -v

docker volume prune
docker ps -aq | xargs -I {} docker stop {} | xargs -I {} docker rm {}
docker images -aq | xargs -I {} docker rmi -f {}

docker-compose up

[tirumaraiselvan]

@shakedlokits Great that it worked. Yeah, it is weird that there were no schemas at all. Anyway, I think we should raise a better error here.

[tirumaraiselvan]

@andretorres00123 Any more info you could provide? The guess is that this is a problem with the state of the database. So if you could answer the questions here it would help pin-point: https://github.com/hasura/graphql-engine/issues/2764#issuecomment-524626969

[tirumaraiselvan]

Closing due to inactivity.

[reinoldus]

I hit the same issue today. I solved it by recreating the pgcrypto extension:

https://dba.stackexchange.com/questions/146135/psql-9-5-gen-random-uuid-not-working

[timhere]

I experienced this issue upgrading my database from Postgres 11.8 to 12.3. I solved it by using public in my search path, e.g:

SELECT pg_catalog.set_config('search_path', 'public', false);

The value for search path created by pgdump was empty.

More detail, in case it helps someone else:
I made backup of my database created using pgdump and used the hasura cli to manage the migration.

The schema restored fine, however attempting to execute the sql failed due to gen_random_id() (pgcrypto extension) being missing.

Here was my error message:

ERROR:  function gen_random_uuid() does not exist
LINE 1: SELECT gen_random_uuid()
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT gen_random_uuid()
CONTEXT:  PL/pgSQL function hdb_catalog.insert_event_log(text,text,text,text,json) line 8 at assignment
SQL statement "SELECT hdb_catalog.insert_event_log(CAST(TG_TABLE_SCHEMA AS text), CAST(TG_TABLE_NAME AS text), CAST('notify_session_uploaded' AS text), TG_OP, _data)"
PL/pgSQL function hdb_views."notify_hasura_notify_session_uploaded_INSERT"() line 25 at PERFORM

[FickleLife]

@timhere I've just encountered your exact issue.

I manually changed SELECT pg_catalog.set_config('search_path', '', false); to SELECT pg_catalog.set_config('search_path', 'public', false); but am still getting the same error.

I also attempted to drop extension pgcrypto as suggested in @reinoldus comment above but had too many [2BP01] ERROR: cannot drop extension pgcrypto because other objects depend on it. I then recreated a new DB, ran drop extension pgcrypto and reinstalled it but same error on the restore of the pg_dump file (using command line, not hasura migraiton tooling)

Not sure where to go from here.... running SELECT gen_random_uuid() returns a UUID so that makes the the error all the more puzzling.

[Uthpala]

I am getting the same error. For me this happens when i create an event for a table. I previously had another event that worked. But now it is not working as well.