Invalid graphql identifier in unique constraint name in tracked table prevents server from starting

[BenoitRanque]

Version Information

Server Version: 2.0.10

Environment

All

What is the expected behaviour?

Starting in 2.0, inconsistent metadata should no longer prevent server start. Instead, hasura will still start with reduced functionality depending on the metadata issue, allowing the user to use the console to diagnose and possibly solve the problem.

This can be seen when for example a table has been dropped since last server start, causing inconsistent metadata. The user will still be able to start the server, and be shown a list of inconsistent metadata object, with the ability to delete such metadata objects to solve the problem.

This bug report details an issue that should be a metadata problem only, but instead prevents server start altogether. This will soft lock the user out of their hasura instance until they access their database to fix the problem. This is especially concerning on hasura cloud.

What is the current behaviour?

Server start fails when the following conditions are met:

1. At table is tracked and thus included in hasura metadata
2. This table has a unique constraint with a custom name that is not a valid graphql identifier

The issue won't present itself if the table is not tracked, and the user won't be able to track such a table. Therefore the constraint must be added after the table has already been tracked, or the metadata must be written manually.

This can happen when using third party tools to manage database schema.

How to reproduce the issue?

1. create a table by running the following SQL, or the equivalent in the console UI:

   CREATE TABLE test (id INT);

2. Track the table (not required if you used the console UI for step 1)
3. Run the following SQL (warning: this will softlock you out of your instance once the server restarts)

   -- note "invalid identifier" is an invalid graphql identifier because it contains a space
   ALTER TABLE test ADD CONSTRAINT "invalid identifier" UNIQUE (id)

4. If you run this in the console and attempt to track the change you will get a warning, but things will otherwise keep working
5. Restart the instance. Startup will fail.
6. To restore access to the instance use a third party tool to run the following SQL and restart the server

   ALTER TABLE test DROP CONSTRAINT "invalid identifier"

Screenshots or Screencast

The console ui tries really hard to prevent you from doing this. Here is the tooltip you get when attempting to track a table with an invalid identifier name, things work as expected:

And here is the error when you try to track a table that has a unique constraint with an invalid identifier name:

Keywords

Invalid graphql identifier

Please provide any traces or logs that could help here.

The failed startup generates a single relevant log line (note "invalid identifier" is literally the name of the constrain in this case):

{
  "path": "$",
  "error": "cannot include \"invalid identifier\" in the GraphQL schema because  it is not a valid GraphQL identifier",
  "code": "validation-failed"
}

Any possible solutions?

Currently it is possible to work around this by connecting to the database directly using a third party tool, and either dropping or renaming the offending constraints before attempting to restart the hasura server. If using hasura cloud, you can trigger a restart by modifying environment variables.

[BenoitRanque]

Users attempting to migrate existing databases to hasura may encounter this issue. Currently the best way around this is to identify and rename all offending constraints.

This query can help in identifying such constraint names. The regex is based on the graphl specification for valid names

SELECT con.*
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel
    ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp
    ON nsp.oid = connamespace
WHERE con.conname !~ '^[_A-Za-z][_0-9A-Za-z]*$';

Users would then need to rename the constraints.

A similar query can be used to look for any non-compliant column names:

SELECT *
FROM information_schema.columns
WHERE column_name !~ '^[_A-Za-z][_0-9A-Za-z]*$';

Similarly, these columns would need to be renamed.

[BenoitRanque]

Important note that the workaround above is just that: a workaround. We cannot assume that all users will have the ability to modify their database schema, case in point.

[pscanlon1]

bump. I was having this issue today because DBT names my index with a number to start. So the whole server cant start? The table is only for select queries, so im not sure why this causes a complete meltdown