remove direct use of gen_random_uuid to avoid dropping column default

[tirumaraiselvan]

Hasura installs pgcrypto during initialization. This provides the function gen_random_uuid() which is used in many hdb_catalog tables as column DEFAULT.

Now, pgcrypto components are installed in public schema (although extensions itself are db wide). The reason for this is mentioned here: #3657 (comment)

pgcrypto provides gen_random_uuid() which can be used as a default for uuid columns. Some of graphql-engine's uuid columns use this function. So why not install this extension into hdb_catalog schema? Currently in Postgres, an extension can only be installed once and only into a single schema, so if the extension is installed into hdb_catalog schema, you'll have to depend on hdb_catalog schema if you were to use gen_random_uuid or any of the pgcrypto functions in your own schemas. You shouldn't have such a dependency as it wouldn't be easy to clean your database of any graphql-engine related changes. Hence, pgcrypto extension is installed in public schema.

The problem is that people might want to do DROP SCHEMA public. This will throw dependency error that gen_random_uuid() is being used by other tables but this could be overridden with DROP SCHEMA public CASCADE. The effect of that is the column DEFAULT is dropped which leads to issues like #4009

One way to workaround is this is to create a UDF like so:

CREATE FUNCTION hdb_catalog.hasura_uuid() RETURNS uuid AS 
  'select gen_random_uuid()' LANGUAGE SQL;

Since, this only indirectly uses gen_random_uuid(), the column default is not dropped if pgcrypto or public schema is dropped. See https://www.postgresql.org/docs/9.1/ddl-depend.html

We should migrate all uses of gen_random_uuid() to hasura_catalog.hasura_uuid()

[tirumaraiselvan]

Closed via: #6085