Graphql-engine: Hasura Console "Relation does not exist"

[afreix]

I am running a Hasura locally using docker compose with a hasura container and a postgres container.

I am using migrations and making changes to my local container with hasura console. I am trying to run SQL in order to insert data into my database. Any query that references public.placeTypes gives an error along these lines:

{
    "statement": "SELECT * FROM public.placeTypes",
    "prepared": false,
    "error": {
        "exec_status": "FatalError",
        "hint": null,
        "message": "relation \"public.placetypes\" does not exist",
        "status_code": "42P01",
        "description": null
    },
    "arguments": []
}

The console shows the table public.placeTypes, and when I connect to the postgres container, I also see the table there as well.

I am able to insert data using the console, but not using SQL

I commented here since I was seeing some issues with my migrations: https://github.com/hasura/graphql-engine/issues/1211#issuecomment-541475896, but I don't know if that is at all related to this issue

[shahidhk]

@afreix I took a quick look. The table name is placeTypes and whenever you have capital letters in a relation name, you should use double quotes (") around it. Otherwise, Postgres just lower cases everything.

Try SELECT * FROM "public"."placeTypes"

[afreix]

Ahh I feel silly. Thank you so much for you help

[raitucarp]

I encounter this issue

[42P01] FatalError: relation "public.market" does not exist

• my table is case sensitive, and snake case
• I think maybe there are functions above before CREATE TABLE, that uses public.market as a relation in a query.
• I've tried to replace all public.market with "public"."market", and still encounter this error.

Do you have a suggestion to fix this migration error?
Maybe migration create should put all CREATE TABLE and all creation first before function.

The strategy is, split schema/table creation, functions, alterations, into separate files, and then merge all into 1 file.

[rikinsk]

@raitucarp Are you facing this issue while applying migrations? in that case the exact sql command
/ migration file causing the error would help debug further. Also, as there are no capital letters involved, public.market should behave the same as "public"."market" and hence this issue is most likely due to something else.

[raitucarp]

@rikinsk As I said before

I think maybe there are functions before CREATE TABLE, that uses public.market as a relation in a query.

My suggestion is hasura migrate create should grouping CREATE TABLE before any other command. ie: create function.

[rikinsk]

The hasura migrate create command uses pg_dump underneath. Seems like it might be an issue with it. cc: @scriptonist

[scriptonist]

@raitucarp to make sure we are on the same page,

• you put together the database using hasura console.
• try creating a "init" migration using hasura migrate create init --sql-from-server
• while creating the "init" migration you encounter the above-mentioned issue

Did I get it right?

[littlepea]

I've had the same issue, and manually moving functions to the bottom of the migration script helped

[yash2code]

@raitucarp to make sure we are on the same page,

• you put together the database using hasura console.
• try creating a "init" migration using hasura migrate create init --sql-from-server
• while creating the "init" migration you encounter the above-mentioned issue

Did I get it right?

not while creating it. The above issue pops up while applying it .

[eltonvsouza]

I am also having this issue. We could fix this issue if the dump generated by "hasura migrate create" came with the command:
SET check_function_bodies = FALSE;