Repository: hasura/graphql-engine
File: server/src-rsr/initialise.sql (SQL)

Code before:
/* We define our own uuid generator function that uses gen_random_uuid() underneath.
Since the column default is not directly referencing gen_random_uuid(),
it prevents the column default to be dropped when pgcrypto or public schema is dropped unwittingly.

See https://github.com/hasura/graphql-engine/issues/4217
*/
CREATE FUNCTION hdb_catalog.gen_hasura_uuid() RETURNS uuid AS
'select gen_random_uuid()' LANGUAGE SQL STABLE;

CREATE TABLE hdb_catalog.hdb_version (
    hasura_uuid UUID PRIMARY KEY DEFAULT hdb_catalog.gen_hasura_uuid(),
    version TEXT NOT NULL,
    upgraded_on TIMESTAMPTZ NOT NULL,
    cli_state JSONB NOT NULL DEFAULT '{}'::jsonb,
    console_state JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE UNIQUE INDEX hdb_version_one_row
ON hdb_catalog.hdb_version((version IS NOT NULL));

/* Note [Reference system columns using type name]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
While working on #3394, I (Alexis) discovered that Postgres seems to sometimes generate very bad
query plans when joining against the system catalogs if we store things like table/schema names
using type `text` rather than type `name`, the latter of which is used internally. The two types are
compatible in the sense that Postgres will willingly widen `name` to `type` automatically, but
`name`s are restricted to 64 bytes.

Using `name` for ordinary user data would be a deep sin, but using it to store references to actual
Postgres identifiers makes a lot of sense, so using `name` in those places is alright. And by doing
so, we make Postgres much more likely to take advantage of certain indexes that can significantly
improve query performance. */

CREATE TABLE hdb_catalog.hdb_table
(

Review comment (suggestion):
Ideally it has to be `select <schema-where-pgcrypto-is-installed>.gen_random_uuid()`. We don't do it on master but it'll be great if it is added as a TODO comment in the code. 

Code after:
/* We define our own uuid generator function that uses gen_random_uuid() underneath.
Since the column default is not directly referencing gen_random_uuid(),
it prevents the column default to be dropped when pgcrypto or public schema is dropped unwittingly.

See https://github.com/hasura/graphql-engine/issues/4217
*/
CREATE FUNCTION hdb_catalog.gen_hasura_uuid() RETURNS uuid AS
-- We assume gen_random_uuid() is available in the search_path.
-- This may not be true but we can't do much till https://github.com/hasura/graphql-engine/issues/3657
'select gen_random_uuid()' LANGUAGE SQL;

CREATE TABLE hdb_catalog.hdb_version (
    hasura_uuid UUID PRIMARY KEY DEFAULT hdb_catalog.gen_hasura_uuid(),
    version TEXT NOT NULL,
    upgraded_on TIMESTAMPTZ NOT NULL,
    cli_state JSONB NOT NULL DEFAULT '{}'::jsonb,
    console_state JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE UNIQUE INDEX hdb_version_one_row
ON hdb_catalog.hdb_version((version IS NOT NULL));

/* Note [Reference system columns using type name]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
While working on #3394, I (Alexis) discovered that Postgres seems to sometimes generate very bad
query plans when joining against the system catalogs if we store things like table/schema names
using type `text` rather than type `name`, the latter of which is used internally. The two types are
compatible in the sense that Postgres will willingly widen `name` to `type` automatically, but
`name`s are restricted to 64 bytes.

Using `name` for ordinary user data would be a deep sin, but using it to store references to actual
Postgres identifiers makes a lot of sense, so using `name` in those places is alright. And by doing
so, we make Postgres much more likely to take advantage of certain indexes that can significantly
improve query performance. */